Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1523
  • Last Modified:

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.
0
jonlong
Asked:
jonlong
  • 6
  • 6
  • 3
  • +2
1 Solution
 
brewdogCommented:
I think you might be further ahead doing this:

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
0
 
nationnonCommented:
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.  
0
 
jonlongAuthor Commented:
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?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
jonlongAuthor Commented:
nationnon:  That is not what I'm talking about either, but since you mentioned tabs, can you separate forms using tabs?
0
 
nationnonCommented:
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.
0
 
brewdogCommented:
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?
0
 
BelieverCommented:
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.
0
 
BrianWrenCommented:
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
0
 
BelieverCommented:
Oops, that was my first day on EE... changing it to a comment before I get another mild reprimand...
0
 
BelieverCommented:
Oops, that was my first day on EE... changing it to a comment before I get another mild reprimand...
0
 
BelieverCommented:
jonlong: Noticed his question has been sitting for a week... "wassup?!?"  I think we'd all love to know how your solution is going!
0
 
nationnonCommented:
It's ok , maybe he needs more time.
0
 
jonlongAuthor Commented:
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

 
0
 
BelieverCommented:
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!
0
 
BrianWrenCommented:
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
0
 
jonlongAuthor Commented:
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
0
 
jonlongAuthor Commented:
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.
0
 
BelieverCommented:
A continous form subform can have frozen OR unfrozen column headings...
If "frozen" means they don't move at all, then put the column headings in the parent form.
If "unfrozen" means the column headings move with the columns as you scroll horizontally, the put the column headings in the subform's Form Header section (*not* the Page Heading secytion).

"Command button to set the default view"?  If you mean a command button that switches a subform between continuous forms and datasheet view, then here goes...
First, I suggest you make a toggle button.  You could even change the caption of the toggle button when it changes states (on/off) to reflect the current view or the view you *would* get if you clicked it.
Second, some bad news: The way a subform is currently displayed (form/sheet) is read-only.  The property is .CurrentView.
What you will have to do instead is put two subforms on the screen and toggle which one is visible.
0
 
jonlongAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 6
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now