Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Dave,

I am so glad you have it working. I know the feeling of relief when you find a solution. 

Jim Wagner

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Tuesday, February 23, 2021 10:32 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

 

Jim,

Thanks. I had to do a bit more to stop Text15 ending up with a flashing cursor. My code now looks like:

Private Sub List11_AfterUpdate()
Me.Painting = False
Text13.Requery
Text15.SetFocus
DoCmd.FindRecord Text13, acEntire, False, acSearchAll, True, acCurrent, True
List11.SetFocus
Me.Painting = True
End Sub

I was a bit concerned that as both text boxes are still enabled, they could be selected by the cursor, but strangely this is not the case.

Dave W

On 23/02/2021 12:51, Jim Wagner wrote:

Dave

If you do not need to see the controls. Make the background transparent and the font color the same color as the form. That way it is there but not visible to the eye

Jim Wagner

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Tuesday, February 23, 2021 4:17 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

 

Paul,
Thanks for that - it could be useful, but I've found a solution now. I added another text box (called Text15) to the form, with its data source as the ID field of the table to which the form is bound. My code for List11 is now:

Text13.Requery
Text15.SetFocus
DoCmd.FindRecord Text13, acEntire, False, acSearchAll, True, acCurrent, True

The only trouble is that I can't make Text13 invisible because then it can't be found, and I can't make Text15 invisible because then it can't take focus.
Text13 data source is =DLookUp("[1st photo ID]","[FirstPhotoOfYear]","[Year]=[List11]")

Dave

On 23/02/2021 01:34, Paul Baldy wrote:

The code below can easily be adapted to work on the same form instead of opening another.  You can also try the combo box wizard and choose the third option, "Find a record...".  You'll only see that option if the form is bound to a table though, if memory serves.  The wizard will use a bookmark as well, or at least it used to.

http://www.baldyweb.com/Bookmark.htm

Paul

------ Original Message ------

From: "Dave Williams" <davewillgmale@gmail.com>

To: MSAccessProfessionals@groups.io

Sent: 2/22/2021 4:01:09 PM

Subject: Re: [MSAccessProfessionals] How to make a form go to a record with a particular ID

Jim,

That looks very simple but I don't see the relevance to my problem. I don't want to open another form, which would open at its first record. I just want to change the record already being displayed by my form.

Regards,
Dave

On 22/02/2021 22:36, Jim Wagner wrote:

Dave,

I created this solution a long time ago but it works. 

I have a form with mini subforms that break out tasks sort of like a card in task apps. when i click on the TaskId in one of the suborms a macro is executied

If IsNull([TaskID])

Beep

End If

If Not IsNull([TaskID])

OpenForm

form Name "name of the form goes here"

view  Form

Where Condition  =="[TaskID]=" & [TaskID]

 windows mode Normal

End if

OnError

Go to Next

Macro Name

Requery

Control Name

hope that gives you some ideas or an approach

good luck

Jim Wagner

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Dave Williams <davewillgmale@gmail.com>
Sent: Monday, February 22, 2021 2:44 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] How to make a form go to a record with a particular ID

 

I have created a form to display photos from various folders on my PC. The record source is a table as follows:
Name: Photos
Field Name, Data Type
ImageID, Autonumber
Folder, Text
Photo, Text
It contains 100 records e.g.
38, 14/07/2018, 13880718, PICT0001.JPG

This works fine when using the normal navigation buttons, but as there are so many records I have added a list box containing the years, each with the appropriate starting record ID, contained in a table as follows:

Name: FirstPhotoOfYear
Field Name, Data Type
Year, Text
1st photo ID, Number

5 records e.g.
2018, 38

So when I select 2018 in the list box, I want to make the form show the record with ID = 38. I thought it might help to add a text box (called Text13) on the form that shows the ID number produced by the list box (called List11). This shows the ID fine, using a suitable expression as its control source.
In code which operates when the list box is updated, I first use Text13.Requery, followed by DoCmd.FindRecord but without success. I don't know how to make it search just the ID field, or even if it's found anything, as the form does not change to a new record.

I await suggestions.
.