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 "frmBreckenridg eData." 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 - “frmGermination TestWorkOrder" - in Add Data Mode. The frmGerminationT estWorkOrder work order form has a sub form - "fsubWorkOrderD ata."

The frmGerminationT estWorkOrder work order form contains a command button – “cmdAddRecord” - which creates a new work order record by generating an auto number in the “GermTestWorkOr derID” 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 fsubWorkOrderDa ta sub form where the frmBreckenridge Data 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 frmGerminationT estWorkOrder is GermTestWorkOrd er table.

2. Record Source for fsubGermination TestWorkOrderDa ta (please note that I used the wrong sub-form name in my initial post) is GermTestWorkOrd erData table.

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

4. Record Source for frmBreckenridge Data form is ztblBreckenridg eInventory 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 frmBreckenridge Data form by clicking the [SelectRecord] control. The [SelectRecord] control is bound to the [SelectRecord] field on the ztblBreckenridg eInventory table. After selecting seed lots, the next step is to click on the cmdCreateWorkOr der command button control which opens the frmGerminationT estWorkOrder 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 ztblBreckenridg eInventory table to the germination test work order sub-form where the [SelectRecord] field = true in the ztblBreckenridg eData table.

5a. Control Names on frmBreckenridge Data form are: [SelectRecord] (this is a checkbox), [ClassName], [ProductCode], [LotCode]. These fields are bound to fields with same names in the ztblBreckeridge Inventory table.
5b. Control Names on frmGerminationT estWorkOrder form are: [GermTestWorkOrd erID] (master field), [GermTestWorkOrd erDate], and [KeyTestLocation]. These controls are bound to fields with the same names in the GermTestWorkOrd er table. The [GermTestWorkOrd erID] field in the table is the Primary Key.
5c. Control Names on the fsubGermination TestWorkOrderDa ta sub-form are: [GermTestWorkOrd erID] (child field), [ClassName], [ProductCode], and [LotCode]. These controls are bound to fields with the same names in the GermTestWorkOrd erData 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 frmGerminationT estWorkOrder 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