How can open a recordset with conditions

[tasawer]

Hi,

I am opening recordset that performs well until I set some criteria in the underlying query.

This is my query and VBA code that work:

SELECT Booking.BookingID, Booking.BookingDate, Booking.BookingTime, Booking.EstimateTime, ACC_Vehicle.Reg, Staff.StaffName, Booking.MechanicID
FROM (Booking INNER JOIN ACC_Vehicle ON Booking.ACCVehicleID = ACC_Vehicle.ACCVehicleID) INNER JOIN Staff ON Booking.MechanicID = Staff.StaffID;

    'set array
    Dim TimeSlot(9, 3) As Long 'Define Array
    Dim TimeReg(9) As String ' array for vehicle reg
    Dim NoofBookings As Integer, A As Integer 'Set Counters
    Dim lngHours As Long, lngMins As Long 'Seperate Hours Mins of Booking Time
    Dim lngDurHours As Long, lngDurMins As Long ' Seperate Hours and Mins of Duration
    
    Dim LeftPos As Long, tw As Long  'Define StartPosition for Chart
    
    'Configure Datbase and RecordSet
    Dim rs As Recordset
    Dim db As Database
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Booking_TimeGraph", dbOpenDynaset)
    
    'Count no. of bookings
    rs.MoveFirst
    rs.MoveLast
    NoofBookings = rs.RecordCount

The moment I change my query to include a filter, the form gives an error message "Too Few Parameters expected 1". (Query opens to give required results). VBA code is in the ON-OPEN property of the form.
I feel that critera has to be set when opening the recordset rather than in the underlying query, but I'm not sure how to do that. :)

SELECT Booking.BookingID, Booking.BookingDate, Booking.BookingTime, Booking.EstimateTime, ACC_Vehicle.Reg, Staff.StaffName, Booking.MechanicID
FROM (Booking INNER JOIN ACC_Vehicle ON Booking.ACCVehicleID = ACC_Vehicle.ACCVehicleID) INNER JOIN Staff ON Booking.MechanicID = Staff.StaffID
WHERE (((Booking.MechanicID)=[Forms]![Bookings]![MechanicID]));

[ADezii]

I feel that critera has to be set when opening the recordset rather than in the underlying query

Not true, kindly display all Code in the Open() Event.

[tasawer]

* UPDATE - PLEASE DO NOT WORK IN THIS YET AS I AM MAKING SEVERAL CHANGES TO MY BOOKINGS FORM *

Thank you providing a solution to my previous problem. initially I was using the boxes.. but I am now using a label (lbl) becuase I need to display vehicle reg as the caption.

the object of this code is to convert the booking time and job duration to twips and postion a label in an appropriate position to represent used time slots. The label will display Vehicle Reg.

As for query problem. Error is generated on two seperate conditions.
1. When MechanicID exists but Booking time is Null - I get the error, 'The Control or Subform Control is too large for this Location.

2. Whenever the criteria is set to Forms!Bookings! MechanicID, I get error 3061 Too Few Parameters

Complete code is provided here.

On Error GoTo Error_TimeSlots

    Me.Caption = "Bookings for " & Me.StaffName
        
    'set array
    Dim TimeSlot(13, 3) As Long 'Define Array
    Dim TimeReg(13) As String ' array for vehicle reg
    Dim NoofBookings As Integer, A As Integer, wot As Integer 'Set Counters
    Dim lblx As Label 'to reference multiple labels on form
    
    Dim LeftPos As Long, tw As Long  'Define StartPosition for Chart
    
    'Configure Datbase and RecordSet
    Dim rs As Recordset
    Dim db As Database
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Booking_TimeGraph", dbOpenDynaset)
    
    'Count no. of bookings
    rs.MoveFirst
    rs.MoveLast
    NoofBookings = rs.RecordCount
    
'    MsgBox NoofBookings
    
    rs.MoveFirst
    For A = 1 To NoofBookings
           
            'Seperate Hours and Minutes
            TimeSlot(A, 0) = IIf(IsNull(rs!BookingTime), 0, Format(rs!BookingTime, "h"))
            TimeSlot(A, 1) = IIf(IsNull(rs!BookingTime), 0, Format(rs!BookingTime, "n"))
            TimeSlot(A, 2) = IIf(IsNull(rs!EstimateTime), 0, Format(rs!EstimateTime, "h"))
            'When Duration time is not set a thin line will indicate job start time
            TimeSlot(A, 3) = IIf(IsNull(rs!EstimateTime), 5, Format(rs!EstimateTime, "n"))
            TimeReg(A) = rs!Reg

            rs.MoveNext
        
        Next A

'loop to draw boxes

    wot = 9 'Workshop Opening Time
    LeftPos = 1 'start pos of 1 cm
    tw = 567 '567 twips per cm
    
    'Hide all Labels
    For A = 1 To 13
        Set lblx = Me.Controls("lbl" & CStr(A))
        lblx.Visible = False
        Next A
    
    For A = 1 To NoofBookings
      
        Set lblx = Me.Controls("lbl" & CStr(A))
            lblx.Caption = TimeReg(A)
            lblx.Visible = True
            lblx.Left = ((((TimeSlot(A, 0) + (TimeSlot(A, 1) / 60) - wot) * 2) + LeftPos)) * tw
            lblx.Width = ((TimeSlot(A, 2) + (TimeSlot(A, 3) / 60))) * tw * 2 'x2 becuase 2cm represent 1 hour
            lblx.Top = tw
            lblx.Caption = TimeReg(A)

        Next A
    
Error_Exit: Exit Sub
Error_TimeSlots: MsgBox Err.Number & " " & Err.Description, , "Time Slots"
    Resume Error_Exit

[tasawer]

Open Recordset with Conditions

HI Adezii,

I have finished my mods on the booking form.

Everytime I put criteria in the underlying query, I get the error 3061 Too Few Parameters

This query works:

SELECT Booking.BookingID, Booking.BookingDate, Booking.BookingTime, ACC_Vehicle.Reg, Staff.StaffName, Booking.MechanicID, Booking.JobEndTime, Booking.JobEndDate, Booking.bkTimeHours, Booking.bkTimeMins, Booking.BookingStatusID
FROM (Booking INNER JOIN ACC_Vehicle ON Booking.ACCVehicleID = ACC_Vehicle.ACCVehicleID) INNER JOIN Staff ON Booking.MechanicID = Staff.StaffID
WHERE (((Booking.BookingTime) Is Not Null) AND ((Booking.BookingStatusID)<>5));

This Query will give errors when the associated form is opened. Otherwise it will give desired results.

SELECT Booking.BookingID, Booking.BookingDate, Booking.BookingTime, ACC_Vehicle.Reg, Staff.StaffName, Booking.MechanicID, Booking.JobEndTime, Booking.JobEndDate, Booking.bkTimeHours, Booking.bkTimeMins, Booking.BookingStatusID
FROM (Booking INNER JOIN ACC_Vehicle ON Booking.ACCVehicleID = ACC_Vehicle.ACCVehicleID) INNER JOIN Staff ON Booking.MechanicID = Staff.StaffID
WHERE (((Booking.BookingDate)=[Forms]![Bookings]![BookingDate]) AND ((Booking.BookingTime) Is Not Null) AND ((Booking.BookingStatusID)<>5));

This is my code in the ON_Open() event:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_TimeSlots

    Me.Caption = "Bookings for " & Me.StaffName
        
    'Define array
    Dim lngTimeSlot(14, 3) As Long 'Define Array
    Dim strTimeReg(14) As String ' array for vehicle reg
    Dim intNoofBookings As Integer, intCounter As Integer, wot As Integer 'Set Counters
    Dim lblx As Label 'to reference multiple labels on form
    
    Dim LeftPos As Long, tw As Long  'Define StartPosition for Chart
    
    'Configure Datbase and RecordSet
    Dim rs As Recordset
    Dim db As Database
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Booking_TimeGraph", dbOpenDynaset)
    
    'Count no. of bookings
    rs.MoveFirst
    rs.MoveLast
    intNoofBookings = rs.RecordCount
    
    'MsgBox intNoofBookings
    
    rs.MoveFirst
    For intCounter = 1 To intNoofBookings
             
            'Seperate Hours and Minutes
            lngTimeSlot(intCounter, 0) = IIf(IsNull(rs!BookingTime), 0, Format(rs!BookingTime, "h"))
            lngTimeSlot(intCounter, 1) = IIf(IsNull(rs!BookingTime), 0, Format(rs!BookingTime, "n"))
             'Calculate Time Difference between Job Start Time and Job End Time (in Minutes)
            lngTimeSlot(intCounter, 2) = DateDiff("n", rs!BookingTime, rs!JobEndTime)
            strTimeReg(intCounter) = rs!Reg

            rs.MoveNext
        
        Next intCounter

'loop to draw boxes

    wot = 9 'Workshop Opening Time
    LeftPos = 1 'start pos of 1 cm
    tw = 567 '567 twips per cm
    
    'Hide all Labels
    For intCounter = 1 To 14
        Set lblx = Me.Controls("lbl" & CStr(intCounter))
        lblx.Visible = False
        Next intCounter
    
    For intCounter = 1 To intNoofBookings
      
        Set lblx = Me.Controls("lbl" & CStr(intCounter))
            lblx.Visible = True
            lblx.Left = ((((lngTimeSlot(intCounter, 0) + (lngTimeSlot(intCounter, 1) / 60) - wot) * 2) + LeftPos)) * tw
            lblx.Width = ((lngTimeSlot(intCounter, 2) / 60)) * tw * 2 'x2 becuase 2cm represent 1 hour
            lblx.Top = tw
            lblx.Caption = strTimeReg(intCounter)

        Next intCounter
    
Error_Exit: Exit Sub
Error_TimeSlots: MsgBox Err.Number & " " & Err.Description, , "Time Slots"
    Resume Error_Exit

End Sub

[ADezii]

I would actually have to see the Database at this point, any chance of Uploading it?

[tasawer]

can I send it to your personal email as I would not like anyone to open it? and How?

[tasawer]

Hi Adezii,

I have found the answer.

My original code was for a module where I was opening a recordset
when I moved the code to a form that had an underlying query, it was opening recordset on info that was already there.

I have done a RecordClone and it works fine.

Regards

[ADezii]

Glad you found the answer, and shared it with us.

[tasawer]

Hi Adezii,
Further to the above, I am now stuck on the logic to display the jobs as a pictograph.

it works this way:

1) A booking is made for a certain date
2) Booking time is selected
3) Mechanic Name is selected
3) Approximate time on job is specified.
4) Logic works out the JobEndDate & Time.

Click of a command button displays this booking as a chart together with other bookings for the day.

if a job is to last over several days, this job has to be seen on the pictograph whenever the correspnding date is selected. At the moment I am not able to achieve this.

can you look at this for me please?
I can email my database to your personal email address.

[ADezii]

I've given you my E-Mail Address in a Private Message. Please provide as much detail as possible, including Object Names, Events, Error Messages, etc., and I'll look at it as soon as I can.