open another form with matching field

[Guest]

Hi everyone,
I've tried to open a form (frmReidProductDrawings) with matching field
(Initials) to a particular field (Originator) in the existing form
(frmStaffList) after clicking a command button. My trial code below opens
frmStaffList alright, but it doesn't go to the record with matching fields.
Can someone please help?

--- start of code ---
Private Sub cbStaffListOpen1_Click()
' Open frmStaffList in pop-up with record which has Initials value
' matches with Originator in frmReidProductDrawings
DoCmd.OpenForm "frmStaffList", , , Initials =
Forms!frmReidProductDrawings!Originator
End Sub
--- end of code ---

 

[Wayne Phillips]

Hi Sam,

You need to specify a VB string for the WhereCondition parameter of
DoCmd.OpenForm - at the moment you are passing the result of an expression.

Try this...

--- start of code ---
Private Sub cbStaffListOpen1_Click()
' Open frmStaffList in pop-up with record which has Initials value
' matches with Originator in frmReidProductDrawings
DoCmd.OpenForm "frmStaffList", , , _
"Initials = " & Forms!frmReidProductDrawings!Originator
End Sub
--- end of code ---

Regards,

Wayne Phillips
http://www.everythingaccess.com/forums

 

[Penguin]

Try this:

DoCmd.OpenForm "frmStaffList",,,"[Initials] =
Forms!frmReidProductDrawings![Originator]"

Above code is all in one line.
Hope this helps.

 

[Wayne Phillips]

Just to clarify....

If the field 'Initials' is a string, then use this code:

DoCmd.OpenForm "frmStaffList", , , _
"Initials = '" & Forms!frmReidProductDrawings!Originator &
"'"

However if the field 'Initials' is a number, then use this code...

DoCmd.OpenForm "frmStaffList", , , _
"Initials = " & Forms!frmReidProductDrawings!Originator

Try to avoid this way (which another user has just posted):
DoCmd.OpenForm "frmStaffList", , , _
"Initials = Forms!frmReidProductDrawings!Originator"
-> It will work, however your passing a dynamic where clause to the new form
which is not needed.

Regards,

Wayne Phillips
http://www.everythingaccess.com

 

[Guest]

Thanks so much Wayne and Penguin,
That's very clear and helpful.

Regards,
Sam