Jumping to a particular existing record in VBA

[RCurtin]

Hi,
I have a table called 'Drawings register' that stores the names of technical drawings, the drawing numbers and their revision numbers.

The idea is that the user clicks on the Add new drawing button on the form, enters the drawing number in the first text box and the revision number in the second text box. Then what I would like is to check if that drawing with that revision aleady exists.

I finally got the code working so that it checks if a drawing number with a particular revision number exists but now I want to jump to that record if it does exist. Otherwise the user continues to add a new drawing.

How do I jump to a specific record in VBA? Is this possible to do in VBA or is there a better way of doing it?

Dim drawingNum As Variant

    'checks if drawing num and revision number entered in the form already exists in the drawings table
    
    drawingNum = DLookup("[Drawing num]", "Drawings register", "[Drawing num]=Forms!frmAddDrawings![txtDrawing] And [Rev]=Forms!frmAddDrawings![txtRev]")
     

   If Not IsNull(drawingNum) Then
      Beep
      MsgBox "That drawing number and revision already exists", vbOKOnly, "Duplicate Value"
      Cancel = True
   End If

 

[RuralGuy]

Something like the following:

Me.RecordsetClone.FindFirst "[Drawing num]= " & Forms!frmAddDrawings![txtDrawing] & " And [Rev] = '" & Forms!frmAddDrawings![txtRev] & "'"

If Not Me.RecordsetClone.NoMatch Then
    Beep
    MsgBox "That drawing number and revision already exists", vbOKOnly, "Duplicate Value"
    Me.UnDo
    Cancel = True
    Me.Bookmark = Me.RecordsetClone.Bookmark
End If

But I'm not sure where you have the current code. What event?

 

[RCurtin]

Thankk a million for that RG - it works! Took me a little while to figure out the syntax - I had to put single quotes around the reference to the textbox because drawing number is a text field. This example explains that:http://www.blueclaw-db.com/access_findfirst.htm

 Me.RecordsetClone.FindFirst "[Drawing num]= '" & Forms!frmAddDrawings![txtDrawing] & "' And [Rev] = '" & Forms!frmAddDrawings![txtRev] & "'"

Does anyone know where I could find a tutorial explaining VBA syntax - I don't fully understand when you need to use double quotes and this symbol: &

Thanks again,
RCurtin.

 

[RuralGuy]

& is the concantenation character and double quotes delimit strings. Glad you got it working.