Custom message box to prevent error Microsoft Office Access database engine cannot find a record in the table 'tbl_Product'with key matching field(s) 'Barcode'

I have an Access 2016 database which I use to scan barcodes to keep track of products that are already stored in the database. These products will always match the barcode number that is entered. Sometimes the wrong number gets entered from the scanner which comes up with the 3101 error.

The way that the data is scanned in is through a Subform with the field called barcode.
I would like a way to be able to show a message box to catch the error rather than the built in error on access.
Daniel JonesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Well there must be some code or a macro running in your database, probably an afterUpdate event on the control in the subform. Maybe if you show that, we can make a suggestion?
0
Daniel JonesAuthor Commented:
There isn't any code or macro that is currently running on the Subform. I found a similar question with the error but do not know how it could be implemented onto my db to test that solution. Similar error
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Open the subform while in design view, and look at the form properties. Select the events tab, and find the form before update event, and select "Event Procedure", then click the builder icon "...". Exact details may wary depending on your settings.
This should take you to the VBE editor, where you can add some code to your database.  It will probably look like this:
Private Sub Form_BeforeUpdate(Cancel as Integer)

End Sub

Open in new window


And now modify it to:
Private Sub Form_BeforeUpdate(Cancel as Integer)
  Dim InputCode as string
  InputCode=Me.NameOfYourTextboxGoesHere
  if Dcount("*","tbl_Product","Barcode='" & InputCode & "'")=0 then
   Cancel=True
   Me.Undo
   Msgbox "We could not find the code scanned. Please try again
  End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If the error is being generated by Access, you can use the forms OnError event to capture it.

 For that event, there are two arguments that are passed in:

DataErr   - This is the error number that occured.
Response - This is a variable that you set to indicate if Access should put up it's own message or not.

 Using acDataErrContinue tells Access to ignore the error and not display a message.  This would allow you to put up your own error message.    Using acDataErrDisplay is the default.

 Set the property sheet to "[Event Procedure]"   then put a STOP in the procedure.   When you execute, you'll hit the stop if Access is generating the error.   You can then look at DataErr to see what number is occurring.

 You can of course try to catch the error before it occurs with a Before or AfterUpdate event as Anders suggested and what you saw Scott suggest in the other question.

Jim.
0
Daniel JonesAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.