Form Question Regarding Primary Key Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've created a simple form from a table where the primary key field holds
serial numbers. This field is the 3rd or 4th field of over 20 entries
necessary on the form. Right now, my form doesn't notify the user until the
entire data entry for the record is completed that a duplication of a serial
number has occurred. Is there any way to give the user immediate
notification that they've enterd a duplicate serial number as soon as they
enter it and move to the next field?
 
Use the AfterUpdate event of the control to DLookup() the value in the
table, or use the control's BeforeUpdate if you want to cancel the event and
keep the user in the control until a valid entry is made.

This kind of thing:

Private Sub ID_AfterUpate()
Dim strWhere As String
Dim varResult As Variant
If IsNull(Me.ID) or Me.ID = Me.ID.OldValue Then
'do nothing
Else
strWhere = "ID = " & Me.ID
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
MsgBox "Dupe!"
End If
End If
End Sub

If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html
 
Have you try the DLookup function? You can put it in the LostFocus of your
Serial Number control.

If Dlookup return Null (Dlookup("FieldToReturn","Domain","Where condition"),
the serial does not exist. You can then stop processing and advise the user
with a message Box.

Yanick.
 
Back
Top