Open Form if record does not exsist

[TomProff]

Hi all,

I'm relatively new to this, so this might be a silly question.

I have one table (Data Table). I have a form that it used to input some of the data into the table, in this form is an ID number. I need it so that when a user inputs an ID number into this form, it checks whether or not that number is already in the database. If it is already in the table then i want another form to open automatically (lets call it preview form) which contains the information from the table. If the ID number is not already in the table then I don't want the form to do anything (so that the data related to that ID number can be entered).

Is this possible?

Thank you in advance

All the best

Tom

 

[Addyman]

On the AfterUpdate event of the text box where the user enters the ID you can do a DLookup to check if it exists:

If DLookup("ID","YourTableName","ID = " & Me.YourTextBoxName) = True Then

DoCmd.OpenForm "YourOtherFormName"

'Enter any other code you need here

End If

Please note, I have assumed your ID is numeric but if it is not then you need to add single quotes in where relevant to the DLookup above.

 

[TomProff]

Thank you, the problem Im having is that the after update event is not firing.
It works if i get it to open a MsgBox but as soon as any other code is in there nothing happens.

 

[Addyman]

Could you maybe post the code that isn't working or doesn't appear to be firing and we can take a look?

 

[TomProff]

No Worries

Private Sub BarcodeMuseum_AfterUpdate()
If DLookup("Barcode", "Data_Artifact", "Barcode = " & Me.BarcodeMuseum) = True Then

DoCmd.OpenForm "Data_Artifact preview"
End Sub