INSERT INTO with mix sources of Field Data and Constant

[Kc-Mass]

I need to fill tblA from within VBA code.

Two of the fields in tblB will provide data for two of the fields in tblA.

I think that is pretty straight forward as in:

strInsertSQL = "INSERT INTO tblA(fld1, fld2) " _
& "Select fldA, fldB " _
& "From tblB ;"
DoCmd.RunSQL (strInsertSQL)

Can I, in the same statement, INSERT a repeating Value for fld3.
The Value would be a string equating to the FieldName of fldC in tblB.
I can get the fieldName into a variable, strFldName but do not know
the syntax to include it in the INSERT statement.

Access help file does not help.

I think I could do it with first an INSERT and then an UPDATE but would
prefer
something where the process either succeeds or fails as one.

Thanks in advance for any help.

[Gord]

Kc-Mass wrote:

I need to fill tblA from within VBA code.

Two of the fields in tblB will provide data for two of the fields in tblA.

I think that is pretty straight forward as in:

strInsertSQL = "INSERT INTO tblA(fld1, fld2) " _
& "Select fldA, fldB " _
& "From tblB ;"
DoCmd.RunSQL (strInsertSQL)

Can I, in the same statement, INSERT a repeating Value for fld3.
The Value would be a string equating to the FieldName of fldC in tblB.
I can get the fieldName into a variable, strFldName but do not know
the syntax to include it in the INSERT statement.

Access help file does not help.

I think I could do it with first an INSERT and then an UPDATE but would
prefer
something where the process either succeeds or fails as one.

Try something like this:

strText = "foo"
strInsertSQL = _
"INSERT INTO tblA(fld1, fld2, fld3) " _
& "Select fldA, fldB, '" & strText & "' AS Expr1 " _
& "From tblB ;"

[Kc-Mass]

Terrific. Thank you very much!
"Gord" <gd*@kingston.netwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

>
Kc-Mass wrote:

>I need to fill tblA from within VBA code.

Two of the fields in tblB will provide data for two of the fields in
tblA.

I think that is pretty straight forward as in:

strInsertSQL = "INSERT INTO tblA(fld1, fld2) " _
& "Select fldA, fldB " _
& "From tblB ;"
DoCmd.RunSQL (strInsertSQL)

Can I, in the same statement, INSERT a repeating Value for fld3.
The Value would be a string equating to the FieldName of fldC in tblB.
I can get the fieldName into a variable, strFldName but do not know
the syntax to include it in the INSERT statement.

Access help file does not help.

I think I could do it with first an INSERT and then an UPDATE but would
prefer
something where the process either succeeds or fails as one.

Try something like this:

strText = "foo"
strInsertSQL = _
"INSERT INTO tblA(fld1, fld2, fld3) " _
& "Select fldA, fldB, '" & strText & "' AS Expr1 " _
& "From tblB ;"