Getting data from second list box into table

[Techie1201]

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:

Public Sub CopySelected(ByRef frm As Form)

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

Set ctlSource = frm!lstsource
Set ctlDest = frm!lstdestination

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


ctlDest.RowSource = ""
ctlDest.RowSource = strItems

Set ctlSource = Nothing
Set ctlDest = Nothing

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.

[ADezii]

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:

Public Sub CopySelected(By Ref frm As Form)

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

Set ctlSource = frm!lstsource
Set ctlDest = frm!lstdestinat ion

For intCurrentRow = 0 To ctlSource.ListC ount - 1
If ctlSource.Selec ted(intCurrentR ow) Then
strItems = strItems & ctlSource.Colum n(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow


ctlDest.RowSour ce = ""
ctlDest.RowSour ce = strItems

Set ctlSource = Nothing
Set ctlDest = Nothing

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.

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.

Private Sub cmdAddToStaffTable_Click()
On Error GoTo Err_cmdAddToStaffTable_Click
Dim intRowCtr As Integer
Dim intResponse As String

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

With Me![lst2]
  For intRowCtr = 0 To .ListCount - 1
    intResponse = MsgBox("ADD " & .ItemData(intRowCtr) & " to the Staff Taqble?", _
                          vbQuestion + vbYesNo, "ADD Name to Table")
      If intResponse = vbYes Then
        CurrentDb.Execute "INSERT INTO Staff ([Employee]) VALUES ('" & .ItemData(intRowCtr) & "')", _
                           dbFailOnError
      End If
  Next
End With

Exit_cmdAddToStaffTable_Click:
  Exit Sub

Err_cmdAddToStaffTable_Click:
  MsgBox Err.Description, vbExclamation, "Error in cmdAddToStaffTable_Click()"
  Resume Exit_cmdAddToStaffTable_Click
End Sub

[NeoPa]

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.

[ADezii]

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.

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)

[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.

Private Sub cmdAddToStaffTable_Click()
On Error GoTo Err_cmdAddToStaffTable_Click
Dim intRowCtr As Integer
Dim intResponse As String

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

With Me![lst2]
  For intRowCtr = 0 To .ListCount - 1
    intResponse = MsgBox("ADD " & .ItemData(intRowCtr) & " to the Staff Taqble?", _
                          vbQuestion + vbYesNo, "ADD Name to Table")
      If intResponse = vbYes Then
        CurrentDb.Execute "INSERT INTO Staff ([Employee]) VALUES ('" & .ItemData(intRowCtr) & "')", _
                           dbFailOnError
      End If
  Next
End With

Exit_cmdAddToStaffTable_Click:
  Exit Sub

Err_cmdAddToStaffTable_Click:
  MsgBox Err.Description, vbExclamation, "Error in cmdAddToStaffTable_Click()"
  Resume Exit_cmdAddToStaffTable_Click
End Sub

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

[NeoPa]

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.

[Techie1201]

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

If intResponse = vbYes Then
         CurrentDb.Execute "INSERT INTO tblNMainOtherStaffPresent (Staff, [B]brfid[/B]) VALUES ('" & .ItemData(intRowCtr) & "')", _
     [B]  Chr(34) & Me.RevisedBRFID & Chr(34) & ", " &  [/B]                           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

[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 :

If intResponse = vbYes Then
    CurrentDb.Execute "INSERT INTO tblNMainOtherStaffPresent " & _
                      "(Staff, [B]brfid[/B]) " & _
                      "VALUES ('" & .ItemData(intRowCtr) & "','" & _
                      [B]Me.RevisedBRFID & "')", dbFailOnError

This assumes that both values are string/textual (See Quotes (') and Double-Quotes (") - Where and When to use them).

[Techie1201]

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

Russ

[NeoPa]

Very pleased to hear it - and thanks for letting us know :)