Unwanted update to the first record in a table when form is refreshed.

[jordatf]

I have a form with a sub form. The master form has a select combo box for selecting the records to display in the sub form, and that works fine.

The problem is that when a new record is added to the sub form and then the form is either refreshed, closed and reopened (via macro), or the select criteria is re-entered, the first record in the table is updated with the select identifier. It is important to refresh the sub form data to get it in the right order.

The details are as follows:

Sub Form table, Appointments, has the following fields: [ApptID] (key); [PractID]; [ApptDate];[TimeID];[PatID].
It is a bridge table. [PractID], [TimeID], and [PatID] are links to other tables. The sub form has all the fields.

Master form has one field, [PractID], to select Appointments record(s).

The forms are linked via [PractID].

Any suggestions for avoiding the unwanted update to the first record?

Thank you in advance for any suggestions.

 

[CBrighton]

What is being done with the select criteria which would cause it to be input into one of the bound fields in the subform?

I am guessing what you have done is set the main form to be bound to the same query / table as the subform, and as soon as you change the combobox that's saving that change to the record which is currently open on that main form. Therefore it's the main form at fault rather than the subform.

Solution:

Remove the control source & record source from the combobox & mainform. Remove the link between the main & sub form.

The main form should not be editing a record so remove it's ability to do so.

 

[jordatf]

Thank you for your insight. The master form was not bound to the same table, but apparently was the culprit. Based on your comments, I used a different approach that is working. Instead of a master and subform, there are now two separate forms. The first selects one of the criteria and the second displays the related data using the seletion criteria from the first.

Thanks again for your comment and insight.