Running a Query from a Combo box

[kent christensen]

Okay so i have been trying to do something within Access and cannot seem to get it to work.

Heres what i have:

i have 3 tables set up that are identical. The only difference is that the tables have different titles as they are databases for different customers.

I have a form called form1 that has:

A combo box (combo2) set up with the name of the three customer tables.

I have 4 text boxes(text1-text4) that are blank.

WHAT I WANT:
i was wondering if there was a way to be able to select the name of the customer table from the combo box, and then type data into the 4 text boxes. Then click a button that appends to the selected table the data of the text boxes into fields 3-6 (text box 4 would go in field 3, 5 into 6 ect ect...)

Is this possible. I know that i can create 3 different append queries but i was wondering if there was an easier way. My reasoning for this is that this sequence is set up as a pilot for three customers but will soon be expanded using all 250 of our customers. Do i really need to create 250 append queries or is there a short cut?

thanks in advance,
Kent

[MMcCarthy]

I think what you need is something like the following code presumably behind a command button.

Private Sub cmdButton1()
 
    DoCmd.RunSQL "INSERT INTO " & Me!ComboBox1 & " (Field1, Field2, Field3) VALUES(" & Me!txtBox1 & ", " & Me!txtBox2 & ", " & Me!txtBox3 & ")"
 
End Sub

Notes:

• Replace Field1, Field2, etc with your actual Field Names. This assumes each table has the same field names.
  
• Replace ComboBox1 with the name of your combo box and likewise for txtbox1 etc.
  
• There are also rules for passing values.
  
  If the field a value is being passed to has a datatype of text then you will have to add a single quote before and after txtbox control '" & Me!txtBox1 & "' and if the field has a date/time data type you have to add a hash #" & Me!txtBox1 & "#

[kent christensen]

First of all thank you MMcCarthy for helping me with my problem. So i did what you said but i am recieving an error that says "systax error in INSERT INTO statement". I am working in Access 2007 if that matters. I added more fields so the code that i am working on looks as follows:

Private Sub Command25_Click()
On Error GoTo Command25_Click_Err
 
 
 
    DoCmd.RunSQL "INSERT INTO " & Me!Combo23 & " (RMA #, Quote ID, Credit(USD), Credit(#), Credit(Euros), Credit(CAD), Credit(YEN), Credit(AUD)) VALUES(" & Me!Text5 & ", '" & Me!Text7 & "', " & Me!Text13 & ", " & Me!Text19 & ", " & Me!Text17 & ", " & Me!Text15 & ", " & Me!Text11 & ", " & Me!Text9 & ",)"
 
 
 
Command25_Click_Exit:
    Exit Sub
 
Command25_Click_Err:
    MsgBox Error$
    Resume Command25_Click_Exit
 
End Sub

Any idea how to fix this error?

[MMcCarthy]

Hi Kent

Access doesn't like spaces in field names and it doesn't like a number of other characters including I think brackets. To get around this enclose the field names in square brackets as follows:

DoCmd.RunSQL "INSERT INTO " & Me!Combo23 & " ([RMA #], [Quote ID], [Credit(USD)], [Credit(#)], [Credit(Euros)], [Credit(CAD)], [Credit(YEN)], [Credit(AUD)]) VALUES(" & Me!Text5 & ", '" & Me!Text7 & "', " & Me!Text13 & ", " & Me!Text19 & ", " & Me!Text17 & ", " & Me!Text15 & ", " & Me!Text11 & ", " & Me!Text9 & ",)"

[kent christensen]

Once agian thanks for the help, however,
Im still getting the same "systax error in INSERT INTO statement" message.

Any other ideas?

[MMcCarthy]

There was an extra comma at the end.

DoCmd.RunSQL "INSERT INTO " & Me!Combo23 & " ([RMA #], [Quote ID], [Credit(USD)], [Credit(#)], [Credit(Euros)], [Credit(CAD)], [Credit(YEN)], [Credit(AUD)]) VALUES(" & Me!Text5 & ", '" & Me!Text7 & "', " & Me!Text13 & ", " & Me!Text19 & ", " & Me!Text17 & ", " & Me!Text15 & ", " & Me!Text11 & ", " & Me!Text9 & ")"

[kent christensen]

I should have noticed that. Thanks. It works!!!
I have once more question for you, using the same combo box (combo23, is there a way to use a new command button that i can use to sum the fields in the table. For example i want to sum fields : Credit(USD), Credit(#), Credit(Euros), Credit(CAD), Credit(YEN), Credit(AUD).

Can this be done?

[MMcCarthy]

Ask it as a separate question. We don't allow multiple questions in a thread as it gets too confusing. Just start a new thread.