Flexgrid or data grid and how?

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.

Oops :blush: it was just the person column not the time. Sorry! I have also decided to add a textbox to search by name. I can do that part. I think.. :)
I'm also having I guess a problem when I switch between queries sometimes it makes an appointment at 7:30 appt on the first column until I restart the app but that not a big deal. I'm thinking I'll play with refresh and redraw and see if that will fix it.

BTW. Is there a way to make a right-click on an appointment go into edit appointment mode? Or what would you say would be an appropriate way to edit an already scheduled appointment. I had forgotten all about this part?

I thought as much, but it's better that you know why the Time message would appear (just in case it ever does).

As to why the switching queries is a bit odd, are you running both subs to fill the grid? (you should be).


The editing of appointments could be awkward, I'm not really sure what would be the best way.

To detect a right click, use the MouseUp event (to keep the same style as other Windows apps & controls), and check that the Button parameter is 2. The next thing to do (or possibly before the check above) is to store the MouseRow and MouseCol properties into variables - just in case the mouse moves while your code is running.

Here's where things get debatable... I'm thinking that the first thing to check is if there is text in the cell (using TextMatrix with the variables you stored MouseRow and MouseCol in), if not then add a new appointment.

If there is text, you can tell the person (from ColData which we used in FillAppts), and the time of the row that was clicked in (using TextMatrix with a column of 0, and your MouseRow variable). From these you could use an SQL statement to load the appropriate record for the 'edit' form.

I'm sure there's a better way to do this, but I cant think of it at the moment!

Hi

I trying to do something like this, but I'm using Spread 3.0 , It do not have a Merge Cells :( :(

Are there way to do it, without user Control?

You can use the FlexGrid, as it comes with VB (you just need to select it in "Project" -> "Components").

If that isn't suitable, but the control you are using still has a "grid" style, you could do something fairly similar but without actually merging cells. What you could do is set the background colour of the cells that "should" be merged, and just place one line of text in each of the cells.

Thanks

I do not know, how Can I to resolve this :


appt1 09:00 - 10:00

If I want to do apointment in 08:00 -11:00 , How can I to verify that APPT1 is using 09:00- 10:00 ?

I have no idea what you mean I'm afraid.

As this thread is already so long (and as your problem isn't quite the same thing) it would be better to post as a separate thread - perhaps the one where I told you about this one. ;)

Hi graphixphantix,

I need you help, I need your project.

Can you help me?

graphixphantix, help me.

I need your help.

I have a problem in "Column person not found!"

Do you have any idea?