Access Appointment Form Help

[June7]

I threw together a very simple example of a query approach. Hopefully I understood what you want.

[AccessHelp12]

It does not seem to work.
I can not book a client in.
And the time slot is still shown even though their has been a bokking made with a previous owner.


All i want to know is the code that i need to make the combo box work so that if i select a time slot from the combo box and book a client in for that certain time.
That time slot should not be visible to the user so that it prevents double booking (Another client being booked at the same time)

how would i go about doing this.

[BRZ-Ryan]

Is this just needed for a single day?

[June7]

Try this version.

[AccessHelp12]

Hey
Sorry June7 but it does not seem to work do i have to enable something so that the option is no longer displayed
I already enabled Macros but would i need to enable something else

[AccessHelp12]

Hey Guys
Wandering if you could help me out

Here is the database
the part i am trying to figure out is trying to make the appointment time combo box on the main form to work in a way that the time slot is no longer shown if there is already an appointment booked with an animal

eg:2/2/2014 10:00AM is already booked with a client
the combo box should no longer display the 10:00 AM timeslot but can be shown on other days

[ItsMe]

I took a look at the DB and it does not offer this feature. You will have to understand the concept of the code example offered to you by June in order to implement it. Your current DB is using a value list for the combo. Using a table as a RowSource for your combo may better illustrate the concept behind the code June7 offered. Suggest you create a table and add records to represent all possible times. Format the column to be Medium Time and add your records.

With that I would practice building a query using the Wizard. Create a query using the find unmatched query wizard. Include your new table as the table to start with and read the wizard instructions carefully, The resulting SQL, along with the help files on Dlookup, will need to be analyzed by you in order to reverse engineer the sample code offered by June7.

There is not an easy way to learn this stuff. It takes time, patience and a lot of failed attempts.

Another drawback to your DB is that it seems to pull all appts from days past....

Bottom line, this is your assignment. It is an assignment to learn something. So far, I see you are capable of pasting code from examples. Your questions posted here remind me of questions I would hear from a client, not from a developer.

[June7]

It does work. I tested the example before I uploaded. Shouldn't require anything special to work for you.

However, I see from your db that the available appointment times need to consider the selected doctor. I did not include that in my example because I missed that specification. So I tested the procedure you have and it seems to work. I had to change some appointments to a future date so records would be retrieved. The combobox list excludes occupied time periods for the specified date and doctor.

Might want to filter the subform so it shows only future appointments and maybe also for the date entered in textbox. I think that is what ItsMe was referencing. This would be the subform RowSource to exclude past appointments:
SELECT tblAppointments.*
FROM tblAppointments
WHERE (((tblAppointments.AppointDate)>=Date()));

Regardless of which approach you use, learn to debug code. Review link at bottom of my post for debugging guidelines. An understanding of basic programming structures and some simple debugging on your part could have had this procedure operational days ago.

[ItsMe]

When you mention the following code is giving you an error

'   Do
          If i <= dLowerbreak Or i >= dUpperBreak Then
             oRS.FindFirst "[AppointmentTime] Between #" & i - TimeValue("00:00:05") & _
             "# And #" & i + TimeValue("00:00:05") & "#"
             If oRS.NoMatch Then cboTime.AddItem i
          End If
    oRS.Close
    End If

I notice two things. One; you still have the Do commented out and I recall the original code incorporated this along with a loop until. Two; your SQL clause is calculating a date/time within the SQL string and within a single date operator. I have never tried this. I usualy do math outside the SQL string and pass the result to the string. If you look at he original code example, it does the math by passing the result to a date variable.

Do
            If i <= dLowerbreak Or i >= dUpperBreak Then
                dLowerPrecision = i - TimeValue("00:00:05")
                dUpperPrecision = i + TimeValue("00:00:05")
                oRS.FindFirst "[AppointTime] Between #" & dLowerPrecision & "# And #" & dUpperPrecision & "#"
                If oRS.NoMatch Then cboTime.AddItem i
            End If
            i = i + dDuration
        Loop Until i >= txtEnd

If it is any consolation, you picked a difficult template to incorporate.

[AccessHelp12]

Sorry I dont understand what you just said
Could you explain it in simpler terms or could you just help me out
it would be much appreciated

[ItsMe]

You have edited the code from its original version and it is not working.

You are looking for a time that is 'i'

In order to do this effectively you need to use the BETWEEN operator. In order to compare two dates/times they need to be different. You are looking for 'i'. Trim a few seconds off of 'i' and assign the result to a variable of the Date/Time data type
dLowerPrecision = i - TimeValue("00:00:05")

Now you need another variable for your BETWEEN conditional operator. Add a few seconds to 'i' and assign the result to a variable of the Date/Time data type
dUpperPrecision = i + TimeValue("00:00:05")

With that, you can loop through your recordset and see if an existing time slot overlaps the request. To be honest, as I type this out, I am not sure why the original author is using BETWEEN.

That is my effort in explaining it. As for offering assistance, try the original code and see what result you get. It is quoted in post #24