Add records to a subform

[rclandreth]

I have a continuous form which displays a list of flower seed lot records. The name of this form is "frmBreckenridgeData." Each seed lot record has a check box control – “SelectRedord” - which can be selected or deselected. This form contains a command button which opens a work order form - “frmGerminationTestWorkOrder" - in Add Data Mode. The frmGerminationTestWorkOrder work order form has a sub form - "fsubWorkOrderData."

The frmGerminationTestWorkOrder work order form contains a command button – “cmdAddRecord” - which creates a new work order record by generating an auto number in the “GermTestWorkOrderID” control on the form.

When I click on the cmdAddRecord command button, I would also like to use code to add seed lot record(s) to the fsubWorkOrderData sub form where the frmBreckenridgeData form “SelectRecord” checkbox control = true.

I’m assuming that I need to do this by using code that creates a record set but I have no idea how to do this. I am fairly new to MS Access VBA programming so please be gentle.

Thanks.

[ADezii]

We would need additional information such as:

1. What is the Record Source for the Main Form?
2. What is the Record Source for the Sub-Form?
3. What are the linking Fields for the Main/Sub-Form?
4. Where are you deriving the data for the newly added Record in the Sub-Form?
5. What are the Control Names on the Main Sub-Form, and what Fields are they 'Bound' to?
6. etc.

[rclandreth]

ADezii,

I'm grateful to have a response to my question. I hope I can adequately answer your questions and provide you with enough detail:

1. Record Source for frmGerminationTestWorkOrder is GermTestWorkOrder table.

2. Record Source for fsubGerminationTestWorkOrderData (please note that I used the wrong sub-form name in my initial post) is GermTestWorkOrderData table.

3. Linking fields for Main/Sub-form is: Master Field = GermTestWorkOrderID (Primary Key in GermTestWorkOrder table) and Child Field = GermTestWorkOrderID.

4. Record Source for frmBreckenridgeData form is ztblBreckenridgeInventory table. I want to derive data for the sub-form from this temp table. The process I am trying to accomplish is to select specific flower seed lots to be germination tested. To do this, I select specific flower seed lot records on the frmBreckenridgeData form by clicking the [SelectRecord] control. The [SelectRecord] control is bound to the [SelectRecord] field on the ztblBreckenridgeInventory table. After selecting seed lots, the next step is to click on the cmdCreateWorkOrder command button control which opens the frmGerminationTestWorkOrder work order form in Add Data Mode. If I decide to proceed with creating a work order, I click on the cmdAddRecord command button control on the Work Order form and would like to add records to the work order subform. I want to add [ClassName], [ProductCode], and [LotCode] data from the ztblBreckenridgeInventory table to the germination test work order sub-form where the [SelectRecord] field = true in the ztblBreckenridgeData table.

5a. Control Names on frmBreckenridgeData form are: [SelectRecord] (this is a checkbox), [ClassName], [ProductCode], [LotCode]. These fields are bound to fields with same names in the ztblBreckeridgeInventory table.
5b. Control Names on frmGerminationTestWorkOrder form are: [GermTestWorkOrderID] (master field), [GermTestWorkOrderDate], and [KeyTestLocation]. These controls are bound to fields with the same names in the GermTestWorkOrder table. The [GermTestWorkOrderID] field in the table is the Primary Key.
5c. Control Names on the fsubGerminationTestWorkOrderData sub-form are: [GermTestWorkOrderID] (child field), [ClassName], [ProductCode], and [LotCode]. These controls are bound to fields with the same names in the GermTestWorkOrderData table.

I hope this is enough detail to help you understand my database and provide me with a clue as to how I can accomplish this. Thank you for your time.

RCL

[ADezii]

Just give me a little time to get to it, I'm swamped at the moment.

[ADezii]

RCL, this is one of those Threads that is very difficult to diagnose without the actual Database being in front of you, but I've made every effort to do so, especially since you were very detailed and meticulous in your replies.

1. Pay special attention to the Comments, since they were strategically placed for very good reasons.
2. I'm assuming that Code Execution is in the frmGerminationTestWorkOrder Form, thus the Me in certain code references.
3. Remember, that you can never add Record(s) to a Linked Sub-Form unless a Record has been Added and Saved to the Parent first. The code partially checks for this.
4. Much of the code is related to Comments and Error Checking, I suggest you leave these in tact.
5. Any further questions, feel free to ask.

Private Sub cmdTest_Click()
On Error GoTo Err_cmdTest_Click
Dim strSQL As String
Dim lngID As Long
Dim MyDB As DAO.Database
Dim rst_1 As DAO.Recordset
Dim rst_2 As DAO.Recordset
 
'***********************************************************************************
'Create a Recordset based on ztblBreckenridgeInventory, and return
'all Records where the [RecordSelect] Field = True
strSQL = "SELECT * FROM ztblBreckenridgeInventory WHERE [SelectRecord] = True;"
 
Set MyDB = CurrentDb
Set rst_1 = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
'***********************************************************************************
 
'***********************************************************************************
'Create a Recordset representing the Record Source for the Sub-Form
'fsubGerminationTestWorkOrderData, namely GermTestWorkOrderData
Set rst_2 = MyDB.OpenRecordset("GermTestWorkOrderData", dbOpenDynaset)
'***********************************************************************************
 
'***********************************************************************************
'The Main Record MUST be Saved prior to adding a Record to the Sub-Form
If Me.Dirty Then
  Me.Dirty = False
End If
'***********************************************************************************
 
'Extract the Linking ID for the Sub-Form from the Main Form (assume a LONG INTEGER)
lngID = Me![GermTestWorkOrderID]
'***********************************************************************************
 
With rst_1
  Do While Not .EOF
    rst_2.AddNew
      'ADD Records from ztblBreckenridgeInventory WHERE [SelectRecord] = True to
      'Sub-Form but Record in frmGerminationTestWorkOrder MUST be Saved prior to this
      rst_2![ClassName] = ![ClassName]
      rst_2![ProductCode] = ![ProductCode]
      rst_2![LotCode] = ![LotCode]
      rst_2![GermTestWorkOrderID] = lngID
    rst_2.Update
      .MoveNext
  Loop
End With
 
'***********************************************************************************
 
'Requery the Sub-Form so that newly added Data will be immediately visible
Me!fsubGerminationTestWorkOrderData.Requery
 
'***********************************************************************************
'Don't forget to Clean Up!
rst_1.Close
rst_2.Close
Set rst_1 = Nothing
Set rst_2 = Nothing
'***********************************************************************************
 
Exit_cmdTest_Click:
  Exit Sub
 
Err_cmdTest_Click:
  MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    If Not rst_1 Is Nothing Then
      rst_1.Close
      Set rst_1 = Nothing
    End If
    If Not rst_2 Is Nothing Then
      rst_2.Close
      Set rst_2 = Nothing
    End If
  Resume Exit_cmdTest_Click
End Sub