VBA code to Insert a combo box selected value to a specified column in Access table. (1 Viewer)

[lekkala]

Hi everyone,

I have a table tblUserLog with 16 columns- A1, A2, so on till A16; these are decimal data type columns which has time taken by each user for designated area. All these values are entered via an input Time Sheet Form which has: Textbox1 - user enters time taken and Combo box - drop down box with options A1,A2,A3,.....,A16. I want to write VBA code to enter TextBox1. Value, using the combo box selected value. For example if user selects A1 in combo box then a SQL insert command should be executed to insert that value in A1 field in table.Can anyone please help me? Thank you.

 

[theDBguy]

Hi. Your description of your table structure makes it sound like you may have a bad (non-normalized) table design. If so, you should consider changing (normalizing) it. In any case, you can "construct" your SQL statement in code by concatenating the combobox value and then execute it.

 

[lekkala]

Hi. Your description of your table structure makes it sound like you may have a bad (non-normalized) table design. If so, you should consider changing (normalizing) it. In any case, you can "construct" your SQL statement in code by concatenating the combobox value and then execute it.

Sorry, I did not describe the complete table structure here. I do have a unique id key and other columns beside the list I mentioned above. The ID column and Assign columns are being inserted to the table via another input form which is User Assignment Form. So, basically I need to fill in A1, A2, ....A16 columns in the table using combo box selected value.

Here in below code how should I select Col name and Value? Please can you suggest the exact syntax?

Dim strSQL As String
strSQL = "INSERT INTO tblUserLog(Col1, Col2.......) Values(------)
DoCmd.RunSQL (strSQL)

 

[Isaac]

Just use a bound form. That way the value selected by the user is saved to the table.
The most common way to get a value from a form control into a table is to bind that form to a recordsource. When a value is updated on a form control, the value is automatically saved into the table.

I understand that the way you have your form set up makes this difficult. What you probably NEED to do is set up a form bound to the table, with comboboxes representing EACH of those columns .... I am not one of those "never use unbound forms" folk, but still, the way you are doing it seems harder.

 

[theDBguy]

Sorry, I did not describe the complete table structure here. I do have a unique id key and other columns beside the list I mentioned above. The ID column and Assign columns are being inserted to the table via another input form which is User Assignment Form. So, basically I need to fill in A1, A2, ....A16 columns in the table using combo box selected value.

Here in below code how should I select Col name and Value? Please can you suggest the exact syntax?

Dim strSQL As String
strSQL = "INSERT INTO tblUserLog(Col1, Col2.......) Values(------)
DoCmd.RunSQL (strSQL)

Okay, ignoring the table structure for the moment, here's what I imagine it would look like for one input.

strSQL="INSERT INTO tblUserLog(" & Me.ComboboxName & ") VALUES(" & Me.Textbox.Name & ")"

 

[James Dickinson]

ok cool cool, you can do this many ways. some of the better ways include...

-A table that holds only A1....A16 in a single column then bind your drop down to this table on your User Assignment Form.
-Or you could add a value list to your combo with A1;A2;A3;A4;A5;A6.......;A16 as the values

Your User Assignment Form needs a button or some kind of event.
in that event you want some code.
that code will need to include an execute statement which will look like...
currentdb.execute "INSERT INTO [YOUR TABLE NAME HERE]" _
& " ([THE FIELD YOU WANT TO INSERT INTO])" _
& " VALUES(" & Me.ComboCtlName & ")",dbfailonerror