Insert Data into table from List box

[Russmun]

Hi all,

Just joined and hope to post correctly. Novice access programmer-still learning

My issue.

I have table (test1) with following fields. All set to text until I get it to work. They are in the following order
Case_code, Case_Type_Code, Case_Wildcard_Code, Employee_Number, Building_Code, Team_Code, Location_Code, Student_Code, Action_Item.

I have a form with combo boxes for all above mentioned except Employee_Number. That is a lstbox.

LstEmployeeSelect (extended) has a bound query that populates with employee name and employee numberwhen I hightligh name(s) clcik arrow to move them to lstboxadd (Valuelist) this all works as it should. When I hit submit I want the data from the comboboxes and the lstEmployeeAdd to go to the test1 table.

This is my code;
On Error GoTo Err_cmdUpdateRecord_Click
Dim intRowCtr As Integer
Dim intResponse As String
If Me![lstEmployeeAdd].ListCount = 0 Then Exit Sub

With Me![lstEmployeeAdd]
For intRowCtr = 0 To .ListCount - 1
intResponse = MsgBox("ADD " & .ItemData(intRowCtr) & " to Case Load / Treatment Team Table?", _
vbQuestion + vbYesNo, "ADD Name to Table")
If intResponse = vbYes Then
CurrentDb.Execute "INSERT INTO test1 " & _
"(Case_Code, Case_Type_Code, Case_Wildcard_Code, Employee_Number, Building_Code, Team_Code, Location_Code, Student_Code, Action_Item) " & _
"VALUES ('" & .ItemData(intRowCtr) & "','" & _
Me.AddNewCaseCode & "','" & _
Me.AddCaseLoadCode & "','" & _
Me.AddWildCardCode & "','" & _
Me.AddBuildingCode & "','" & _
Me.AddTeamCode & "','" & _
Me.AddLocationCode & "','" & _
Me.AddParticipantCode & "','" & _
Me.AddActionCode & "')", dbFailOnError
End If
Next
End With


Exit_cmdUpdateRecord_Click:
Exit Sub

Err_cmdUpdateRecord_Click:
MsgBox Err.Description, vbExclamation, "Error in cmdUpdateRecord_Click()"
Resume Exit_cmdUpdateRecord_Click

Issues are these. It this configuration the data submits without error but goes to the wrong fields. The employee name goes to the Case)Load field I'm assuming because of where I have this "VALUES ('" & .ItemData(intRowCtr) & "','" & _ in the insert statement.

I move it down to this.

Me.AddNewCaseCode & "','" & _
Me.AddCaseLoadCode & "','" & _
Me.AddWildCardCode & "','" & _
"VALUES ('" & .ItemData(intRowCtr) & "','" & _
Me.AddBuildingCode & "','" & _
Me.AddTeamCode & "','" & _
Me.AddLocationCode & "','" & _
Me.AddParticipantCode & "','" & _
Me.AddActionCode & "')", dbFailOnError

but then I get an insert error.

How do I get this to submit correctly and how do I get the employee number to submit instead of the name. Again the lstEmployeeSelect box is populated by a query with employee name in column 0 and employee number in column 1. I move selections to lstEmployeeAdd

Thank you in advance for any help

 

[strongm]

You might be better off asking that in forum705

 

[Russmun]

Ok-Thank you