Posted this is the wrong place the 1

[Russmun]

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

 

[Andrzejek]

You may want to examine your INSERT statement before you execute it, something like:

Dim strSQL As String

strSQL = "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 & "')"
[blue]
Debug.Print strSQL
[/blue]
CurrentDb.Execute strSQL, dbFailOnError

And see what is in the Immediate Window from [blue]Debug[/blue] statement.


---- Andy

There is a great need for a sarcasm font.


---- Andy

There is a great need for a sarcasm font.

 

[Russmun]

Thank you for the reply.

I put a different cmd button on the form to execute what you posted. Result is the .itemdata gets highlighted and compile error of invalid or unqualified reference. Again newbie here, I googled that and I'm assuming it needs the with statements. I add them back in and get run-time error 3078-the Microsoft access database engine cannot find the input table or query false. Make sure it exists and that its name is spelled correctly.

debug code

Private Sub Command86_Click()

Dim intRowCtr As Integer


If Me![lstEmployeeAdd].ListCount = 0 Then Exit Sub

With Me![lstEmployeeAdd]
For intRowCtr = 0 To .ListCount - 1

Dim strSQL As String

CurrentDb.Execute strSQL = "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 & "')"

Debug.Print strSQL

CurrentDb.Execute strSQL, dbFailOnError

Next
End With



Exit Sub


End Sub

 

[Andrzejek]

This is wrong:[pre]
CurrentDb.Execute strSQL = "INSERT INTO test1 " & _
[/pre]
You may just try this:

Private Sub Command86_Click()
Dim strSQL As String
Dim intRowCtr As Integer

If Me![lstEmployeeAdd].ListCount = 0 Then Exit Sub

For intRowCtr = 0 To .ListCount - 1

    strSQL = "INSERT INTO test1 " & _
    "(Case_Code, Case_Type_Code, Case_Wildcard_Code, Employee_Number, Building_Code, Team_Code, Location_Code, Student_Code, Action_Item) " & _
    "VALUES ('" & [blue]Me![lstEmployeeAdd].ItemData(intRowCtr)[/blue] & "','" & _
    Me.AddNewCaseCode & "','" & _
    Me.AddCaseLoadCode & "','" & _
    Me.AddWildCardCode & "','" & _
    Me.AddBuildingCode & "','" & _
    Me.AddTeamCode & "','" & _
    Me.AddLocationCode & "','" & _
    Me.AddParticipantCode & "','" & _
    Me.AddActionCode & "')"

    Debug.Print strSQL
[green]
    'CurrentDb.Execute strSQL, dbFailOnError[/green]
Next

End Sub

And see what is in the Immediate Window from Debug statement.

---- Andy

There is a great need for a sarcasm font.

 

[Russmun]

Again, thanks for the reply. Used your code and got an error. Did quick watch to see if I could figure it out, still looking. 2 snippets attached of error and quick watch results

I'll continue to try to figure it out. May just start from scratch.

 

[Andrzejek]

I guess:

For intRowCtr = 0 To [blue]Me![lstEmployeeAdd][/blue].ListCount - 1

All I am trying to show you is how to create a String with your INSERT statement so you can actually see what you are trying to execute and if your INSERT makes sense.


---- Andy

There is a great need for a sarcasm font.

 

[Russmun]

Andrzejek , I really appreciate your time. So to recap, I could submit the data to the table but issue was, it would submit in the wrong order. I just solved that because sometimes I get stuck on stupid and the following was wrong.

Employee_Number was after Case_Wildcard_Code. I moved it to below and now all data submits to proper fields in table

CurrentDb.Execute "INSERT INTO test1 " & _
"(Employee_Number,Case_Code, Case_Type_Code, Case_Wildcard_Code, Building_Code, Team_Code, Location_Code, Student_Code, Action_Item) " & _
"VALUES ('" & .ItemData(intRowCtr) & "','" & _


Second problem was this-

lstemployeeselect is populated by a query that has 2 columns. Column 0 is their name and column 1 is their employee_Number. In this list box I have both viewable so the user sees the name and employee_number. They highlight the employee(s)they want to move to lstemployeeadd, click an arrow, they move over but just their name is displyed not employee number (could be hidden?)

What I am trying to accomplish is- when they hit submit, the value in lstemployeeadd = the employee_Number that gets submitted to the table and not the employee name

I also changed "VALUES ('" & .ItemData(intRowCtr) & "','" & _ to "VALUES ('" & .Column(0, intRowCtr) & "','" & _ but still get their name instead of their employee_number

Hopefully I'm explaining this correctly

 

[Russmun]

update I changed it to column 1 not 0

"VALUES ('" & .Column(1, intRowCtr) & "','" & _

When I did this change nothing is submitted to the employee_number field in the test1 table when it is column 0 I get their name

 

[Andrzejek]

This is why I insist NOT to have this:

CurrentDb.Execute "INSERT INTO test1 " & _
"(Employee_Number,Case_Code, Case_Type_Code, Case_Wildcard_Code, Building_Code, Team_Code, Location_Code, Student_Code, Action_Item) " & _
"VALUES ('" & .ItemData(intRowCtr) & "','" & _

but to have THIS:

[blue]strSQL[/blue] = "INSERT INTO test1 " & _
"(Employee_Number,Case_Code, Case_Type_Code, Case_Wildcard_Code, Building_Code, Team_Code, Location_Code, Student_Code, Action_Item) " & _
"VALUES ('" & .ItemData(intRowCtr) & "','" & _

Debug.Print [blue]strSQL[/blue]

So you can SEE your Insert statement before you execute it.
Otherwise, you keep trying stuff in the dark, IMO


---- Andy

There is a great need for a sarcasm font.

 

[Russmun]

immediate window show this

INSERT INTO test1 (Employee_Number,Case_Code, Case_Type_Code, Case_Wildcard_Code, Building_Code, Team_Code, Location_Code, Student_Code, Action_Item) VALUES ('John Doe','N','1','1','0','0','0','1','I')

 

[Andrzejek]

So now you know this is what your Insert statement would do:

[pre]
Employee_Number, 'John Doe'
Case_Code, 'N'
Case_Type_Code, '1'
Case_Wildcard_Code, '1'
Building_Code, '0'
Team_Code, '0'
Location_Code, '0'
Student_Code, '1'
Action_Item 'I'
[/pre]

If that's correct, you are set.


---- Andy

There is a great need for a sarcasm font.

 

[Russmun]

Yes-thank you. But my question is still the same. How do I code the insert statement to insert the employee number instead of the employee name?

I explained the 2 lstbox setups previously.

Thanks in advance

 

[Andrzejek]

You need to examine:
[tt]
"VALUES ('" & [blue].Column(0, intRowCtr)[/blue] & "','"
[/tt]
And figure out how to get the employee number from your list.

You may want to use some ways mentioned here

My guess is, if "Column 0 is their name and column 1 is their employee_Number" so would need to change:
[tt]"VALUES ('" & .Column([highlight #8AE234]0[/highlight], intRowCtr) & "','" & [/tt]
to
[tt]"VALUES ('" & .Column([highlight #8AE234]1[/highlight], intRowCtr) & "','" & [/tt]

" They highlight the employee(s)they want to move to lstemployeeadd, click an arrow, they move over but just their name is displyed not employee number (could be hidden?)"
If you move both: the name and the number, you should be set.
But if you move just the name and NOT the number, you have a problem because where will you get the number from...

---- Andy

There is a great need for a sarcasm font.

 

[Russmun]

Again, Thank you. I fully understand why I'm not getting the number. I expressed that in pervious posts. Maybe I'm just not asking the right question, so let me try again.

lstemployeeselect is populated by a query with 2 columns. column 0 is their name, column 1 is their number. When they highlight the name(s) to move to lstemplyeeadd they press a command button which moves the highlighted selections. It is only moving column 0 (Their name) so I guess my question is this

What would be the code on the cmd button that moves the highlighted selection in lstemployeeselect be to move both colunm 0 AND column 1 to lstemployeeadd?

I know that if I just change the query populating lstemployeeselect to have employee number in column 0 and their name in column 1 it works fine but then the lstemployeeadd populates with their employee number and not their name. If there's not a way to move a multi select, multi column lstbox from one to another, I'll just live with employee number in column 0 .

Thank your for your all of your time assisting me.

 

[Andrzejek]

What is "the code on the cmd button that moves the highlighted selection in lstemployeeselect be to move [...] to lstemployeeadd?"

If you show your code, somebody may be able to help you.

It is the time for you to know how to format your code as CODE here in your posts:

Always use Preview before Submit Post


---- Andy

There is a great need for a sarcasm font.

 

[Russmun]

Thank you for the feedback, proper way to format code.

I have a public sub with the following

Public Sub CopyEmplyeeSelected(ByRef frm As Form)

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = frm!lstEmployeeSelect
Set ctlDest = frm!lstEmployeeAdd

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
[highlight #FCE94F]strItems = strItems & ctlSource.Column(0, _
intCurrentRow)[/highlight] & ";"
End If
Next intCurrentRow

' Reset destination control's RowSource property.
'ctlDest.RowSource = ""
ctlDest.RowSource = strItems

Set ctlSource = Nothing
Set ctlDest = Nothing

End Sub

The code on the cmd button is

CopyEmplyeeSelected Me

I'm pretty sure I have to add to the strItems...(highlighted above)to get both columns to move to lstemplyeeadd but not sure what. I have tried different things but meet a dead end each time.

Russ

 

[Andrzejek]

How about - the code on the cmd button:

Call CopyEmplyeeSelected(lstEmployeeSelect, lstEmployeeAdd)

A public sub with the following:

Public Sub CopyEmplyeeSelected(ByRef ctlSource As Control, ByRef ctlDest As Control)
Dim intCurrentRow As Integer
[green]
'ctlDest.Clear[/green]

For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
        ctlDest.AddItem (ctlSource.Column(0, intCurrentRow) & _
                   ";" & ctlSource.Column(1, intCurrentRow))
    End If
Next intCurrentRow

End Sub

Assuming your (unbound) lstEmployeeAdd is capable of accepting 2 columns' data


---- Andy

There is a great need for a sarcasm font.

 

[Russmun]

Andy,

This code work PERFECTLY. Thank you for everything. The only change I had to make with other code was this.

changed
"

VALUES ('" & .Column(0, intRowCtr) & "','" & _

to

"VALUES ('" & .Column(1, intRowCtr) & "','" & _

Last question since I'm new to this site. Is there a "solved" button somewhere?

Russ

 

[dhookom]

Hi Russ,
Thanks for asking about the "solved". Click the Great Post link to the bottom right of the post that answered your question.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Great! I am glad your code is working the way you want.
By the way - welcome to TT (Tek-Tips)


---- Andy

There is a great need for a sarcasm font.