How would I create a table like the one below? Would I use flexgrid or datagrid? and finally......... How would I populate it with data from a access data? I am familar with ado but how do I get the info in the grid. Oh yeah would I make a field for the times to the left in the database.......I'm stumped
Well I'm taking aver as the scheduler at my job and they use this excel spreadsheet that's way too busy for my eyes. So what I want to do is display each person as column on the grid and be able to right click on a time which opens a form and allow me to determine from a dropdown menu how long the appointment will be and have it display like the image below.
I see.. in that case I would recommend a FlexGrid - as it allows you to merge cells as you have shown.
As to the database design, presumably this is for multiple days, in which case I would recommend a design like this:
Table: Person
PersonID - Autonumber
PersonName - Text
Title ("Dentist" etc) - Text
Room - Number (assuming numbers are assigned to the person, rather than the appointment)
Table: Appointment
AppointmentID - Autonumber
PersonID - Number (link to Person table)
Description - Text
ScheduledBy - Number (link to PersonID of Person table)
ApptStart - Date/Time
ApptEnd - Date/Time
Good stuff.. and I like the way you are thinking - most people would aim for the whole thing at once.
First of all load the data into a recordset, using SQL like this:
Code:
SELECT PersonID, PersonName, Title, Room
FROM Person
Then you can load the data into the grid. I would recommend setting the initial number of columns/rows to 2 (and FixedRows/Columns to 1) using the property pages at design time, then use code like this:
VB Code:
With MsFlexGrid1 'change to the name of your control
.Cols = 2
.Rows = 2
.FixedCols = 1 '(if set via property pages, these 2 lines can be removed)
.FixedRows = 1
Do While Not [U]MyRecordset[/U].EOF 'change (and below) to the name of your recordset
If Not [U]MyRecordset[/U].EOF Then .Cols = .Cols + 1
Loop
End With
The <date> bit needs to be changed to whatever date you want (or removed from the grid) - presumably this will be selectable in another control, so that you can see multiple days.
Note that you will need to store the PersonID too, but I've had a few drinks so can't quite work out the right method of doing it at the moment!!
Ahhhh I figured it out now on to the next question. How do I get the times down the first coloumn to the left in 30minute increments. I almost forgot how would I get the text in the header centered and make all headers same width.
Last edited by graphixphantix; Apr 6th, 2006 at 01:31 PM.
Reason: add another question
To use times we'll actually use the Date data type, combined with the DateAdd function. This code should go inside the "With.. ..End With" block, probably after the "Loop":
VB Code:
Dim dteTime As Date
dteTime = "8:30" 'whatever start time you want (24hr clock)
Do While dteTime < "17:30" 'whatever end time you want (24hr clock)
.TextMatrix(.Rows-1, 0) = dteTime
dteTime = DateAdd("n", 30, dteTime)
.Rows = .Rows + 1
Loop
To set column widths, simply use something like this:
VB Code:
.ColWidth([i]column number[/i]) = 1000 '(whatever width you want)
You can use a column number of 0 (or higher) to set the width of a specific column, or -1 to set the width of all of them at once.
For centering, check out CellAlignment (unfortunately ColAlignment is no use in this case). I think theres a good example for it in the help.
Looking good........ So now how do I make the times a user picks from another forms combobox merge cells from start-end times. I want them to be able to click a column and open the form and create appointment for person who's column was clicked. Oh this is what I have so far. Thanks si
What I would do is have the other form save the data to the database, then reload the grid on this form based on data in the database (that way you can use exactly the same routine for exisiting data - and if the method changes it only needs to change in one place). Note that the date needs to be stored along with the time (in the same field).
You need to set the Merge properties of the grid, it would be best to do this in the properties window, as the merge style it will not change.
In order to get the appointment data into the grid, you need to first load the relevant data from the Appointment table (all data for the specified date - ignoring the time part of the field). I would recommend having an extra control (possibly a DatePicker from "Microsoft Windows Common Controls X.X") to specify the date.
When you have the Appointment data loaded, for each row of data you need to find the column to put the data into (using the PersonID found when filling the fixed row), then the first/last row based on the time part of the ApptStart/ApptEnd fields; then put the text you want into all of the cells in the range of cells that you want merged - the merging will happen automatically if you have set the right properties of the grid.
I'm guessing you'll have a couple of problems doing all of this (as it is the most awkward part of the process), but I'll let you have a go at it yourself first.
1. combobox -PersonName --- auto filled from database
2. combobox StartTime & End Time --- filled with times from 7:30 - 5:00
3. combobox ApptType --- different types of appointments change cell color
4. dtpicker --- for date so that appointment can be placed on certain day
5 textbox notes --- for additional notes
Now I am stuck on how to make appointments show on grid in-between the times selected. I know that content has to be the same in the cells for them to merge. Also how do I trick cell into believing content in-between selected times are the same.
I hope you are saving PersonID (rather than name) to the database, as even tho this is a small system there is the possibility that at some point you will have two people with the same name.
In order to get the grid to show the merged cells you need to fill in all cells in the range, so if for example in the grid in post #11 you wanted to show "William James" as having an appointment from 8:00 to 10:00 you would put all of the text into the all of the cells in that range (column 2, rows 4 to 7).
If you are struggling at this point is it probably best to show us the code you have, as it is likely to be clearer for you if we show you appropriate "corrections".
would I use an if statement to say if cboStartTime.text=8:00 then ...... and if cboEndTIme.text=10:00 then .......
MSFlexGrid1.TextMatrix(4, 2) = "How do I make text from appttype and personname fill here"
MSFlexGrid1.TextMatrix(5, 2) = "and here"
MSFlexGrid1.TextMatrix(6, 2) = "and here"
MSFlexGrid1.TextMatrix(7, 2) = "and here"
And if I'm not mistaken all text has to be the same to merge....right?
You should use a loop (combined with If statements) to find the rows for the start time and end time, and something similar for finding the person. You can then use another loop to set the text.
I don't understand why you are relating this to combo boxes tho, you should be getting the values from the database (at least for when you load the grid - which can be used for both cases, so why bother to code it twice? )
Originally Posted by graphixphantix
And if I'm not mistaken all text has to be the same to merge....right?
Correct - what I would do is put the text into a variable, and assign the variable to each cell in the range within the final loop mentioned above. The (final) loop to set the text could be something as simple this:
VB Code:
For lngRowNo = [U]lngStartRow[/U] to [u]lngEndRow[/u]
I'm completely stumped. Do I need to write if statements for each time. Do you have any samples I can review that explains this a little more in depth. I know how to get text in the rows like this
VB Code:
MSFlexGrid1.TextMatrix(1, 1) = "help"
MSFlexGrid1.TextMatrix(2, 1) = "help"
but I'm lost with the loops and other if statements.
In between 7:30 and 10:00 is 30 minute increments.
If I wanted to merge all of the cells in between would I have to write if statements for them all. ex. If statement for 8:00, 9:00 etc
VB Code:
Private Sub Command2_Click()
If cboStartTime(0).Text = "7:30" Then mStartHour = "1"
If cboEndTime(1).Text = "10:30" Then mEndHour = "2"
Ok, well you basically need to use loops combined with If's... to find the row for the start time I would do something like this:
VB Code:
With MSFlexGrid1
lngStartRow = .FixedRows
For lngStartRow = .FixedRows to (.Rows - 1)
If .TextMatrix(lngStartRow, 0) = [U]"8:00"[/U] Then 'this value should come from the recordset
Exit For 'we have the row, so exit
End If
Next lngStartRow
If lngStartRow >= .Rows Then
MsgBox "Row for start time not found!"
Else
'find the end row, column, etc..
'fill the text (as in the post above)
End if
..note that if appointments can start at times other than every 30 minutes you will need to have an extra If statement in the loop.
The loop here just repeats for each (data) row in the grid, and for each one checks the set time (in the example "8:00") against the data in column 0 for that row. If the values are equal the loop is exited (with lngStartRow being equal to the row it was found on); if the text is not found, the loop keeps going until lngStartRow gets too high - and causes the message to be shown.
Sorry I know this must be frustrating. What I want is to be able to dblclick the grid open up a form and on the form I want to place appoinments for people at a certain time and in order for me to do that I have to some type of control to say (ex. William James has appt from 8:00- 10:30) So I used combobox to select the times. Once I'm done I save. It updates database and slots from 8:00-10:30 are merged.
No problem - I know you are trying to actually learn, rather than have me write the code for you. That is so much better it is hard to explain, so I have no problem with it taking a while.
As mentioned above you can enter the appointments in the grid either:
a) from the "add" form when a new appointment is added, and from the database when this form loads,
or b) just from the database. (just calling the same code after you add a new record as when you load the form).
The second method is far less work, as you are only writing the code once (it will only be one line of code to call the same code). It will also mean that you dont need to change code in two places if you find something that needs to be added later.
Have you got the "add" form saving the data to the database? (if not, see this FAQ thread)
Once you've got that bit working, you just need to work on filling the grid. To do this, you need to do the following:
Fill in the fixed rows/columns (as you have shown in post #11).
Load the appointment data from the database, but only for the particular day.
Add each appointment to the grid, which entails:
Find the row for the start time (post #20/#22)
Find the row for the end time (very similar - just change the row variable and database field)
Find the column for the person ID (almost the same - depending how you have stored the ID data)
Make the text you want (just joining a few fields from the database)
Add the text to the range of cells (post #17)
If you have any problems it is probably best to upload your form, so we can see exactly where you are (so we can tell which bits you need help with), and be sure that what you have so far is on track.
Okay now I'm on step 3a. I have placed the code from post 20 and 22 to a cmdbuttton just to check code. I have added the statement below. Now with that code it knows how to find a row. How would I make it find person ID.
VB Code:
Dim lngStartRow as Integer
Dim lngEndRow as Integer
Private Sub Command2_Click()
With MSFlexGrid1
lngStartRow = .FixedRows
For lngStartRow = .FixedRows To (.Rows - 1)
If .TextMatrix(lngStartRow, 0) = Format(rs1("ApptStart"), "h:nn") Then
Exit For 'we have the row, so exit
End If
Next lngStartRow
If lngStartRow >= .Rows Then
MsgBox "Row for start time not found!"
Else
'find the end row, column, etc..
'fill the text (as in the post above)
End If
End With
With MSFlexGrid1
lngEndRow = .FixedRows
For lngEndRow = .FixedRows To (.Rows - 1)
If .TextMatrix(lngEndRow, 0) = Format(rs1("ApptEnd"), "h:nn") Then
Exit For 'we have the row, so exit
End If
Next lngEndRow
If lngEndRow >= .Rows Then
MsgBox "Row for end time not found!"
Else
'find the end row, column, etc..
'fill the text (as in the post above)
End If
End With
End Sub
Last edited by graphixphantix; Apr 13th, 2006 at 07:40 AM.
you should have PersonID from when you click the flexgrid - the user clicks in a certain column because it's for that Person(ID). Just put that into memory somewhere (read it into the db or into a variable) and pull it out again when you need it
New Post please take a look --Flexgrid or data grid and how?
I have mostly everything setup. I'm just having a hard time with getting data in to the flexgrid. When you dblclick on flexgrid an appt from opens. The selections you make are saved. Now how do I get the cells to merge for each provider on a specific day. (ie. If person has appt from 7:30 - 10:00.. merge those cells). I have uploaded the zip file
I've realised that I hadn't told you where to store the PersonID! There is a "Hidden" property for the grid called ColData, which can hold a single piece of numeric data for each column.
To set this, just add the following line of code to the first do loop (before the ".TextMatrix" line):
VB Code:
.ColData(.Cols - 1) = rs.Fields("PersonID")
You can then get use this to find which column to use, so the code for 3a-c would be like this:
VB Code:
Dim lngStartRow As Long
Dim lngEndRow As Long
Dim lngPersonCol As Long
With MSFlexGrid1
For lngStartRow = .FixedRows To (.Rows - 1)
If .TextMatrix(lngStartRow, 0) = Format(rs1("ApptStart"), "h:nn") Then
Exit For 'we have the row, so exit
End If
Next lngStartRow
For lngEndRow = .FixedRows To (.Rows - 1)
If .TextMatrix(lngEndRow, 0) = Format(rs1("ApptEnd"), "h:nn") Then
Exit For 'we have the row, so exit
End If
Next lngEndRow
For lngPersonCol = .FixedCols To (.Cols - 1)
If .ColData(lngPersonCol) = rs1("PersonID") Then
Exit For 'we have the column, so exit
End If
Next lngPersonCol
If lngStartRow >= .Rows Then
MsgBox "Row for start time not found!"
ElseIf lngEndRow >= .Rows Then
MsgBox "Row for end time not found!"
ElseIf lngPersonCol >= .Cols Then
MsgBox "Column for person not found!"
Else
'3d and 3e here :)
End If
End With
Note that this code (along with the rest of the code in Form_Load, except the first line) should be in a different sub, which you call from Form_Load and when an appointment is added.
This is so that you don't have to re-load the form to see updates from new appointments (or new people if you allow that too), and the grid is cleared before re-filling.
One extra thing to note is that rs and rs1 should be closed when you finish with them (at the end of the sub), and so should the connection (presumably when you close the form). The code for those is like this:
What I have decide to do is instaed of opening another form to create an appointment. I added the controls to the same form. I am running into several problems now.
1. My do loops keep conflicting with each other. (cbobox fill loop and the loop that fills the header). Either the cbo will fill or only one column.
2. I have created subs to fill header, FindRow, Fillgrid which are all under the save cmd but when I save I only get msg from msgbox.
I've read the code looking to learn as I go. But I just can't figure out where I am going wrong. with the whole 3rd step.
Fair enough.. from a "user" point of view I would personally prefer an extra form, but that is compeletly up to you.
For 2 there, I would make only one sub which contains all of the grid functions, as that will also clear the grid and add/remove any changed data for people/appointments.
For 1 there I presume you are using the same recordset for both, which I wouldnt recommend. I'd use a local recordset in form_load to fill the combo, and a second local recordset in the grid sub.
You're right about the additional form. I'll keep it that way. but When I update the data from the form and try to call the the sub to fill the grid I get and error. The Sub is public so why can't I call it from the appt form. Also I only got the Loop conflict when I combined the forms. I'll switch that back. So just to recap
1. Save data to db.
2. call subs to fill grid
3. close form
Once I make changes could I upload prog for you to review and give suggestions?
I made a deliberate mistake above... instead of lngColNo (as in post #17) I used lngPersonCol for the column, just replace lngColNo with lngPersonCol in the loop to make it work.
Note that the code for 3e should be before the "End If", as 3d is. This is because the lines before it are error messages for when (or if!) things go wrong, and you cant find the rows/column. If you cant find them, you will be writing to the wrong columns - and then getting errors that will crash your program (as you havent got error handlers yet). Note that as the code is already inside a "With MsFlexGrid1" block, you dont need that again.
The code for 3d is ok, but you will probably want to add new lines, eg:
'you can use vbNewLine instead of vbCrLf if you prefer
Also, the code to load the recordsets should be in the same place as the data is used, as otherwise you cannot tell what they will contain. You also need to close them (and the connection) when you are finished with them, as mentioned a few posts ago; failure to do so could cause crashes and database lock-ups. The recordsets should also be declared locally (ie: within the subs they are used in) unless you need to share their data between forms.
Oh, and currently you are not loading appointments when the form is loaded. Every time you call Fillnames you should also call FillAppts (and vice-versa). Alternatively, put both subs into one (as between them they fill the grid).
I have made the changes I suggested above (and at the start of post #30), and it is almost working - just a minor bug on the "find a row" bits, which should have CDate around the text value, like this:
VB Code:
If CDate(.TextMatrix(lngStartRow, 0)) = ...
Note tho that we are currently only showing the first appointment - which is empty in the database you posted! (the cells are merged, but there is no text).
I'll tell you how to show all the appointments once you show me that you are closing your recordsets and connection when you are finished with them.
Reply With Quote
)
Originally Posted by graphixphantix
with the whole 3rd step.
