Posted this is the wrong place the first time. Hope this is the right place
HomeForumsProgrammersLanguagesVisual Basic (Classic) Forum
Insert Data into table from List box
thread222-1803655
Forum Search FAQs Links MVPs
Read
New Posts
Reply To
This Thread
Start A
New Thread
E-mail
Thread
Print
Thread
More Like
This
Next
Thread
Russmun (IS/IT--Management)(OP)23 Jun 20 16:26
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
HomeForumsProgrammersLanguagesVisual Basic (Classic) Forum
Insert Data into table from List box
thread222-1803655
Forum Search FAQs Links MVPs
Read
New Posts
Reply To
This Thread
Start A
New Thread
Thread
Thread
More Like
This
Next
Thread
Russmun (IS/IT--Management)(OP)23 Jun 20 16:26
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