record doesn't exist

[slimjen]

All, using access 2010. I have a data entry form. right now I have it on load to new record. I have an unbound field based on the cust# field from a table. When the user enters a cust number; the corresponding record comes up. But, if the number isn't in the table, nothing happens and the data for the current record exist. I need to write some code where as if a user enters a cust# that doesn't exitst; the form goes blank letting them know that number is not in the table to prevent overwriting data of the current record. Thanks

[burrina]

Why not use a simple Search Combo?

[Missinglinq]

I agree with Dave...Record retrieval using a Combobox (the Combobox Wizard will walk you through process) means that the user will realize that the customer number doesn't exist, when AutoExpand doesn't go find the entered number .

Linq ;0)>

[slimjen]

I agree. But I've taken over this database and the user may not pay attention and overwrite the current fields trying to update info for the cust# not realizing that's not the associated record. Need to put fail safes in place.

[Missinglinq]

If the entered number is not in the Combobox then no Record will be retrieved! How do you overwrite a Record that isn't there?

But if you insist on going down your current path we'll need to see what code you're using to do the Record retrieval, in order to advise you on how to accomplish your goal. Either post it or (better) ZIP up the file and post it, here, removing confidential data, if necessary.

Linq ;0)>

[slimjen]

Thanks for replying. For some reason the code was used like:

Sub Combo188_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[Cust #] = '" & Me![Combo188] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

This allows the user to input a cust#. If the number is not in the list it does nothing and all the data from the current record remainsL So if not paying attention changes can be made. Smh. So I need to change this to where if the record does not exist; a msgbox appears telling them the cust# doesn’t exist and make it so they cannot move on. Even possibly allow them to enter a new cust# by moving to a blank record and adding. I’m pretty sure I’ve done this before but not in a long time. Trying to remember.

[Missinglinq]

I see...so if you retrieve one Record, then try to retrieve another Record, which is not found, the previous Record is still displayed...is that correct? See if this does it for you:

Sub Combo188_AfterUpdate()
 
 Me.RecordsetClone.FindFirst "[Cust #] = '" & Me![Combo188] & "'"
  
 If Me.RecordsetClone.NoMatch Then
   MsgBox "No Match to Customer Number Found", vbOKOnly + vbInformation, "Sorry"
   DoCmd.GoToRecord , , acNewRec
  Else
   Me.Bookmark = Me.RecordsetClone.Bookmark
 End If

End Sub

Linq ;0)>

[slimjen]

You are exactly correct. I'll try this. Thanks