In Access 2007, Is there a way to populate a continuous form based on another continu

[bakkouz]

Ok I'm not sure how to explain this so i'll try my best to be as specific as possible.
I need to build a preventive maintenance form that records actions taken on cabinets for certain dates, each cabinet is maintained weekly and contains multiple items, the actions are records for the items inside the cabinet and the date is recorded for the cabinet itself.
I have created a table for the cabinets which is structured simply as follows:
ID__ Cabinet__
1__ Administration
2__ HR
3__ Financial
4__ Salaries
and another table for the cabinet contents which is structured as follows:
ID__ LineNumber_ Item__
1__ 1________ Switch
1__ 2________ Router
1__ 3________ Transformer
2__ 1________ Switch
2__ 2________ Converter
And so on. I've built a continous form for the second table (cabint contents) and embeded it onto the main form for the cabinets and used the ID as master/child link. this is simply to add/modify the items/cabinet names/location.
Now for the question: I want to create a third form for the actual maintenance data; ie: date and action per item per cabinet. what I want to do is create a form which a combo box that selects the cabinet name, and beneath it is a continuous form to display the items inside the cabinet with the action performed on each item. But, I am not sure how to do that, I need the continuous form in the new main maintenance form to somehow pull data from the first form and add them into the second form and store them into a third table. I've tried to figure it out but I cannot understand how to do this.
I hope that I've managed to clearly explained my predicament. Is there a way to do this?

 

[Mihail]

Your DB is not normalized
Database Normalization and Table Structures - Microsoft Access / VBA

Upload your DB (in Access 2003 version)

 

[bakkouz]

Mihail:
Maybe I haven't made myself clear, this is not a matter of database normalization, I don't know how to build the form.

I need a form that will read the data from the previous tables, and add them into a new table. without changing the structure or data in the original tables.

Basically, I have data concerning Cabinets and the items inside them. I need to take this data and add another field for action taken on each item and date for the action for the cabinet as a whole and add the new data into another table. ie: the new form needs to incorporate the form that lists the cabinets/items (form containing a sub form) and add new fields to it and store it into a different table. using a combo box to select which cabinet i need to display the data for.

Anyway i have attached what i have right in 2003 and 2007 format.

 

[Mihail]

All your last post is a matter of NORMALIZATION.
Take it, or not, as a criticism, but this is a fact.
Also, note, that every one started, at a time, without the "normalization" knowledge.
There are few that never heard about this because, at that time, the concept not exist. So, this few, invented it. And they do a good job. A very good job.

One of the main rule of DBs (that is based on the normalization concept) say that never store the same information in two (or more) places. And all your posts are about how to broke this rule.

I'll take a look to your DB (hope I'll not be the single) and I'll try to help yo.
All of this after I eat something .

 

[bakkouz]

Ok, I understand what you're saying, and I've read the article you linked, and I understand it. but I don't see how this helps my problem. and I'm not complaining or anything, on the contrary I welcome your help and input very much, I just don't see what I'm missing.

 

[bakkouz]

Edit: I would just like to add that I need the user to add/remove/modify the items in the cabinets and/or cabint information as necessary that is why i needed to user a form/subform.

Then i would like to take the existing data and apply the necessary modifications onto a form to add "action taken" / date / to the individual items and cabinets in the tables above, without messing with the components of the cabinet and/or the cabinet information.

 

[Mihail]

... I've read the article you linked

Hm... maybe

... and I understand it.

NO. For sure.

Sorry, but I'll not help you because:
1) That mean to do all your work. From scratch.
2) That also mean to teach you something VERY wrong, and I can't do this.

Happy New Year !

 

[David R]

I don't understand your question. Maintenance should be tied to the item, not the cabinet, right?

 

[bakkouz]

David R:
Yes correct, the cabinet has only a date field and the technician's name field. and a field for any other remarks.

 

[David R]

I need the continuous form in the new main maintenance form to somehow pull data from the first form and add them into the second form and store them into a third table.

Can you give an example or two? Draw it in MS Paint and attach it if you like, or just walk us through with some 'real world' examples. Don't worry about using MS Access language, we can translate that pretty well. What we can't translate is things like 'it doesn't work'.

i.e. "I need my subform to show all of the cabinet contents, but also allow me to enter/review each content item's actual full maintenance history. How do I make that third form know what cabinet and item I am talking about?". Am I close?

 

[bakkouz]

David,
Ok here it is basically:

This is the cabinet/cabint items form:

As you can see, it shows the cabinet and its contents. the user of course an add/modify cabinet names and contents as necessary, I managed to do this using the continuous form.

Now, what I want is the procedures form, which should look like this:

So, the user selects the cabinet from the combo box and the top, and in the subform it shows the items inside the cabinet which extra fields for date/action... etc.

I hope this makes it more clear