none
How to add values to different table with combobox? RRS feed

  • 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.


    • Edited by syahid noor Tuesday, May 15, 2018 12:24 AM
    • Changed type syahid noor Wednesday, May 23, 2018 4:48 AM
    Tuesday, May 15, 2018 12:18 AM

Answers

  • 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...

    • Marked as answer by syahid noor Wednesday, May 23, 2018 4:48 AM
    Tuesday, May 15, 2018 12:49 AM

All replies

  • 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...

    • Marked as answer by syahid noor Wednesday, May 23, 2018 4:48 AM
    Tuesday, May 15, 2018 12:49 AM
  • 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.

     
    Tuesday, May 15, 2018 1:01 AM
  • 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?

    Tuesday, May 15, 2018 1:04 AM
  • 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

    Tuesday, May 15, 2018 1:08 AM
  • 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.

    Tuesday, May 15, 2018 2:44 PM