Access Form does not fill the first time

Question

Hi everyone,

I am new to Access, Using Access 2016, I am working on a labor entry database, I have a main form that displays currently open work orders, an open work order is one that has a null value in the time/date completion field. I have a button on the form to bring up and autofill the current values to a data entry form, so the machinists can enter a part count, end time/date and completion status. When I click the main form button the form displays, without any records. If after that, I go directly back to the main form click the button again it fills properly. If I use the save button on the second form, I return to the main form, but when I use the button if=t reverts back to not filling the second form. This is the code behind the button:

Private Sub Command48_Click()
DoCmd.OpenForm "SubfrmLaborDataCompletion", acNormal, , "ID = " & Me.ID

ID is the autonumber primary key to the labor data table. 

Any help would be greatly appreciated.

Phil

Answer 1

My guess would be that you are opening SubfrmLaborDataCompletion before the current record in the main form is committed to the table.  Add a line before calling the OpenForm method to save the current record:

   Me.Dirty = False
   DoCmd.OpenForm "SubfrmLaborDataCompletion",WhereCondition:="ID = " & Me.ID

A couple of points:

1. SubfrmLaborDataCompletion is a rather confusing name for the form as it suggest it is a subform.  Subform has a very specific meaning; it's a form which is embedded in a parent form as the SourceObject of a subform control.  I would normally term what you have as a 'linked' form and name it frmLaborDataCompletion.

2.  Avoid the generic column name ID.  It gives no indication of what it is an attribute of.  Use names such as OrderID for the primary key of the referenced table, and the corresponding foreign key in the referencing table.

BTW, my father's name was Phil Sheridan, in his case short for Felix.

---

Ken Sheridan, Stafford, England