Forms that can 'grow' as more records are added

Jen_RT

Registered User.
Local time
Today, 02:42
Joined
Jan 19, 2010
Messages
30
I have quite a complex database so I will just explain the relevant tables/forms.

I have an equipment table which collects information in the following fields: Equipment ID (primary key), Equipment Description (text field), Equipment Sub Sub Category (lookup from another table).

A piece of equipment can be linked to many Equipment Categories (lookup from another table) and many Equipment Sub Categories (lookup from another table). I therefore have a join table to establish these many to many relationships. Note that there are 4 equipment categories, say A, B, C & D for simplicity.

I also have 4 compatibility tables (one for each category of equipment eg. A, B, C and D) that compare each piece of equipment with every other piece of equipment within the same equipment category.

eg. Equipment E-0001 applies to equipment category A and D and is therefore compared with itself and equipment E-0002 which applies to equipment category A.

The fields that are in this equipment compatibility table are E vs E compatibility ID (Primary Key), Primary Equipment ID (lookup from another table), Secondary Equipment ID (lookup from another table), Confirmed for use (Yes/No field).

I am basically trying to develop a matrix type structure that captures whether or not a piece of equipment can be paired with another piece of equipment. Obviously you would not want to put a piece of equipment with itself because the equipment item does not exist twice and it therefore isn’t a possible combination.

When a new piece of equipment is added the user fills out the information in the equipment table and this is straight forward. However, I would like to try and automate adding the equipment item to the relevant equipment compatibility tables and comparing the newly added piece of equipment to all existing equipment items in this table. The reason for wanting to automate this process is there are already as many as 42 separate items in one compatibility table which has resulting in 903 compatibility combinations and the equipment list will continue to expand in the future. Without automating, this is a time consuming and tedious process for the user as they will have to select each equipment item in turn from a combo box.

Does anyone know if what I am trying to create is even possible with access and if so, how would you go about creating this?

Thanks in advance.
 
you need a form, with a subform. The subform needs to be either a data-sheet, or a continuous form, for you purpose.

Your mainform then shows all the items, one at a time.

As you progress through the items the subform shows all the "linked" items. If there are too many to fit on the screen, the subform will automatically get scroll bars.

This is normal access behaviour, and involves NO coding at all


just do this simply first

a) design a form to show the items (FORM1)

b) have a table, saying that say, items 1 is linked to items 3,4,5
items 2 is linked to items 4,6,7 etc

c) have another form to display all these links (FORM2)

now make form2 a subform of form1 - just pull the wizard from the toolbox
access will ask you how the forms are linked

that's the basic way of doing this. Your situation sounds a bit more complex, but hopefully this will get you thinking on the right lines

----------------
I am not sure, but your setup reads as if it isnt normalised correctly. The need for 4 tables to manage the links seems wrong.
 
Hi. Thanks for you speedy response.

The reason I have four tables is that I only want to compare equipment items that apply to category A with other equipment items that apply to category A. I am basically going to filter the options the user can pick from at a later stage using a query on equipment category (say A) so there is no point in making all of the options available. E.g. in my database on selecting equipment A, the user will have a reduced choice of equipment items.

Also, If I don’t split my compatibility matrix into four tables according to equipment category, the number of records would be huge because I would like to establish a link for every combination of equipment within the same category, not just those that can be used together. The reason for this is that some combinations of equipment with equipment compatibility status could change in the future, should the equipment be modified and rather than creating a link, I would like to hold a yes or no answer in the database. I also have other fields that capture information such as constraints and exceptions, as well as links to documentation. I just didn’t mention these in my original post to keep it simple.

Also, you said to create a subform on form one that shows the links. To identify what equipment items apply to what equipment categories and equipment sub categories, I have created the following query: Equipment ID, Equipment Description, Equipment Category, Equipment Sub Category, Equipment Sub Sub Category.

Would I use this query to create the subform on form 1 as this demonstrates all of my links?

Thanks
 
as I said, just try it, and see what happens. Then you can amend this to suit your own circumstance. Once you see and understand what access is doing to help you, other things should become clearer.

----------------------
I repeat that I still cannot see the need for 4 tables, even if an item can be linked to other items in more than 1 category.

so a given item may be treated as being in Category A, for some reasons, and in category B, for others, and in each case linked to other CategoryA/CategoryB items respectively.

Even so, I am sure this could and should be managed with just 1 table.

------------------
 
Ok. Thanks. I will have a look at my tables and give what you have suggested a try. Will let you know how I progress. Cheers
 
I did as you suggested above.

I have an equipment form (which also had to have a subform to establish the links to equipment category and equipment sub category). I then set up the subform you suggested (as a datasheet) which was compatibility table A. (this could have been any compatibility table [A, B, C or D] or even combined compatibility like you suggested).

However, I have a couple of issues with this. In doing this I still have to manually pick the drop down menus for each of the equipment ID's. Given that I presently have in excess of 60 equipment items, this is very time consuming. and will only get worse in the future as more equipment items become available. Is there no way to automate the database to search through the equipment table and automatically establish a record in the compatibility table for the new equipment ID with all of the equipment IDs in the equipment table and then all the user would have to specify is the confirmed for use field? Like something similar to a marco or could this be done using VBA?

Thanks.
 
Just to add to my last post and clarify, what I am trying to get the database to do is:
1. Query the equipment table, equipment link to equipment categories and sub categories table, equipment category table and equipment sub category table to draw out an extensive list of equipment and what category(s) it applies to. (*I have already done this)

2. Pull out records with a specific equipment category e.g. equipment category = A and count the number of records. (*with online help I believe I can work this out myself)

3. Automatically create a number of new records in the compatibility table (as calculated in 2) and list each equipment ID as identified in (2) against the newly added piece of equipment. (perhaps this could be done using an append query or something similar?!? This is what I’m really struggling with) :confused:

4. Allow the user to complete the rest of the fields. E.g whether or not the equipment vs equipment combination is confirmed for use?


If there was a way to automatically create the equipment vs equipment compatibility matrix (as in 3 above) I would happily alter the database to have a single compatibility table. The main reason I introduced four was to significantly cut down on the number of equipment vs equipment combinations in the first place as I currently have in excess of 1000!

Cheers :)
 
sorry - not looked fro a while

you could add a button that gives you the functionality


you could either code a bulk access append query, to add all the items in a certain category. judicious use of keys would stop access adding duplicates for one already
in use

or, you could step through a recordset in code, and write vba statements to append records one at a time
 
I was thinking if I could make a link to every existing piece of equipment when a new piece of equipment was added this would enable me to capture the compatibility in a single table, instead of separating into the four. I think this would also help to normalise my database correctly as the four existing tables all have identical fields and this is probably incorrect, liable to more problems down the line and taking up unnecessary space. I just couldn't see a way around this before!

I've also only started using access recently (at the start of this year) and although I am learning relatively quickly I dont have much experience with VBA. What do you recommend for a novice user, an access append query or VBA statements? If VBA is more appropriate, I am prepared to try and learn this.

Thank you for you help. This forum is proving to be a life saver! :)
 
I have been working on the append query to try and avoid VBA.

The fields I am trying to append are both from the same equipment table. Just "Equipment ID" which is a text field but also the primary key for this table and I think this is causing me problems.

I am trying to append the equipment ID combinations to the compatibility table so that the following would occur:

E vs E ID, Primary Equipment ID, Secondary Equipment ID, Confirmed for use? (where these are the field names)
000001, 0001, 0001, 1 or 0 (user will define later on the forms)
000002, 0002, 0001
000003, 0002, 0001
000004, 0003, 0001
000005, 0003, 0002
000006, 0004, 0001
000007, 0004, 0002
000008, 0004, 0003
000009, 0004, 0004

Hopefully the pattern is obvious. When there is only one piece of equipment in the database (0001) it is compared with itself.

When equipment 0002 is added, it is compared with the equipment already held in the database (0001) and itself.

When equipment 0003 is added, it is compared with 0001, 0002, and itself.

And so on...

So, when I go to append the records I am getting the following message. "Microsoft Office Access can't append all the record in the append query... it didnt add 5 record(s) to the table due to key violations"

Does anyone know how I can append this field, even though it is my primary key, or alternatively a means around this problem?

Cheers :)
 

Users who are viewing this thread

Back
Top Bottom