How do I use contents of a text box as a field name

[Chase]

Below is the SQL of a query where the field "Period1" needs to be the
contents of a text box on a form. How do I do this?

INSERT INTO tblTemp ( Period, Product, SubProduct1, SubProduct2,
[MR/NMR/OM], Channel, OutputDescription, [New/Existing], COMMONELEMENT,
[Value] )
SELECT tblCalendar.Period, tblTempInputData.Product,
tblTempInputData.SubProduct1, tblTempInputData.SubProduct2,
tblTempInputData.[MR/NMR/OM], tblTempInputData.Channel,
tblTempInputData.OutputDescription, tblTempInputData.[New/Existing],
tblTempInputData.COMMONELEMENT, tblTempInputData.[Period1] AS [Value]
FROM tblTempInputData, tblCalendar INNER JOIN tblTempfrmIn ON
tblCalendar.FldMth = tblTempfrmIn.FldMth
WHERE (((tblTempInputData.[Period1])<>0));

Regards

Chase

 

[mscertified]

[Forms]![Formname]![Fieldname]

-Dorian

 

[Dale Fye]

Chase,

In order to do this, you could:

1. Build the SQL string at runtime. Generally, this would occur in the
Click event of a command button, or something like that.

Private sub cmd_InsertRecord()

Dim strSQL as string

strSQL = "INSERT INTO tblTemp ( Period, Product, SubProduct1, " _
& "SubProduct2,
[MR/NMR/OM], " _
& "Channel,
OutputDescription, " _
& "[New/Existing],
COMMONELEMENT, " _
& "[Value] ) " _
& "SELECT tblCalendar.Period, TID.Product, " _
& "TID.SubProduct1, TID.SubProduct2, " _
& "TID.[MR/NMR/OM], TID.Channel, " _
& "TID.OutputDescription, TID.[New/Existing], " _
& "TID.COMMONELEMENT, TID.[Period1] AS [Value] "
& "FROM tblTempInputData as TID, " _
& "tblCalendar INNER JOIN tblTempfrmIn " _
& "ON tblCalendar.FldMth = tblTempfrmIn.FldMth " _
& "WHERE TID.[" Form!yourFormName.ControlName & "]<>0;"

Currentdb.execute strsql

End Sub

or

2. Save this query, and use it like:

Private sub cmd_InsertRecord()

Dim strSQL as string

strSQL = currentdb.querydefs("SavedQueryName").SQL
strSQL = REPLACE(strSQL, "Period1", Form!yourFormName.ControlName)
Currentdb.execute strsql

End Sub

In either routine, I would probable also add some error checking to ensure
that the name entered was actually the name of a field in the appropriate
table. You might even consider, rather than using a textbox, using a list or
combo box and set its RowSourceType to "FieldList". To do this, set the
RowSource property to the name of the table, then setting the RowSourceType
to fieldlist will result in that combo or listbox containing the names of the
fields in the table.

HTH
Dale

 

[Dale Fye]

BTW,

I would not use the word "Value" as a field name in Access. It is a
reserved word (like Date, Month, Year, ...) and should be avoided as a field
name.

Watch the word wrap in my previous post. I was hoping that the formatting I
was doing would work properly, but it appears to have wrapped.

Also, change "Form" to "Forms" in both code examples.

Dale