Error 3077 Code isn't handling double click in blank field

[traycee]

Hi All!

I have the following code in the on double click event:

Private Sub cboPhyLocation_DblClick(Cancel As Integer)
Dim frm As Form, rst As Recordset
DoCmd.OpenForm "Contact Info"
DoEvents
Set frm = Forms![Contact Info]
Set rst = frm.RecordsetClone
If rst.BOF Then
MsgBox "No Records!"
Else
rst.FindFirst "[ContactID] = " & Me!PhysicalLocation & ""
If rst.NoMatch Then
MsgBox "Record Not Found!"
Else
frm.Bookmark = rst.Bookmark
End If
End If
Set frm = Nothing
Set rst = Nothing
End Sub

If user double clicks the field, the contact form opens correctly to the matching record. If user double clicks the field and it is blank, I would like it to either say "No Records" or open the form so they can add a new record. Either would be fine. But instead I get Error 3077 and the rst.FindFirst "[ContactID] = & Me!PhysicalLocation & "" line.

Thanks

 

[PHV]

Replace this:
rst.FindFirst "[ContactID] = " & Me!PhysicalLocation & ""
with this
rst.FindFirst "[ContactID] = " & Nz(Me!PhysicalLocation, 0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[traycee]

Thanks so much. I was sure I'd have some user(s) clicking on the blank field and didn't want them to get the debug window.

 

[missinglinq]

PH is assuming (as I would have) that ContactID and PhysicalLocation are defined as Numbers, as opposed to being defined as Text, and gave you the correct syntax for Numbers.

But, FYI, the code you posted

rst.FindFirst "[ContactID] = " & Me!PhysicalLocation & ""

is not correct for any Datatype. If these fields had been defined as Text, the line should have been

rst.FindFirst "[ContactID] = '" & Me!PhysicalLocation & "'"


The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[PHV]

is not correct for any Datatype
Really ?
Even for number ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[traycee]

ContactID and PhysicalLocation are both defined as numbers in my database. PHVs code did solve my problem. If someone with text is having the same issue, they can now find the answer on this tek-tip, too.