Remove warning pop up choice

[Hank]

My append query is run from a command button on a form. The purpose of the append query is to post attendance on a table. Unique key values are used to prohibit using the attendancedate for the same employeeID.
If someone ties to use a date that has already been posted the following message appears:
"Microsoft Access can't append all the records in the append query""Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add <number> record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations."

"Do you want to run the action query anyway?"

To just ignore the error and run the query anyway click 'Yes'. Or, for an explanation of the causes and a description of the violations click 'Help'.

Everything is fine as long as the user does not click "YES" - then double attendance records are added to the table.

How can I get rid of this message and/or PROHIBIT choosing the "YES" answer?

Most of the answers I have seen show how to disable the warnings, but do not show how to prohibit the append query from double posting.

Rather than the pop up warning, I would like a message box to pop up saying: " ATTENDANCE RECORDS FOR THIS DATE HAVE BEEN POSTED"

Thanks

Hank

 

[Jeanette Cunningham]

Hi Hank,
You can turn off the warnings. A2003 - tools/ options/ edit find/ confirm
action queries.
In A2007 - office button, access options, advanced tab

You can also use code like this -->
Dim strSQL as String
strSQL = "Name of saved query or sql in vba"
CurrentDb.Execute strSQL, dbFailOnError

Using the CurrentDb in vba bypasses the error messages.
Any records that can't be appended will be silently discarded.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


My append query is run from a command button on a form. The purpose of the
append query is to post attendance on a table. Unique key values are used
to prohibit using the attendancedate for the same employeeID.
If someone ties to use a date that has already been posted the following
message appears:
"Microsoft Access can't append all the records in the append query""Microsoft
Access set 0 field(s) to Null due to a type conversion failure, and it
didn't add <number> record(s) to the table due to key violations, 0
record(s) due to lock violations, and 0 record(s) due to validation rule
violations."
"Do you want to run the action query anyway?"
To just ignore the error and run the query anyway click 'Yes'. Or, for an
explanation of the causes and a description of the violations click 'Help'.
Everything is fine as long as the user does not click "YES" - then double
attendance records are added to the table.
How can I get rid of this message and/or PROHIBIT choosing the "YES" answer?
Most of the answers I have seen show how to disable the warnings, but do not
show how to prohibit the append query from double posting.
Rather than the pop up warning, I would like a message box to pop up saying:
" ATTENDANCE RECORDS FOR THIS DATE HAVE BEEN POSTED"
Thanks
Hank

 

[Hank]

Thanks for your reply Jeanette,
I'm using A2003. I have unchecked the Action query tick mark. That
removes the warning that I am about to add records - but does not remove the
warning I mentioned.
The code choice sounds like it would work - but I am not sure where it
should go?
I also would like to be sure I get the pop up message box stating
"Attendance Records for this date have been posted" or "Records have been
posted, Thank you."

Thanks again
Hank

 

[Ron2006]

I have a question. If you have the unique key and you get the key
violations message saying it DID NOT add the records, how do you have
double posting?


That being said,
1) create a select query that looks at the table you are posting the
activity to and has a criteria of the date that the activity will be
posted to.
2) When you press the button to post issue a dcount with that query
and if the # > 0 then issue you waring message and get out of the
routine without doing anything else, and if the # is 0 then go ahead
and add the records as requested.

Ron

 

[Jeanette Cunningham]

I always have these 3 turned off-->
Record changes
Document deletions
Action queries

All 3 of above are in the same tab of the tools options.

I always do this using code with CurrentDb.Execute method.

To do this with code you need a form with a button.
For this air code example the button is call 'cmdUpdate'

Private Sub cmdUpdate_Click()
Dim strSQL as String
strSQL = "Name of saved query or sql in vba"
CurrentDb.Execute strSQL, dbFailOnError
End Sub

To prevent adding duplicate records requires some more work on the database.
A table level validation that prevents duplicates would prevent it.
For help on record and table level validations see this link
http://allenbrowne.com/ValidationRule.html

To show a message telling users that the update was successful, you need to
build this yourself.
Replace the above code with this code-->

Private Sub cmdUpdate_Click()
Dim strSQL as String
Dim strMsg as String

strSQL = "Name of saved query or sql in vba"
CurrentDb.Execute strSQL, dbFailOnError
If CurrentDb.RecordsAffected <=0 Then
strMsg = "ATTENDANCE RECORDS FOR THIS DATE HAVE BEEN POSTED"
Else
strMsg = "Updated successfully"
End Sub
MsgBox strMsg
End Sub

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia