Solved

Datasheet View Column Headers

Posted on 2000-03-01
19
1,468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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
 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

752 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