How to link a selected option to check boxes (IF this, THEN that)

[tim.cassey]

Hi there,

I have a database where new products are entered into it. Over time there are revisions that are made to these products and to capture this, a drop down box has been added that allows the user inputting the product to select the current revision number of this product.

I also have another form which shows the product serial numbers, and then a bunch of fields relating to revision numbers (a check box for each), this can be used by field technicians who can locate older models and tick off a revision when they have updated a product to this specification.

What I am hoping to do is that when the user initially enters in a product and selects the revision number of it, the other form will automatically update (the check boxes) up to and including this revision... so for example if a product is being entered and is up to revision 5 - the check boxes under this serial number on the 'revision' form will all be checked automatically (Revision 1, Revision 2, Revision 3, Revision 4, and Revision 5 check boxes).

Is this possible? How would I go about it?

[June7]

These multiple 'version' fields is not a normalized data structure. Will there never be more than 5 versions? Will every product have 5 versions? Why have 5 check fields instead of just one text field with the most current version number?

Are 'model' and 'product' synonymous?

I don't really understand relationship of these two tables. Provide examples of raw data.

[tim.cassey]

These multiple 'version' fields is not a normalized data structure. Will there never be more than 5 versions? Will every product have 5 versions? Why have 5 check fields instead of just one text field with the most current version number?

Are 'model' and 'product' synonymous?

I don't really understand relationship of these two tables. Provide examples of raw data.

Hi there,

I cannot provide raw data at this time but I will attempt to explain the situation to you in better terms.

Basically we have products that are entered in with a corresponding serial number, and then this occurs:

- Every so often something on the product is changed which will be added to the table as a revision, and a check box on the corresponding form for this revision will be added
- Each time there is something changed on the product, a new revision number will be added to the table, and on the corresponding form (could be hundreds eventually depending on the rate of changes)
- The reason for the check boxes is that all 'old' products/serial numbers need to be updated out in the field so that all products are in-line with the current spec
- They may not be fixed/repaired/upgraded all at once, hence the need for individual check boxes for each revision, so that whoever does the changes on the old products can check each box as each new change is applied

Now what I want to do is:

- When we make a new product to add into the table/form, I want the user to be able to either check a box or select the current revision, and for this to check all current revision boxes so that the user entering the data doesn't have to sit there and check many boxes each time they enter in a product.

Does this make sense?

[June7]

Clear as mud.

If there could be 'hundreds' of revisions then how can there be a checkbox for each?

Need to see examples.

[tim.cassey]

Database1.zip

I've attached an example database... not sure if this will work but see what you think.

[June7]

There is only 1 table and 1 form. You discuss interaction of 2 forms.

Now there are only 5 Revision fields. Are you saying there could be 'hundreds'. This is not practical. For one thing, tables have a limit of 255 fields.

[tim.cassey]

Database1.zip

There could be up to a hundred (let's keep it at that for arguments sake).

I've re-attached the database with a secondary form to show you what I want to achieve - I don't know how to link them, and I don't know whether I can check the 'revisions up to date' check box and have all checkboxes for that product be checked.... and how to amend this command each time a new revision is added...

appreciate your help.

[June7]

So you want to add a new field every time any product version goes beyond the number accommodated by current table design? I would NOT build a db like this. Might as well just build the 100 or more fields now and have them ready and waiting.

[tim.cassey]

Is there a better way of doing this? I'm not terribly experienced in Access so any help is appreciated.

Would a drop down list be more useful, where a user could choose the revision number and this would cascade through to ticking the check boxes previous to this revision?

[June7]

I wouldn't have these checkbox fields. Options:

1. one field for version number and one record for each product

2. one field for version number but each new version number for a product is a new record - that is if you need history of version number, such as effective dates

[tim.cassey]

I wouldn't have these checkbox fields. Options:

1. one field for version number and one record for each product

2. one field for version number but each new version number for a product is a new record - that is if you need history of version number, such as effective dates

Option 2 is the one I was originally going for - however it doesn't allow anyone to go back and update the 'older' products revision by revision (as older products aren't brought up to the latest revision all at once - technicians will slowly update one revision at a time which is the reasoning for a system where they can see this somehow?).

Any ideas?

[June7]

Yes, code can check off the Yes/No fields up to and including the version number selected by user input.

What I describe for option 2 is NOT a field for each version - it is one field and each new version is a new record. I don't understand what you mean by "update the 'older' products revision by revision". Exactly what are technicians doing when they 'update' a revision?

I still don't understand why you need a field for each version. Why not just a single field and simply enter the latest version number to show how far the versioning has progressed? If the field has a 5 then apparently 1-4 were done at some point. So unless you need other info about each version (date completed, etc), 1 field and 1 record per product serves purpose.

[tim.cassey]

Code to check off fields up to and including the version number selected by user input would be great - any idea on how to do this / obtain this?

I understand completely that I wouldn't have a field for each version, this would mean I would have a table with version numbers and the user could select what version number the product is from say a drop down menu - correct?

The reason why I need to know about previous versions I can explain below:

Let's say I have 5 products out in the field that were the very first ones we created, these are called Product1, Product2, Product3, Product4, Product5. These are all under 'Revision 0' as they are new and nothing has been changed.

The next 5 products after this were made after a change was made to the design, so they are Product6, Product7, Product8, Product9, Product10, under 'Revision 1'.

The next 5 products after this were made with another change to the design, so they are Product11, Product12, Product13, Product14, Product15, under 'Revision 2'.

Now Products1-5 are out in the field and need 2 changes done to them to get them up to the current specification of 'Revision 2'. Technicians will go out to these products (which are with customers) to 'upgrade' them based on the changes made, but they may not do both changes at once... they may do one change (getting them up to revision 1, but not revision 2), and then do another change a week later. I need a way to track this, so that we know when a product is up to date completely.

Does that make sense?

[June7]

Consider:

tblCustomers
CustomerID

tblProducts
ProductID
ProductName

tblCustomerProducts
CP_ID
CustomerID
ProductID
DateInstalled

tblProductRevisions
RevID
ProductID
RevisionNo
DateEffective

tblCustomerProductUpgrades
ID
CP_ID
RevID
DateUpgrade

[tim.cassey]

Would this work if for example a product originally from revision0 and needing to go up to revision3 firstly had the upgrade/repairs done in the order of revision3, then revision1, then finally revision2?