Saving to sub-form from primary form

[kagoodwin13]

I am trying to create an input form for shift reporting in manufacturing. I have attached a picture of the primary form.





I am currently stuck trying to create a system to fill in the Downtime Causes and Safety Report sections. The user may need to fill in multiple downtime causes or safety reports per shift, so I want the form to enter information into the respective fields, submit the information from the fields to a temporary, and use a query to refresh the temporary table. The best analogy I have for this function is building a "shopping cart" for downtime causes and safety reports.

I am trying to use this code to submit the field information to the temporary table:

Private Sub Command74_Click()
[tblDOWNTIME_REPORTS_TEMP].[DOWNTIME_INCIDENT_DESCRIPTION] = Me!txtDOWNTIME_INCIDENT_DESCRIPTION
[tblDOWNTIME_REPORTS_TEMP].[CORRECTIVE_ACTION] = Me!txtDOWNTIME_CORRECTIVE_ACTION
[tblDOWNTIME_REPORTS_TEMP].[DOWNTIME_MINUTES] = Me!txtDOWNTIME_MINUTES
[tblDOWNTIME_REPORTS_TEMP].[DOWNTIME_REASON_CODE] = Me!cmboDOWNTIME_REASON_CODE
    With Forms!frmTOTAL_SHIFT_REPORT!frmDOWNTIME_REPORTS_SUBFORM
        .Requery
    End With
Me!txtDOWNTIME_INCIDENT_DESCRIPTION = ""
Me!txtDOWNTIME_CORRECTIVE_ACTION = ""
Me!txtDOWNTIME_MINUTES = ""
Me!cmboDOWNTIME_REASON_CODE = ""
End Sub

When I use this though, the first four lines always result in the following error: "Run time error '2465': Microsoft Office Access cannot find the field '|' referred to in your expression."

The error message isn't much help. As far as I can see, I have no field "|" anywhere. I have tried removing brackets, changing periods to exclamation points and vice versa, but I can't seem to solve this problem. I also haven't been able to find a good fix for this error through Google. Any thoughts?

[June7]

If the code is behind the form referenced, then simply:

Me.controlname = Me!fieldname

If you are setting value of a control in another form:

Forms![formname].[controlname] = Me!fieldname

However, if you are trying to set value in a table, can't be done like that. Need to open recordset and edit the recordset or use SQL UPDATE action.

[kagoodwin13]

Thanks for your help. The solution was to make an interim form bound to the table I was trying to save to. The primary form saves to the interim form (opened in hidden mode with the primary form), then the interim form is saved to the temporary table, and the subform is requery-ed to show what was just entered. The interim form is the "bridge" between my primary form and the temporary table.

Private Sub Command74_Click()

Forms![frmDOWNTIME_REPORTS_INPUT].[txtSUPPLIER_NUMBER] = Me!txtSUPPLIER_NUMBER
Forms![frmDOWNTIME_REPORTS_INPUT].[txtSHIFT_ID] = Me!txtANT_SHIFT_ID
Forms![frmDOWNTIME_REPORTS_INPUT].[txtDOWNTIME_INCIDENT_DESCRIPTION] = Me!txtDOWNTIME_INCIDENT_DESCRIPTION
Forms![frmDOWNTIME_REPORTS_INPUT].[txtDOWNTIME_CORRECTIVE_ACTION] = Me!txtDOWNTIME_CORRECTIVE_ACTION
Forms![frmDOWNTIME_REPORTS_INPUT].[txtDOWNTIME_MINUTES] = Me!txtDOWNTIME_MINUTES
Forms![frmDOWNTIME_REPORTS_INPUT].[cmboDOWNTIME_REASON_CODE] = cmboDOWNTIME_REASON_CODE


DoCmd.Save acForm, "frmDOWNTIME_REPORTS_INPUT"
DoCmd.GoToRecord acDataForm, "frmDOWNTIME_REPORTS_INPUT", acNewRec


    With Forms!frmTOTAL_SHIFT_REPORT!frmDOWNTIME_REPORTS_SUBFORM
        .Requery
    End With
    
Me!txtDOWNTIME_INCIDENT_DESCRIPTION = ""
Me!txtDOWNTIME_CORRECTIVE_ACTION = ""
Me!txtDOWNTIME_MINUTES = ""
Me!cmboDOWNTIME_REASON_CODE = ""




End Sub

[Lewis825]

However, if you are trying to set value in a table, can't be done like that. Need to open recordset and edit the recordset or use SQL UPDATE action.

Can you please explain how to do this.....

[June7]

Opening and manipulating a recordset is rather involved. Many examples in threads and in web tutorials.


Otherwise, running an SQL action in VBA is like:

CurrentDb.Execute "UPDATE tablename SET fieldname=something"