Auto Search REF number with lookup? (1 Viewer)

[stu_c]

Hi all
I have a Field called
AccountNumber on my form, when a user types the number I want it to automatically look up to see if it is currently on the system, if its a new number then carry on with the form, if there is already the same number present I want a popup message to say "this number is already in use, do you wish to go to this record" [YES] [NO]

I wouldn't even know where to start

 

[theDBguy]

Hi. For starters, you could use the AfterUpdate event, if you want to do the check "after" the user finished entering the entire number. Otherwise, you can use the Change event to check after every character is entered. For this case, you can use a couple of approaches. You can use the DCount() function or a RecordsetClone and the FindFirst method.

 

[stu_c]

would be better once the whole number is entered I think,

Hi. For starters, you could use the AfterUpdate event, if you want to do the check "after" the user finished entering the entire number. Otherwise, you can use the Change event to check after every character is entered. For this case, you can use a couple of approaches. You can use the DCount() function or a RecordsetClone and the FindFirst method.

 

[HalloweenWeed]

Like this:

Private Sub TextBox_BeforeUpdate(Cancel As Integer)

If Dcount("*","Tables![yourTableName]","[Field]=TextBox") <> 0 Then
   Cancel = True
   MsgBox "Value exists, try another..."
End If
Exit Sub
End Sub

Assuming your textbox is formatted to the same type variant as [Field] (otherwise you may need to add conversion).