RE: How Do I Extract Data from my Form to load new table records?

I whipped up a couple of tables, a form and some code...
hope this is what you are looking for.. :)


'watch for line wrap
'*****beg code***************
Option Compare Database
Option Explicit

Private Sub Generate_Click()
Dim db As Database
Dim strSQL As String
Dim i As Long

Set db = CurrentDb

' these are the fields in the destination table -> tbl_Checklist
strSQL = "INSERT INTO tbl_Checklist ( CKL_ItemNo, CKL_Description,
CKL_Complexity, FF1, FF2, FF3 )"

'this is temp table rows . change tbl_Template to the name of your table
strSQL = strSQL & " SELECT tbl_Template.CKL_ItemNo,
tbl_Template.CKL_Description, tbl_Template.CKL_Complexity, '"

' these are the controls on the form
' change "Form5" to the name of your form
' change tbF1, tbF2, tbF3to the names of your controls

strSQL = strSQL & [Forms]![Form5]![tbF1] & "' AS F1, '"
strSQL = strSQL & [Forms]![Form5]![tbF2] & "' AS F2, '"
strSQL = strSQL & [Forms]![Form5]![tbF3] & "' AS F3"

'change tbl_Template to the name of your temp table
strSQL = strSQL & " FROM tbl_Template;"

db.Execute strSQL, dbFailOnError

' the next two lines can be deleted if you want
i = db.RecordsAffected
MsgBox i & " records (rows) were added"
End Sub
'*****end code***************

If I didn't comment well enough, post back.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"PatK" wrote:

I am VERY new to this, so sorry for the dumb question. I have the best MS
Access books you can by, and they all take for granted you know how to do
this, so gloss over it.

I have the need to create skeleton our outline records in a table. Assume
the destination table is called tbl_Checklist. I have create a form from
which I allow three fields to be input (unbound text boxes). I then hit a
button I created call Generate. The intent is that this then executes code
to:

1) Open a "template table with a number of predefined "standard" rows
2) Read these rows one at a time
3) Take data from these rows PLUS the three fields from the unbound text boxes
4) Write this data out to my empty tbl_Checklist table.

The template table data looks like this:

CKL_ItemNo (numeric) CKL_Description (text) CKL_Complexity (Numeric)
100.10 Description 1 1
100.20 Description 2 2
100.50 Description 3 1
...etc

When I am done, I want my Tbl_Checklist to look something like:
Formfield 1 (text) Formfield2 (txt) Formfield3 (txt) CKL_ItemNo (numeric)
CKL_Description (text) CKL_Complexity (Numeric)
or

FF1 FF2 FF3 100.10 Description 1 1
FF1 FF2 FF3 100.20 Description 2 2
FF1 FF2 FF3 100.50 Description 3 1 ....etc

Basically, I am creating a standard set of skeleton data. If my Template
file has 10 records in it, then every time I execute this, I would be adding
all the data from the template file, plus the same three input fields, to
each of the 10 records, and thus, loading 10 records every time I hit the
generate button.

Any thought? I am basically ok with the coding part, but cannot figure out
how to reference the form fields, in my code. ANy good coding examples of
what is probably hand extremely simply form-to-code process?

Thanks

.