How to access and use data from records?

[arnhemb]

Hello, posted this in the wrong place earlier, but...

I am doing a booking system, and working in VBA
I want to check if certain records exist by date.
My "bookings" table has 'roomname' and 'date' and 'timeslot' as fields.

My query is:
strSQL = "SELECT * FROM bookings WHERE (((bookings.room)=" & roomName & ") AND ((bookings.datebook)=#" & dateStr & "#));"

I need to get all those records, and be able do certain checks on them, but I dont know how to execute that in VBA, so i check the records to see which time slots have been booked.

In pseudocode:
If(bookings.date == #xxx# AND bookings.timeslot == xx)
then 'some sort of action'

In short how do i filter specific records using my query and how do i access record information directly in VBA? research suggests recordsets, but not sure how to implement that

Thanks in advance

 

[oerris]

Hi arnhemb

You could try the attached code. I haven't had a change to check it, but it should get you started.

Private Sub Something()
    Dim sSQL As String
    Dim sProgramID As String
    Dim sProgramName As String
    
    On Error GoTo ErrorHandler
    
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    
    sSQL = "SELECT * " & _
              "FROM bookings " & _
              "WHERE room=" & roomName & " AND datebook=#" & dateStr & "#));"
    cmd.CommandType = adCmdText
    cmd.CommandText = sSQL
    
    rs.Open cmd, , adOpenKeyset, adLockOptimistic
    rs.MoveFirst
    
    Do
        'Do actions
        rs.MoveNext
    Loop Until rs.EOF
    
    Set cmd = Nothing
    Set rs = Nothing
    
ExitProcedure:
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case Else
        MsgBox Err.Description
    End Select
    Resume ExitProcedure
End Sub

Ørris.

 

[arnhemb]

Thanks a lot, thats exactly part of what i needed, appreciate the help. been struggling on this problem for a while now