How to open another screen when error reported

[Petewxm]

Hi all,


Just seeing if someone can help, I used access a good 10 years ago in my previous role and its all coming back slowly, I have created a very simple database where we scan serial numbers where duplicates are not allowed. If a duplicate is scanned access shows a error message showing duplicates are not allowed within the database rules, some along those lines. Problem is if the next serial number is scanned it clears the error message and you can carry on. Is it possible for access to complete stop and not allow you to carry on or even better the error message triggers a form to open with a big stop message coming up.

Thanks for any advise

Pete

[ranman256]

sub myTest()
if HasQryCount("qsFindDupes") then 
    docmd.openquery "qsFindDupes"
    msgbox "Duplicates found"
    exit sub   'or close the form:  docmd.close
else
   'continue program
endif
end sub

function HasQryCount(pvQry) as boolean
     HasQryCount= DCount("*", pvQry) > 0
end function

[Petewxm]

Great thankyou ranman, where would I put the code? In a module, macro? Sorry bit of a beginner with visual basic

[Petewxm]

Sorry anyone able to advise where to add the code, tried vb on the form but didn't work

Thanks for any help
Pete

[Micron]

You're using a scanner to input into a form? Then you would use code like that in the form BeforeUpdate event (or perhaps BeforeUpdate event for the textbox receiving your scanner input). However, I think I would add Cancel = True after the message box IF using the form BeforeUpdate event.

You substituted your own query name for that code, yes?

I think I'd just use DLookup in the same event to see if the scanned value is already in the table. Not sure I see a need to create a query and a function to run it and do a domain aggregate function call on its results every time something is scanned.

[Petewxm]

Hi Sorry I`m struggling, yes I`m using a barcode scanner, its the table that does not allow duplicates and brings up the following error when a duplicate is scanned in, which is great. The problem is that if another barcode is scanned it clears the error and you can carry on - I don`t want the person scanning to be able to carry on and to use the mouse to close the error (so the error message stops them scanning)




Table is called "sheet2"

Form where the serial number is entered is called "MM"

I have no query's setup

what do I need to change in the code?

sub myTest()
if HasQryCount("qsFindDupes") then
docmd.openquery "qsFindDupes"
msgbox "Duplicates found"
exit sub 'or close the form: docmd.close
else
'continue program
endif
end sub

function HasQryCount(pvQry) as boolean
HasQryCount= DCount("*", pvQry) > 0
end function
Do I enter the code as follows? but change it to my form name?



Sorry for not understanding and any help you can give

I can upload the database if it helps?

Thanks

Pete

[Micron]

this is calling a function and passing the name of a query to that function: if HasQryCount("qsFindDupes") then
the function returns true if the DCount of its records returns any results, in which case the message pops up.

If you don't have a query named qsFindDupes, which refers to the form textbox that holds the scanned number and looks for records with that value, then it will never work as written.
Not sure if the db would help unless you can type a value in the form instead of scanning.