code not working

[bdjb]

Hello,
I have a form that filters from a combobox, then the user selects another item from a different combobox. The I want then to click a button that will take that data and append it to a table. I have this code in the on click of the button, but it doesn't do anything. Anyone able to see where I messed up?

Thanks
Option Compare Database

Private Sub Command17_Click()

Dim sSQL As String
sSQL = "Insert into [Training] (Employee_number, last_name, first_name, Job_Title, BPML)" _
& "Values (Forms!frmComboTest.employee, Forms!frmComboTest.Last_Name, Forms!frmComboTest.first_name, Forms!frmComboTest.field11,Forms!frmComboTest.combo11);"


End Sub

What happens if you get scared half to death twice?

Bob

 

[SgtJarrow]

Your tying to build a SQL statement, and you don't quite have the syntax right. The problem lies in that you need to ass the values of the fields, and instead you are passing the reference stuff. What I mean is this - You string ends up being:

Insert into [Training] (Employee_number, last_name, first_name, Job_Title, BPML) Values (Forms!frmComboTest.employee, Forms!frmComboTest.Last_Name, Forms!frmComboTest.first_name, Forms!frmComboTest.field11,Forms!frmComboTest.combo11);

That is what the SQL statement is looking like. What you really want is to put the values from the appropriate boxes into the statement. So your code need to be this:

Private Sub Command17_Click()

Dim sSQL As String
sSQL = "Insert into [Training] (Employee_number, last_name, first_name, Job_Title, BPML)" _
       & "Values ('" & Forms!frmComboTest.employee & "', '" & Forms!frmComboTest.Last_Name & "', '" &  Forms!frmComboTest.first_name, & "', '" & Forms!frmComboTest.field11 & "', '" & Forms!frmComboTest.combo11 & "');"
End Sub

You may have to adjust the single quotes for the last couple fields...if those hold numeric values the single quotes are not needed. That should set you down the right pat. Good luck and let us know.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[jrbarnett]

You declare the variable with the SQL code, but you don't actually run the SQL Code:

Try putting
CurrentDb.Execute sSQL

Additionally, you include the form control values in the string, rather than the value itself.
Something like:

Values (" & Forms!frmComboTest.employee & "','" & Forms!frmComboTest.Last_Name & "','" & Forms!frmComboTest.first_name & "','" & Forms!frmComboTest.field11 & "','" & Forms!frmComboTest.combo11);"

John

 

[bdjb]

Thanks for the replies. I've cut the code down for testing to:

Private Sub Command17_Click()

Dim sSQL As String

CurrentDb.Execute sSQL = "INSERT INTO Training(employee_name) VALUES (Forms!frmComboTest.employee);"

End Sub

But I get the error:
Run time error 3078, Microsoft jet database engine cannot find the input table or query 'false'....

I'm using access XP, the input and output fields are both numeric.

What happens if you get scared half to death twice?

Bob

 

[SgtJarrow]

Take a look at the two previous posts....You are trying to run a SQL statement, but do no have the SQL statement correct. A SQL statement must be complete and correct to execute. The two previous posts show the necessary changes...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[bdjb]

OK, I think I have the code correct now, but still get the same error. Is it because of multiple results on the form?

Private Sub Command17_Click()

Dim sSQL As String
CurrentDb.Execute sSQL = "Insert into [Training](Employee_number)" & "Values(" & Forms!frmComboTest.employee & ");"

End Sub

What happens if you get scared half to death twice?

Bob

 

[SgtJarrow]

Probably not...your statement still is not quite correct. All text fields must be enclosed in single quotes and you are missing some spacing (I think the spacing doesn't really matter, but I prefer to keep it clean myself)

"Insert into [Training] (Employee_number) Values ('" & Forms!frmComboTest.employee & "');"

Since employee is most likely a name (if not, the naming convention you used is not the greatest..should be employeeid in my opinion) then you need the single quotes I added...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer