I have an unbound form (Main Form)

[Chyld]

I have an unbound form (Main Form)
and a sub-form (Main Form_Subform)

(Main Form) supplies criteria for a query (Main Form_Query)
(Main Form_Subform) is based on (Main Form_Query)

Once (Main Form) last field is updated, it automatically requeries the (Main Form_Subform) and displays the data appropriately.

I have the following problem.

How do I add the contents of (Main Form_Subform) to a seperate table once a command button is pressed?

The idea is to have the selections made on the (Main Form), it's criteria requerying the (Main Form_Subform) and displaying the appropriate contents, then, on pressing the 'Add Line' button would reveal a new (Main Form_Selection) subform that would look like this:

Record ID - 55125 (Would this be another table?)
------------------------------------------------
Type Product Size Quantity
1
2

1 & 2 would be seperate lines that will update on pressing the 'Add Line' key on the (Main Form_Subform)

If anyone can point me in the right direction it will be much appreciated...

craig@chyld.co.uk

 

[Databaseguy]

This is for a combo box on exit, but it's the same basic principle.

Private Sub cboAdd_Click()
On Error GoTo Err_Hand
'Dim Variables for recordset
Dim DBS As Database
Dim rst As Recordset
'Dim confirmation message box stuff
Dim msg, resp, sty
sty = vbYesNo
msg = "Do you really want to add this record for " & Me.txtYourField & " ?" 'The Employees Name
'Fire off confirmation
resp = MsgBox(msg, sty)
'If user responds yes
If resp = vbYes Then
'Instancate DBS & Recordset objects
Set DBS = CurrentDb
Set rst = DBS.OpenRecordset("tblYourTable")
'Add new record & update recordset
With rst
.AddNew
!YourField = cboYourFiled.Value
.Update
End With
'Clear reference to objects
Set DBS = Nothing
Set rst = Nothing
'Refresh the form
Me.Refresh
Else
'Bail out of procedure. OK is only response allowed
MsgBox "Add Canceled", vbOKOnly
End If
'Error trapping
Err_Hand:
Select Case Err.Number
'Ignore 0, which is always generated for no error
Case 0
'3022 is Access key violation. If it occurs, record is already there
Case 3022
MsgBox "You have this record "
Resume Next
Case Else
'Display error description
MsgBox Err.Description
End Select
End Sub