Bound form not saving data to table (1 Viewer)

GS500

Registered User.
Local time
Today, 18:26
Joined
Nov 20, 2012
Messages
40
This is a split database, with the backend stored on a LAN and the front end on each user’s machine. Sometimes the users access the database from home, over VPN. And did in the case outlined below. I’ve tried to discourage them from doing so, but...

I have a continuous subform bound to a query. The users enter data on the form via combobox, there is one field per record on the form. I'm hearing from certain users that they have entered data in all the fields in the form and it looks to them like all the data is there, but when I look in the table later only some of the records have data, always the first few records.

My question is, is this possible? I can’t think of a reason why this would happen. If they select a value from the combo box and move to the next record, Access would write the data to the table immediately, right? Wouldn’t it show an error if it was unable to write the data? I don’t have any error handling on the form that would prevent them from seeing an error. And they would get an error if the front end loses connection to the back end. So according to the users the form just stops writing the data part way through the form, but they have no indication of that on their end.

I have so far been unable to reproduce this issue. Any suggestions on troubleshooting this?
 

llkhoutx

Registered User.
Local time
Today, 18:26
Joined
Feb 26, 2001
Messages
4,018
Forms bound to queries can be problematic. Queries can be and are often non-updatable record sets.
 

MarkK

bit cruncher
Local time
Today, 16:26
Joined
Mar 17, 2004
Messages
8,178
I think you need to find a way to reproduce the problem. The symptoms as you've described them are not specific enough to form any kind of conclusion about what is going on.

As llkhoutx suggests, if the query you are using is a join between multiple tables, and referential integrity is being enforced on that join, and your user only enters data on the 'many' side of the relationship, the update may fail. To test this, just run that query in the designer, and only enter data on the many side, and see if it fails, and if it does, that may be a sufficient reproduction of the problem.

Typically though, I would not bind a form to a query that joins multiple tables. Just bind a form to a single table, and use a subform to provide the 'many' side of the relationship.

hth
Mark
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:26
Joined
Feb 19, 2002
Messages
42,979
Forms bound to queries can be problematic. Queries can be and are often non-updatable record sets.
I disagree. Besides, if the recordset the form is bound to is not updateable, the controls are locked so the user would never even think he was making a change.

Virtually all my forms are bound to queries that join to multiple tables. Most of the time the secondary tables are "lookups" and will never be updated but occasionally, a form will update multiple tables at once. For example, in an Order Entry application, the order entry form is bound to tblOrders but you always want to show information from tblCustomer. The simplest way to do that is to join to tblCustomer so the customer's name and billing address are available on the form as well as possibly some of his order history statistics such as his outstanding balance. Typically, the controls holding the lookup data are locked unless it makes sense to allow the user to change them here also. This is rare because you don't want to have to replicate your validation rules over multiple forms but it is sometimes desirable.

Make absolutely certain that All users are linked to the same BE And that they have CRUD permissions on the folder containing the BE. If they cannot create the lock file, Access will open the BE as read only. I think this locks the forms but I am not sure and cannot test right now but that is also a possibility.
 

GS500

Registered User.
Local time
Today, 18:26
Joined
Nov 20, 2012
Messages
40
The form in question is a subform, and the one-to-many relationship is handled by the form/subform. The query that is the record source for the subform pulls in some related fields from other tables, and those fields are locked on the subform so they can’t be edited anyway. I’ve also verified that for the records in question the related data does exist in the parent table.

The way this form works, is the subform is disabled until the user fills in fields in the main form and clicks a command button to run an append query that appends records to the related child table, which is in the query that the subform is bound to. The subform is then activated and requeried, and the user is then able to update the records in the subform. They click a combobox, select their answer, and move to the next record in the continuous subform.
In this situation, the user claims he was able to fill in all the fields in the subform (22 total) but Access only captured the first 4 of those, and didn’t give him any indication that it was not actually updating the rest of the records.

That means something had to happen between records 4 and 5 that caused Access to stop updating the table, but not give the user any indication that something was wrong.
 

Users who are viewing this thread

Top Bottom