Solved

Datasheet View Column Headers

Posted on 2000-03-01
19
1,454 Views
Last Modified: 2010-11-16
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
Comment
Question by:jonlong
  • 6
  • 6
  • 3
  • +2
19 Comments
 
LVL 10

Expert Comment

by:brewdog
ID: 2575045
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
 
LVL 1

Expert Comment

by:nationnon
ID: 2576573
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
 

Author Comment

by:jonlong
ID: 2578538
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
 

Author Comment

by:jonlong
ID: 2578551
nationnon:  That is not what I'm talking about either, but since you mentioned tabs, can you separate forms using tabs?
0
 
LVL 1

Expert Comment

by:nationnon
ID: 2579795
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
 
LVL 10

Expert Comment

by:brewdog
ID: 2580264
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
 
LVL 7

Expert Comment

by:Believer
ID: 2581619
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
 
LVL 9

Expert Comment

by:BrianWren
ID: 2581848
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
 
LVL 7

Expert Comment

by:Believer
ID: 2591821
Oops, that was my first day on EE... changing it to a comment before I get another mild reprimand...
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 7

Expert Comment

by:Believer
ID: 2591822
Oops, that was my first day on EE... changing it to a comment before I get another mild reprimand...
0
 
LVL 7

Expert Comment

by:Believer
ID: 2600851
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
 
LVL 1

Expert Comment

by:nationnon
ID: 2608018
It's ok , maybe he needs more time.
0
 

Author Comment

by:jonlong
ID: 2626133
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
 
LVL 7

Expert Comment

by:Believer
ID: 2628037
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
 
LVL 9

Expert Comment

by:BrianWren
ID: 2628521
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
 

Author Comment

by:jonlong
ID: 2630351
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
 

Author Comment

by:jonlong
ID: 2630368
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
 
LVL 7

Accepted Solution

by:
Believer earned 50 total points
ID: 2631759
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
 

Author Comment

by:jonlong
ID: 2632343
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now