Appointment Booking Time query.

K

k3ll1n

Registered User.
Local time
Today, 16:10
Joined
Jun 2, 2016
Messages
29
Hey all,

This seems to be the final piece to my puzzle but I'm not very experienced in queries yet. I've been researching for days now and hope someone here can shed some light on what I can do to accomplish this.

Currently my setup is like so;

ACCDB file attached to thread should anyone like to take a peek.

tblAppointmentHoursDaily

| Show | Hours |
| Checkbox | 2:00 PM |
| Checkbox | 2:30 PM |
| Checkbox | 3:00 PM |
| Checkbox | 3:30 PM |

tblAppointments

* | TimeStamp | | AppointmentDate | AppointmentTime | UserID |


qAppointmentHoursAvailability (I was assisted with this query but can't seem to tweak it just right)

Code: 
SELECT DISTINCT tblAppointmentHoursDaily.HoursFROM tblAppointmentHoursDaily LEFT JOIN tblAppointments ON tblAppointmentHoursDaily.[Hours] = tblAppointments.AppointmentTime
WHERE (((tblAppointments.AppointmentDate)<> Date() Or (tblAppointments.AppointmentDate) Is Null) AND ((tblAppointmentHoursDaily.Show)=True));


frmMain

* cboAvailability
*Runs the qAppointmentHoursAvailability query and displays available appointment times for that day. If the user selects 2:00 PM, the field is refreshed using Me.Recalc so that it reserves the time in the record.

txtAppointmentDate

I think this is another point where I'm stuck. Control Source is tblAppointments.AppointmentDate. I believe the issue may be also with the Me.Recalc of the cboAvailability. When that recalcs it also stores the changed date into the record and may now affect the query.


What I would like to happen is that when a user books a time, say 2:00 PM TODAY, no one else can select that time. But if the user changes the date field to TOMORROW, 2:00 PM is in the cbo dropdown.


Does this make sense?
Any advice would be appreciated.

ACCDB file attached. It's tiny. I've tried to remove all non important records for this query.

Cheers,
k3ll1n
 

Attachments

i was just tinkering around and did a slightly different method of populating the combo for the times. if you want to exclude the times for all users and not just the one selected, remove the userid criteria in the module sql.
 
Last edited:
moke123 said:
i was just tinkering around and did a slightly different method of populating the combo for the times. if you want to exclude the times for all users and not just the one selected, remove the userid criteria in the module sql.
Click to expand...

Your additions seem to be doing to trick moke! I'm understanding most of what you've done and I'll research the rest for my own education.

Would you mind sharing where I would insert to only show the time if the Yes/No check box is selected in the tblAppointmentHoursDaily.Show ?

Very much appreciate your contribution to this.
 
Running in to a few snags with my continued testing and no edits. Not exactly sure what's triggering it. Most of the time it works.

I have attached some snips.

---------------
-=Edit=- Believe I found the culprit on that one. There was a stray "2:00:00 PM;2:30:00 PM" in the row source of the cboAppointmentTime.

Still working on those check boxes.
 

Attachments

  • debug.PNG
    debug.PNG
    3.5 KB · Views: 158
  • debug2.PNG
    debug2.PNG
    2.3 KB · Views: 154
Last edited:
Would you mind sharing where I would insert to only show the time if the Yes/No check box is selected in the tblAppointmentHoursDaily.Show ?
Click to expand...
i ignored that, i didnt know what it was for.
 
moke123 said:
i ignored that, i didnt know what it was for.
Click to expand...

Some days we have a meeting during those hours and I'd like to disable the check mark. That way they don't show up in the drop down. Just need to figure out where to put it in your macro to check for it.
 
try..

Code: 
If StrCrit = "0" Then
strSql = "select * from  tblAppointmentHoursDaily where show = yes"
Else
strSql = "select * from  tblAppointmentHoursDaily where Hours not in (" & StrCrit & ") and Show = yes"
End If

edit:
personally i would probably have a form to schedule meetings with a multiselect listbox of staff and a date and time text/combo boxes. and schedule each employee that will be attending. that way there is no need to toggle the times on or off.
 
Last edited:
Thanks, I'll give that a shot when I'm at my access machine next.

What you described isn't what I meant though. I meant to say that if I have to go to a meeting or lunch, I would deselect 3:00 and 3:30 pm and it wouldn't show for people trying to book an appointment with me. The cbo would only list the hours that have SHOW as True.
 
I wouldn't have thought this would cause an problem, but it seems when I load the Appointment form using a NavigationControl / Subform I get this error.

Application-defined or object-defined error

Sends me to
Screen.ActiveForm.cboAvailability.RowSource = ""
Click to expand...


Main form name is : frmMainNavigation
Appointment form with cbo : frmAppointmentBooking


Is it because it's not an ActiveForm but a subform? I've tried changing it to ActiveObject but no luck. Anyone have a clue?

I'll attach an updated version of the db that includes the Navigation Control / Subform.

Cheers,
k3ll1n
 

Attachments

here give it a shot.
review the code in Module1.
 

Attachments

couple questions -
is this for a single user? in other words just for yourself or is this to schedule for several people?

i see an additional table which gives the hours for given days. ie. tuesday 2:00pm - 4:00PM. Are you planning on only displaying hours tied to that table?
 
arnelgp said:
here give it a shot.
review the code in Module1.
Click to expand...

I'm getting a couple errors using it. I've attached them.

Any chance moke or youself can have another looksee to the code opening on the subform after entering, USERID -> Date?


couple questions -
is this for a single user? in other words just for yourself or is this to schedule for several people?

i see an additional table which gives the hours for given days. ie. tuesday 2:00pm - 4:00PM. Are you planning on only displaying hours tied to that table?
Click to expand...

Heya moke,
To explain more in details what this is, it's a db used by 3 people. Myself and 2 others. It will be used for coworker to book appointments with our team rather than having them enter it on a line in excel.

The additional table you see with the weekly hours is displayed on the main form *using a subform*. It just displays the hours for the week. I don't need them tied to anything. Right now if I update the table, it updates the hours displayed. Leaving it up to the users to not book on days that isn't listed. We would pull the daily appointments almost hourly so I'll notice if they book on a date that we don't accept and let them know. It's all internal.
 

Attachments

  • debug1.PNG
    debug1.PNG
    8.3 KB · Views: 177
  • debug2.PNG
    debug2.PNG
    10.7 KB · Views: 180
  • Subform.accdb
    Subform.accdb
    644 KB · Views: 154
Last edited:
i believe those errors are from having an appointment without a time assigned. check the table for missing info.
 
edit. Unclear what is happening. I'm still getting that error message now. I haven't changed anything since yesterday except deleted all the records for testing. Please have a look at the next post.
 
Last edited:
The errors are coming back now and again. It must have something to do with the code below. After I enter a USER, Date and try to open the Availability dropdown, I get the error;

Code: 
"Syntax error in date in query expression. 'AppointmentDate = #2016.06.08'
Which is the date I'm attempting to select from the date picker. Any date I try to pick gives that error with the new date. With all records deleted I get this error. When I manually add one with all fields, then try the form, I get the same error.

Is there another way to write that line?

Code: 
Public Function GetExcludedTimes(vDate As Date) As String

Dim db As DAO.Database
Dim rs  As DAO.Recordset
Dim strSql  As String
 
Dim excluded As String
 
strSql = "Select AppointmentTime from tblAppointments where AppointmentDate = #" & vDate & "#"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql)

If rs.BOF And rs.EOF Then
GetExcludedTimes = "0"
Else

Do Until rs.EOF
excluded = "#" & rs!AppointmentTime & "#" & "," & excluded
excluded = Replace(excluded, "##", "")
rs.MoveNext
Loop
GetExcludedTimes = excluded
End If
 
Last edited:
did you change your date format? (2016.06.08)
 
I didn't think it had periods in it originally, but when I found my older backed up work they were there also. And it does work off and on... I don't believe it was ever changed.


Seems to be working now though. I'm not understanding why it's happening randomly. I don't know much about how ghost records work but I'm deleting the test appointment rows I create. Not sure if that's jamming it at times.



-=edit=- The issue is that Date format is tied to the Windows PCs regional system setting. I've since realized my work PC and home PC are formatted differently. I wasn't aware access's date format worked that way.

Raises another question but this is not a query related one. Cheers,
 
Last edited:
You must log in or register to reply here.

Similar threads

R
Merge two tables in query by date/time, preserving hourly time in one of the tables
Replies
12
Views
943
Ken Sheridan
Ken Sheridan
T
Baffled by Time
Replies
9
Views
525
DHookom
DHookom
K
Copy Pasting Queries
Replies
2
Views
273
KitaYama
K
mloucel
Solved Interesting QUERY problem
Replies
11
Views
768
mloucel
mloucel
E
Help query Count
Replies
5
Views
807
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom