Access Database - Unable to force a datasheet form to have a fixed width for text box.

I have a form.  On this form there is a subform.  On this subform there is another form that is a datasheet.
My problem is this:  How can I force the width of the text box on this datasheet to be a certain size without code and the accompaning overhead.  Basically the user can fiddle until the column width is 0 thus the field will no longer show.  

I coded a Current Event (see below) but it seems this is a lot of everhead.  BTW - this is an unbound .adp against a SQL Server - so I am looking for the least overhead possible since this thing is getting hit by 250 users.

Private Sub Form_Current()
    Forms!frmMain.frmA.Form.Controls(0).ColumnWidth = 500
    Forms!frmMain.frmA.Form.Controls(1).ColumnWidth = 2000
End Sub
michaelrobertfrenchAsked:
Who is Participating?
 
Leigh PurvisDatabase DeveloperCommented:
Stephen Lebans Auto Column width (and datasheet column freezing).
http://www.lebans.com/autocolumnwidth.htm
0
 
GRayLCommented:
What you have written will set the column widths of the first two controls in the first subform, not the second. For that:

Private Sub Form_Current()
    Forms!frmMain.frmA.Form!frmB.Form.Controls(0).ColumnWidth = 500
    Forms!frmMain.frmA.Form!frmB.Form.Controls(1).ColumnWidth = 2000
End Sub
0
 
michaelrobertfrenchAuthor Commented:
Actually I perhaps had my form - subform - datasheet a bit mixed up. Thanks for the clarity.
But that aside, the VBA with the .columnwidth property is what I am trying to avoid but see no property in the User Interface where I can set the width and have it retained.  Do you know how I can set the width without the overhead of code?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
GRayLCommented:
In Access, if you adjust the widths of a query in datasheet view and then save the query, when the query is re-run, the column widths are retained from the previous save.  This help?
0
 
michaelrobertfrenchAuthor Commented:
Access .adp unbound forms - forms are populated with stored procedures.  Still needing to know how to hardcode the width of the text box without VBA.
0
 
Jeffrey CoachmanMIS LiasonCommented:
michaelrobertfrench,

"hardcode"... "without VBA"

Isn't that an Oxymoron?
:)

Anyway...

If you don't want your users to "fiddle" with the column widths, then you could change the SubForm's DefaultView Property to: "Continuous Form" (Tabular).
Then squeeze all the controls together Horizontally, so they touch.
Then get rid of even the smallest empty spaces in the Detail Section of this subform.

This will create the illusion of Datasheet view, however, users can’t "fiddle".
;)

(The above technique is pretty standard here at EE)

Another benefit to this technique is that users will now be seeing the Caption Property of the Field, instead of the actual FieldName. (You can even change the text in the label to whatever you want)
So users will see: “Customer First Name” instead of “CUST_FN”

Hope this helps as well

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
michaelrobertfrench,

"hardcode"... "without VBA"

Isn't that an Oxymoron?
:)

Anyway...

If you don't want your users to "fiddle" with the column widths, then you could change the SubForm's DefaultView Property to: "Continuous Form" (Tabular).
Then squeeze all the controls together Horizontally, so they touch.
Then get rid of even the smallest empty spaces in the Detail Section of this subform.

This will create the illusion of Datasheet view, however, users can't "fiddle".
;)

(The above technique is pretty standard here at EE)

Another benefit to this technique is that users will now be seeing the Caption Property of the Field, instead of the actual FieldName. (You can even change the text in the label to whatever you want)
So users will see: "Customer First Name" instead of "CUST_FN"

Hope this helps as well

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
^That time, without the crazy characters!
.\_/.
0
 
michaelrobertfrenchAuthor Commented:
As far as I can determine - the vba must exist in order for the screen to have described properties.  Thanks
0
 
michaelrobertfrenchAuthor Commented:
Thank You
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.

All Courses

From novice to tech pro — start learning today.