Go to existing record after search

[tscotti]

Hello All,

Below is the code that I use for searching an existing record in a form after entering the "ProductNo":

Private Sub ProductNo_BeforeUpdate(Cancel As Integer)
Dim varX As Variant
varX = DLookup("[ProductNo]", "tbLProducts", "[ProductNo] = '" & Forms!frmProducts.[ProductNo] & "'")
If Not IsNull(varX) Then
MsgBox [ProductNo].Value & " already exists as a product number"
Me.Undo
Cancel = True

Else
'do nothing!
End If
End Sub
​

My question is...After the message, how can I have Access go to the existing record in the same form?

Thanks, in advance, for responding.

Tony

 

[MarkK]

Not sure which form you mean by "same form", but one of these might do...

Me.Recordset.FindFirst "ProductNo = " & varX
...or...
Forms("frmProducts").Recordset.FindFirst "ProductNo = " & varX

 

[tscotti]

Lagbolt,

Thanks for replying.

I'm getting a "data type mismatch" on both of your expressions.

Any ideas why?

Thanks.

Tony

 

[MarkK]

Your ProductNo is a string.

Me.Recordset.FindFirst "ProductNo = '" & varX & "'"

Cheers,
Mark

 

[tscotti]

It works! Great!

Thanks lagbolt!