Microsoft Access- Default values on my multiple Items form keep resetting

Question

Hi There,

I am trying to create a form where I can put multiple entries in at one time. Since many of these instances have repetitive fields I would like to reuse data to minimize redundancy. Specifically I want to have multiple fields where I can input a default value (either by typing one in, putting in the date or selecting from a combo box list).

I have been following tutorials as shown below. The boxes in my header input data for the first new instance but then reset when I go to another new instance (as shown below).

(38) Dynamic Default Value: Use the Same Value For Multiple Records During Data Entry in Microsoft Access - YouTube

(38) Microsoft Access Tutorial: Set a Default Value with a Combo Box in a Form Header or Footer - YouTube

The combo box in the header seems to be doing a good job of keeping the data but when I create new entries the other fields in the header "Part", "Date" ,"Po_Number" are resetting each time. Not sure why. Would anyone have any experience in this?

Thanks!

Answer 1

You might like to take a look at this thread:

https://answers.microsoft.com/en-us/msoffice/forum/all/microsoft-access-list-box-or-combo-box/e908f0d4-cd8b-4017-9eaa-df3fd66f8eae

It looks to me like the model I describe in my latest post in that thread is analogous to what you are modelling:

Schematically the model is:

Customers---<Inspections---<PartDimensions>---Parts

PartDimensions would also reference a Dimensions table so the relationship type modelled by PartDimensions is actually ternary, not binary.

Answer 2

Hi Ken,

I understand what you mean and I have tries splitting it into two tables. One would be called inspection which contains; Po_Number, Company_FK (which is the foreign key to another table which contains company name), Part, InspectionDate, Unit. The new measurement table just has a measurement key and associated InspectionID_FK in it. This way It is normalized as you said but I find that isn't exactly what I want.

The problem with normalizing for me is that for this database I want to be able to make measurements of different parts that can be compared to engineering drawings. In this way I want to be able to document what each instance of each parts actual dimensions are compared to what they should be so I can monitor inventory quality. By documenting on the drawings which measurement should be measurement 1- measurement "x". However, these parts have different amounts of measurements that need to be taken.

A way to explain this is if I wanted to take measurements of a bunch instances of book in my drawing I would have that the length is measuement1 , width is measuement2 and thickness is measuementn3 and document in my database accordingly.

But then the next part I might want to take measurements are bicycles where measurement1 could be wheel diameter, measurment2 could be total length, measurement3 is handlebar width and measuremtn4 could be foot pedal thickness.

The number of measurements I need to take will depend on which part I have . My plan is to just export the data to PowerBi and then separate into tables based on each measurement and then when I filter for Part it will only show the data I want. I understand that if I do not normalize my data then I will have the problem of having a ton of zeros in the unused measurement fields and people tell me it could be a nightmare to query. But if I am filtering through powerbi anyway is it a problem. I do realize its not efficient to have a varying amount of useless 0's in my database.

One thing I could do is create a new column that is a combo box that says between measurement 1-5 to make sure that it can query properly but that seems like it will be super work intensive for the operators.

I don't know if its VBA that I need to use or what but I hope I am explaining my problem well. Attached are some photos of my new database.

Answer 3

You should be able to set the defaults for the bound controls easily enough by means of a set of unbound controls in the form header, one for each relevant bound control:

In the AfterUpdate event procedure of each unbound control in the header set the DefaultValue property for the corresponding bound control, e.g. for Part

    Me.NameOfBoundPartControl.DefaultValue = """" & Me.NameOfUnboundPartControl & """"

Do similarly for the other controls.  Note that the DefaultValue property is always a string expression so should be wrapped in literal quotes characters as above, regardless of the data type of the field in question, i.e. for date or number data types as well as text.

I should point out that your table is badly in need of normalization by decomposition into two related tables.  By having multiple Measurement# columns the table is not normalized to First Normal Form (1NF), which requires only one value of each attribute to be stored in each row in a table.  You should have a separate Measurements table with a foreign key column referencing the primary key of the current table, and a single Measurement column

The appropriate interface would then be to have two correlated subforms in an otherwise empty 'container' form, one equivalent to the current form, but without the Measurement# columns**.**  A second subform, also in continuous forms view would be correlated with the first subform by referencing the primary key of the first subform's recordset as a parameter in its RecordSource query.  The second subform would be requeried in the Current event procedure of the first subform.

For an example of correlated subforms take a look at CorrelatedSubs.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

This little demo file uses Northwind data as its example, correlating an order details subform with an orders subform, both in continuous forms view.