Schedule and appointment (1 Viewer)

[teddy1]

I want to create a small database for consulting purposes. I have created the tables for that:
Client
Employee
Appointments (Session) - the appointment has the following status options (noticed, session OK, canceled, missed

The question is now with the form or the table for scheduling an appointment prior to the actual consulting hours - session.
The form or the table must consider the employee's working days and working hours. If there is an appointment coalition in the scheduling and/or the planned appointment is outside of the working day and/or the working hours of the employees, the form should either give a warning or should not permit the schedule at all. The employees can have same or/and different working days, working hours.

My Idea is to have another table for employee’s Working days and working hours per weekdays. Then Form Schedule must check whether the would be appointment is ok in terms of the working days, working hours and the availability of open time of the employee. However, I do not know how to do.

I am very grateful if you can give a sample Tables for scheduling an appointment or step by step instruction.

Thank you in advance

 

[Uncle Gizmo]

Appointments (Session) - the appointment has the following status options (noticed, session OK, cancelled, missed

When you say that this table has the following options, my guess is you have separate columns for the selection of the options. Your table probably has a design similar to this >>> Field - AppointmentID,
Field - noticed,
Field - session OK,
Field - cancelled,
Field - missed...

If that's the case you should only have one field, Field - fldStatus, have that field as an integer field. On your Form, use an Option Group to provide the Status options as Numbers. If you've already got a single status field as Integer, then please ignore my comment.

 

[June7]

Bing: access database appointment manager

Review

Appointment Database Template | Appointment Tracking Software

The Appointment Database - Appointment Tracking Database is a full featured Microsoft Access Database Template that allows for Scheduling and viewing Tasks and Appointments. All the forms and reports used in the database are built using only native Access controls. All Source code include in...

www.microsoftaccessexpert.com

The more 'user friendly' the more code. What you want is certainly doable with enough code.

 

[teddy1]

yes I have a single status filed.

 

[teddy1]

Thank you. I watched this video. However, I am not sure if the form/table schedule can check whether the employee is free for the appointment that can be held in the future.

 

[June7]

That would require customization with code. What you want is not a trivial exercise (basically asking for someone to design and build a db from scratch). We don't know enough about your data structure and business process. A forum is not the place for 'step by step' tutoring. If you are not able or willing to do research or take classes to learn db design and coding, perhaps you should seek a professional consultant.

 

[teddy1]

I totally understand what you mean. I am on the process of learning MS Access. What I have done till now is setting the above Tables, Forms for new Clients, new Records to sessions and Lists for all sessions.

What I want (and make research) is now to add to this database the possibility of arranging appointments or appointment scheduling. If there is any support of any kind or a Totorial or a link. I would be very satisfied.

 

[June7]

I don't know of anything specific to your need but basically code would pull data from your "WorkSchedule" table and compare to data proposed for an appointment with conditional structure such as If Then Else. This could involve opening a recordset or using domain aggregate functions such as DLookup and DCount.

 

[teddy1]

Thank you for the hint.

 

[Micron]

Are you looking for an Outlook appointment calendar view in a db? I believe I have a copy of that, but have never used it.

 

[teddy1]

Are you looking for an Outlook appointment calendar view in a db? I believe I have a copy of that, but have never used it.

I will try with it if you send me. Thank you.

 

[Micron]

Unfortunately I can't seem to find it, but I did find an AWF post I made where I posted this url
with a notation that it came from about post 9. I've never been a member there so I don't know how I got a copy of it without joining. I found the path in my recent Access files list but it's missing. It turns out that if you join to get the file, you could actually do me a favour and post a copy of it here - it's kind of like the tables have been turned!

 

[mike60smart]

Hi

This is a good example by Peter Hibbs

 

[teddy1]

Now I decided to use this Video:

as June 7 proposed. In this Process I used the same table, Form and query names as it is.

Option Compare Database

Private Sub cboTime_Enter()
    Dim i As Date, n As Integer, oRS As DAO.Recordset, sSQL As String
    Dim dLowerbreak As Date, dUpperBreak As Date, dDuration As Date
    Dim dLowerPrecision As Date, dUpperPrecision As Date
    cboTime.RowSourceType = "Value List"
    cboTime.RowSource = ""
    If IsNull(Start) Then Exit Sub Else i = Start
    If Me.NewRecord = True Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    sSQL = "SELECT DoctorsID, AppointDate, AppointTime"
    sSQL = sSQL & " FROM qrySubformAppoints"
    sSQL = sSQL & " WHERE DoctorsID= " & Me.ID & _
                            " AND AppointDate=#" & Me.txtAppointDate & "#"
    Set oRS = CurrentDb.OpenRecordset(sSQL)
 
    dDuration = TimeValue("00:30")
    dLowerbreak = Break - TimeValue("00:25") 'Break is a field
    dUpperBreak = Break + TimeValue("00:25")
 
    If oRS.RecordCount = 0 Then
        Do
            If i <= dLowerbreak Or i >= dUpperBreak Then
                cboTime.AddItem i
            End If
            i = i + dDuration
        Loop Until i >= txtEnd
    Else
        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
    End If
    oRS.Close
End Sub

Private Sub cboTime_AfterUpdate()
    subform.SetFocus
    DoCmd.GoToControl "AppointTime"
    DoCmd.GoToRecord , , acNewRec
    subform.Form.Controls("AppointTime") = Me.cboTime
    subform.Form.Controls("AppointDate") = Me.txtAppointDate
    subform.Form.Controls("cboClient").SetFocus
    subform.Form.Controls("cboClient").Dropdown
End Sub

Private Sub txtAppointDate_BeforeUpdate(Cancel As Integer)
    If CDate(txtAppointDate) <= Date Then
        MsgBox "No more new appointments on this date"
        Cancel = True
    End If
End Sub

The only thing I've changed is
from

    cboTime.RowSourceType = "Value List"
    cboTime.RowSource = ""

to

    cboTime.RowSourceType = "Table/Query"
    cboTime.RowSource = "tlbTime"

Now I get the run-Time Error '3075': Syntax Error in Date query expression DoctorsID=1 AND AppointDate=#08.08.2020'.

I use MS Access German version and regional settings - Germany
I tried to change the date format with the following code:

    Dim strDate As String
    Dim strSQL As String
    strDate = Format(AppointDate, Me.txtAppointDate, "\#yyyy\-mm\-dd\#")
    strSQL = "SELECT * FROM tblAppointments  WHERE tblAppointments.AppointDate > " & strDate

And now I get the runtime error 13 type mismatch.

Any help is highly appreciated.
Thank you.

 

[Gasman]

Your Format() looks wrong? It should be the item and a format?. You have two data items?
Debug.Print strSQL

Format function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[teddy1]

Sorry with my basic manual skills I can't get any further.

 

[Gasman]

Sorry with my basic manual skills I can't get any further.

What is AppointDate meant to be?, where does it come from? If the table, no need to format it. You need to format each arrgument in turn, so you would need two date variables, however you only appear to using one?

If you do need to format it do each separately, as I indicated before . look at the link on how Format is meant to be used.
Add Debug.Print after the strSQL line and look in the Immediate window (Ctrl+G) as to what it displays.

Thank you for using code tags BTW.

 

[teddy1]

AppointDate is a field in the table Appointments and is meant to hold appointment dates. The reason why I wanted to format is that because my regional setting Europe-Germany is, I get runtime error in date format. "run-Time Error '3075': Syntax Error in Date query expression DoctorsID=1 AND AppointDate=#08.08.2020'" Me.txtAppointDate is actually an entry field in the form, where the database user assignes the appointment date i.e. the value of AppointDate. The date entry in Me.txtAppointDate together with the field AppointTime remove the appointment time already given from the dropdown time list.

 

[Gasman]

That is what I guessed from what you had, but needed to be sure.

No need to even try and format AppointDate, just your data in the control txtAppointDate.

FWIW I use this constant a lot.

  Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.

An example can be found at https://www.everythingaccess.com/tutorials.asp?ID=Limiting-a-Report-to-a-Date-Range which is where I just copied that from.
So try

   Dim strDate As String
    Dim strSQL As String
    strDate = Format(Me.txtAppointDate, strcJetDate)
    strSQL = "SELECT * FROM tblAppointments  WHERE tblAppointments.AppointDate > " & strDate
    Debug.Print strSQL

Post back (copy and paste) what the debug.print displays in the Immediate Window.

The Const should be copied and pasted to the top of your module where this code is, or perhaps an external module, though I have not tried that location. In fact that link just has it with some Dim statements?

Just checked and I have mine in a separate module as

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

so you only need to declare it once.

 

[teddy1]

Dim strDate As String
Dim strSQL As String
strDate = Format(Me.txtAppointDate, strcJetDate)
strSQL = "SELECT * FROM tblAppointments WHERE tblAppointments.AppointDate > " & strDate
Debug.Print strSQL
SELECT * FROM tblAppointments WHERE tblAppointments.AppointDate > #08/08/2020#