Datebook scheduling - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Datebook scheduling

Jerry Fowler

3 years ago

As a project for a neighbor who owns a small Pet salon that up till now has been using index cards and a date book, I'm trying my hand at a scheduling database. That down the road may be used to send monthly emails out or Pet's birthday cards and such.

I'm doing pretty good and have got several tables PetT, HumanT, AppiontmentT, and ServiceT. recording pet info, their Human info, Service items, and finally Appointment info.
The place that's giving me issues is I would like a form similar to a page in a date book with, times going down from 8am to 4pm at 15 minute intervals, so 8:00am, 8:15am, 8:30am....4:00pm or 36 slots for a day. Then I would like to take the appointments for that day and place the appropriate data on that line. I have tried several ways with no luck.  Right now I have an unbound TextBox as the first field on each row with a VBA =TimeSerial(8,0,0)+TimeSerial(0,15*1,0) and the 1 after the * is the row number so it adds the minutes to 8:00:00.  But I'm lost as to figure out where to add the appt data. Ex:

Time    PetName   Start    End      Service
8:00am  Dixie     8:00     8:15     Pet's nails clipped
8:15am
8:30am  Fido      8:30     9:00     Pet's Wash and Dry
8:45am
9:00am  Snowball  9:00     9:15     Pet's nails clipped
9:15am

Am I making sense? If you shove me in the right direction I would still try to do this on my own, or possibly it might make a good TechHelp video or series.
Thanks

Jerry Fowler OP @Reply

3 years ago

In working on this I'm trying to load a Recordset and my SQL statement is erroring our (Prob quotes :(  ) here is the statement

    SQL = "SELECT AppointmentT.AppointmentID & "", "" & AppointmentT.PetID & "", "" & PetT.PetName & "", "" & ServiceT.ServiceID" & _
    "", "" & ServiceT.Notes & "", "" & DateValue([StartDateTime]) AS StartDate & "", "" & TimeValue([StartDateTime]) AS StartTime" & _
    "", "" & TimeValue([EndDateTime]) AS EndTime FROM ((AppointmentT INNER JOIN PetT ON AppointmentT.PetID = PetT.PetId) " & _
    "INNER JOIN ServiceT ON AppointmentT.ServiceID = ServiceT.ServiceID) INNER JOIN HumanT ON PetT.HumanID = HumanT.HumanID " & _
    "ORDER BY AppointmentT.StartDateTime"

	Alex Hedley @Reply 3 years ago
Does it work as a Query object?

	Jerry Fowler OP @Reply 3 years ago
That's where I got it from, but then I had to add the quotes to make it work in a string, I first created a Query, got it to do what I needed and then grabbed the SQL

	Alex Hedley @Reply 3 years ago
Convert Query SQL to VBA

Jerry Fowler OP @Reply

3 years ago

Thank you for the suggestions, they were helpful. But as in the past, I was making it much harder than it needed to be. I put it away for a couple of days and came back to it. All I needed was a simple join between the DateBookT (which just has in it the times from 8am to 4pm and the other fields for the DateBookPage. Then I joined that to the AppointmentT, which has all the data for the appointment, then joined on the StartTime field from both and wha la. And then used the DataBookT as the RecordSource for the Form.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View