Combo Box for Column Heading

D

Devon

Hello

I am attempting to automate the process of importing data from a
spreadsheet, then cleaning up the data on the spreadsheet. The spreadsheet I
am using comes from another area within the company. I am running a
DoCmd.TransferSpreadsheet acImport to accomplish the importing.

The spreadsheet is as follows:

Phone Number Carrier January_2009 February_2009 etc..
xxx-xxx-xxxx xxx 100.00 102.00
103.85
yyy-yyy-yyyy xxx 85.52 75.85
102.65

etc... represents the remaining months of 2009

The person in accounting is using this as summary data, similar to a
crosstab query from Access. I created an append query that inserts a new
column called month_year, and then changed the January_2009 column heading to
read monthly_charges.

My current SQL is as follows:

INSERT INTO tblMonthlyCharges ( IMSI, Carrier_Information, Phone_Number,
Month_Year, Monthly_Charge )
SELECT SIM_Data.IMSI, SIM_Data.Carrier_Information, SIM_Data.Phone_Number,
'December_2009' AS Month_Year, SPREADSHEET.December_2009 AS Monthly_Charge
FROM SIM_Data INNER JOIN SPREADSHEET ON SIM_Data.Phone_Number =
SPREADSHEET.[Phone Number];

The above works fine, but I would like to find a way to automate entering
the month_year data (e.g. December_2009). SPREADSHEET is the name of the
spreadsheet I import into the database.

I have created a form (frmAction) with an unbound combo box (cboDate), and
can update the following field by typing in the date (e.g. January_2009)
(Forms![frmAction].cboDate) AS Month_Year.

I would like to figure out how to use an unbound combo box to update the
monthly charge field for the following: SPREADSHEET.December_2009 AS
Monthly_Charge. As my subject heading indicates, since December_2009 is a
column heading, (Forms![frmAction].cboDate) does not appear to work.

Can anyone help me automate this final piece of the puzzle?

Thanks in advance

Devon
 
M

Marshall Barton

Devon said:
I am attempting to automate the process of importing data from a
spreadsheet, then cleaning up the data on the spreadsheet. The spreadsheet I
am using comes from another area within the company. I am running a
DoCmd.TransferSpreadsheet acImport to accomplish the importing.

The spreadsheet is as follows:

Phone Number Carrier January_2009 February_2009 etc..
xxx-xxx-xxxx xxx 100.00 102.00
103.85
yyy-yyy-yyyy xxx 85.52 75.85
102.65

etc... represents the remaining months of 2009

The person in accounting is using this as summary data, similar to a
crosstab query from Access. I created an append query that inserts a new
column called month_year, and then changed the January_2009 column heading to
read monthly_charges.

My current SQL is as follows:

INSERT INTO tblMonthlyCharges ( IMSI, Carrier_Information, Phone_Number,
Month_Year, Monthly_Charge )
SELECT SIM_Data.IMSI, SIM_Data.Carrier_Information, SIM_Data.Phone_Number,
'December_2009' AS Month_Year, SPREADSHEET.December_2009 AS Monthly_Charge
FROM SIM_Data INNER JOIN SPREADSHEET ON SIM_Data.Phone_Number =
SPREADSHEET.[Phone Number];

The above works fine, but I would like to find a way to automate entering
the month_year data (e.g. December_2009). SPREADSHEET is the name of the
spreadsheet I import into the database.

I have created a form (frmAction) with an unbound combo box (cboDate), and
can update the following field by typing in the date (e.g. January_2009)
(Forms![frmAction].cboDate) AS Month_Year.

I would like to figure out how to use an unbound combo box to update the
monthly charge field for the following: SPREADSHEET.December_2009 AS
Monthly_Charge. As my subject heading indicates, since December_2009 is a
column heading, (Forms![frmAction].cboDate) does not appear to work.

Can anyone help me automate this final piece of the puzzle?
Click to expand...


You can't parameterize s field name. Instead you can use
code to create the SQL that runs the query. Try something
like this (air code) in the Click event of the form button
that runs the query:

Dim SQL As String
SQL = "INSERT INTO tblMonthlyCharges ( IMSI, " _
& "Carrier_Information, Phone_Number, " _
& "Month_Year, Monthly_Charge ) " & _
"SELECT SIM_Data.IMSI, SIM_Data.Carrier_Information," _
& "SIM_Data.Phone_Number, " _
& '" & Me.combo1 & "' AS Month_Year, " _
& "SPREADSHEET." & Me.combo1 & "] AS Monthly_Charge " &
_
"FROM SIM_Data INNER JOIN SPREADSHEET " _
& "ON SIM_Data.Phone_Number = SPREADSHEET.[Phone
Number]"

DbEngin(0)(0).Execute SQL, dbFailOnError
MsgBox DbEngine.RecordsAffected & " records were added"
 
D

Devon

Marsh

This works great. Thanks for the information. It will definitely save me a
lot of time and effort.

Devon

Marshall Barton said:
Devon said:
I am attempting to automate the process of importing data from a
spreadsheet, then cleaning up the data on the spreadsheet. The spreadsheet I
am using comes from another area within the company. I am running a
DoCmd.TransferSpreadsheet acImport to accomplish the importing.

The spreadsheet is as follows:

Phone Number Carrier January_2009 February_2009 etc..
xxx-xxx-xxxx xxx 100.00 102.00
103.85
yyy-yyy-yyyy xxx 85.52 75.85
102.65

etc... represents the remaining months of 2009

The person in accounting is using this as summary data, similar to a
crosstab query from Access. I created an append query that inserts a new
column called month_year, and then changed the January_2009 column heading to
read monthly_charges.

My current SQL is as follows:

INSERT INTO tblMonthlyCharges ( IMSI, Carrier_Information, Phone_Number,
Month_Year, Monthly_Charge )
SELECT SIM_Data.IMSI, SIM_Data.Carrier_Information, SIM_Data.Phone_Number,
'December_2009' AS Month_Year, SPREADSHEET.December_2009 AS Monthly_Charge
FROM SIM_Data INNER JOIN SPREADSHEET ON SIM_Data.Phone_Number =
SPREADSHEET.[Phone Number];

The above works fine, but I would like to find a way to automate entering
the month_year data (e.g. December_2009). SPREADSHEET is the name of the
spreadsheet I import into the database.

I have created a form (frmAction) with an unbound combo box (cboDate), and
can update the following field by typing in the date (e.g. January_2009)
(Forms![frmAction].cboDate) AS Month_Year.

I would like to figure out how to use an unbound combo box to update the
monthly charge field for the following: SPREADSHEET.December_2009 AS
Monthly_Charge. As my subject heading indicates, since December_2009 is a
column heading, (Forms![frmAction].cboDate) does not appear to work.

Can anyone help me automate this final piece of the puzzle?
Click to expand...


You can't parameterize s field name. Instead you can use
code to create the SQL that runs the query. Try something
like this (air code) in the Click event of the form button
that runs the query:

Dim SQL As String
SQL = "INSERT INTO tblMonthlyCharges ( IMSI, " _
& "Carrier_Information, Phone_Number, " _
& "Month_Year, Monthly_Charge ) " & _
"SELECT SIM_Data.IMSI, SIM_Data.Carrier_Information," _
& "SIM_Data.Phone_Number, " _
& '" & Me.combo1 & "' AS Month_Year, " _
& "SPREADSHEET." & Me.combo1 & "] AS Monthly_Charge " &
_
"FROM SIM_Data INNER JOIN SPREADSHEET " _
& "ON SIM_Data.Phone_Number = SPREADSHEET.[Phone
Number]"

DbEngin(0)(0).Execute SQL, dbFailOnError
MsgBox DbEngine.RecordsAffected & " records were added"
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

make a combo box repeat for entire column? 3
Column heads for unbound combo box 4
Fill two column combo box 9
code for combo box 2
InputTitle and InputMessage for a column in combo box drop down 1

Top