Flexgrid or data grid and how?

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

now..... I'm get this runtime error 2147217900 (80040e14)

wrong number of arguments with function in query expression 'Date(ApptStart) = ##'

Oh dear... I got confused again (there is no conversion function called Date in SQL), sorry about that!

Instead of: Date(ApptStart)

It should be: Format(ApptStart, 'yyyy/mm/dd')

okay it compiled and ran without error:D but no appts show in the grid

I used this code just to pull dates from today. Does the format in the db make a difference DD/MM/YYYY or YYYY/MM/DD?

VB Code:

Call GetConnString
    Call Fillnames
    Call FillAppts(2006 / 4 / 20)

First thing to check, is do you actually have this:

VB Code:

Call FillAppts(2006 / 4 / 20)

or do you have (as it should be, and was when you posted before) like this:

VB Code:

Call FillAppts("2006/04/20")

If you have the latter, what values do you have in the database for ApptStart?

I sat looking at the code............. This is what I found.


the date is stored under ApptDate so I switched ApptStart which is the appointment start time with Appt Date

VB Code:

Format(ApptStart, 'yyyy/mm/dd')

Then I switch around the format of the date to mm/dd/yyyy to match the way the db stored my date. That didn't work because the Access db doesn't put a zero in front of single digits. So I swithed to m/dd/yyyy to test it and it worked. So my next question is how do I get the db to store the zero in front of single digits. I will be researching on my own until I get a reply thanks. Oh yeah I'm gonna try to use the Monthview1_DateClick instead of a date.

I see no reason for a separate AppDate field (it can be held in the Start/End fields along with the time), but that is up to you.

The format inside the SQL statement (that works with the field) doesn't care what format the database field is - as long as it is a date. What you should probably do tho is change the # marks to ' so that you are definitely comparing equivalent items (as the format function in the SQL returns a string).

The problem with using a format other than as I showed is that if a user has different settings on their computer for dates, then comparisons may not work (or may work for the wrong dates).

I'm still taking this thing step by step and now that we have this part somewhat complete. I see more possibilities for this app. But I will search forum more before I post but I would like your suggestion or somethings I'm going to try.

1. I have made a form which allows user to Add/Edit/Delete Employee info. I have textboxes with buttons which control navigations and update of records. Would it be better to use flexgrid?

2. I would like to change the color of the merged cells based on thie ApptType selected when the appt was scheduled. (ex. If user selected ApptType Medical then make merged cells blue). Would I have to make an if statement for each appttype

VB Code:

If rs.fields("ApptType") = "Medical" then vb=blue

3. Is there a way to put a button on the form which will resize the flexgrid so that all columns will fit on screen. Because I'm planning on make an option where you can view only appt for employees with title Dentist or Technician, and so on.

Thanks Si :wave:

Oooh.. that looks nice, good work! :)


1. This is a personal choice thing.. my view is that you will probably only want to see one persons details at a time (and will know which you want to see) so separate textboxes would be my choice.

2. That's a good idea, it'll help with readability. You don't need separate If's, you can use a Select Case instead. Alternatively you could create an extra database table which contains the App't types and their colours, and return this (linked via ApptType) in your recordset - this method would arguably allow for more flexibility, but may be awkward if multiple users want different colours.


3. a) You already know how to set the column widths, all you would need to do to make it "fit" is set the values lower... but the text may not fit in the columns then!

b) In order to show only "Dentist" (or more than one type), you can just add to the SQL statements so that they only return the appropriate data.

I found one of your posts that uses Select Case but I'm gonna go with creating the table called Color with ApptType and Color. Linking ApptType from this table to the other Appointment table with ApptType.

1. Now my next question is how would I get this to execute thorugh the db. Would I create a seperate Sub and call it. How? :confused:


I have completed 1 and 3a from the preivous post. I think I can figure out 3b or work my way through some post to figure it out.

There's definitely no need for a separate sub, as each sub can have as much as you like in it. Arguably you could use an extra recordset, but it is neater (and quicker to run) to get all the data in one recordset.

To do that you just need to amend the "Appointments" query a bit, to also join to the other table (and add the appropriate field to the Select list).

AFAIK, the current query you are using is something like this (for rs1.Open):

Code:

---

SELECT ApptID, PersonID, ApptStart, ApptEnd, ApptType, Notes
FROM Appointment
WHERE Format(ApptStart, 'yyyy/mm/dd') = '<value>'

---

To add the extra field "Color" to this query (with relevant data) we just tell the database to Join to the other table - and return the data where the ApptType fields for each table has the same value.

As we are now using more than one table it is a good idea to specify which table each field comes from, especially ApptType as it is in both (you will get errors if you dont specify that one). I have used aliases here to make the code shorter and easier to read:

Code:

---

SELECT A.ApptID, A.PersonID, A.ApptStart, A.ApptEnd, A.ApptType, A.Notes , C.Color
FROM Appointment A
INNER JOIN Color C ON (A.ApptType = C.ApptType)
WHERE Format(A.ApptStart, 'yyyy/mm/dd') = '<value>'

---

Note that for this to work properly, all values of ApptType used in Appointment also need to be in the Color table - otherwise the appointments will not be returned (you could alter this behaviour with a slighty different query).

I'm getting this message when I put the code.(runtime error -2147217904--No value given for one or more required parameters) I'm new to the alias part of the code but what are the A and C's for. Oh yeah is this code right.

VB Code:

rs1.Open "SELECT A.ApptID, A.PersonID, A.ApptStart, A.ApptEnd, A.ApptType, A.Notes , C.Color " _
  & " FROM Appointment A " _
  & " INNER JOIN Color C ON (A.ApptType = C.ApptType) " _
  & " WHERE Format(ApptDate, 'mm/dd/yyyy') = '" & Format(dteDateToShow, "mm/dd/yyyy") & "'", cn, adOpenDynamic, adLockOptimistic

THANKS!!!

The A and C are just abbreivations I chose - you could put anything you like (as long as you change all A's to the same thing, or all C's to the same thing). The place that matters most is just after the FROM (and JOIN), as this is where it is actually specified what you intend to use elsewhere.


The error (generally) means that part of the Where clause hasn't been written properly, but as it's late here (1:30 am) I can't really see what it is. I'd recommend adding A. before ApptDate, as that may be it. You should also check the fields are all spelt correctly.

If that doesn't work, print out the SQL statement (as shown here, ignore the last couple of paragraphs!) and post it here.

I figured it out once you explained to me what the abbreviations were. In my table C.Color was changed to C.ApptColor. :)

So would I use this code to make the cells change color? ANd where would I place it if so.

VB Code:

MSFlexGrid1.CellBackColor = rs1.Fields("C.ApptColor")

That's the right code to set the colour. :)

Unfortunately you also need to select the cell first (by setting MSFlexGrid1.Row and MSFlexGrid1.Col), which is relatively slow - for this tho it shouldnt make much difference (as you aren't going to be colouring thousands of cells). Note that if the code is inside the With block you dont need to use MSFlexGrid1.XX , just .XX on its own.

As to where to put this code, it should be in the same area as where you set strApptText and place the values using .TextMatrix. I'd recommend just after Next LngRowNo.

If only one cell in the merged range is coloured let us know - as there is a trick to set more than one (using .FillStyle and .RowSel before setting the colour).

I'm sorry Si :( but I'm lost. When you said .

Quote:

---

Unfortunately you also need to select the cell first (by setting MSFlexGrid1.Row and MSFlexGrid1.Col), which is relatively slow - for this tho it shouldnt make much difference (as you aren't going to be colouring thousands of cells). As to where to put this code, it should be in the same area as where you set strApptText and place the values using .TextMatrix[/COLOR].

---

I kinda got confused :confused:

Something like this:

VB Code:

...
  Next LngRowNo
 
  .Row = lngStartRow
  .Col = lngPersonCol
  .CellBackColor = rs1.Fields("C.ApptColor")  
 
  ...

That what I had but I second guessed my self when I got the error "Item cannot be found in collection corresponding to the requested name or ordinal."
Would that have anything to do with the way the SQL statment was prepared? BTW In my ApptColor field in the table I have vbred, vbblue, vbgreen etc. Could that be the problem..........?

I did it again :blush: .. you don't need the alias when getting the values, you should have this:

VB Code:

.CellBackColor = rs1.Fields("ApptColor")

If the values in the database are the text "vbRed" etc then it wont work - you need to store the value instead, in a numeric field. To find the values, type vbRed (or whichever one) into a code window, then right-click on it and select "Definition"; vbRed is 255, vbBlue is 16711680.

I'm learning I'm learning... :lol: Can you tell me why do the msgboxes column not found and row not found pop up when I do my technicians and dentists queries?

You seem to be getting there ;)

The msgbox'es are shown because the relevant start/end time (or person) is not shown in the grid.

The person bit is understandable - but avoidable (you just need a sub-query or Join in the SQL that loads the appointments, to only get appointments for the relevant people).

The row (time) messages are worrying, as they mean that either you have "dodgy" times in the data, or we arent finding the rows properly.