jonlong
asked on
Datasheet View Column Headers
Goal: To be able to enjoy the features of a datasheet view (hide colums, freeze columns, and change column width), while maintaining the aesthetically pleasing column headers of a form view.
My attempt:
1. I took a table, and created two forms from it. The first form has only bound control boxes in the detail section of the form. I named all the fields, and set the default view to datasheet.
2. I placed all of the labels that I want to use as column headers in the form header section of the second form. Then I copied all of the control boxes from the first form and placed them in the detail section of the second form. I set the "visible" property to no.
3. Then, on the second form, I inserted the first form as a subform. I linked the child and the master to the same field, which is my primary key. I also removed the scroll bars and record selectors from the second form.
The result of my attempt: The datasheet appears in the subform below the column headers I want, but when I scroll the page to the right, the labels do not move with the datasheet columns. In other words, the two forms, the main form and the second form scroll independently of one another.
What is the best way? Am I heading in the wrong direction?
Did I describe what I want to do well enough? It would be so easy if I could just do an alt printscreen, and post it somewhere where you all could see it. A picture is worth a thousand words.
My attempt:
1. I took a table, and created two forms from it. The first form has only bound control boxes in the detail section of the form. I named all the fields, and set the default view to datasheet.
2. I placed all of the labels that I want to use as column headers in the form header section of the second form. Then I copied all of the control boxes from the first form and placed them in the detail section of the second form. I set the "visible" property to no.
3. Then, on the second form, I inserted the first form as a subform. I linked the child and the master to the same field, which is my primary key. I also removed the scroll bars and record selectors from the second form.
The result of my attempt: The datasheet appears in the subform below the column headers I want, but when I scroll the page to the right, the labels do not move with the datasheet columns. In other words, the two forms, the main form and the second form scroll independently of one another.
What is the best way? Am I heading in the wrong direction?
Did I describe what I want to do well enough? It would be so easy if I could just do an alt printscreen, and post it somewhere where you all could see it. A picture is worth a thousand words.
If you're trying to toggle between tabs and you get the spreadsheet in both of them , cut the spreadsheet then paste it in the tab you want it in. Or cut and paste in any scenario with tabs.
ASKER
brewdog: I don't think we are talking about the same thing.
Basically, what I'm trying to do is simulate how an Excel spreadsheet works with my form.
I want to be able to hide and freeze columns.
The datasheet view does what I want, but it uses field name as the column header.
I want to be able to use text boxes in a form header, and have the detail section of the form be in datasheet view.
Is this possible?
Basically, what I'm trying to do is simulate how an Excel spreadsheet works with my form.
I want to be able to hide and freeze columns.
The datasheet view does what I want, but it uses field name as the column header.
I want to be able to use text boxes in a form header, and have the detail section of the form be in datasheet view.
Is this possible?
ASKER
nationnon: That is not what I'm talking about either, but since you mentioned tabs, can you separate forms using tabs?
Yes you can seperate forms using tabs. You can put say a customers information in one tab and then their order history in another using either forms or a data sheet.
jonlong:
***brewdog: I don't think we are talking about the same thing. ***
Actually, we are. :o)
***Basically, what I'm trying to do is simulate how an Excel spreadsheet works with my form. I want to be able to hide and freeze columns. The datasheet view does what I want, but it uses field name as the column header. I want to be able to use text boxes in a form header, and have the detail section of the form be in datasheet view. Is this possible?***
Yes, it is, and my code gives you the start of an idea. My sample code specifically deals with the reordering of columns (which also covers hiding columns), but similar things could be built for freezing and all that, too. I didn't want to develop a whole application if you didn't want to go down the road I was proposing.
I will tell you that what you want to do is going to be a lot of work. I don't suppose you'd consider putting the data in Excel and then dropping an OLE object on the form, which would automatically include Excel's abilities for columns, etc?
***brewdog: I don't think we are talking about the same thing. ***
Actually, we are. :o)
***Basically, what I'm trying to do is simulate how an Excel spreadsheet works with my form. I want to be able to hide and freeze columns. The datasheet view does what I want, but it uses field name as the column header. I want to be able to use text boxes in a form header, and have the detail section of the form be in datasheet view. Is this possible?***
Yes, it is, and my code gives you the start of an idea. My sample code specifically deals with the reordering of columns (which also covers hiding columns), but similar things could be built for freezing and all that, too. I didn't want to develop a whole application if you didn't want to go down the road I was proposing.
I will tell you that what you want to do is going to be a lot of work. I don't suppose you'd consider putting the data in Excel and then dropping an OLE object on the form, which would automatically include Excel's abilities for columns, etc?
If what you are looking for is a true datasheet view where the column headings are user-friendly, here goes...
Create a query over the table and alias the field names in the query. For example, if you have a table, "tblTable," with one field, "strField," and you want the field to have a datasheet column heading of "Customer Name" then this is the SQL thhat will do it:
SELECT tblTable.strField AS [Customer Name] FROM tblTable;
The easy way to do this is to drop all the table's fields into the query grid, then before each field name insert "Customer Name:" or whatever you want the datasheet column heading to look like.
Create a query over the table and alias the field names in the query. For example, if you have a table, "tblTable," with one field, "strField," and you want the field to have a datasheet column heading of "Customer Name" then this is the SQL thhat will do it:
SELECT tblTable.strField AS [Customer Name] FROM tblTable;
The easy way to do this is to drop all the table's fields into the query grid, then before each field name insert "Customer Name:" or whatever you want the datasheet column heading to look like.
2¢ worth: doing ss brewdog suggested, you can get the column title that you like by changing the name of the controls. When you add them to the form, they get the same name as the field that is their control source. In this case you see the recordsets field names, (or at least, a copy of them) as the column headers. But if you change the control names the headings will be different.
I personally prefer the query-with-field-name aliases better, because forms if datasheet view do not behave like tables, but queries do.
Brian
I personally prefer the query-with-field-name aliases better, because forms if datasheet view do not behave like tables, but queries do.
Brian
Oops, that was my first day on EE... changing it to a comment before I get another mild reprimand...
Oops, that was my first day on EE... changing it to a comment before I get another mild reprimand...
jonlong: Noticed his question has been sitting for a week... "wassup?!?" I think we'd all love to know how your solution is going!
It's ok , maybe he needs more time.
ASKER
Ok. I'm back. I just sold my house and moved. Anyway, I know how to do everything you guys are suggesting. What I need to do is something different.
If a field name is "Prototype 1 Order Qty", I know how to give it an alias and call it, for example, "Proto1 Ord Qty" to shorten it. The problem is, that's still too long for this reason; If the quantity being ordered is only 10 then you have a column that is too wide for what I need. In Excel you can wrap cells to reduce the width of a column and yet still be able to see the header. That's what I want to do.
Example:
Proto 1
Order Qty
10
instead of
Proto 1 Order Qty
10
If a field name is "Prototype 1 Order Qty", I know how to give it an alias and call it, for example, "Proto1 Ord Qty" to shorten it. The problem is, that's still too long for this reason; If the quantity being ordered is only 10 then you have a column that is too wide for what I need. In Excel you can wrap cells to reduce the width of a column and yet still be able to see the header. That's what I want to do.
Example:
Proto 1
Order Qty
10
instead of
Proto 1 Order Qty
10
I don't think you're going to get this to happen in datasheet view. :( If you didn't have the horizontal scrolling problem, you could do it like you said before.
I recall reading recently in EE that someone said it's possible to format a continuous form to *look* like a datasheet...
Oh, congrats on the new house!
I recall reading recently in EE that someone said it's possible to format a continuous form to *look* like a datasheet...
Oh, congrats on the new house!
The column headers are a special class of window. (Windows uses all sorts of windows that you wouldn't think are windows.)
To get this behavior, you would have to get ahold of the class for this window class, and re-define it.
Tell me: What you are talking about in Excel is wrapping the text in a cell that's holding the title that you want to head your columns with, right? It's not the actual grey column headers that usually say 'A', 'B', etc., right?
Brian
To get this behavior, you would have to get ahold of the class for this window class, and re-define it.
Tell me: What you are talking about in Excel is wrapping the text in a cell that's holding the title that you want to head your columns with, right? It's not the actual grey column headers that usually say 'A', 'B', etc., right?
Brian
ASKER
Brian,
Yes. You know what, I think this is not possible. I have been doing some reading, and everything seems to indicate that it's either, or.
Thanks anyway.
--JonLong
Yes. You know what, I think this is not possible. I have been doing some reading, and everything seems to indicate that it's either, or.
Thanks anyway.
--JonLong
ASKER
Believer,
Thanks. Yeah to make a form look like a datasheet, all you have to do is make sure the height of the detail section is set to your row height, and then set the view to continuous forms. The problem with that is if your form is wider than the computer screen you have to scroll over. Form view does not have freeze column capability.
Anyway, I'm giving up on this one, but I'll give the points to the first person who tells me how to make a command button that will set the default view property to datasheet.
Thanks. Yeah to make a form look like a datasheet, all you have to do is make sure the height of the detail section is set to your row height, and then set the view to continuous forms. The problem with that is if your form is wider than the computer screen you have to scroll over. Form view does not have freeze column capability.
Anyway, I'm giving up on this one, but I'll give the points to the first person who tells me how to make a command button that will set the default view property to datasheet.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Believer:
I appreciate your help, but no. There is no way to explain what I need in words. Why doesn't EE, if it has such a gathering of computer intelligence, just put a billboard where you could post OLE Objects which would allow the users to show what they mean. Not have to explain it with words.
Anyway I'm giving you the points just to get rid of this question. It's nothing personal, this format jsut doesn't work for me.
I appreciate your help, but no. There is no way to explain what I need in words. Why doesn't EE, if it has such a gathering of computer intelligence, just put a billboard where you could post OLE Objects which would allow the users to show what they mean. Not have to explain it with words.
Anyway I'm giving you the points just to get rid of this question. It's nothing personal, this format jsut doesn't work for me.
1. Make this all one form, with the default view being Datasheet. (Or, if datasheet view doesn't let you add the column headers in the form header, make that part a subform.)
2. Leave the form header as it is, but name the labels something like lbl1, lbl2, etc.
3. Have a button in the form footer or some such that lets the user specify what order the columns should be in. When they say "OK", build an SQL string that does something like this (I'm envisioning a list box where they click the items they want in the exact order):
Dim strSQL as string
Dim varItem as variant
Dim i as integer
strSQL = "Select "
i = 1
for each varItem in lstFields.ItemsSelected
strSQL = strSQL & lstFields.ItemData(varItem
me("lbl" & i).caption = lstFields.ItemData(varItem
i = i + 1
Next varItem
strSQL = left(strSQL, len(strSQL) -1) & "from YourTable"
me.recordsource = strSQL
This code would do both the reordering (and/or hiding) of the columns and the setting of the column titles. (The one thing I didn't include is the "blanking" of the labels for fields that weren't selected in the list box. That would be another loop, for j = i to (number of labels).
Does this give you an idea to start with?
brewdog