Grid and Header problem

[Rich196]

I have a many-to-many relationship between two tables necessitating a third table. All are connected via unique ID's and foreign keys. All is working fine except that on one of my forms I have a grid that displays firstname, lastname and some telephone numers in different columns. I need to have the grid ordered by lastname. Since the ID's in the RecordSource are not in lastname order I can see no way to order the grid. I have tried using the 'click on header' method mentioned in a previous thread but it did not work. My first question is should it be able to work? If so, how?

I then tried creating a cursor for the grid and all worked fine as far as putting the data in the columns but the TRANSFORM to include hyphens in the telephone numbers caused the headers in the grid to change to the field names from the target table and EXP_3, EXP_4, EXP_5 for the three telephone number columns. I have tried putting thisform.grid1.column3.caption = "Home Phone" etc. in the INITEVENT of the form but it did nothing. The code I am using is:

In the form's LoadEvent...

CREATE CURSOR scl (firstname c(15), lastname c(15), ;
hphone c(12), wphone c(12), ;
cphone c(12), address m, ;
paid c(1), paidby c(6), datechrgd d)

In the DestroyEvent...

SELECT scl
use

In the COMBO1 InteractiveChangeEvent used to choose the event for listing the attendees in the grid...

PUBLIC eve as Integer
eve = events.eventid

SELECT attendees.firstname, attendees.lastname, ;
TRANSFORM(attendees.hphone, '999-999-9999'), ;
TRANSFORM(attendees.wphone, '999-999-9999'), ;
TRANSFORM(attendees.cphone, '999-999-9999'), ;
attendees.address ;
FROM attendees ;
left JOIN atteve ON atteve.attendeeid = attendees.attendeeid ;
and atteve.eventid = eve ;
where atteve.attendeeid is .not. null ;
ORDER BY 2 ;
INTO CURSOR scl

thisform.grid11.refresh
thisform.grid11.recordsource=thisform.grid11.recordsource

How and where can should I reset the captions for the headings to what they should be?

I read Mike's comment in an earlier thread that if properly coded the cursor should not have to be recreated. In my case I don't see how it is possible.

Any help appreciated,
Rich

 

[OhioBill]

Q: How and where can should I reset the captions for the headings to what they should be?


Not that I'm a grid expert, but I ended up setting my header text after I had bound the grid to a data source, and before the refresh method. The syntax is something like:

grid1.column1.header1.caption = "my caption"
grid1.column2.header1.caption = "col 2 caption"

although you could use for-each, etc to run through the columns of the grid.

 

[BPeisch]

With your first try, you would have needed to create a tag on the FK plus the lastname in order to see the child records in the order you wanted. But personally, I prefer using a cursor or view for this type of thing.

As far as your header caption situation, are you aware that you can set header captions, and lot of the other things you're doing in code in the form designer? It's often easier there, where you can see what things will look like.

Another option is to use the AS clause in your SQL SELECT statement, so that the field names make more sense. Something like this:

SELECT attendees.firstname, attendees.lastname, ;
    TRANSFORM(attendees.hphone, '999-999-9999') AS HomePhone, ;
    TRANSFORM(attendees.wphone, '999-999-9999') AS WorkPhone, ;
    TRANSFORM(attendees.cphone, '999-999-9999') AS CellPhone, ;
    attendees.address ;
    FROM attendees ;
        left JOIN atteve ON atteve.attendeeid = attendees.attendeeid ;
        and atteve.eventid = eve  ;
        where atteve.attendeeid is .not. null ;    
            ORDER BY 2 ;
            INTO CURSOR scl

I read Mike's comment in an earlier thread that if properly coded the cursor should not have to be recreated. In my case I don't see how it is possible.

I believe what Mike meant by this comment was if you use a parameterized view, you can requery it instead of recreating it, which keeps you from having to clear the grid's recordsource first.



-BP (Barbara Peisch)

http://www.peisch.com

 

[Rich196]

Barbara and Bill,

Thanks for your replies. I have set the headers in the form designer but those are changing when I include the TRANSFORM. The AS clause gave me some good headers .

If the first row contains a column that has no phone number in the table, I get a wrong width for the column and it cuts off the phone numbers below. Also I get
' - - 0'for the blank(null) phone numbers. Any thoughts? I tried thisform.grid11.column4.width = 15 after the SELECT and just before the refresh and got an error "Unknown member COLUMN4" so I have something out of scope. I don't know how to correct it but I will keep playing with it. Suggestions welcomed...

Thanks,
Rich

 

[BPeisch]

Again, I think you're trying to do too much in code that you're better off doing in the designer. Will your cursor have a fixed number of columns? If so, set that as the columncount of the grid. What you set for the column headers after that should not be affected by the field names.

It sounds to me like the grid is rebuilding itself and losing your settings. It will do this when you clear and reset the RecordSource. This is why a parameterized view works better as a RecordSource for a grid than a cursor. With a parameterized view, you simply requery the view when you need to change the filter, and you don't have to reset the RecordSource.


-BP (Barbara Peisch)

http://www.peisch.com

 

[Rich196]

Barbara,
Your suggestion works fine. Thanks again.
Rich