look up a value on a form

[phill86]

Hi,

I have two forms with the the same ID field (ProjID) in each

Form1:
[Forms]![F_ClientDetails]![SF_Session].[Form]![ProjID]

Form2:
[Forms]![F_Project]![ProjID]

I have Form 1 open and i want to be able to open form two and navigate automatically to the relevant record determined by the ProjID field in form 1

So for example form 1 will have the ProjID fiels value as 10 when i open form2 i want it to automatically find the record that has 10 as the ProjID value.

hope this makes sense

please help

Regards Phill

[phill86]

Hi,

Its ok i found the answer

DoCmd.OpenForm "F_artistproj", , , "[ProjBandID] = " & Me.Combo101

Thanks anyway

[missinglinq]

Make note, Phill, for future reference, that your syntax is only good when the ID field is a Numeric field. Here's the general syntax for Numeric, Text and Date fields.


If ProjectID is Numeric DataType:

DoCmd.OpenForm "Form2", , , "ProjectID = " & Me.ProjectID

If ProjectID is Text DataType:

 DoCmd.OpenForm "Form2", , , "ProjectID = '" & Me.ProjectID & "'"

If ProjectID is Date/Time Datatype:

 DoCmd.OpenForm "Form2", , , "ProjectDate = #" & Me.ProjectDate & "#"

Linq ;0)>

[NeoPa]

Am I right to guess that you are actually talking about A being a subform of B in this case?

This completely changes the question of course (such that your solution, though it may work, would be a kludgy work-around rather than a good solution).

[phill86]

Hi Linq

Thanks for that, it has solved the problem to an extent....

I am able to open the relevant record in the 2nd form but for some reason it only displays that record.

So for example i have three records in the underlying table
Record 1
Record 2
Record 3

I want to be automatically taken to record 2 but i still want to have the option to be able to scroll through all three records at the moment i can only see record that is being displayed in form 1 even though there are three records in the underlying table.

Regards Phill

[missinglinq]

Well, Phill, you really need to state your complete needs in your initial post, so that people don't waste time giving you answers that won't work for you. To do this you need to use code like this, using the OpenArgs argument of the DoCmd.OpenForm command:

In your calling form:

DoCmd.OpenForm "Form2", , , , , , Me.ProjectID

Then, in Form2, where ProjectID holds the field you're using:

Private Sub Form_Load()
     If Len(Nz(Me.OpenArgs, "")) > 0 Then
       ProjectID.SetFocus
       DoCmd.FindRecord Me.OpenArgs
   End If
End Sub

Linq ;0)>