Error Message Questions for when append query fails 1

[Scootman]

Hi all,

I looked through many posts on the forums for a solution to this problem, but I couldn't find any help.

I've created a mult-user database that serves as both a look-up tool and a way for them to submit new records.

Most of the records that are being submitted will get appended successfully, but there are some that are not getting appended. I know this because there's a separate table that's updated when the same 'Submit' button is clicked on the form, but the append query isn't working for some users. The users don't know there was an error though because I put in the 'Setwarnings false' code before it ran the action queries.

_________________________________________________

My questions:

1. Is Access capable of displaying an error message to the user (i.e. Record Not Saved!) when it doesn't append a record? And can it do this even without having to turn the warnings back on?

2. Is there a way for the actual error problem to be appended to a different table (which isn't having problems) so I can review it to make a determination of what's going on?

_________________________________________________

And just in case anyone wanted to know...I don't know how to write DAO. My coding is primarily written as "docmd.openquery" when it comes to performing action queries.

I'm not an Access guru as many on Tek-tips are. I love working with it and I figure out a lot of things on my own, but I'm stumped here.

I appreciate any examples or help anyone can give. If you have questions, I'll do my best to answer them.

 

[Scootman]

Well I did find a FAQ about Generic Error Handling in the Modules FAQ.

http://www.tek-tips.com/faqs.cfm?fid=6245

I looked a little of it over. While its a bit advanced and complex for me (I like how the first sentence of the FAQ said 'A rather simple routine...'), I can try to figure it out.

But I don't know if this FAQ will show an error message to the user letting them know the record didn't append even WITH the Setwarnings as False.

Can someone let me know if the code in the FAQ will do that or not? I can't really test it since I don't know where the error is happening (95% of the time the appends work, its just the 5% I can't figure out).

 

[Scootman]

Nevermind on this question/post. I was able to figure it out using the Generic Error Handler and Error Log Table FAQ.

Thanks.

 

[Zion7]

No Scootman, on its own, it won't.
Again, as is, it's a "generic" handler.
As long as an error fires, it will simply record
the number, description, etc...

To handle or trap specific errors, or to show results,
of what had or hadn't been accomplished, it would depend on
your code.

I know using ADO or DAO (which you've already stated,
that you're unfamiliar with),
you can use the dbFailOnError,
or ReturnRecords arguments to gain a little more control.

you could possibly use
a Dcount(), or DLookUp() after the fact, to check if the intended record,
had been added or not.

I personally would write something like this,

CurrentProject.connection.Execute _
"INSERT INTO tblCountry(txtCountry, txtCapital, txtPopulation)" & _
" VALUES('Latvia','Riga',2541854)",,adExecuteNoRecords

Dim varRecord As Variant

If IsNull(DLookUp("pkCountryID","tblCountry", _
"txtCountry ='Latvia'") Then
MsgBox "Yo, looks like yo' code didn't work, brudda?"
'append to different table.../
CurrentProject.connection.Execute _
"INSERT INTO tblCountryII(txtCountry, txtCapital, txtPopulation)" & _
" VALUES('Latvia','Riga',2541854)",,adExecuteNoRecords


BUT, I don't think it's the table necessarily,
maybe the values aren't getting populated cotrectly?

...add more data verification before running the query?

 

[Scootman]

Thanks for the response Zion7.

All the fields that are appending are unbound on the form. Most of the fields are ones that I set depending on what was brought up from the Lookup.

Since most of the records append without any problem, I can only assume that the problem is coming from what the user enters.

The Append Query uses IIF statements in the Field row. Could this be causing a problem? I'm doing most of my data verification from the query itself. That's probably a bad idea, but hey, I love queries.

For example, one of the statements is:

IIf(Forms!frmLookup!frmData!subfrmSubmitRequest!txtOSO="r" And Forms!frmLookup!frmData!subfrmSubmitRequest!cmboTypeRequest="Address Change","OSO - Address Change",IIf(Forms!frmLookup!frmData!subfrmSubmitRequest!txtOSO="r" And Forms!frmLookup!frmData!subfrmSubmitRequest!cmboTypeRequest="Lost/Misplaced W-2","OSO - Lost/Misplaced W-2",Forms!frmLookup!frmData!subfrmSubmitRequest!cmboTypeRequest))

The actual code behind the Submit button is a lot better than what it used to be (thanks to my overloading of the append query).

Something's failing somewhere, but I don't know where.

My normal job is handling Payroll. Access was supposed to be a passing amusement, but now this is my 8th or 9th database in 6 months and they're getting more difficult all the time.

 

[Scootman]

Zion7,

I looked at your code and I don't know if I can convert it to a version for my needs because the append query reads from another query that uses information from the form as its criteria. Its rather confusing.

I thought about putting in an error handler that displays a message box (or a pop-up form...my users don't usually read anything unless it stands out). The form would tell them to contact me at my desk (since I always work the same hours they do) so I can come over and fix the problem.

That could at least be a short-term solution to my problem, right? Tomorrow I am going to do a bit more testing to see what else could cause the record not to append. If 2 out of 10 people are having the problem, then something peculiar is certainly going on.

I usually love Access, but its times like these that burn me out.

 

[Zion7]

Well, any query should run regardless of the dependants
or parameters.
are you familiar with SQL view, from the query pane?
From query pane, you have design, spreadsheet(Run),
and SQL view. VBA developpers often write queries using
the graph then open in SQL view to copy & paste the results,
into a VBA module(with a few modifications).

So, there's no difference between using the OpenQuery
commasnd, or running the ststement itself.

If you like, go to sql view, Copy & paste he results here.
one of us will be more than happy to alter the statement,
to make it VBA compatible.
That way, you can be more premptive about running the query.
You can set a lot more controls in plasce.

If IsNull(Me.txtCountry) Or _
IsNull(Me.txtCity) Or _
IsNull(Me.txtPopulation) Then
MsgBox "All criteria has not been entered!"


Also things like
If Not IsNumeric(Me.txtPopulation) Then
MsgBox "Population must be a numeric value with
no punctuation...."


ELSE
CurrentDB.Execute "INSERT INTO.....",,dbFailOnError
End If

 

[Scootman]

Zion7,

I really appreciate your help and attention. With your example and with some others here on Tek-Tips (searching always helps!), I was able to get the following to work:

CurrentDb.Execute "INSERT INTO [tblDateRequested-Temp](PersNo, SSNo, ReasonRequest, NewAddress, DateRequested, TimeRequested, EnteredbyUserID, EnteredbyComputerName, PTAComments, CurrentAddress, DateMailedOut)" _
& "Values (" & [PersNo] & ",'" & [SSNo] & "','Address Change', '" & [txtNewAddress] & "', #" & [TxtDate] & "#, '" & [txtTime] & "', '" & GetUserID & "', '" & GetComputerID & "','No Comments', '" & [txtAddress] & "', #" & [TXTdATE] & "#);", dbFailOnError

This is just one version though of when the query should run.

2 and a half more questions:

1. How many 'CurrentDB.execute' lines can I have in a VBA sub? I have about 6 or 7 variables (if..then statements).

2. I had 11 more records not append today and the users didn't get an error message even with my DLookup (it tested well on my pc, but not on the others). At the end of the day, I did try an IsNull(Dlookup... technique. That tested well on my pc too and hopefully will work on theirs also. The only data they can enter is a textbox (which appends to a memo field) and a drop-down box that's limited to the values in the list. I don't know why its only working 90% of the time. Is it good to append to memo fields from unbound text in a form? Am I missing something obvious?


Thanks again! I'll use the CurrentDb.Execute more often for now on. I have many Update queries, I'll need to search for how to do those. This should make the whole process quicker than running oversized query controls...I hope.

 

[Scootman]

I meant to clarify:

txtNewAddress is the text field on the form that appends to the NewAddress memo field in the table (its the person's mailing address)

I'm actually bypassing the combo boxe's value by just typing it in as 'Address Change' (it appends to the ReasonRequest field).

All the other fields that are in my code in the previous message are 'created' behind the scenes based on who is being looked up on the form and who is entering in the data (as well as the date and time fields).

I still can't figure out where something's going wrong, but it has to be with the two fields because the user doesn't mess with the other fields that create themselves.

Could entering in something wrong in the txtNewAddress field cause a problem? Its a text box that appends to a memo field, so it should be able to take many characters, right?

 

[Zion7]

Scootman, I am not knowledgable enough to tell you why,
sometimes it fails, outside of the reasons I suggested,
ie, "invalid data".

Yes, you can run as many Execute commands as you like,
within a VBA procedure.

Textbox to memo shouldn't be a problem, it shopuld
accept any values.

one thing I foolishly overlooked was, to opt for the
AffectedRecords argument, rather than the DLookUp(),
...Less taxing!

Here's a reckless sub I came up with, because I was having the same problem. Sometimes the UPDATE query would work,
other times not.
It shows, for one, that Execute is being run several times,
and how to use Records affected to check for success.

I say reckless because, for one, it doesn't work, and
secondly it's a rather brutal way to attempt to solve
a VBA issue.

Dim intRecAff As Integer, x As Integer

Do Until x = 5
CurrentProject.Connection.Execute strSQL, intRecAff, adExecuteNoRecords
If intRecAff = 0 Then
CurrentDb.Execute strSQL, dbFailOnError 'not failing on error?
If CurrentDb.RecordsAffected = 0 Then
x = x + 1
Else
x = 5
End If
Else
x = 5
End If
Loop

 

[Scootman]

Zion7,

I was able to learn a bit more about the CurrentDB.Execute syntax over the past day and a half (now I can go to sleep!!!...just kidding...).

Here's some data verification I added:

If cmboTypeRequest = "Address Change" And Nz(Me!txtNewAddress) <> "" And txtOSO <> "r" And CurrentProject.AllForms("frmComments").IsLoaded Then
CurrentDb.Execute "INSERT INTO [tblDateRequested-TemporaryList](Pernr, SSNo, ReasonRequest, NewAddress, DateRequested, TimeRequested, EnteredbyUserID, EnteredbyComputerName, PTAComments, CurrentAddress, DateMailedOut)" _
& "Values (" & [Pernr] & ",'" & [SSNo] & "','Address Change', '" & [txtNewAddress] & "', #" & [TXTdATE] & "#, '" & [txtTime] & "', '" & GetUserID & "', '" & GetComputerID & "', '" & Forms!frmComments!txtComments & "', '" & [txtAddress] & "', #" & [TXTdATE] & "#);", dbFailOnError
DoCmd.Close acForm, "frmComments"
CurrentDb.Execute "Update tblW2 Set Stage = 'Address Change Waiting For Reprint', OSO = 'No', PrevRequests = 1, ActiveAddressChangeReq = 1,LastRequestedByUser= '" & GetUserID & "'"
End If

It works very well. I will try your AffectedRecords argument tomorrow to see if that'll fit too as another 'record check' for what I'm doing.

Sorry for all the delayed responses. I'm at work a lot and I don't have Internet Access to check on Tek-Tips (if I did, I'd get a lot more ideas on how to complete my projects better).

I forgot to tell you exactly how much is involved with the actual form I'm running....

A Mainform with a subform and within that subform, 5 other subforms that populate and become visible with VBA code.

Within all of that, there are 21 controls (each with their own label), 11 command buttons (some are labels that work as command buttons), and about 7 separate labels for titles and explanations.

I created the database in a span of about 25 hours on my own. Its been a monster of a headache, but it works (except for the occasional bad appends...but that may hopefully be fixed now).

I'll send one more update tomorrow night on whether or not there were any errors during the day with the database.

Thanks again,

Scootman (aka Scott)