How to add values to different table with combobox?

Question

So basically i am working on a project with microsoft access 2007.

I have a form with a combobox - comboTrainings  
and also a textbox - txtBoxTrainings - longText

and i have created 3 tables - tableA. tableB and tableC.

so what i want to achieve in the form is to let the user select from the combobox in which the items inside are tableA, tableB and tableC. When the user select comboBox value and enter a text in the textbox, the value will be pass inside.

example - combo box select tableA with textbox value "hello", - the data will be inserted into tableA. 

how can i achieve this?

Thanks.

Answer 1

Hi,

Not sure exactly what you're trying to do but you could add a Button on your form so when the user clicks it, the Textbox will save the entry into the table selected from the Combobox.

The code for the button might look something like this (assuming all tables have the same name for the field to enter the value in the Textbox):

Dim strSQL As String

strSQL = "INSERT INTO " & Me.ComboboxName & " (FieldName) VALUES ('" & Me.TextboxName & "')"

CurrentDb.Execute strSQL, dbFailOnError

Hope it helps...

Answer 2

This is what i essentially want to achieve.

however, i still receive a few errors upon running the mentioned code. 

Pls advise :D

Private Sub addButton_Click()

Dim strSQL As String


strSQL = strSQL = "INSERT INTO " & Me.comboTrainings & " (Training) VALUES ('" & Me.txtBoxTrainings & "')"
                  
CurrentDb.Execute strSQL, dbFailOnError
End Sub

This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs.

thanks in advance.

 

Answer 3

Hi,

On your form, make sure you don't have any code behind the combo and the textbox just to eliminate any conflict for now.

Also, what are the names of your tables? Do they have spaces in them? If so, add enclosing square brackets in your code. Is the combobox set up to only retrieve one column? Is it a Value List?

Answer 4

Hi,

I managed to make it work for now. However, i have a table name with a special character - "EH&S"

Is it advisable to not use special characters in table? 

Sorry i'm totally asking a noob questions.

Thanks

Answer 5

Hi,

Yes, it's highly recommend to avoid using spaces and special characters in naming your database objects. But just to be safe, you can get into the habit of enclosing the names in square brackets.

Good luck with your project.