Passing values to a form on lookup

[Stretch617]

I'm using the entry of a drop down on a form to select the record on another form using a select button, but I can't seem to workout where I'm going wrong with my code:

Private Sub btnSelect_Click()

DoCmd.OpenForm "frmcase", acNormal, "test", "where '" & Form_frmCase.txtCaseNum & "' =" & Me.ddCaseNo.Value

End Sub

Please see attached. Many thanks

 

[namliam]

the "Where" is automatic...

Also you want to refer to the field on the form ...

Your where clause would look something like
"txtCaseNum =" & Me.ddCaseNo.Value

Good luck

 

[Stretch617]

I've now got:

DoCmd.OpenForm "frmcase", acNormal, , "txtCaseNum =" & Me.ddCaseNo.Value & Form_frmCase.txtCaseNum & "' =" & Me.ddCaseNo.Value

But am getting the attached error msg, any ideas why? Sorry I'm not very competent with the code side of things.

 

[JANR]

Change your code to:

Private Sub btnSelect_Click()
    
    'look up passing values to a form on lookup
    DoCmd.OpenForm "frmcase", acNormal, , "CaseNum =" & Me.ddCaseNo.Value
End Sub

JR

 

[hiền muội]

JANR 's code is correct
You can't use Form_frmCase.txtCaseNum = & Me.ddCaseNo.Value when you try to open frmCase

 

[namliam]

DoCmd.OpenForm "frmcase", acNormal, , "txtCaseNum =" & Me.ddCaseNo.Value & " and txtCaseNum = " & Me.ddCaseNo.Value

This is assuming the "NUM"s are actually numbers, if they are strings you have to enclose the values by ' like so:
DoCmd.OpenForm "frmcase", acNormal, , "txtCaseNum ='" & Me.ddCaseNo.Value & "' and txtCaseNum = '" & Me.ddCaseNo.Value &"'"

Or for dates:
DoCmd.OpenForm "frmcase", acNormal, , "txtCaseNum =#" & Me.ddCaseNo.Value & "# and txtCaseNum = #" & Me.ddCaseNo.Value &"#"

Good luck !

 

[JANR]

Namlian's examples is a good template, just change txtCaseNum too CaseNum since that's the actual name of the contol in frmCase!!

JR

 

[Stretch617]

Thanks alot guys, that got that working. How would I modify the code so that if there was no value entered into the drop down list, if it were left blank, it would open up the first record?

 

[JANR]

How about just open the form in the else statement in an if-else.

Private Sub btnSelect_Click()
    If Not IsNull(Me!ddCaseNo) Then
    'look up passing values to a form on lookup
        DoCmd.OpenForm "frmcase", acNormal, , "CaseNum =" & Me.ddCaseNo.Value
    Else
        DoCmd.OpenForm "frmcase", acNormal
    End If
End Sub

JR

 

[Stretch617]

Thanks JR, that's spot on.