Utilising forms to display an Interactive Manufacturing Quality Matrix?

[Benaal]

Good morning,



I have a simple database design, recording quality losses by work area and product within the plant. What I want to do is present the user with a matrix of the work areas at the top, the products at the side, and a list of losses recorded against each combination, with a button to add another if it is required. Please see attached picture.

The problem is that the work areas and the products are fairly fluid. They change every six months or so, and I want to make a flexible matrix that can be changed at a moment's notice. Hence, I don't want to hard-code the areas and products onto the form.

Any ideas would be greatly appreciated as i'm currently drawing up a blank.

Thanks in advance.

[June7]

Then you will need a lot of VBA code to set properties of controls on form and for reading/writing data.

[rpeare]

This looks like a fun project, have you got a sample database you can upload so I don't have to slog through creating one?

Also, would you be modifying the same matrix over and over, or creating a new matrix every few months?

if you modify the same matrix let's say 4 months from now you add a new product 'dips' would you be adding that to this matrix and perhaps removing a previous product? what happens to the previous product if you are modifying the same matrix, do you basically void the item so it doesn't appear in your matrix? Do you have start date and termination dates associated with the matrix?

Like June says, this will be programming heavy but there's no reason it can't be done as long as there are reliable rules.

[Benaal]

Thanks for the replies. I'm not shy with VBA, since i've been coding for most my life.

The vision would be that commercial could decide at any point that we want to sell a new product. We would then add this in the table, and have this reflected in the matrix.
The same would be said for factory areas - If we add a new process in, for example 'painting', it would also be reflected.
On taking away an option, I would envisage that the records stay in the database, but 'archived' in some way.

To be honest i'm more interested in making the Matrix form reflecting the options at the moment.

If I were doing this in PHP, which is my other language, I would have it programatically create <table><tr><td></td></tr></table> tags for each row. However, i'm not really sure what the equivalent control would be in Access VB.
Whatever control I use, will ideally need to be a container within which I can add a button.

Please see attached test database.

testBase.zip

[June7]

Use form with textboxes/comboboxes/listboxes/ and command buttons.

Changing the quantity of 'columns' for areas and 'rows' for products in the 'matrix' would likely mean design changes to the form. Programmatically modifying objects in Access db seldom has benefit. Design edits cause the db to grow in size, requiring Compact & Repair procedure to be run.

[rpeare]

Rather than redo an example here's an example of entering a matrix on a form but the underlying table having a properly normalized structure.

https://www.accessforums.net/forms/a...31/index2.html

Look for the last attachment on page 2, Update_Example.zip

It's got one matrix element per 'box' but you can modify to either handle a series of list boxes or other controls.