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

[Guest]

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

 

[Guest]

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

 

[Guest]

Wow...I am blown away. As I look through it, it logically seems to make
sense. I will give it a try and get back to you. THANKS SO MUCH!

Patk

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.)

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

 

[Guest]

Steve: I have adapted your code (Hopefully, correctly) as follows, but am
getting an error on execution

Run-time error '3061'
Too few parameters. Expected 3.

Here is the code, as it stands:

Option Compare Database
Option Explicit
'this is the name of the form control to execute
Private Sub Command4_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
' in your initial code, you had the Form fields last....I wanted them to
be first (prepend) so I put the FF fields first.
strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

'this is temp table rows . change tbl_Template to the name of your table
'I am simply using the name tbl_Template...good as any table, and this
matches DB.
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
' I made these changes, however, in your code, you had the fields as
simply F1, F2, F3. I assume you meant FF1, FF2, and FF3. Let me know if not
correct.

strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS FF1, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS FF2, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS FF3"

' I tried adding an additional single quote before FF3 in the above line,
but that resulted in a different error (run time error 3067: query input must
contain at least one table or query).

'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

What did I miss, or did I transpose something in building the sql string?

Thanks! I think this is darn close.....and I REALLY appreciate your taking
the time. Pat

Wow...I am blown away. As I look through it, it logically seems to make
sense. I will give it a try and get back to you. THANKS SO MUCH!

Patk

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.)

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

 

[Guest]

Sorry....found an obvious error..did not have the tbl_template fields
properly named. I still get the 3067 error, but here is the current code:

Option Compare Database
Option Explicit

Private Sub Command4_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_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

'this is temp table rows . change tbl_Template to the name of your table
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

' 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]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS FF1, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS FF2, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS FF3'"

'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

 

[Guest]

Pat,

It looks like there is an extra (') at the end of one line

Expanded, the line looks like & " ' AS FF3 ' "

It should be & "' AS FF3 "
(expanded & " ' AS FF3 " )

See if that fixes the error

For debugging I use:

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

MsgBox strSQL '<< FOR DEBUGGING

'db.Execute strSQL, dbFailOnError ' < comment out so it doesn't run

' the next two lines can be deleted if you want
'-------SNIP-----------

 

[Guest]

I am trying to capture that message window as exactly as possible (after
eliminating the extra single quote). Here is the strSQL field displayed in
the MsgBox:

INSERT INTO tbl_CKLT (FF1, FF2, FF3, RF_Outline_Number, RF_Descriptor,
RF_Multiplier ) Select tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, 'Test' AS FF1,
'Assisted Customer Support (CP)' AS FF2, '2' as FF3 FROM tbl_Template;

The form input values look right. If I do execute the db.Execute, i get
this error: Run-time error '3127': The INSERT INTO statement contains the
following unknown field name: 'FF1'. Make sure you have typed the name
correctly, and try the operation again. Here is the current code:

Option Compare Database
Option Explicit

Private Sub Command4_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_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

'this is temp table rows . change tbl_Template to the name of your table
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

' 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]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS FF1, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS FF2, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS FF3"

'change tbl_Template to the name of your temp table
strSQL = strSQL & " FROM tbl_Template;"
MsgBox strSQL
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






Hope I got that right

Pat,

It looks like there is an extra (') at the end of one line

Expanded, the line looks like & " ' AS FF3 ' "

It should be & "' AS FF3 "
(expanded & " ' AS FF3 " )

See if that fixes the error

For debugging I use:

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

MsgBox strSQL '<< FOR DEBUGGING

'db.Execute strSQL, dbFailOnError ' < comment out so it doesn't run

' the next two lines can be deleted if you want
'-------SNIP-----------


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

Sorry....found an obvious error..did not have the tbl_template fields
properly named. I still get the 3067 error, but here is the current code:

Option Compare Database
Option Explicit

Private Sub Command4_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_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

'this is temp table rows . change tbl_Template to the name of your table
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

' 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]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS FF1, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS FF2, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS FF3'"

'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

 

[Guest]

Pat,

I see the problem...... you changed theorder of the fields in the INSERT
clause!!!

This is the way I wrote it (old field names): \/ \/
strSQL = "INSERT INTO tbl_Checklist ( CKL_ItemNo, CKL_Description,
CKL_Complexity, FF1, FF2, FF3 )"

This is what you changed it to: \/ \/ \/
strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

The order of the fields in the INSERT part *MUST* be in the same order as
the data being inserted.

change this line:

strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

to:

strSQL = "INSERT INTO tbl_CKLT ( RF_Outline_Number, > RF_Descriptor,
RF_Multiplier, FF1, FF2, FF3)"

See if that makes a difference!

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

I am trying to capture that message window as exactly as possible (after
eliminating the extra single quote). Here is the strSQL field displayed in
the MsgBox:

INSERT INTO tbl_CKLT (FF1, FF2, FF3, RF_Outline_Number, RF_Descriptor,
RF_Multiplier ) Select tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, 'Test' AS FF1,
'Assisted Customer Support (CP)' AS FF2, '2' as FF3 FROM tbl_Template;

The form input values look right. If I do execute the db.Execute, i get
this error: Run-time error '3127': The INSERT INTO statement contains the
following unknown field name: 'FF1'. Make sure you have typed the name
correctly, and try the operation again. Here is the current code:

Option Compare Database
Option Explicit

Private Sub Command4_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_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

'this is temp table rows . change tbl_Template to the name of your table
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

' 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]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS FF1, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS FF2, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS FF3"

'change tbl_Template to the name of your temp table
strSQL = strSQL & " FROM tbl_Template;"
MsgBox strSQL
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






Hope I got that right

Pat,

It looks like there is an extra (') at the end of one line

Expanded, the line looks like & " ' AS FF3 ' "

It should be & "' AS FF3 "
(expanded & " ' AS FF3 " )

See if that fixes the error

For debugging I use:

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

MsgBox strSQL '<< FOR DEBUGGING

'db.Execute strSQL, dbFailOnError ' < comment out so it doesn't run

' the next two lines can be deleted if you want
'-------SNIP-----------


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

Sorry....found an obvious error..did not have the tbl_template fields
properly named. I still get the 3067 error, but here is the current code:

Option Compare Database
Option Explicit

Private Sub Command4_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_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

'this is temp table rows . change tbl_Template to the name of your table
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

' 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]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS FF1, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS FF2, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS FF3'"

'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

 

[Guest]

Sorry..I did that because the order of the data and how it existed in the
output table. Maybe I have a very wrong basic assumption (think so): Do the
fields FF1, FF2, and FF3 have to be defined fields in the output table? IF
so, then I need to rename them to match. Also, the RF fields are coming from
the template file, but going INTO the tbl_CKLT...do I have to move the data
incoming from the template file into fields named same as in tbl_CKLT? I
guess that is my disconnect...how does it know that I want to put data from,
say the template file, into another field in the CKLT table field name? That
is where I am lost.

Patk

Pat,

I see the problem...... you changed theorder of the fields in the INSERT
clause!!!

This is the way I wrote it (old field names): \/ \/
strSQL = "INSERT INTO tbl_Checklist ( CKL_ItemNo, CKL_Description,
CKL_Complexity, FF1, FF2, FF3 )"

This is what you changed it to: \/ \/ \/
strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

The order of the fields in the INSERT part *MUST* be in the same order as
the data being inserted.

change this line:

strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

to:

strSQL = "INSERT INTO tbl_CKLT ( RF_Outline_Number, > RF_Descriptor,
RF_Multiplier, FF1, FF2, FF3)"

See if that makes a difference!

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

I am trying to capture that message window as exactly as possible (after
eliminating the extra single quote). Here is the strSQL field displayed in
the MsgBox:

INSERT INTO tbl_CKLT (FF1, FF2, FF3, RF_Outline_Number, RF_Descriptor,
RF_Multiplier ) Select tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, 'Test' AS FF1,
'Assisted Customer Support (CP)' AS FF2, '2' as FF3 FROM tbl_Template;

The form input values look right. If I do execute the db.Execute, i get
this error: Run-time error '3127': The INSERT INTO statement contains the
following unknown field name: 'FF1'. Make sure you have typed the name
correctly, and try the operation again. Here is the current code:

Option Compare Database
Option Explicit

Private Sub Command4_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_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

'this is temp table rows . change tbl_Template to the name of your table
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

' 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]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS FF1, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS FF2, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS FF3"

'change tbl_Template to the name of your temp table
strSQL = strSQL & " FROM tbl_Template;"
MsgBox strSQL
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






Hope I got that right

Pat,

It looks like there is an extra (') at the end of one line

Expanded, the line looks like & " ' AS FF3 ' "

It should be & "' AS FF3 "
(expanded & " ' AS FF3 " )

See if that fixes the error

For debugging I use:

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

MsgBox strSQL '<< FOR DEBUGGING

'db.Execute strSQL, dbFailOnError ' < comment out so it doesn't run

' the next two lines can be deleted if you want
'-------SNIP-----------


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


:

Sorry....found an obvious error..did not have the tbl_template fields
properly named. I still get the 3067 error, but here is the current code:

Option Compare Database
Option Explicit

Private Sub Command4_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_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

'this is temp table rows . change tbl_Template to the name of your table
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

' 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]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS FF1, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS FF2, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS FF3'"

'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

 

[Guest]

output table. Maybe I have a very wrong basic assumption (think so): Do the

fields FF1, FF2, and FF3 have to be defined fields in the output table?
YES

so, then I need to rename them to match. Also, the RF fields are coming from
the template file, but going INTO the tbl_CKLT...do I have to move the data
incoming from the template file into fields named same as in tbl_CKLT?

NO

Change the FF1 - 3 in the following line to the field names in tbl_CKLT

strSQL = "INSERT INTO tbl_CKLT (RF_Outline_Number, RF_Descriptor,
RF_Multiplier, FF1, FF2, FF3)"

and the code should run. (it did in my test mdb <g>)

~~~~
From Help (such as it is ):

"
INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

You can also use INSERT INTO to append a set of records from another table
or query by using the SELECT ... FROM clause as shown above in the
multiple-record append query syntax. In this case, the SELECT clause
specifies the fields to append to the specified target table.


target =
The name of the table or query to append records *to*.

field1, field2 =
Names of the fields to append data to.
(They have to match the *names* of the fields in target, but not the
order.)

source =
The name of the table or query to copy records *from*.
(They have to match the *order and datatypes* of the fields in target, but
not names.)

tableexpression =
The name of the table or tables (query) from which records are inserted.

value1, value2 =
The values to insert into the specific fields of the new record. Each value
is inserted into the field that corresponds to the value's position in the
list: value1 is inserted into field1 of the new record, value2 into field2,
and so on. You must separate values with a comma, and enclose text fields in
quotation marks (' ').

(And must have the same number of fields as the number of values)



So, what is the name of the table where you want to append the records?
destination table = tbl_CKLT

What are the names of the fields in the destination table?
RF_Outline_Number,
RF_Descriptor,
RF_Multiplier,
FF1, \
FF2, you need to rename these if they are not in tbl_CKLT
FF3 /

What is the source table name?
source table = tbl_Template

What are the names of the fields in the source table?
RF_Outline_Number,
RF_Descriptor,
RF_Multiplier
[Forms]![frm_GenerateAssetChecklist]![inputAsset] AS FF1,
[Forms]![frm_GenerateAssetChecklist]![inputPlatform] AS FF2,
[Forms]![frm_GenerateAssetChecklist]![inputComplexity] AS FF3
~~~~


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

Sorry..I did that because the order of the data and how it existed in the
output table. Maybe I have a very wrong basic assumption (think so): Do the
fields FF1, FF2, and FF3 have to be defined fields in the output table? IF
so, then I need to rename them to match. Also, the RF fields are coming from
the template file, but going INTO the tbl_CKLT...do I have to move the data
incoming from the template file into fields named same as in tbl_CKLT? I
guess that is my disconnect...how does it know that I want to put data from,
say the template file, into another field in the CKLT table field name? That
is where I am lost.

Patk

Pat,

I see the problem...... you changed theorder of the fields in the INSERT
clause!!!

This is the way I wrote it (old field names): \/ \/
strSQL = "INSERT INTO tbl_Checklist ( CKL_ItemNo, CKL_Description,
CKL_Complexity, FF1, FF2, FF3 )"

This is what you changed it to: \/ \/ \/
strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

The order of the fields in the INSERT part *MUST* be in the same order as
the data being inserted.

change this line:

strSQL = "INSERT INTO tbl_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

to:

strSQL = "INSERT INTO tbl_CKLT ( RF_Outline_Number, > RF_Descriptor,
RF_Multiplier, FF1, FF2, FF3)"

See if that makes a difference!

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

I am trying to capture that message window as exactly as possible (after
eliminating the extra single quote). Here is the strSQL field displayed in
the MsgBox:

INSERT INTO tbl_CKLT (FF1, FF2, FF3, RF_Outline_Number, RF_Descriptor,
RF_Multiplier ) Select tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, 'Test' AS FF1,
'Assisted Customer Support (CP)' AS FF2, '2' as FF3 FROM tbl_Template;

The form input values look right. If I do execute the db.Execute, i get
this error: Run-time error '3127': The INSERT INTO statement contains the
following unknown field name: 'FF1'. Make sure you have typed the name
correctly, and try the operation again. Here is the current code:

Option Compare Database
Option Explicit

Private Sub Command4_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_CKLT ( FF1, FF2, FF3, RF_Outline_Number,
RF_Descriptor, RF_Multiplier )"

'this is temp table rows . change tbl_Template to the name of your table
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

' 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]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS FF1, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS FF2, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS FF3"

'change tbl_Template to the name of your temp table
strSQL = strSQL & " FROM tbl_Template;"
MsgBox strSQL
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






Hope I got that right

 

[Guest]

I think I am really close. That last post cleared up a lot of things for me,
I think. I am now down to the point that I am getting an error on the
db.execute "data type mismatch in criteria expression.:

I think that may be due to either the fact that the inputComplexity value is
coming in as a text field (I think this because of the single quotes) and
maybe i just need to remove the single quotes...I will try that. Or, the
boolean field I am trying to initialize to FALSE (or NO...tried both) is not
correct. Initially, I just want to set CKLT_Complete to NO (false) in every
row, so there is nothing to accept from the form or the DB, to fill this out.

HEre is the current code (gosh...I HAVE to be close???):
Private Sub Command4_Click()
Dim db As Database
Dim strSQL As String
Dim i As Long
Dim booValue As Boolean

Set db = CurrentDb
booValue = False

' these are the fields in the destination table -> tbl_Checklist
strSQL = "INSERT INTO tbl_CKLT ( CKLT_Item_Num, CKLT_Description,
CKLT_Multiplier, CKLT_Asset, CKLT_Platform, CKLT_Complexity, CKLT_Complete )"

'this is temp table rows . change tbl_Template to the name of your table
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

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

MsgBox [Forms]![frm_GenerateAssetChecklist]![inputAsset]
MsgBox [Forms]![frm_GenerateAssetChecklist]![inputPlatform]
MsgBox [Forms]![frm_GenerateAssetChecklist]![inputComplexity]


strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS CKLT_Asset, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS CKLT_Platform, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS CKLT_Complexity, '"
strSQL = strSQL & "NO" & "' AS CKLT_Complete "
'change tbl_Template to the name of your temp table
strSQL = strSQL & " FROM tbl_Template;"
MsgBox strSQL
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

 

[Guest]

By jove, I got it. THANKS A MILLION, STEVE! I owe you big time. Mismatch
issue was the yes/no field and the way I was inserting that value into the
sql string. Here is the final code:

Private Sub Command4_Click()
Dim db As Database
Dim strSQL As String
Dim i As Long
Dim booValue As Boolean

Set db = CurrentDb
booValue = False

' these are the fields in the destination table -> tbl_CKLT
strSQL = "INSERT INTO tbl_CKLT ( CKLT_Item_Num, CKLT_Description,
CKLT_Multiplier, CKLT_Asset, CKLT_Platform, CKLT_Complexity, CKLT_Complete )"

'this is temp table rows.
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS CKLT_Asset, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS CKLT_Platform, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS CKLT_Complexity, "
strSQL = strSQL & False & " AS CKLT_Complete "

strSQL = strSQL & " FROM tbl_Template;"
'MsgBox strSQL
db.Execute strSQL, dbFailOnError

' Output information about the numbers of rows added.
i = db.RecordsAffected
MsgBox i & " records (rows) were added"
End Sub

Again, my sincere thanks...this will help solve a process that will need to
be done many times. By the way, the template file could contain hundreds of
times, so entering these values into the DB every time a new asset is
introduced would have been painful, at best (even if we just did excel
imports...which was the original plan). This is SOOO much slicker!

Patk

 

[Guest]

Wonderful.

IMPORTANT!!! You should put code at the beginning to check if the unbound
controls are NULL - the code *will* bomb if any of the controls do not have
an entry. I'm not sure if it will bomb if any of the fields in the template
table are NULL - you'll have to try it.


Since you defined a variable booValue as Boolean, then set it to FALSE, I
was going to suggest using
\/
strSQL = strSQL & booValue & " AS CKLT_Complete "


If you stay with using FALSE in the line (not wrong, just another way), then
you should delete the booValue lines - don't want extra code laying around to
confuse yourself later.

Since we are talking about "other ways", you could have run the "INSERT
INTO" statement without the data from the 3 unbound (plus the boolean field),
then run 4 "UPDATE" queries to fill in the 4 fields.

But as long as it is working .......

 

[Guest]

For some reason, my browser went flaky when I typed a response to your last
message, so apologize if this is redundant. I have, indeed, made the change
relate to the booValue and it is working fine. My next challenge is to test
to make sure, before I even load these records, if records for this asset do
not already exist. Right now, I can add the same asset name, multiple times
(and have done so during my testing), but I do not want that to happen in
real life.

Anyway, thanks again for all your help in getting me over this hump. You
are the best...patk

Wonderful.

IMPORTANT!!! You should put code at the beginning to check if the unbound
controls are NULL - the code *will* bomb if any of the controls do not have
an entry. I'm not sure if it will bomb if any of the fields in the template
table are NULL - you'll have to try it.


Since you defined a variable booValue as Boolean, then set it to FALSE, I
was going to suggest using
\/
strSQL = strSQL & booValue & " AS CKLT_Complete "


If you stay with using FALSE in the line (not wrong, just another way), then
you should delete the booValue lines - don't want extra code laying around to
confuse yourself later.

Since we are talking about "other ways", you could have run the "INSERT
INTO" statement without the data from the 3 unbound (plus the boolean field),
then run 4 "UPDATE" queries to fill in the 4 fields.

But as long as it is working .......

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

By jove, I got it. THANKS A MILLION, STEVE! I owe you big time. Mismatch
issue was the yes/no field and the way I was inserting that value into the
sql string. Here is the final code:

Private Sub Command4_Click()
Dim db As Database
Dim strSQL As String
Dim i As Long
Dim booValue As Boolean

Set db = CurrentDb
booValue = False

' these are the fields in the destination table -> tbl_CKLT
strSQL = "INSERT INTO tbl_CKLT ( CKLT_Item_Num, CKLT_Description,
CKLT_Multiplier, CKLT_Asset, CKLT_Platform, CKLT_Complexity, CKLT_Complete )"

'this is temp table rows.
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS CKLT_Asset, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS CKLT_Platform, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS CKLT_Complexity, "
strSQL = strSQL & False & " AS CKLT_Complete "

strSQL = strSQL & " FROM tbl_Template;"
'MsgBox strSQL
db.Execute strSQL, dbFailOnError

' Output information about the numbers of rows added.
i = db.RecordsAffected
MsgBox i & " records (rows) were added"
End Sub

Again, my sincere thanks...this will help solve a process that will need to
be done many times. By the way, the template file could contain hundreds of
times, so entering these values into the DB every time a new asset is
introduced would have been painful, at best (even if we just did excel
imports...which was the original plan). This is SOOO much slicker!

Patk

 

[Guest]

relate to the booValue and it is working fine. My next challenge is to test

to make sure, before I even load these records, if records for this asset do
not already exist.

Check out this page for SQL. Towards the bottom of the page, in the FAQ
section, is an example of the EXISTS clause of the INSERT INOT statement.

http://www.techonthenet.com/sql/insert.php


Using the subquery will not enter duplicate records. No extra code!

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

For some reason, my browser went flaky when I typed a response to your last
message, so apologize if this is redundant. I have, indeed, made the change
relate to the booValue and it is working fine. My next challenge is to test
to make sure, before I even load these records, if records for this asset do
not already exist. Right now, I can add the same asset name, multiple times
(and have done so during my testing), but I do not want that to happen in
real life.

Anyway, thanks again for all your help in getting me over this hump. You
are the best...patk

Wonderful.

IMPORTANT!!! You should put code at the beginning to check if the unbound
controls are NULL - the code *will* bomb if any of the controls do not have
an entry. I'm not sure if it will bomb if any of the fields in the template
table are NULL - you'll have to try it.


Since you defined a variable booValue as Boolean, then set it to FALSE, I
was going to suggest using
\/
strSQL = strSQL & booValue & " AS CKLT_Complete "


If you stay with using FALSE in the line (not wrong, just another way), then
you should delete the booValue lines - don't want extra code laying around to
confuse yourself later.

Since we are talking about "other ways", you could have run the "INSERT
INTO" statement without the data from the 3 unbound (plus the boolean field),
then run 4 "UPDATE" queries to fill in the 4 fields.

But as long as it is working .......

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

By jove, I got it. THANKS A MILLION, STEVE! I owe you big time. Mismatch
issue was the yes/no field and the way I was inserting that value into the
sql string. Here is the final code:

Private Sub Command4_Click()
Dim db As Database
Dim strSQL As String
Dim i As Long
Dim booValue As Boolean

Set db = CurrentDb
booValue = False

' these are the fields in the destination table -> tbl_CKLT
strSQL = "INSERT INTO tbl_CKLT ( CKLT_Item_Num, CKLT_Description,
CKLT_Multiplier, CKLT_Asset, CKLT_Platform, CKLT_Complexity, CKLT_Complete )"

'this is temp table rows.
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS CKLT_Asset, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS CKLT_Platform, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS CKLT_Complexity, "
strSQL = strSQL & False & " AS CKLT_Complete "

strSQL = strSQL & " FROM tbl_Template;"
'MsgBox strSQL
db.Execute strSQL, dbFailOnError

' Output information about the numbers of rows added.
i = db.RecordsAffected
MsgBox i & " records (rows) were added"
End Sub

Again, my sincere thanks...this will help solve a process that will need to
be done many times. By the way, the template file could contain hundreds of
times, so entering these values into the DB every time a new asset is
introduced would have been painful, at best (even if we just did excel
imports...which was the original plan). This is SOOO much slicker!

Patk

 

[Guest]

That did the trick! Thanks Steve. Here is the code as it now stands (and
works!):

Option Compare Database
Option Explicit

Private Sub Command4_Click()
Dim db As Database
Dim strSQL As String
Dim i As Long
Dim booValue As Boolean
Dim strAsset As String

Set db = CurrentDb
booValue = False
strAsset = [Forms]![frm_GenerateAssetChecklist]![inputAsset]
'MsgBox "Form: " & strAsset
' these are the fields in the destination table -> tbl_CKLT
strSQL = "INSERT INTO tbl_CKLT ( CKLT_Item_Num, CKLT_Description,
CKLT_Multiplier, CKLT_Asset, CKLT_Platform, CKLT_Complexity, CKLT_Complete )"

'this is temp table rows.
strSQL = strSQL & " SELECT tbl_Template.RF_Outline_Number,
tbl_Template.RF_Descriptor, tbl_Template.RF_Multiplier, '"

strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputAsset] & "'
AS CKLT_Asset, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputPlatform] &
"' AS CKLT_Platform, '"
strSQL = strSQL & [Forms]![frm_GenerateAssetChecklist]![inputComplexity]
& "' AS CKLT_Complexity, "
strSQL = strSQL & booValue & " AS CKLT_Complete "

strSQL = strSQL & " FROM tbl_Template WHERE not exists (SELECT * from
tbl_CKLT WHERE CKLT_Asset = '" & strAsset & "' ) ;"
'MsgBox strSQL
db.Execute strSQL, dbFailOnError

' Output information about the numbers of rows added.
i = db.RecordsAffected
If i <> 0 Then
MsgBox "A checklist of " & i & " records (rows) were added to the
RMA database."
Else
MsgBox "Zero rows added. This might be due to the fact that the
asset already exists in the checklist table."
End If

End Sub

 

[SteveS]

Excellent! I wasn't sure you were still watching this thread.... amazing how
things fall together to get the results you want.