×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Posted this is the wrong place the

Posted this is the wrong place the

Posted this is the wrong place the

(OP)
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: Insert Data into table from List box
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

RE: Posted this is the wrong place the



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

CODE

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 & "')"

Debug.Print strSQL

CurrentDb.Execute strSQL, dbFailOnError 

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


---- Andy

There is a great need for a sarcasm font.


---- Andy

There is a great need for a sarcasm font.

RE: Posted this is the wrong place the

(OP)
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

RE: Posted this is the wrong place the

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

CODE

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 ('" & Me![lstEmployeeAdd].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 Sub 

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

---- Andy

There is a great need for a sarcasm font.

RE: Posted this is the wrong place the

(OP)
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.

RE: Posted this is the wrong place the

I guess:

CODE

For intRowCtr = 0 To Me![lstEmployeeAdd].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.

RE: Posted this is the wrong place the

(OP)
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

RE: Posted this is the wrong place the

(OP)
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

RE: Posted this is the wrong place the

This is why I insist NOT to have this:

CODE

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:

CODE

strSQL = "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 strSQL 

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.

RE: Posted this is the wrong place the

(OP)
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')



RE: Posted this is the wrong place the

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

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'
 

If that's correct, you are set.


---- Andy

There is a great need for a sarcasm font.

RE: Posted this is the wrong place the

(OP)
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

RE: Posted this is the wrong place the

You need to examine:

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

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:
"VALUES ('" & .Column(0, intRowCtr) & "','" &
to
"VALUES ('" & .Column(1, intRowCtr) & "','" &

" 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... ponder

---- Andy

There is a great need for a sarcasm font.

RE: Posted this is the wrong place the

(OP)
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.

RE: Posted this is the wrong place the

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.

RE: Posted this is the wrong place the

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

I have a public sub with the following

CODE --> vba

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
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
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

CODE --> vba

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

RE: Posted this is the wrong place the

How about - the code on the cmd button:

CODE

Call CopyEmplyeeSelected(lstEmployeeSelect, lstEmployeeAdd) 

A public sub with the following:

CODE

Public Sub CopyEmplyeeSelected(ByRef ctlSource As Control, ByRef ctlDest As Control)
Dim intCurrentRow As Integer

'ctlDest.Clear

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.

RE: Posted this is the wrong place the

(OP)
Andy,

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

changed
"

CODE --> vba

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


to

CODE --> vba

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

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

Russ

RE: Posted this is the wrong place the

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

RE: Posted this is the wrong place the

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


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close