I have a form where there is an action button with the next code. I get an error when I click on this action button. Does anyone know what might be wrong with te code I already have???

Private Sub cmdCheckIn_Click()
On Error GoTo Err_cmdCheckIn_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sStaff As String
Dim sReden As String
Dim varItem As Variant

'check if everything needed has been filled in
If Me.lstVisitors.ListCount = 0 Then
    MsgBox "Gelieve minstens 1 bezoeker in te geven." & vbCrLf & _
        "Veuillez entrer au moins 1 visiteur.", vbExclamation, "CobelAdmin"
    Me.cboSelect.SetFocus
    Me.cboSelect.Dropdown
    Exit Sub
End If

If (IsNull(Me.cboStaff) Or Me.cboStaff = "") And (IsNull(Me.cboRedenAndere) Or Me.cboRedenAndere = "") Then
    MsgBox "Gelieve de naam van een medewerker of een andere reden in te geven." & vbCrLf & vbCrLf & _
        "Veuillez entrer le nom d'un collaborateur ou une raison différente." _
        , vbExclamation, "CobelAdmin"
    Me.cboStaff.SetFocus
    Me.cboStaff.Dropdown
    Exit Sub
End If

If IsNull(Me.cboRedenAndere) Or Me.cboRedenAndere = "" Then
    sStaff = Me.cboStaff
Else
    sReden = Me.cboRedenAndere
End If

'insert every visitor in the table and print the badge
For Each varItem In Me.lstVisitors
    'insert data in the table
    Set db = Application.CurrentDb()
    Set rst = db.OpenRecordset("tblOpvolgingBezoekers")
    rst.AddNew
        rst("NaamVisitor") = varItem
        rst("VoorStaff") = sStaff
        rst("VoorReden") = sReden
        rst("InvoerDoor") = sAanlog
        rst("InvoerOp") = Now()
    rst.Update
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    'print the badge
    'FIRST PART OF THE CODE THAT GOES HERE IS NOT WRITTEN YET
    Me.lstVisitors.RemoveItem (varItem)

Next

'empty form for next input
Me.cboRedenAndere = ""
Me.cboSelect = ""
Me.cboStaff = ""

Exit_cmdCheckIn_Click:
    Exit Sub

Err_cmdCheckIn_Click:
    MsgBox Err.Description
    Resume Exit_cmdCheckIn_Click

End Sub

Any ideas?

What is the error code, what line of the code is highlighted when you click debug?



mmcdonal

Hi Vince 421

I have had a look at your code and would suggest a couple of things to try. Firstly there is a variable missing. sAanlog is not declared. You need to declare this variable.

Second, try the following:

    Set db = Application.CurrentDb()
    Set rst = db.OpenRecordset("tblOpvolgingBezoekers", dbOpenDynaset)' This allows you to open an updateable recordset.

    For Each varItem In Me.lstVisitors
    'insert data in the table
    rst.AddNew

        rst("NaamVisitor") = varItem
        rst("VoorStaff") = sStaff
        rst("VoorReden") = sReden
        rst("InvoerDoor") = sAanlog
        rst("InvoerOp") = Now()

    rst.Update

    'print the badge
    'FIRST PART OF THE CODE THAT GOES HERE IS NOT WRITTEN YET
     Me.lstVisitors.RemoveItem (varItem)
Next

    rst.Close

    Set rst = Nothing
    Set db = Nothing


I have recreated this with out the list and it updates on my machine.

I would suggest that you only open the recordset once, this will speed up the process, maybe look at printing after you have updated the table. Again only cklose the record set once.

Give it a try and see if it helps.

Post a reply.

Martin

Thanks for your reply's , but I still have the problem...

sAanlog comes from a public variable in a module, don't need to declare it here.

I changed what you said, but I still have the same problem.
The error message says:

Object doesn't support this property or method (error number 438)

I'm guessing that this comes from the Me.lstVisitors in the for each varitem... line

lstVistors is a list box. I have almost the same on an other form, where it is me.lstVisitors.itemsselected and it works fine there...

On this form it should not do the code for each item selected in the list box, but for each item present in the list, because data is first added to this list and then the present data in this list box is inputted in the table by pressing the action button.

Don't know if this is very clear for you guys...

I think you are trying to declare an array, and you haven't built the array yet before you start looping through it.

For example, I created a list box, and then a button underneath, then I added the following code to the button. When you click the button, you get some messageboxes (as many as items you selected) and it displays the PK of the list item:

    Dim ctlSource As Control
    Dim ctlDest As Control
    Dim strItems As String
    Dim intCurrentRow As Integer
    Dim varItem As Variant
    Dim i As Integer

    Set ctlSource = Me.List2

    ReDim varItem(0)
    i = 0
    For intCurrentRow = 0 To ctlSource.ListCount - 1
        If ctlSource.Selected(intCurrentRow) Then
            varItem(i) = ctlSource.Column(0, intCurrentRow)
            i = i + 1
        End If
    Next intCurrentRow

i = 0

Do Until i = UBound(varItem) + 1
    MsgBox varItem(i)
    i = i + 1
Loop

You see, I had to create an Array FIRST, and then run the function on the items in the array. You are not doing this here. You are trying to create the array at the same time you are looping through it.

Does this help?



mmcdonal

You are kind of losing me on this :(

I changed the code to this:

Private Sub cmdCheckIn_Click()
On Error GoTo Err_cmdCheckIn_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sStaff As String
Dim sReden As String
Dim varItem As Variant
Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim i As Integer

'check if everything needed has been filled in
If Me.lstVisitors.ListCount = 0 Then
    MsgBox "Gelieve minstens 1 bezoeker in te geven." & vbCrLf & _
        "Veuillez entrer au moins 1 visiteur.", vbExclamation, "CobelAdmin"
    Me.cboSelect.SetFocus
    Me.cboSelect.Dropdown
    Exit Sub
End If

If (IsNull(Me.cboStaff) Or Me.cboStaff = "") And (IsNull(Me.cboRedenAndere) Or Me.cboRedenAndere = "") Then
    MsgBox "Gelieve de naam van een medewerker of een andere reden in te geven." & vbCrLf & vbCrLf & _
        "Veuillez entrer le nom d'un collaborateur ou une raison différente." _
        , vbExclamation, "CobelAdmin"
    Me.cboStaff.SetFocus
    Me.cboStaff.Dropdown
    Exit Sub
End If

If IsNull(Me.cboRedenAndere) Or Me.cboRedenAndere = "" Then
    sStaff = Me.cboStaff
Else
    sReden = Me.cboRedenAndere
End If

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

Set ctlSource = Me.lstVisitors

    ReDim varItem(0)
    i = 0
    For intCurrentRow = 0 To ctlSource.ListCount - 1
        If ctlSource.Selected(intCurrentRow) Then
            varItem(i) = ctlSource.Column(0, intCurrentRow)
            i = i + 1
        End If
    Next intCurrentRow

i = 0

Do Until i = UBound(varItem) + 1
    MsgBox varItem(i)
    i = i + 1
Loop

For Each varItem In ctlSource
    'insert data in the table
    rst.AddNew
        rst("NaamVisitor") = varItem
        rst("VoorStaff") = sStaff
        rst("VoorReden") = sReden
        rst("InvoerDoor") = sAanlog
        rst("InvoerOp") = Now()
    rst.Update
Next

rst.Close
Set rst = Nothing
Set db = Nothing

For Each varItem In Me.lstVisitors
    'print the badge
    'FIRST PART OF THE CODE THAT GOES HERE IS NOT WRITTEN YET
    Me.lstVisitors.RemoveItem (varItem)
Next

'empty form for next input
Me.cboRedenAndere = ""
Me.cboSelect = ""
Me.cboStaff = ""

Exit_cmdCheckIn_Click:
    Exit Sub

Err_cmdCheckIn_Click:
    MsgBox Err.Description & " Error number = " & Err.Number
    Resume Exit_cmdCheckIn_Click

End Sub

This generates 2 errors:
The first one has no message
The second one is the same as I had before.

Where do I need to change the code to make this one working?

Thanks for your help so far...

I had posted sample code to demonstrate the issue. You pasted the code verbatum. The first error you are getting is actually the message box from this section:

Do Until i = UBound(varItem) + 1
    MsgBox varItem(i)
    i = i + 1
Loop

What this is telling me is that you are not making a selection in the list box, or your code is not taking the selection from the list box.

Instead of posting actual code, can you post psuedo code so I can see what you are trying to do with each step?

It sounds like you are allowing the user to make multiple selections from a list box, and then based on those selections, you want to post those selections to another table, and then remove the selections they made from the list box. Is that correct?

mmcdonal

I'll try to explain so it is clear for you...

When the form is opened, I get the following screen:

http://img409.imageshack.us/img409/4...inemptyiu4.jpg

Like you can see, I have a few controls on the subform. After the user has done some action he has to do the screen should look like this:

http://img394.imageshack.us/img394/9...nfilledgc3.jpg

What happened is that the user selected a name in the cboSelect combo box, pressed the cmdAddVisitor and this has put the name in the list box. The user has done this a few times to get the list you have.
This list is based on a temporary table where the names are stored into. So when the user clicks the cmdAddVisitor, it actually adds the value in the table and refreshes the lstVisitors to show the change.
Then the user fills in either cboStaff or cboRedenAndere.
When all this is done, the user click on the cmdCheckIn. What should happen then is the following:

For each name that is in the lstVisitors, it should add a record to the table tblOpvolgingBezoekers where the name is the person in the list and the 'Reden' = cboRedenAndere and the 'Staff' = cboStaff...
The second step would be to print a badge for the person, but I first want to get the first part right before I continue...

The elements in the list box are never selected, they are just shown to the user. I don't want the user to select them, because what is the point of putting them in the list if you have to select them before pressing the action button... It's kind of like doing the work twice.

I hope this is clear for you this way. If not, let me know what you do not understand.

Okay, that is better. Well, if you are already storing the names (and PK's, right?) in a temp table that the list is based on, forget the list and just work from the table. Just be sure to clear the temp table when you are done.

But one last question: It looks like you are creating new records in "tblOpvolgingBezoekers" which has a look up "NaamVisitor". Is this a foreign key?

If it is, then you can take this code:

Dim iFK As Integer

'Open recordset here:

    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
        End If
    Next intCurrentRow

This will add new records related to another table by the FK. It looks like this other table is what is populating the combo box.

Did that help any?


mmcdonal

ok, I changed a part of the code to this:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sStaff As String
Dim sReden As String
Dim varItem As Variant
Dim ctlSource As Control
Dim intCurrentRow As Integer
Dim iFK As Integer

'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
        End If
    Next intCurrentRow

rst.Close
Set rst = Nothing
Set db = Nothing

But I still get the same error...

What should happen when I click the action button is that for each element present in the list box, which is made with 3 colums (1st = PK (hidden), 2nd = Name and 3th = Company name). It should take the hidden value for each element and insert this into the table. So the primary key comes from another table indeed.

I also have this on the cboStaff combo box, but her it will be easy to fix, cause it is not correct here for now either...

The table the list box is based on has multiple field: Person_ID (PK), Person_Name, Person_Company, and one other that is not important here... This table has no relation with any other table, cause I only use this has a temporary table to store the values... Once inserting will be done, the table will be emptied.

Don't know if this is clear enough.