By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

Getting data from second list box into table

P: 6
I have a form with 2 listboxes. lst1 is populated with staff names (first and last concatenated) from a query. Ii is set to mutli-select.

lst2 is populated with selections from lst1 (it is set to valuelist) using a cmd button with the following code:
Expand|Select|Wrap|Line Numbers
  1. Public Sub CopySelected(ByRef frm As Form)
  2.  
  3. Dim ctlSource As Control
  4. Dim ctlDest As Control
  5. Dim strItems As String
  6. Dim intCurrentRow As Integer
  7.  
  8. Set ctlSource = frm!lstsource
  9. Set ctlDest = frm!lstdestination
  10.  
  11. For intCurrentRow = 0 To ctlSource.ListCount - 1
  12. If ctlSource.Selected(intCurrentRow) Then
  13. strItems = strItems & ctlSource.Column(0, _
  14. intCurrentRow) & ";"
  15. End If
  16. Next intCurrentRow
  17.  
  18.  
  19. ctlDest.RowSource = ""
  20. ctlDest.RowSource = strItems
  21.  
  22. Set ctlSource = Nothing
  23. Set ctlDest = Nothing
  24.  
  25. End Sub
This works fine but I wish I knew how to to switch the data back and fourth between the 2 listboxes-anyway

What I am trying to figure out is how to get the data from lst2 into a table named staff using another cmd button and having a vb yes/no pop up with the names of the staff picked so they can ensure they picked the right names.

I am still in the newbie stage of access so if I am going about this wrong let me know.
Apr 21 '10 #1

✓ answered by NeoPa

The SQL you are setting up to be run in the DB.Execute line is flawed. The closed parenthesis [)] comes immediately after the first parameter, then the second appears outside the parentheses.

Try instead :
Expand|Select|Wrap|Line Numbers
  1. If intResponse = vbYes Then
  2.     CurrentDb.Execute "INSERT INTO tblNMainOtherStaffPresent " & _
  3.                       "(Staff, brfid) " & _
  4.                       "VALUES ('" & .ItemData(intRowCtr) & "','" & _
  5.                       [b]Me.RevisedBRFID & "')", dbFailOnError
This assumes that both values are string/textual (See Quotes (') and Double-Quotes (") - Where and When to use them).

Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,636
@Techie1201
The following code, executed from the Click() Event of a Command Button, will prompt the User on every Value in lst2, and see if he/she would like to ADD that Value to a Table named Staff. If the User clicks on Yes, the Value is added, otherwise it is bypassed.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddToStaffTable_Click()
  2. On Error GoTo Err_cmdAddToStaffTable_Click
  3. Dim intRowCtr As Integer
  4. Dim intResponse As String
  5.  
  6. If Me![lst2].ListCount = 0 Then Exit Sub
  7.  
  8. With Me![lst2]
  9.   For intRowCtr = 0 To .ListCount - 1
  10.     intResponse = MsgBox("ADD " & .ItemData(intRowCtr) & " to the Staff Taqble?", _
  11.                           vbQuestion + vbYesNo, "ADD Name to Table")
  12.       If intResponse = vbYes Then
  13.         CurrentDb.Execute "INSERT INTO Staff ([Employee]) VALUES ('" & .ItemData(intRowCtr) & "')", _
  14.                            dbFailOnError
  15.       End If
  16.   Next
  17. End With
  18.  
  19. Exit_cmdAddToStaffTable_Click:
  20.   Exit Sub
  21.  
  22. Err_cmdAddToStaffTable_Click:
  23.   MsgBox Err.Description, vbExclamation, "Error in cmdAddToStaffTable_Click()"
  24.   Resume Exit_cmdAddToStaffTable_Click
  25. End Sub
Apr 21 '10 #2

NeoPa
Expert Mod 15k+
P: 31,489
Alternatively, you could populate a string variable with the list of names formulated within a message prompting the operator to confirm all names shown are required (Continue Yes/No). A simple (and single) MsgBox() call could then handle allowing the action or not.
Apr 21 '10 #3

ADezii
Expert 5K+
P: 8,636
@NeoPa
That was my initial thought, but what if you only wanted to Add one or several of the names contained within the List Box (lst2) and not all of them. There would now be no recourse. If every Name in lst2 is to always be added to Table Stuff, there would then be no need for lst2 since you can Multi-Select items in the original List Box, then after a confirmation, Append them to Table Stuff. Does this make sense to you, or am I again off-track? (LOL)
Apr 21 '10 #4

P: 6
@ADezii
Thank you all for your responses. I am going to try what ADezii suggested.

To answer your question about one lstbox using multi-select-that is how I set up the form originally. The issue we had was the end-user was sometimes adding the wrong staff.

Me being new to this-was able to figure out how to use suggested code posted in forums, reading the Access Bible, and access help menues. What I didn't do is put in some kind of validation to ensure the staff picked from the single lstbox was actually the staff that supposed to be picked.

I then went with the 2 lstbox setup so the end-user could see what staff was going to be etered into the DB for the current record. The vb yes/no was a second layer of validation just in case the end-users eyes weren't seeing the correct staff in the 2nd lstbox.

Again-when I insert the code and try, I will come back and choose the best answer. This may not be tomorrow but I will try to get it done today.

All your help is appreciated
Apr 21 '10 #5

NeoPa
Expert Mod 15k+
P: 31,489
ADezii, the requirements of the OP are not crystal here. Either solution could make sense as the process of selecting individual staff members from the first (multi-select) ListBox has already taken place. If it were I doing this then I wouldn't follow that with a confirmation for every staff member. Everyone has their own ideas about what is appropriate for such things and what is overkill though, so the OP can choose which approach to take.

At the end of the day, your approach is not how I would do it, but is certainly not incorrect in any way, and may well prove preferable to the OP in the end. This is especially true if they find from experience that end-users can still get it wrong even with my one message serves all appraoch.
Apr 21 '10 #6

P: 6
Morning,

NeoPa-you are correct with the no need for the extra validation in a perfect world but with the environment I work in and the reports that are generated form this DB going to State Agencies, I want to give the end user every chance not to mess up.

With that said, there is one part more I need to do with this form. beisdes the 2 list boxes there is a textbox (unbound) named (brfid)(number) that pulls information from the form that opens this form. In other words the data source is foms!revisedbrf!brfid.

The Brfid comes into the form correctly. So when the cmd button is clicked it runs the code you provided perfectly without pulling in the brfid into the table.

I modifird this line of code (bolded)-
Expand|Select|Wrap|Line Numbers
  1. If intResponse = vbYes Then
  2.          CurrentDb.Execute "INSERT INTO tblNMainOtherStaffPresent (Staff, brfid) VALUES ('" & .ItemData(intRowCtr) & "')", _
  3.        Chr(34) & Me.RevisedBRFID & Chr(34) & ", " &                             dbFailOnError
But I get a data conversion error. Do I have to write a seperate INSERT INTO table statement to bet the data from the textbox into the table? Or is it because the BrfID datatype is set to number?

Thank you
Apr 22 '10 #7

NeoPa
Expert Mod 15k+
P: 31,489
The SQL you are setting up to be run in the DB.Execute line is flawed. The closed parenthesis [)] comes immediately after the first parameter, then the second appears outside the parentheses.

Try instead :
Expand|Select|Wrap|Line Numbers
  1. If intResponse = vbYes Then
  2.     CurrentDb.Execute "INSERT INTO tblNMainOtherStaffPresent " & _
  3.                       "(Staff, brfid) " & _
  4.                       "VALUES ('" & .ItemData(intRowCtr) & "','" & _
  5.                       [b]Me.RevisedBRFID & "')", dbFailOnError
This assumes that both values are string/textual (See Quotes (') and Double-Quotes (") - Where and When to use them).
Apr 22 '10 #8

P: 6
Thanks to both Adezii and Neopa for your help. The form works perfectly.

Russ
Apr 22 '10 #9

NeoPa
Expert Mod 15k+
P: 31,489
Very pleased to hear it - and thanks for letting us know :)
Apr 22 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.