Datasheet Form with Many Fields (1 Viewer)

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
I need to allow users to see a datasheet format an editable query with nearly 100 fields (it describes every possible piece of information about the products we sell).

I need for the users to be able to edit some fields, while locking other fields. The users need to be able to see more than 1 record at a time (there are often groups of products with similar data).

When I create datasheet style form, I run into the maximum width limitation after a dozen fields are displayed. If I show the raw query, I can't prevent editing of some fields.

Is there a way to meet the requirements.
 

Ranman256

Registered User
Joined
Apr 9, 2015
Messages
3,429
this will be a very busy form.

make a single form with text boxes,
set the form to CONTINUOUS form to see many records,
Lock the text boxes you don't want users to edit. Unlocked (default) are editable.
 

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
I will try that out. It will be a busy form, but as long as users can navigate with arrow keys, it should be easy to use.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,690
Hi. If you're displaying the form as datasheet, then it doesn't matter where you place the controls on the form, so I can't imagine how you're running out of real estate.
 

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
Hi. If you're displaying the form as datasheet, then it doesn't matter where you place the controls on the form, so I can't imagine how you're running out of real estate.
It does matter, as each records needs to be on a single row, so users can use arrow keys to easily navigate.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,690
It does matter, as each records needs to be on a single row, so users can use arrow keys to easily navigate.
Hi. I don't think you understood my point. What I meant was if you put two fields on top of each other on a form, when you display the form as datasheet, those two fields will show up side by side.
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,534
If you let the wizard create the DS form, you will notice that the wizard creates the controls as a LIST. But when it creates a form in continuous view, it creates the controls horizontally. The thing that controls the display is the property that says how to display the data.

HOWEVER, the reason that the wizard builds the DS view form as a control LIST is so that each control will have an ATTACHED label. That way, you can change the caption property of the attached label and that will change the column header over the column. When the form is built with the labels in the form header, they are separated from the control and therefore, a DS form built that way will always use the column name from the query as the column header SO --- if you use properly formed column names, you will be unhappy that the user is seeing something like DOB rather than "Birth Date".

When I first started using Access, I didn't use DS view subforms for years because I hated seeing my column names as headers and creating a query with Alias' didn't help because that forced me to use the Alias name in my code. There was no way to win. Then, at some point, I realized WHY the wizard build the DS form the way it did and now I use them all the time.
 

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
Hi. I don't think you understood my point. What I meant was if you put two fields on top of each other on a form, when you display the form as datasheet, those two fields will show up side by side.
It won't let me display the form as a datasheet.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,016
Pat,

I for one would like to know why as well, please.

If you let the wizard create the DS form, you will notice that the wizard creates the controls as a LIST. But when it creates a form in continuous view, it creates the controls horizontally. The thing that controls the display is the property that says how to display the data.

HOWEVER, the reason that the wizard builds the DS view form as a control LIST is so that each control will have an ATTACHED label. That way, you can change the caption property of the attached label and that will change the column header over the column. When the form is built with the labels in the form header, they are separated from the control and therefore, a DS form built that way will always use the column name from the query as the column header SO --- if you use properly formed column names, you will be unhappy that the user is seeing something like DOB rather than "Birth Date".

When I first started using Access, I didn't use DS view subforms for years because I hated seeing my column names as headers and creating a query with Alias' didn't help because that forced me to use the Alias name in my code. There was no way to win. Then, at some point, I realized WHY the wizard build the DS form the way it did and now I use them all the time.
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,534
Why what?

The ONLY way you have any control over editing rules is by using a form. Queries have NO events and so the user can do whatever they want which is why you should NEVER, EVER give a user a query to use to edit data!!!!
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,016
Why what?

The ONLY way you have any control over editing rules is by using a form. Queries have NO events and so the user can do whatever they want which is why you should NEVER, EVER give a user a query to use to edit data!!!!
Sorry, I was asking why after you made this statement
I realized WHY the wizard build the DS form the way it did and now I use them all the time.
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,824
I need to allow users to see a datasheet format an editable query with nearly 100 fields (it describes every possible piece of information about the products we sell).
This data structure is unsustainable. You should consider an Entity-Attribute-Value data model.

There were discussions recently about EAV data structures on this site.

Try this one to start.
 

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
Pat,

I for one would like to know why as well, please.
I created a datasheet form from scratch, using the creating forms wizards. It shows all the fields. Thanks for the assistance.

I still have one issue. In a datasheet form is there a way of graying out uneditable data? I can change the background (and other formatting info) in the underlying form, but it has no effect on the datasheet column. (Locking the field in the underlying form works).
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,690
I created a datasheet form from scratch, using the creating forms wizards. It shows all the fields. Thanks for the assistance.

I still have one issue. In a datasheet form is there a way of graying out uneditable data? I can change the background (and other formatting info) in the underlying form, but it has no effect on the datasheet column. (Locking the field in the underlying form works).
Hi. Have you tried disabling the control(s)?
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,690
Disabling control resulting in it being skipped when navigating the sheet but does not gray it out. Skipping the field helps.
Hi. I am not sure what else to recommend but happy to learn skipping the fields helps a little bit. Good luck with your project.
 

MajP

Access MVP
Joined
May 21, 2018
Messages
2,353
Some observations.
1) If you have 100 fields in a table i can bet you 1000 dollars your table is not normalized, or if in the slim chance it is then you are better off with an entity attribute value design as already been mentioned.
2) Not sure if you are a developer or just an amateur, but if you provided me 100 field datasheet that was editable, I would fire you.
Any chance you can rethink table design and UI or are you stuck with this?
 

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
Some observations.
1) If you have 100 fields in a table i can bet you 1000 dollars your table is not normalized, or if in the slim chance it is then you are better off with an entity attribute value design as already been mentioned.
2) Not sure if you are a developer or just an amateur, but if you provided me 100 field datasheet that was editable, I would fire you.
Any chance you can rethink table design and UI or are you stuck with this?
Products can have many characteristics which need to be recorded. A majority of the fields come from our ERP (and there are more fields which I don't use in my query). When you are manufacturer, you need to keep track of a lot of things. For example, we need to track the dimensions of the product (3 fields), a ship_dimensions text field used in our ERP and a carton field which validates the dimensions. And then the similar info for masterpacks. And a volume field in the ERP. 11 fields just about dimensions.
 

MajP

Access MVP
Joined
May 21, 2018
Messages
2,353
Products can have many characteristics which need to be recorded. A majority of the fields come from our ERP (and there are more fields which I don't use in my query). When you are manufacturer, you need to keep track of a lot of things. For example, we need to track the dimensions of the product (3 fields), a ship_dimensions text field used in our ERP and a carton field which validates the dimensions. And then the similar info for masterpacks. And a volume field in the ERP. 11 fields just about dimensions.
So like I said it is not normalized.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom