open form and jump to a record

[music_al]

I have 2 tables which populate 2 different forms

tbl_Work_Request which populates frm_Work_Request

and tbl_Project which popuates frm_Project

I have a list box on the Work Request form which when double-clicked, takes the value that was double-clicked and passes it to the code below.

Dim ClickedProj As String
ClickedProj = list_Project_ID.Value

DoCmd.OpenForm "frm_Project", , , "[Project_ID]=" & ClickedProj


This almost works fine, but the form is opening with only one record, but I want to open ALL records but jump to the record that I want. (the passed value is the primary key)

 

[John Big Booty]

You are opening the form in a filtered mode by using the where condition "[Project_ID]=" & ClickedProj. Remove this from your DoCmd.OpenForm and then on your next code line use DoCmd.FindRecord to find the record you wish to go to.

 

[boblarson]

Here's even another way to do it (courtesy of our own PBaldy).

 

[harmankardon]

Anyone know a way of implementing either of the two solutions posted without incurring the slow down caused from pulling the whole recordset in?

 

[boblarson]

Anyone know a way of implementing either of the two solutions posted without incurring the slow down caused from pulling the whole recordset in?

If you don't want the whole recordset then you would have to use the original method that music_al had where you open the form with a where condition specified. The drawback is that it will open with just that single record and you won't be able to navigate back and forth through other records with it. But it is either that or load the whole set of data.

 

[harmankardon]

If you don't want the whole recordset then you would have to use the original method that music_al had where you open the form with a where condition specified. The drawback is that it will open with just that single record and you won't be able to navigate back and forth through other records with it. But it is either that or load the whole set of data.

I figured as much. Thanks for the prompt reply Bob, much appreciated.