Web Development

Ask something
Discussion Locked

Access 2000 Query--Very Important

0 helpful
10 replies

by sameer_koranne

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am developing a small package in Access 2000 with Front end and back end both as Access and I have a techical doubt in that which is explained as below
While using the DoCmd.OpenQuery statement in VBA of access, to supress the messages access asks we use the SetWarnings command. This command suppresses all messages given while using Append query in VBA.
But while appending any rows, it also suppresses Error messages. The senario is as under
I have append query which appends some records in a table having Primary key, to avoid duplicates. But my append query, is trying to update some records which may create duplicates in the table. Since, table is indexed, duplicate records will not be appened to the table. So far so good.
If am notusing DoCmd.SetWarning fascility to suppress the messages, it will ask me to update the records, say 100. When I click Yes, to allow the append, system will try to add 100 records to table. but because of duplication of code only 50 records ar updatable. Now system will flash another message saying it cannot add 50 records due to key violation errors. And will ask to click Tes/No button there. If we click Yes, error is supressed and no error is displayed. But if we Click NO then, for every record, it will display the type of error occured. This is fine.
NOW, if I supress all messages i have with DoCmd.SetWarning , then all messages are supressed and I am not able to catch error there and ultimately, I am not able to detect the number of records affected by the save for that instance by the particular user.
Is there any way we can have the number of rows appended OR Trap the error message while using DoCmd.SetWarnings??

I will be highly thankful to you if you can solve my problem as early as possible

10 replies

by timthetoolman

Hi,

Perhaps you're looking at this from the wrong end? If you read for the record before trying to do the append, you could determine whether there was going to be a problem before you got it.

Handle the duplicate then - rather than after the problem has occurred.

Obviously, there is potential for a duplicate to appear between the time the record was (not) read and attempted to be appended. These cases would be rare, though, and maybe not a real problem.

I haven't used access much and I'm not sure of its capabilities for locking, but you may be able to get around that problem entirely by appropriate locking between the read and append.

Does that help?

Cheers,
Tim.

by jfruits

If I am understanding correctly, you want a count of how many records are saved to your database each time an append is run? If so, select a Count of the total records before and after running the append, and subtract them. Just a thought

Good luck!

by sameer_koranne

hi,
Thx for reply, but this is not helping me as i am trying to add one record only. I dont want users to have same numbers in entry. is sort of auto number fascility. pls comment if u have some ideas regarding this. pls keep in mind i m not getting error while adding record coz of duplication and error is suppresed

by bob sellman

You mention that you are appending one row at a time, so I assume that you have the user entering a new record, perhaps into a "data input table" that is basically a copy of the input table, but only used for adding new records. Then I would assumethe user indicates that the record should be saved and you would like to know (and I guess let the user know) when a record cannot be appended to the data table because it is a duplicate.

Based on those assumptions, I would suggest that you coulddo at least two different things. One would be (in VBA code) to do a count (DCount) of the number of records that are currently in the data table that match the data in the new record (whatever data constitutes making it a duplicate). Obviously, if the count =1, the record would be a duplicate and you can notify the user it can't be saved (or whatever message you want to provide), otherwise you append the new record to the data table and provide the user with the appropriate information.

The second possibility is to add an on error statement to your append VBA code. The error capture statements would check for the error code that indicates the append failed because it is a duplicate record, otherwise you might ignore the error code.You can then give messages to the user (or yourself?) based on whether a duplicate row error occurred or if the append was successful.

by shanghai sam

You appear to have two issues; trapping error codes and determining the success of records updated or added.
Trapping error codes is quite straight forward. Using the DoCmd.SetWarnings is usually not the preferred method for getting through a function or procedure and preventing the user from seeing any generic MS Access error screens. In truth - I am yet to find the appropriate circumstance for using this (or turning the echo off) method. You should include error trapping in all functions at the start of the function - as follows:
-----
Private Sub ThisFunction_Click()
On Error GoTo Err_ThisFunction_Click

Exit_ThisFunction_Click:
Exit Sub

Err_ThisFunction_Click:
MsgBox Err.Number & " : " & Err.Description, vbCritical, "ThisFunction_Click"
Resume Exit_ThisFunction_Click
----

This structure will enable you to see the error number that occurs when your code attempts to add/edit records and then causes an error condition (for example below - let's say the err.number for the error condition you have described is 22). Then you can trap the error condition with the following modification:
-----
Err_ThisFunction_Click:
if err.number = 22 then

else
MsgBox Err.Number & " : " & Err.Description, vbCritical, "ThisFunction_Click"
end if
Resume Exit_ThisFunction_Click
-----
That should help with the error trapping.

To determine the number of records that have been added or edited - use the RecordsAffected method of the database object, as follows:
----
Dim dbs as Database
dbs.Execute "UPDATE tblThings SET name = 'Me'"
if dbs.RecordsAffected > 0 then
msgbox dbs.RecordsAffected & " records changed, vbOKOnly, "Records Changed"
else
msgbox "No records have been changed, vbOKOnly, "No Records Changed"
end if
----

Hope this helps - good luck.

This discussion is closed to new replies.