Hi,

What I am trying to do is:

The user needs to select an existing ticket in a combo box. When the user selects this ticket the primary key (=autonumber) is displayed in the combo box. the combo's name is cboTicketSelector.

Then the user has to press an action button (=cmdTicketInvoegen) to 'import' the tickets details on the form.

I have written some code that I thought would work:

Private Sub cmdTicketInvoegen_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String

'check if a ticket has been selected
If IsNull(Me.cboTicketSelector) Or Me.cboTicketSelector = "" Then
    MsgBox "Gelieve eerst een bestaand ticket te selecteren." & vbCrLf & vbCrLf & _
        "Veuillez d'abord choisir un ticket existant.", vbExclamation, "CobelAdmin"
    Me.cboTicketSelector.SetFocus
    Me.cboTicketSelector.Dropdown
    Exit Sub
Else
    'lock some fields that could have been unlocked by another action button
    Me.txtDatum.Enabled = False
    Me.txtDatum.Locked = True
    Me.cboAgent.Enabled = False
    Me.cboAgent.Locked = True
    Me.txtUur.Enabled = False
    Me.txtUur.Locked = True
    'open connection with the table
    Set db = CurrentDb
    sSQL = "SELECT tblKassatickets.Volgnummer, tblKassatickets.Datum, tblKassatickets.NaamAgent, tblKassatickets.Uur, tblKassatickets.NrTicket, tblKassatickets.Bedrag, tblKassatickets.AantalCollies, tblKassatickets.NrKassa, tblKassatickets.Opmerkingen FROM tblKassatickets WHERE (((tblKassatickets.Volgnummer)=[Forms]![frmOpvolgingKassaticketsFoutief]![cboTicketSelector]));"
    Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)

    'put the values of the record in the recordset on the form's text boxes
    Me.txtDatum = rst.Fields(2)
    Me.txtUur = rst.Fields(3)
    Me.cboAgent = rst.Fields(4)
    Me.txtNrTicket = rst.Fields(5)
    Me.txtBedrag = rst.Fields(6)
    Me.txtAantalCollies = rst.Fields(7)
    Me.txtNrKassa = rst.Fields(8)

    'close connection
    rst.Close
    Set rst = Nothing
    Set db = Nothing
End If

End Sub

However, when I run this code I get an error on

    Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)

and I can't seem to find out why... Does anyone have any idea what is wrong here???

Thanks

Hi Vince,

The problem is in your SQL string.
Remember, when working with ADO/DAO and passing SQL strings, they are read as given.
You have entered '(tblKassatickets.Volgnummer)=[Forms]![frmOpvolgingKassaticketsFoutief]![cboTicketSelector]'
in your WHERE clause, which would work fine in the Access Query Designer, because it would parse it for you, but in ADO/DAO you cant do that.

Use this instead:
sSQL = "SELECT tblKassatickets.Volgnummer, tblKassatickets.Datum, tblKassatickets.NaamAgent, tblKassatickets.Uur, tblKassatickets.NrTicket, tblKassatickets.Bedrag, tblKassatickets.AantalCollies, tblKassatickets.NrKassa, tblKassatickets.Opmerkingen FROM tblKassatickets WHERE (((tblKassatickets.Volgnummer)=" & cboTicketSelector & "));"

I hope this helps, post a message with the results.

Best Regards,
Rob

This works great...

Thanks very much robzyc. I was almost going crazy to find the problem...

Your more than welcome!

HTH.

Rob