SubForm Multiple Rec Insert One Table

J

JJ

Hi All,

Ok need some expert advice here. I have the usual main/sub form setup.
On the subform though I would have like a layout of controls in lets say a
3x3
matrix. And after the first 3 controls had been filled I would need to
insert there values into a table to represent a record. See I have like a
survey to build on the subform and each row of different types of controls
gets filled out. On each row that does I would insert record into sub forms
table. How would I go about inserting the records into the table manually? I
have programmed in VBA before. So whats the cleanest way to populate the
table? Code Samples would be great.
I'm thinking I would have a forth control that was a command button to
launch some code that would put the data into the table. But not sure
cleanest or best way to do this?

Thanks,

JJ
 
J

John Nurick

I don't understand your specific situation, but in general there are two
ways to create records using data entered into an unbound form (or
subform) by running code in the form (or subform)

1) Recordset operations. Air code something like this, assuming the
field names are "First", "Second" etc. and the corresponding controls on
the form are named "txtFirst" etc.:

Dim rsR as DAO.Recordset

Set rsR = CurrentDB.OpenRecordset("My Table")
With rsR
.AddNew
.Fields("First").Value = Me.txtFirst.Value
.Fields("Second").Value = Me.txtSecond.Value
...
.Update
.Close
End With

2) Single-record Append query. Air code:

Dim strSQL As String

'Build the SQL statemetn to append a record
'with the valuse from the form
strSQL = "INSERT INTO MyTable (First, Second, Third) VALUES ('" _
& Me.txtFirst.Value & "','" & Me.txtSecond.Value & "','" _
& Me.txtThird.Value & "');"
'Execute it
CurrentDB.Execute strSQL

Doing it myself I'd probably use the SQL route. Either way will need
some additional code to validate what the user has entered and handle
errors.
 
Top