complex schedule / appointment book

[shutzy]

i have asked this question many time but i thought i would give it one more try. i am tryin to create quite a complex appointment book. i already have something in place that got me started and was great for testing the whole database but now i need it with more umph. i have attached an excel file that gives quite a good representation of what i require. i am looking for some major help on this.

so if anyone is able to give me a foot up on this i will be very greatful.

thanks

 

[sparks80]

Hi,

Have you explored cross-tab queries before. You haven't given any details about your table structure, but I assume you have a list of appointments that give details of the customer name, employee name, appointment time etc.

You can use a cross tab query to produce a grid with the employee name as the column headers, time as the row headers, and the customer's name as the data filling the grid. This should be a good starting point.

I would suggest you try and limit the number of colour codes to just 3, rather than 4. This will allow you to use conditional formatting to change the colour of textboxes on a continuous form according to the status.

 

[VilaRestal]

Having done something similar before I warn you it's not easy. There's a lot of coding because you're not displaying the data in the same way it's stored.

I've thrown together a demo of how I would go about it (attached). There's lots left to do but hopefully that will give you some idea of how this approach works. (And I'm not going to try and describe the approach than just show you.)

You might want to put all employees on one subform. It would be nice if you could but I think you would hit the maximum number of controls on a form.

Anyway, see what you think and let me know if you have any questions.

 

[shutzy]

the table structure is like this.

i have tblOrders that is the appointment. this has the start time,date, client name, status.
then i have tblOrdersItems, this has its own unique id,the tblOrders unique id, Items unique id, Individual Item price. individual item lenght(minutes), employee assigned to the item.

i have never explored cross-tab queries. sorry thats a lie. i clicked it in create but deleted it when i didnt know what i was doing.

could cross-tab queries give me the block of colour i am looking for. also you say keep it to 3 colours. the red is only for visual purposes. i suppose if you look at your watch and its more than 10 minutes past the appointment start time you know they havent turned up. also i didnt know you can conditional format. ie my status column has a no. 1. could i turn this #grey in a list box. do i need a list box to show the data in a form. so many questions.

sorry scrap that. just read you say continuous form. how would this work. i dont want to be able to edit any info.

i can upload my database. its rather large(i think at least)5mb. so ill have to trim it down to get under 2mb. also its in 2010 version. is this ok or do you want it in .mdb.

can you give me some insights into cross-tab queries.

thanks for your suggestion. this is the furthest i have got for ages regarding this.

 

[sparks80]

Hi,

If you could upload a sample of the database that would be very helpful. You can remove most of the data (all if it is confidential).

To reduce the file size you could try compacting the database, and zipping the resulting file.

I think given the complexity of what you are trying to do, it might be easier to show you by example, rather than trying to explain in words!

 

[shutzy]

thanks vilarestal. ill have a look at the demo in the morning. what do you mean about maximum controls?

what do you mean about put all the employees on one subform?

i have been told by vbainet that it takes alot of coding. i am prepared to learn as you can probably guess i know not that much about vba. i have picked up bits but at the moment i can only adapt code not write from scratch.

the whole database works great. but its just this niggle of a diary view that i need. the other thing i need to do is create course items. say they pay 60 for 4 of the same. i think i have this sussed but not started it yet. still on paper. i may be back with another thread regarding this.

thanks again for the demo

 

[shutzy]

ill upload it i n the morning. ill kepp all the names and treatment history in as the names mean nothing without addresses and tel no. you will be able to get a full picture then. i am quite proud of it and it is really a credit to this forum. theres alot of things going on in the background like calculation text boxes in the frmDepartures-PaymentScreen. but it work and most of all i know how it works.

thanks

 

[VilaRestal]

it will only really make sense when you see it. Access has a maximum number of controls you can put on a single form (i forget the exact number, about 750). It depends how many employees you'll have and just being able to duplicate the subform for each saves a lot of work.

It's a different approach to what sparks is suggesting. I think it's more what you're after but comes with a lot of code work but it can be done.

It depends how confident you are with VBA. If you're new to it then it'll probably too much to take on this approach.

See what you think when you look at it (and see if you can follow the code that's there).

 

[sparks80]

Hi,

I agree with VilaRestal, the demo provided will be more flexible than my suggestion.

The problem is that unless you are familiar with VBA, I think putting together the code to make it do exactly what you want will be quite hard.

For information, the last time I checked, the maximum number of controls on a form was 754.

If you would still like me to take a look I am happy to!

 

[shutzy]

here we go guys. not that big really. compressed down to 350kb. great. its in .accdb. hope this would give you a clearer idea if what i am after. there is already a useable diary but its only good for 1 employee and i have had to create a few queries to get the end time and put that in another column.

hmm... that was a suprise. i compressed it as a .rar file and it wont accept it. i think they should start doing cause now its a .zip file is 4 times bigger.

thanks

 

[shutzy]

dredging up old post but for me it may be worth it. i have taken a deeper look at the DiaryDemo that VilaRestal provided. after many months of trying to find the perfect diary i have been going over all the files that i downloaded from here and i do like the approach of the DiaryDemo.accdb.

VilaRestal could you please let me know how you get the data in the text boxes. i have followed the code and changed it to what i think it should be in my table structure.

my tables that hold the appointment data are

tblOrders(this is the parent/ client details, time start, total cost)
tblOrdersItems(this is the child/ OrdersID, ItemsID, Employee, Cost, TreatmentLength)

what i would need to have in the frmDiary is the Clients FName & Surname i would also like to have the item that is the appointment ie

Bloggs, Joe - Eyebrow Wax(this should be an easy 1 as the item is only 15 minutes. but if the item was say 45 minutes then it would repeat through 3 txtBoxes. im not too worried about that at the minute as the main task is to get it integrated in my database.

thanks for the demo tho. its a great foundation.

 

[VilaRestal]

VilaRestal could you please let me know how you get the data in the text boxes. i have followed the code and changed it to what i think it should be in my table structure.

Well the subroutine that does it is PopulateForEmployeeAndDate.
You'd need to adapt that for your tables.
I don't quite get the structure. Employee is stored in the subtable (tblOrdersItems). The appointments appear to be the parent table (tblOrders - they have a [time start] field). So each appointment could have more than one employee? So when an appointment is added (clicking on an empty time in the subform) it will open a new appointment (order) form and that will have a subform showing which employees it applies to (the items). But also a TreatmentLength!? Are you sure each item shouldn't have a time - be the appointment? 1 appointment = 1 client and 1 employee?

If it is the way you've done it then whenever an appointment is added (for possibly multiple employees) then you'd probably want to repopulate all the subforms for all employees.

Anyway, assuming it is this way then these are the things you'd need to do:

1. Add more textboxes to the subform for all the time slots you'll need and do the event handlers for them: laborious but easy - just be careful and methodical and make no mistakes. copy and paste existing ones to keep existing properties and just rename the pasted and add their event handlers. Increase the PERIODCOUNT constant accordingly (to the new number of textboxes).
2. Adapt the strSQL in PopulateForEmployeeAndDate for your tables (selecting Orders where there exist Items for the subform's Employee and for the current date). All it really needs to do is select the start time, the start time + TreatmentLength for that employee/date and a calculated display field (I can't see what field 'Eyebrow Wax' would come from). You're going to want to store the date as well as the time and there's no reason not to put those in one field: change [start time] to StartDateTime.
3. Create a data entry/edit/display form for the Orders with accompanying subform for that items.
4. In the generic click event handler for the textboxes (Sub txtClick(i)) change the msgboxes for opening this new form with some opening args to tell it fill in the date and time and at least one item for the employee. (The form's load event would parse the openargs and set things accordingly) if it's a new record. Open the form as a dialog and then put code in after that will repopulate all the subforms (call a public sub on the main form to do that) as any might now have been affected by the changes.
5. The data entry/edit form will need some validation to stop items overlapping. That will be quite complex to do: for each Item's employee look for other Items for other orders on the same date and check none overlap with the new or edited one. The populate sub won't complain if they do overlap it will just overwrite the most recent one over the periods that overlap with the older one. Also stopping order items extending beyond the range of the periods allowed.

I don't want to try to explain it anymore than that. Feel free to upload it and ask me to check over for a specific problem as you try to do each task. It's not going to be easy, far from it, but it is doable and it's important you understand it when you've finished both so you can maintain and improve it and so you can feel proud of it. It will be far above what most people who try ever achieve in Access (but then it is an ambitious design).

 

[shutzy]

Thanks for the reply. I'll go through each step 1 at a time. As far as the time is concerned its just the way it is structured but it can be changed if it will make things run smoother and easier to maintain. I do hope(as a novice) its not too ambitious. I already have a database that we paid over £2000 for and there is some aspects that both me and my partner don't like and the. We bought another place and need one for there. So that is my ambition. The time can go in the tblOrdersItems start and finish. The 'eyebrow wax is in the tblItems which the foreign key in tblOrdersItems relates to.

One question, is it possible to not repeat the same info over 2 or more text boxes.

Just to explain the structure. The appointment(in my eyes at least) is the order. The appointment is compiled of 1 or more treatments. I need it done this way as the system we use at the minute does them all individually. It dosent seem to have appointments just items. So if a client has 5 treatments split over 2 appointments and you arrive the client it will assume that the client has arrived for the whole lot and as the client is departed for the first visit they have to pay for the 2nd visit of that day. Sometimes they may not want to but it can't be done any other way.

I'll let you know how I go on.

 

[shutzy]

suprise suprise i have run into my first issue. i have done all the txt boxes throughout the time i want. i have given them all a unique name. ie txt08:00, txt08:15 etc. i ma now going through all the vba that is in the frmDiary. i have not gone through all the event handlers for each txt box yet as i dont really understand how it works.

this bit:
Private Sub txtClick(ByVal i As Integer)
'TODO: Open Existing or new appointment for this employee on this day at a start time based on the period selected
'Check whether existing appointment is there (the current state of the textbox should do), if so open that, otherwise open new
With Me.Controls("txt" & i)
If EmployeeID = 0 Then
'Bad Employee was supplied to PopulateForEmployeeAndDate. Can't create appointments
Else
If Nz(.Value, "") = "" Then
'Open new appointment
MsgBox "Open New Activity"
Else
'Open existing appointment
MsgBox "Open Existing Activity"
End If
End If
End With
End Sub
is this needed for each text box or do i just put in

Private Sub txt4_Click()
txtClick 4
End Sub

for each txt box ie

Private Sub txt08:00_Click()
txtClick 1
End Sub

and so on.

now my first issue is(if it is relevant as this stage as i have not put in all the event handlers for the txt boxes) runtime error 3265. item not found in this collection.

.Form.PopulateForEmployeeAndDate rs!ID, CDate(DateValue(txtDate))

do i need to change anything for this? as the only item that corresponds to anything on the forms is txtDate and that has not been changed.

thanks

 

[shutzy]

i am currently just formatting the form that it will be in. as i want it to look 'pretty' and the way i have my forms(main form background 1 colour. then a box of a different colour behind each 'object') what will happen with my box behind the subforms and my forms width if i have say 10 employees. i dont have 10 but its a large enough figure for me to describe my worries. a possible solution i have thought of is a subform with a subform. the primary subform would have scroll bars so it would not matter how many employees i have. is that a silly suggestion?

 

[VilaRestal]

OK, for your first question:

Yes that's how you would handle the textbox events. In better languages you could do something like:

Private Sub txtClick() Handles txt1.Click, txt2.Click, txt3.Click etc

but in VBA each control has to have its own event handler but that can be a one liner call to a generic sub passing a parameter showing which textbox the event is for.

Naming the controls with a colon ) in the name isn't good. That's always going to threaten to cause issues. I hate to say that you really should rename them without any spaces or non alphanumeric characters except underscore. It will save you grief down the line.

And in any case naming them simply txt1, txt2, txt3 has the distinct advantage that you can loop through them with For i = 1 to MAX: Me.Controls("txt" & i) and the number each one's event is passing to the generic event handler can be used to get back at the textbox that sent it in the same way. Sorry to be the bearer of that bad news.

Second question:

Yes you will need to change the PopulateForEmployeeAndDate sub for your tables/fields and change that line that calls the sub accordingly . The "item not found in this collection" refers to your table not having an ID field (in the rs recordset - the first parameter). As I said before, feel free to upload where you're at with that.

Third question:

Having overlapping controls can cause visual issues in Access. Probably (mostly) it will be fine but you could see flickering or other strange things at times as a result. A better way I think would be to just change the colour of the background of each subform at runtime.

Fitting 10 of the subforms across the screen is going to be a problem. There are various ways I can think of to make them fit on the screen without having to scroll left-right and without risking exceeding the maximum form width but most require a lot of code and you've already got a lot on your plate so I think the best way would be to put them on a tab control in three, four or five per tab.

 

[shutzy]

im sorry to go off point but is it possible to do this with a query. ive been playing around with cross-tab queries and i can get the time down the 1st column, the employee name as the header for the second column but i cant get the rows to be populated with the first and surname. if its possible to do this with a query then the form control amounts will never be a problem.

or am i just barking up the wrong tree?

 

[VilaRestal]

You could base it on a normal query that outputs the three fields you want:
Format(DateTime, "hh:mm")
Employee
FirstName & " " & Surname

Then they would be the Row, Column and Total = Last fields of the crosstab respectively.

The underlying query would be parameterized to filter for only a particular day that the parent form would set on load and perhaps on change of unbound textbox.

The difficulties are:

You can't base a form on the crosstab without knowing the Employees at design time. New employees added means changing the design of the crosstab. To get round that will require code too.
It has to be a form for it to be anything other than a display system. Presumably you're going to want events on the interior to allow users to add and edit appointments. That will require similar code to any other method.

Most importantly it won't show the duration of the appointments. Just the start time unless you do something complicated and base it on a query or temporary table that has all the periods you want as times and is joined to the data in some way.

So, yes crosstabs would be a bit simpler (a lot simpler in places but no simpler in others) but would be difficult to display the time periods rather than just the start times (not a visual representation of how long each appointment is by default).

I hope that makes sense. So yes it is an option but not my preferred one.

 

[shutzy]

thanks for the reply. i kind of knew that the crosstab query would be an option. when i tried to create 1 i got the column header as the employee and each employee was added automatically dependant on the date and if they had any treatments in. i do also appreciate that turning the results of the query into a functional diary would require code beyond my own capabilities. so at least i know its an option if i ever do need to go beyond the capabilities of the DiaryDemo.accdb.

i have gone through the sub routine and im unsure if i have done it correctly. i dont really follow which table that the code is refering to when it wants the EmployeeID.

ive put it below

Public Sub PopulateForEmployeeAndDate(ByVal Employee As Integer, ByVal DiaryDay As Date)
Dim i As Integer
If DCount("*", "tblEmployeeList", "EmployeeListID = " & Employee) <> 1 Then
'Bad Employee
lblEmployee.Caption = "ERROR"
EmployeeID = 0
For i = 1 To PERIODCOUNT
With Me.Controls("txt" & i)
.Value = ""
.BackColor = RGB(127, 127, 127)
End With
Next i
Else
EmployeeListID = Employee
DiaryDate = DiaryDay
lblEmployee.Caption = DLookup("FirstName", "tblEmployeeList", "EmployeeListID = " & EmployeeListID)
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblOrdersItems WHERE EmployeeID = " & EmployeeID & " AND Int(StartDate) = " & CLng(DiaryDate)
Debug.Print strSQL
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount = 0 Then
For i = 1 To PERIODCOUNT
With Me.Controls("txt" & i)
.Value = ""
.BackColor = vbWhite
End With
Next i
Else
rs.MoveFirst
Dim StartTime As String, EndTime As String
Dim StartHour As Integer, StartMinute As Integer, EndHour As Integer, EndMinute As Integer
Dim PeriodsCovered As Integer, StartPeriod As Integer, LoopEnd As Integer
Dim OrdersItemsID As Integer
Do While Not rs.EOF
ActivityID = rs!ActivityID
StartTime = Format(rs!StartDate, "hh:mm")
StartHour = Left(StartTime, 2)
StartMinute = Right(StartTime, 2)
EndTime = Format(rs!EndDate, "hh:mm")
EndHour = Left(EndTime, 2)
EndMinute = Right(EndTime, 2)
PeriodsCovered = 4 * (EndHour - StartHour) + (EndMinute / 15 - StartMinute / 15) - 1
StartPeriod = 4 * (StartHour - EarliestHour) + StartMinute / 15 + 1
LoopEnd = StartPeriod + PeriodsCovered
If LoopEnd > PERIODCOUNT Then LoopEnd = PERIODCOUNT
For i = StartPeriod To LoopEnd
With Me.Controls("txt" & i)
.Value = DLookup("Items", "tblItems", "ID = " & ItemsID)
Select Case ActivityID
Case 1
.BackColor = RGB(127, 255, 255)
Case Else
.BackColor = RGB(200, 200, 200)
End Select
End With
Next i

rs.MoveNext
Loop
End If
rs.Close
End If
End Sub

thanks again

 

[VilaRestal]

Ouch my head hurts reading that.
To make it a bit easier to read could you edit the above and put it in a Code block (by clicking Go Advanced) with the indentation intact please? You may need to re-copy the code over what you've done to do that.

Also a full recap on what tables and fields and relationships you now have.