Okay, bear with me here since I don't know the language, but try this:

Set ctlSource = Me.lstVisitors

   For intCurrentRow = 0 To ctlSource.ListCount - 1
        If ctlSource.Selected(intCurrentRow) Then

        iFK = ctlSource.Column(0, intCurrentRow)
        sStaff = ctlSource.Column(1, intCurrentRow)
        sReden = ctlSource.Column(2, intCurrentRow)
        sAanlog = ctlSource.Column(3, intCurrentRow)
        rst.AddNew
        rst("NaamVisitor") = iFK
        rst("VoorStaff") = sStaff
        rst("VoorReden") = sReden
        rst("InvoerDoor") = sAanlog
        rst("InvoerOp") = Now()
        rst.Update
        End If
    Next intCurrentRow

Anyway, if the names don't match up with the columns, you will have to make changes, but you see what I am trying to do here. This should take all the values from the list box and paste them one for one into the table.

Did that help?

mmcdonal

I don't need the

        sStaff = ctlSource.Column(1, intCurrentRow)
        sReden = ctlSource.Column(2, intCurrentRow)
        sAanlog = ctlSource.Column(3, intCurrentRow)

cause they are asigned another way.

I have tried someting and this tells me that it does not use the adding to the table. I changed the code to

MsgBox "Step 1"
sAanlog = "Admin"

'insert every visitor in the table and print the badge
Set db = Application.CurrentDb()
Set rst = db.OpenRecordset("tblOpvolgingBezoekers", dbOpenDynaset)

Set ctlSource = Me.lstVisitors

   For intCurrentRow = 0 To ctlSource.ListCount - 1
        If ctlSource.Selected(intCurrentRow) Then

        iFK = ctlSource.Column(0, intCurrentRow)
        rst.AddNew
        rst("NaamVisitor") = iFK
        rst("VoorStaff") = sStaff
        rst("VoorReden") = sReden
        rst("InvoerDoor") = sAanlog
        rst("InvoerOp") = Now()
        rst.Update
        MsgBox "Step 1b"
        End If
        MsgBox "Step 2"
    Next intCurrentRow

rst.Close
Set rst = Nothing
Set db = Nothing

MsgBox "Step 3"

When I run this, I first get the msgbox "Step 1" to apear, then the number of times I have elements in the list box "Step 2" appears and at the end "Step 3". However the msgbox "Step 1b" in the middle does NEVER appear, so it seems like it is not using the code... to insert the data, but just is only looping through if you see what I mean here...

Any idea why this happens?

Modify the code like this and see what happens:

For intCurrentRow = 0 To ctlSource.ListCount - 1
        'If ctlSource.Selected(intCurrentRow) Then

        iFK = ctlSource.Column(0, intCurrentRow)
        rst.AddNew
        rst("NaamVisitor") = iFK
        rst("VoorStaff") = sStaff
        rst("VoorReden") = sReden
        rst("InvoerDoor") = sAanlog
        rst("InvoerOp") = Now()
        rst.Update
        MsgBox "Step 1b"
        'End If
        MsgBox "Step 2"
    Next intCurrentRow

This drops the If Then statement and it should run no matter what.

Did that help?


mmcdonal

After looking at this last solution, it occurs to me that:

1. The user isn't actually selecting anything in the list box. It is just being used for a visual accumulation of the selected data. Therefore:

If ctlSource.Selected(intCurrentRow) Then

   will never be true.

2. You want to loop through ALL of the items in the list box anyway, so there is no need to condition the addnew/updates anyway.

*smacking forehead*


mmcdonal

In fact, you could even say:

If ctlSource.NotSelected(intCurrentRow) Then

but again, no need to make this conditional.


mmcdonal

ok,

when I put a ' in front of the if line and the end if, I get the error

Item not found in this collection (error number 3265).

Any other idea, cause I'm trying to follow you here, but this is hard to understand completely for me... doing the best I can

Well, I think the time has come to stop messing around. Here is your psuedo code:

On Click of the make badge button (correct?)

Instead of using the List Box, use the table on which the list box is built. So for tblTempListBox do this:

sSQL1 = "SELECT TableID FROM tblTempListBox"
sSQL2 = "SELECT * FROM tblTableWhereThisIsGoing"
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

rs1.Open sSQL1, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs2.Open sSQL2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If rs1.RecordCount <> 0 Then
   Do Until rs1.EOF
        rs2.AddNew
        rs2("NaamVisitor") = rs1("TableID")
        rs2("VoorStaff") = sStaff
        rs2("VoorReden") = sReden
        rs2("InvoerDoor") = sAanlog
        rs2("InvoerOp") = Now()
        rs2.Update
   rs1.MoveNext
   Loop
End If

rs1.Close
rs2.Close

That should do it.

Then runa delete query on the temp table, then requery the list box to show it empty.

Did that work?






mmcdonal

Made it able to work thanks to you...

Thanks very much