How to confirm record has been SAVED

[Pawn28]

Hi there
I have a Multi user Access Database which has been split into a Front and Back end. What I am noticing is sometimes when the user enters data into the Front end form, it is not being saved in the Backend tables....
I need some direction on how to confirm when the user clicks save that the data is actually being saved in the backend tables. I have been looking on google for a solution but haven't found one yet

 

[CJ_London]

You'll need to provide some more information.

What is the code behind your save button?

Is the save button the only way a user can leave a form? i.e. can they click the close button in the form bar, close the front end/Access down, or click another button which closes the form without updating?

Is your form using the normal DAO recordset or ADO?

Do you have any code/macros in your form before update event?

Is the network hardwired or wireless?

Can you demostrate that when the save button is clicked the data is not saved.

 

[Pawn28]

Code behind my Save button:

'Calls the Format Sub, qryNotesSaved, CleanUp Sub, mSave, mPrint
Format
DoCmd.SetWarnings (WariningsOff)
DoCmd.RunMacro "mSave"
If Me.cboAddressUpdate.Value = "X" Or Me.cboAddressUpdate.Value = "Y" Or Me.cboAddressUpdate.Value = "Z" Then
MSG1 = MsgBox("Would you like to print a record?", vbYesNo, "Print")
If MSG1 = vbYes Then
DoCmd.RunMacro "mPrint"
ElseIf MSG1 = vbNo Then
End If
End If
Can you demostrate that when the save button is clicked the data is not saved.
My database is set up that when the user clicks SAVE it saves the form data into two different tables. The missing records are saved in the one table but not the other.
The macro calls 2 queries which save the data into the 2 different tables...so it works for the frist table but isn't saving to the other. This doesn't always happen which is why I was thinking that the tables may be locked by other users when some are trying to save.
I was hoping to be able to code something which checks that the form data is indeed in the backend tables before the form clears and tells the user the data has been saved.

 

[Pawn28]

Sorry here are answers to your other questions:
Is the network hardwired or wireless?
the network is hardwired
Is your form using the normal DAO recordset or ADO?
I am not using recordsets
Do you have any code/macros in your form before update event?
Do you mean at the Form level?

 

[CJ_London]

A couple of questions about the code

I presume WariningsOff is set to true? You also don't seem to be setting it back to false once everything is done. Either way, I recommend you disable this for now - it may be hiding an error message which will tell you why one of the tables is not being appended to

Since you are using a macro to do the saving, you'll need to post this since this will be where the problem is. Plus the SQL to the two queries - indicating which one occasionally fails.

I don't understand what you mean by this

The missing records are saved in the one table but not the other.

Normally tables do not get locked - records do, so locking is unlikely to be a reason unless you are talking about updating rather than inserting records (save could mean either) - in which case your setwarnings could be masking the issue.

You say you are not using a recordset, so presumably your query is picking up values directly from the form? Posting the SQL for each query will answer this

 

[Pawn28]

Here is the macro

SetWarnings = NO
OpenQuery = qrySaveToTable1 -> this one is where we see the data stored correctly
OpenQuery = qrySaveToTable2 -> this one is where we are losing the records
SetWarnings = Yes

 

[Pawn28]

qrySaveToTable1:
INSERT INTO tblNotes ( Value, Stamp, Notes, BA, FirstName, LastName )
SELECT Forms!AddNewItem!txtValue, Now(), Forms!AddNewItem!txtNotes, Forms!AddNewItem!txtBA, Forms!AddNewItem!txtFirstName, Forms!AddNewItem!txtLastName;

qrySaveToTable2:

INSERT INTO tblNewItem ( BAName, Today, Value, LastName, FirstName, Address1, Address2, Street, City, Province, Postal, CurrentPC, FPostal, Country, CYDup, PYDup, DupYear, ChangeFrom, ChangeType, AddressUpdate, Print, Distribution )
SELECT Forms!AddNewItem!txtBA AS BA, Now() AS DateEntered, Forms!AddNewItem!txtValue AS Value, [Forms]![AddNewItem]![txtLastName] AS LastName, [Forms]![AddNewItem]![txtFirstName] AS FirstName, [Forms]![AddNewItem]![txtOptAddress1] AS Address1, [Forms]![AddNewItem]![txtOptAddress2] AS Address2, [Forms]![AddNewItem]![txtStreet] AS Street, [Forms]![AddNewItem]![txtCity] AS City, Forms!AddNewItem!cboProv AS Prov, [Forms]![AddNewItem]![txtPC] AS Postal, [Forms]![AddNewItem]![txtCurrentPC] AS CurrentPC, [Forms]![AddNewItem]![txtFPC] AS FPostal, [Forms]![AddNewItem]![txtCountry] AS Country, [Forms]![AddNewItem]![chkCY] AS CYDup, [Forms]![AddNewItem]![chkPY] AS PYDup, Forms!AddNewItem!txtPY AS PY, [Forms]![AddNewItem]![cboFrom] AS [From], [Forms]![AddNewItem]![cboSource] AS Type, [Forms]![AddNewItem]![cboAddressUpdate] AS AddressUpdate, [Forms]![AddNewItem]![txtPrint] AS Print, [Forms]![AddNewItem]![txtDistribution] AS Dist;

 

[GinaWhipp]

I see you are using some Reserved Words which may be causing the problem. (Reserved Words in Access *belong* to Access and when you use them Access can become confused.) To fix that you can...

1. Change the names at the table level OR
2. Bracket them, i.e. Value turns into [Value]

So, for your query...

INSERT INTO tblNewItem ( BAName, Today, [COLOR=red][B][Value][/B][/COLOR], LastName, FirstName, Address1, Address2, Street, City, Province, Postal, CurrentPC, FPostal, Country, CYDup, PYDup, DupYear, ChangeFrom, ChangeType, AddressUpdate, [COLOR=red][B][Print][/B][/COLOR], Distribution )
SELECT Forms!AddNewItem!txtBA AS BA, Now() AS DateEntered, Forms!AddNewItem!txtValue AS Value, [Forms]![AddNewItem]![txtLastName] AS LastName, [Forms]![AddNewItem]![txtFirstName] AS FirstName, [Forms]![AddNewItem]![txtOptAddress1] AS Address1, [Forms]![AddNewItem]![txtOptAddress2] AS Address2, [Forms]![AddNewItem]![txtStreet] AS Street, [Forms]![AddNewItem]![txtCity] AS City, Forms!AddNewItem!cboProv AS Prov, [Forms]![AddNewItem]![txtPC] AS Postal, [Forms]![AddNewItem]![txtCurrentPC] AS CurrentPC, [Forms]![AddNewItem]![txtFPC] AS FPostal, [Forms]![AddNewItem]![txtCountry] AS Country, [Forms]![AddNewItem]![chkCY] AS CYDup, [Forms]![AddNewItem]![chkPY] AS PYDup, Forms!AddNewItem!txtPY AS PY, [Forms]![AddNewItem]![cboFrom] AS [From], [Forms]![AddNewItem]![cboSource] AS Type, [Forms]![AddNewItem]![cboAddressUpdate] AS AddressUpdate, [Forms]![AddNewItem]![txtPrint] AS Print, [Forms]![AddNewItem]![txtDistribution] AS Dist;

With any luck that will *fix* your query as that might have been its only problem!

 

[CJ_London]

I concur, the reserved words issue needs to be resolved first. See these as well

[Forms]![AddNewItem]![cboSource] AS Type (qry2)
Forms!AddNewItem!txtValue AS Value (qry1 & 2)

you are repeating setwarnings in both code and macro

As suggested before, I would remove all setwarnings until you understand how the problem is occuring

neither query allows for null values in the form so may fail if an entry is not made - since most the fields in tblNotes are the same as tblNewItem, suggest look at the fields from [Forms]![AddNewItem]![txtOptAddress1] onwards

As a general comment, not related to your problem, it looks like your tables are repeating data (value, BA, Firstname, lastname). One of these presumably forms a relationship between the two tables, so the other three are not required

 

[Pawn28]

Sorry guys I changed some of the text when I posted the queries so I really am not using reserved words....I will try setting the warnings in only one place and see if that helps. But does anyone know how I can wrtie some code which will confirm that the data is really saved in the table before the users form clears?

 

[GinaWhipp]

Have a look at...
http://www.fmsinc.com/microsoftaccess/query/action-queries/AffectedRecords/number-of-records.htm