Modify the fields shown in a form or report (1 Viewer)

[spectrolab]

Hi all,

I need to add some data to a table using a form that has slightly different fields that need to be entered depending on who the data is for. It is used to enter data based on a particle size analysis and we have a lot of different screen sizes, not all of these are used for each customer.

For example, one customer might have 31.5mm, 6.3mm and 0.15mm data that needs to be entered into the table, whereas another customer might need 10 or so size fractions entered. Currently the form we use has all of the fields shown, which makes data entry clumsy and prone to mistakes. The records shown in the form are based on a query which selects a particular job from a drop down list.

Is there any way that we can only show the required fields in the form based on the particular customer requirements? Or, if it easier, have a series of check boxes to only show the fields selected in a sub-form?

Thanks in advance.

 

[MajP]

If you use a datasheet you can show and hide columns based on user requirements. I did not look at your dB but it is likely not normalized.

You Can Do That with Datasheets?

learn.microsoft.com

You can hide fields on any view but formatting may be complicated to make look nice. You would probably need code to reformat and move controls
You can have a child table that holds information on what fields a user sees or a control on form to show and hide. Use a multiselect list box with a fields list instead of having to redesign you check boxes each time a field gets added/deleted.

Both methods are doable, but creating a user table would be a lot more work

 

[MajP]

These examples allow you to pick a query and show/hide the fields. The second example adds complexity and allows you to select the order of the fields.
The form without the sort is easier to understand the code. The sort adds more complex function.

 

[MajP]

or example, one customer might have 31.5mm, 6.3mm and 0.15mm data that needs to be entered into the table, whereas another customer might need 10 or so size fractions entered.

As mentioned that does not sound like a properly designed table. Likely those Fields instead should be records. You would have something like
TblCustomerScreenSize
customerID_FK (foreign key to a cusomer)
PartcleSize

That would be a list of particle sizes per customer. It is unclear to me if there is then data for that particle size or simply selected. But you can add more fields

TblCustomerScreenSize
customerID_FK (foreign key to a cusomer)
PartcleSize
ParticleSizeNote

Example

CustomerID_FK	ParticleSize	ParticleNote
Customer1	31.5mm
Customer1	.15 mm	Some Note on size .15
Customer1	63mm
Customer2	.10mm
Customer2	.... 10 more records

 

[spectrolab]

As mentioned that does not sound like a properly designed table. Likely those Fields instead should be records. You would have something like
TblCustomerScreenSize
customerID_FK (foreign key to a cusomer)
PartcleSize

That would be a list of particle sizes per customer. It is unclear to me if there is then data for that particle size or simply selected. But you can add more fields

TblCustomerScreenSize
customerID_FK (foreign key to a cusomer)
PartcleSize
ParticleSizeNote

Example

CustomerID_FK	ParticleSize	ParticleNote
Customer1	31.5mm
Customer1	.15 mm	Some Note on size .15
Customer1	63mm
Customer2	.10mm
Customer2	.... 10 more records

Thanks MajP,

The table does look a lot like an excel spreadsheet at present. The DB was designed 15 years ago and was made with the best of my abilities at the time. Unfortunately, there are about 1 million records in there now, so it might be too late to change the table layout/format. I might be able to archive the old table and start again.

 

[MajP]

Unfortunately, there are about 1 million records in there now, so it might be too late to change the table layout/format. I might be able to archive the old table and start again.

Normalizing a database with 1m records is no harder than normalizing a database with 10 records. It is just a series of append queries copying the data into the new tables.

 

[June7]

Sounds like a database I built which also has data table for sieved material samples. I chose not to normalize further because that was the easiest and simplest design for data entry and output for reports. Data entry errors are not common.

"Normalize until it hurts, denormalize until it works."

A UNION query can rearrange fields to records for normalization but don't know if million records will perform slowly.