Flexgrid or data grid and how?

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 :confused:

[IMG]test[/IMG]

Either type of grid should be fine for displaying like that.

To load data into a DataGrid I think it needs to be bound to the recordset in the properties of the grid.

For filling a FlexGrid, you can do it by using code like this.


It sounds to me as if you haven't got a database design yet.. is this correct? If so, what information do you want to store?

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

Ok I have the table completed, now how do I get the data from from the fields to fill in the header of the Flexgrid?

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
    .TextMatrix(0, .Cols-1) = [U]MyRecordset[/U].Fields("PersonName") & vbCrLf _
                            & [U]MyRecordset[/U].Fields("Title") & vbCrLf _
                            & [B]<date>[/B] & vbCrLf _
                            & "Room: " & [U]MyRecordset[/U].Fields("Room")
    [U]MyRecordset[/U].MoveNext
    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!!

Well I'm taking it step by step because I want to actually learn and understand not just cut and paste code. BUT....I'm stuck... I put in this code

VB Code:

Set rs = New ADODB.Recordset 
rs.Open " Select PersonID, PersonName,PersonTitle from Person ", cn, openKeyset, adLockOptimistic

I get syntax error in from clause


Am I using that statement correct.

Is the table name typed correctly...or is that exists in the db...

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.

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. :)

I found this project while browsing the net. I'm gonna see what I can learn from it to fit the needs you posted above.

Thanks

I have created the appointment form with

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.

ex. StartTime 8:00 EndTime 12:00

Sounds good. :)

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? ;))

Quote:

---

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]
    .TextMatrix(lngRowNo, [u]lngColNo[/u]) = [u]strApptText[/u]
  Next lngRow

..you just need to find/set all the underlined values first. ;)

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"
MSFlexGrid1.TextMatrix(mStartHour, 1) = "help"
MSFlexGrid1.TextMatrix(mEndHour, 1) = "help"
End Sub

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.

the times are in the the combobox thats filled by the database. Is that what you meant?

I'm not sure what you are refering to.

The times which the loop is finding are the ones down the left of the grid.

The value in the If statement ("8:00") should ideally come directly from appointment info in the database, possibly like this:

VB Code:

If .TextMatrix(lngStartRow, 0) = Format(rst("ApptStart"),"h:nn") Then

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:

1. Fill in the fixed rows/columns (as you have shown in post #11).
2. Load the appointment data from the database, but only for the particular day.
3. Add each appointment to the grid, which entails:
   
   1. Find the row for the start time (post #20/#22)
   2. Find the row for the end time (very similar - just change the row variable and database field)
   3. Find the column for the person ID (almost the same - depending how you have stored the ID data)
   4. Make the text you want (just joining a few fields from the database)
   5. 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

any help

I'm not completely up to speed on this, but ...

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

cheeries, P

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

This code shows you how to merge cells within a single column

VB Code:

With Me.MSFlexGrid1
        .Rows = 10
        .Cols = 5
        .TextMatrix(3, 2) = "Hello"
        .TextMatrix(4, 2) = "Hello"
        
        .TextMatrix(7, 2) = "World"
        .TextMatrix(8, 2) = "World"
        
        .MergeCol(2) = True
        .MergeCells = flexMergeRestrictColumns
        
    End With

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:

VB Code:

rs.close
  Set rs = Nothing
  rs1.close
  Set rs1 = Nothing

VB Code:

cn.close
  Set cn = Nothing

Thanks for all of your help Si....but I just can' figure these few steps out.

Which bit?

The post above has code for parts 3a-c, part 3d is just making a string (strApptText) from a few fields from the database, and part 3e is also above.

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. :eek2:

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. :sick: 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?

To call the sub from the other form you need to precede it with the form name, eg:

VB Code:

Call Form1.FillGrid

Quote:

---

Once I make changes could I upload prog for you to review and give suggestions?

---

Absolutely. :)

Okay Here I go. I have attached the zip file gettin an error on 3e

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:

VB Code:

strApptText = rs1.Fields("Notes") & vbCrLf & rs1.Fields("ApptType")
'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 more changes. No errors this time but it's still no filling in the rows.

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. ;)

:D :D :D :D

Thanks so much Si. Here's what I have. Everythings flowing well but now it says Column for person can not be found. I'm gonna stay up a while and try to figure this out. It probably something small I'm missing.

The answer is in the first line of my previous post ;)

I've got it and as you requested

Quote:

---

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.

---

I am ready. I just have one question about this code. Can you explain it.

VB Code:

If CDate(.TextMatrix(lngStartRow, 0)) = Format(rs1("ApptStart"), "h:nn") Then

Si, this post is a great primer for working with flexgrids - you should keep a link to it in your signature, or at least the juicy bits

tks, Peter

Thanks Peter, I may make a tutorial out of some of it. :)

Quote:

---

Originally Posted by graphixphantix

I've got it and as you requested

---

Show me ;)

Quote:

---

I just have one question about this code. Can you explain it.

VB Code:

If CDate(.TextMatrix(lngStartRow, 0)) = Format(rs1("ApptStart"), "h:nn") Then

---

I certainly can, there's a few things involved there!

As this is part of a loop, the bit ".TextMatrix(lngStartRow, 0)" reads the text of column 0 (the first column - times) for each of the rows that lngStartRow points to.

The CDate part around it converts the times we read from a String data type into a Date data type (which also includes Time, and doesnt actually need a date!). I have realised however that this bit is not needed - as we are converting the date to text aswell (which is the safer route - as the database has a date too!).

The "Format(rs1("ApptStart"), "h:nn")" bit gets the date/time from the database, and converts it into a text format of "hours:minutes" (for example "8:30"), which is the same format as the fixed column - so (without the CDate!) we are comparing the same style of text.

This means that the If will be true as long as the time of the appointment start is the same as the time in the first column. When the If is true, lngStartRow is pointing to the correct row, so we exit the loop (and can safely use the value later).


It would arguably have been better to use RowData (the same way as we use ColData for people), however as that only accepts numbers rather than times, it would have been more compex.

Look way down at the end for the CLOSED connections and recordsets :)

VB Code:

Public Sub FillAppts()
 
Dim lngStartRow As Long
Dim lngEndRow As Long
Dim lngPersonCol As Long
Dim strApptText As String
Dim LngColNo As Long
Dim LngRowNo As Long
 
Set rs = New ADODB.Recordset 'as we did with the connection
    Set rs1 = New ADODB.Recordset
        
rs.Open "Select PersonID, PersonName, Title  from Person", cn, adOpenDynamic, adLockOptimistic
      rs1.Open "Select ApptID, PersonID, ApptStart, ApptEnd, ApptType , Date, Notes from Appointment", cn, adOpenDynamic, adLockOptimistic
         ' *******************************************************
        '   THIS WILL FIND ROW
        ' *************************************************
        
        With MSFlexGrid1
    For lngStartRow = .FixedRows To (.Rows - 1)
    If CDate(.TextMatrix(lngStartRow, 0)) = Format(rs1("ApptStart"), "h:nn") Then
     ' 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 CDate(.TextMatrix(lngEndRow, 0)) = Format(rs1("ApptEnd"), "h:nn") Then
      '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
           ' THIS CODE I CAME UP WITH FOR 3d
  strApptText = rs1.Fields("Notes") & vbNewLine & vbNewLine & rs1.Fields("ApptType")
 ' THIS CODE I CAME UP WITH FOR 3e
 
    With MSFlexGrid1
For LngRowNo = lngStartRow To lngEndRow
    .TextMatrix(LngRowNo, lngPersonCol) = strApptText
  Next LngRowNo
  
  End With
    End If
       
  End With
    rs.Close
  Set rs = Nothing
  rs1.Close
  Set rs1 = Nothing
  cn.Close
Set cn = Nothing
    
End Sub

That's close, but not quite right I'm afraid.

The recordset rs is not used in that sub, so shouldn't be in there at all (including the Open line, etc).

The connection closing can be where you have it, but then you need to open it again before the next use - it is arguably better (but this is a personal preference thing) to only close it when the program closes (and only open it when the program starts). The bonus of doing it that way is that it takes less code (due to the way connections work behind the scenes, there isn't really any other difference).


Anyhoo... to show all of the appointments, add this line after the "With MSFlexGrid1" line:

VB Code:

Do While Not rs1.EOF

, and these lines just before the "End With" line:

VB Code:

rs1.MoveNext
    Loop

The "Do" and "Loop" lines there basically say "do the stuff between us until the recordset runs out of data", and the MoveNext line says "go on to the next record in the recordset" (in this case, the next Appointment).

B4 Which with statement. I have tried both 1 gives and error and one returns the same results as b4.

VB Code:

' *******************************************************
        '   THIS WILL FIND ROW
        ' *************************************************
        
        With MSFlexGrid1
For lngStartRow = .FixedRows To (.Rows - 1)
    If CDate(.TextMatrix(lngStartRow, 0)) = Format(rs1("ApptStart"), "h:nn") Then
     ' 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 CDate(.TextMatrix(lngEndRow, 0)) = Format(rs1("ApptEnd"), "h:nn") Then
      '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
    
           ' THIS CODE I CAME UP WITH FOR 3d
           
 strApptText = rs1.Fields("Notes") & vbNewLine & vbNewLine & rs1.Fields("ApptType")
 ' THIS CODE I CAME UP WITH FOR 3e
    With MSFlexGrid1
  Do While Not rs1.EOF
For LngRowNo = lngStartRow To lngEndRow
    .TextMatrix(LngRowNo, lngPersonCol) = strApptText
        Next LngRowNo
  rs1.MoveNext
    Loop
  End With
    End If
  End With
  
  rs1.Close
  Set rs1 = Nothing
  'cn.Close
 'Set cn = Nothing
    
End Sub

Ah, I hadn't noticed that extra "With MSFlexGrid1" line. :blush:

As you are already inside a With block for the same object, you dont need another. Remove the "With" line in the 3e bit, and the first "End With" line after it. Then my previous post will make more sense!

i have to fill my flexigrid with data of ms access
field is emp_id, name, basic, hr

also to mention the data is available in mdb file. I just have to retreive from their after check in whther exit.Can u help in this regards

Welcome to VBForums! :wave:

Please don't hijack other peoples threads.

See a useful sub here that can help with filling the grid, and see the ADO Tutorial link in my signature for how to get the data from Access.

If you have any problems getting any of it working, create a new thread in the Database Development forum (saying what code you are using, and what the problem is etc) and I'll take a look.

Ahh I just saw your post Si. I'm gonna try it when I get home later. I knew it was something small. :D

I finally got a chance to finish working on this and it works like a charm :) .

Is it possible to use monthview to determine which day of appointments is shown or do I have to design my own calendar control?

Yep, it's nice and easy... well, as easy as using any control!

As things are at the moment, you are not specifying which date to load data for - so all appointments (no matter which day) are shown. This is obviously not how it should be, and I should have mentioned it before!

To show only appointements for a specific date, you need to load the correct data in the sub that puts appointments into the grid. You also need to tell the sub which date to load the data for.

To tell the sub which date, you can make these changes:

VB Code:

Public Sub FillAppts([u]dteDateToShow as Date[/u])

VB Code:

Call FillAppts[u](Date)[/u]
'note that Date can be replaced by a literal date (eg: "1/1/2006"),
' or a variable/control value (eg: DateClicked , from MonthView1_DateClick)

To load the appointment data for only that date, make this change:

VB Code:

rs1.Open "Select ApptID, PersonID, ApptStart, ApptEnd,ApptType , Notes from Appointment [u]" & _
             & "WHERE Date(ApptStart) = #" & Format(dteDateToShow, "yyyy/mm/dd") & "#[/u]", cn, adOpenDynamic, adLockOptimistic

Would I need to create a new sub or add this (dteDateToShow as Date)to the current FillAppts sub.

Just add it (and the other underlined parts). :)

I get an syntax error in from clause

VB Code:

Private Sub Form_Load()
 
    Call GetConnString
    Call Fillnames
    Call FillAppts("2006/04/19")
 
end sub
 
 
Public Sub FillAppts(dteDateToShow As Date)
 
Dim lngStartRow As Long
Dim lngEndRow As Long
Dim lngPersonCol As Long
Dim strApptText As String
Dim LngColNo As Long
Dim LngRowNo As Long
 
    'Set rs = New ADODB.Recordset 'as we did with the connection
    Set rs1 = New ADODB.Recordset
    'rs.Open "Select PersonID, PersonName, Title  from Person", cn, adOpenDynamic, adLockOptimistic
    rs1.Open "Select ApptID, PersonID, ApptStart, ApptEnd, ApptType , Date, Notes from Appointment" & "WHERE Date(ApptStart) = #" & Format(dteDateToShow, "yyyy/mm/dd") & "#", cn, adOpenDynamic, adLockOptimistic

Yep. Notice the difference between our posts (not just the fact I spread mine across two lines to make it easier to read).

The space (or rather, lack of) is treating Appointment and Where as two parts of the same word.

what is the # sign for? If you don't mind me asking? Show I PM you question like this one.

Keep em all here, PM's are just annoyances when you have as many threads open as I do!


The # sign is what Access uses to mark the start/end of dates (other databases use ' ), without that the text would be intepreted as a calculation (eg: 2006/8/3 = 83.5833) instead of a date.