Formatting column widths and captions/column headings on a datasheet view subform

[jumnhy]

Hello all,

Trying to figure out how to configure two parts of a subform that I'm displaying in datasheet view--making the column widths persistent, and customizing the column names as they appear on the datasheet.

The recordsource is a query that's an amalgam of various joins from several tables. The issue is that despite aliasing the column names in the final query, the names displayed on the columns in the datasheet view persist as the underlying column headings from the upstream queries and tables. I'm just beginning to backtrack which upstream recordsources are persisting from where, so I can change them there if needed and allow those changes to persist, but I'm hoping/assuming there's something I don't know, and I'd think it should be possible to coerce them to read as whatever I want.

How can I set the column names in the datasheet view? I know that configuration options for the datasheet view are limited, and while I've considered just converting to a continuous form, I like the look and familiar feel of the datasheet for our end users, and the inbuilt search/sort/filter features that it provides.

Similarly, if I manually set the widths of the columns, they seem to reset once viewed on the actual subform--ie, if I edit the subform itself separately in datasheet view, those changes don't persist when viewed live as part of the parent form. Read elsewhere something about "freezing" the column width, but when I do that the columns appear to rearrange (pulls the frozen column to the leftmost spot). Is there a better way to configure this?

I'm hoping to avoid needing to rework the subform as a continuous form view, so any input would be very helpful.

 

[MajP]

You Can Do That with Datasheets?

docs.microsoft.com

 

[jumnhy]

You da man, @MajP !

 

[jumnhy]

I'll add for anyone else that stumbles across this: your column names on the datasheet are the control names. Key takeaway from that article is that datasheets are just forms, and many of the properties of them are configurable just like any other form. Name your controls what you want the column heading to appear as.

This is a bit concerning, just because sometimes you'd want to reference a control using a convenient naming scheme, but would be "forced" into naming it whatever you needed to display instead... If you choose to go with a datasheet view, etc etc etc

 

[MajP]

In the datasheet recordsource set the caption property of the field. You can do this on any table or query.

 

[jumnhy]

Lol I totally missed that. I was looking for "Caption" and totally skimmed over "Datasheet Caption" every time I looked at the properties sheet. Little things. Thanks again!

 

[MajP]

FYI if you do this when you make your table it shows up everywhere in the database such as labels and queries.

 

[jumnhy]

Yeah, that was my issue previously--it would appear (I'm not sure) that if you just alias the column name in the query, the caption persists in the datasheet view.

I ended up doing it at the control level rather than the table because I need a different caption here.

 

[Pat Hartman]

I think Access may be handling the Caption properly now. Earlier versions sure didn't so I was never able to use them. The other method is to just change the caption property of the associated label control. NOTE - this only works if the labels are associated with a control. The design view is the top picture and the DS view is the bottom picture.

 

[tomasm]

I never noticed that form controls have a Datasheet Caption property to display whatever you want in the datasheet column heading. That's huge. Column names might be the biggest reason for me using continuous forms instead of datasheets.

Now that I know how to customize datasheet column names, remove the sort/filter option, and lock column position/width, why would I ever want to use a continuous form designed to look like a datasheet?

I can't think of any reason. Maybe to add some color and font customization? Even that can be customized a little with the "sorta hidden" properties. And if you want a header for your datasheet, you can create a main form as the header and add a subform in datasheet view, which automatically gives you things like sort and filter buttons and allows users to change column widths/order if that's something you want.

 

[Gasman]

If you put actual captions for your fields in your tables, then they become the labels/column headings automatically?
That way I can have non spaced fieldnames and spaced descriptions.

 

[MsAccessNL]

I never noticed that form controls have a Datasheet Caption property to display whatever you want in the datasheet column heading. That's huge. Column names might be the biggest reason for me using continuous forms instead of datasheets.

Now that I know how to customize datasheet column names, remove the sort/filter option, and lock column position/width, why would I ever want to use a continuous form designed to look like a datasheet?

I can't think of any reason. Maybe to add some color and font customization? Even that can be customized a little with the "sorta hidden" properties. And if you want a header for your datasheet, you can create a main form as the header and add a subform in datasheet view, which automatically gives you things like sort and filter buttons and allows users to change column widths/order if that's something you want.

The reason i mostly do not use datasheets because they look like an application from the 90’s. Sort and filter can be added with a simple function.

Public Function HeaderClick(HeaderName As String)
DoCmd.GoToControl "[" & HeaderName & "]"
DoCmd.RunCommand acCmdFilterMenu
End Function

 

[gemma-the-husky]

I think if you have made/accde then changed column widths aren't retained in a datasheet. If it's an mdb/accdb then they will be. Maybe it's to do with being able to save the form design changes.

I don't use data sheets vary often, as i tend to add buttons etc to continuous forms.

 

[tomasm]

If you put actual captions for your fields in your tables, then they become the labels/column headings automatically?
That way I can have non spaced fieldnames and spaced descriptions.

That's what is stated above but I haven't tried it. I think I'd rather specify names per form anyway. I wish I saw the Datasheet Caption property a long time ago. You can have short, non spaced table field names but long, spaced datasheet names in the form.

The reason i mostly do not use datasheets because they look like an application from the 90’s. Sort and filter can be added with a simple function.

Public Function HeaderClick(HeaderName As String)
DoCmd.GoToControl "[" & HeaderName & "]"
DoCmd.RunCommand acCmdFilterMenu
End Function

Customizing the appearance is the only reason I can think of for using a continuous form. The hidden datasheet properties allow some visual changes to the font and color so that can help a little. Creating a continuous form that looks nice and has the functionality of a datasheet (sort/filter buttons that change images appropriately) takes a lot more time than creating a datasheet form. That's why I'm glad to learn that datasheets have more customization than I previously thought.

I think if you have made/accde then changed column widths aren't retained in a datasheet. If it's an mdb/accdb then they will be. Maybe it's to do with being able to save the form design changes.

I don't use data sheets vary often, as i tend to add buttons etc to continuous forms.

I also need buttons but with the form I'm currently working on, I'm finding that placing the datasheet as a subform with buttons above and around it on the main form is so far working well. The datasheet can be blended nicely with the main form and it doesn't look too bad.