Update through form

[RemyO]

Table Location: ID, Location_description (each location is Unique)

Table Parts: ID, Part_description (Each part is Unique)
Table Device: ID, Device_description (Each device is unique)



Between parts and device the is an many to may relation so i created the table machine.

Table Machine: ID, Part_ID, Device_ID (each machine is unique)

Table Machine_Location: ID, Location_ID, Machine_ID

I would like a form with the following pull downs

Location_description , Part_description, Device_description

When i select a Part the pull down of Device should be updated with only those values that are in the machine table.

When all entries are filled in i would like to update the Machine_Location record

So far all i got are above tables. But creating the requested form is a total no go. Can someone help me ?

Regards

RemyO

[orange]

The pulldown is a combo box and whenone pull down affects the values in the next pulldown - the subject is cascading combo boxes.
There is a free video tutorial here
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

Here is another reference to cascading combos
http://www.fontstuff.com/access/acctut10.htm

For your benefit and for others you might wish to describe your application and give a one line definition/description of your tables. It would help communications greatly.

Another site that may be helpful to you (the first 3 topics)
http://www.rogersaccesslibrary.com/forum/topic238.html

If you start with a data model you'll have a clearer picture of the pieces and how they fit together.

Good luck

[RemyO]

I am trying to build a small app which keeps track of reparations.

A company has several locations.
Each Location has of 1 or more machines. However there are no duplicate machines within the company

1 Machine consists of several devices put together.
1 Device consists of several parts. But a singular part (for instance a screw) can be use in more then one device.

I think the datamodel is fairly evident given the names I used in the tables.

Table Location: ID, Location_description (each location is Unique)

Table Parts: ID, Part_description (Each part is Unique)
Table Device: ID, Device_description (Each device is unique)

Table Machine: ID, Part_ID, Device_ID (each machine is unique)

Table Machine_Location: ID, Location_ID, Machine_ID

The real problem isnt the comboboxes not the cascading comboboxes. The real issue is how to set up a form using

Location_description , Device_description, Part_description which will result in inserting a record in the table Machine_Location

[orange]

There are free data models at www.databaseanswers.org that may help in organizing tables in your particular environment. Here is a data model dealing with Equipment repair
http://www.databaseanswers.org/data_...pair/index.htm and it has associated business rules here http://www.databaseanswers.org/data_...pair/facts.htm

While this may be overkill for your problem , it may help in understanding how some of the pieces might/could/do fit together.

[orange]

Does your data structure look like the attached?

How did you populate the Machine table?

[RemyO]

Thats exactly my datamodel

As for populating the machine table .. i cheated and put everything in there manually

I want to learn Access and a friend of mine was having issues with the requested form (including cascading comboboxes). Having a programming background myself i thought i could make the form work.
But it appears to be more complex then i thought

Remy

[goodguy]

The relationship diagram shows a relationship between Parts and Machines Tables while your post says that a machine can have many devices and a device can have many parts. If your statement is right, then your diagram is wrong.
1. Delete the Machines-Parts relationship.
2. Delete the PartID field from Machines table.
3. Create a PartID field in Devices table.
4. Create a Devices-Parts relationship.
If this does not make sense at first, try it with a clear head and see the results.

[RemyO]

I understand what you are saying. I also doubt if this model is the thing my friend wants. However the issue with the form that i am describing is something that Access should be able to do.
At the moment it isnt so much anymore is this the thing i want/need but is more the 'Why doesnt this work in Access It should be able to do want i want'.

For me all that counts is the learning experience at the moment.

[orange]

Can you provide some sample data so that we can test the model?
The model proposed was based on your tables and field names.
The model not have a many to many between Parts and Devices.
Each machine is unique, and is composed of a PartId and DeviceId and has a unique Identifier. So a Machine ID identifies a PartID/DeviceID combo. And since a Machine exists in a Location, why can't LocationID be included in the Machine Table?

I am not following your issue with the Form. The drop down for Parts can be sourced from the Parts table. Once a Part is selected, the pull down for Device is sourced from Machine (which was added manually) by identifying those machines that include the Part selected from the first dropdown. But that would identify a duplicate machine according to the model -- since a PartID and DeviceID make up a machine.

Attached is a jpg of the Form with drop downs used to populate the Machine table.The intent was to use the Location drop down to populate a Location field in the Machine table..... but according to the database - Location was a field in the MachineLocation table.

I have concerns with the model and the Tables. It's time to test the model with data and adjust to suit your business requirements.

[goodguy]

Do not expect Access to get things right if you have not gotten them right in the first place.

[orange]

Do not expect Access to get things right if you have not gotten them right in the first place.

I agree totally. If the structure is wrong, all the coding will be focused on programming around the shortcomings of design.

Time to get the model correct - define the business rules and test data and test the model until we're satisfied it fits the situation.

RemyO -- can you show us some sample data; we'll test/adjust the model to get it all right.

[goodguy]

To the ADMINS: Can we please have a 'LIKE' button for posts a la Facebook?

[RemyO]

I tried to simplify the issue leaving out 1 table in the above.

So here is my actual model



A device (apparatuur table) has a unique name and consists of parts (Onderdeel). Naturally parts can be used in several devices.

I solved that by creating the table machine in my database

Machines can be split into several categories. 1 category can have several machines assigned to it
Same counts for locations. 1 location can have more machines.

The category and group are only there for future reporting purposes

We can have multiple machines of the same type within a location and group .. however within a group on a certain location only 1 machine of the same type exists.

The problem here is populating the table Apparaatlijst using the descriptions of groep, location, apparatuur and onderdeel.

This is basically what they want at this moment. This will be merely a prototype. It might be they formulated things wrong or want something else. All i want is to figure out Access. I know it can create the table but for some unknows reason i cant figure out how to get it to work

[orange]

Leaving out a table isn't the answer. You have to understand the requirements, make a model and test the model to make sure all requirements are handled. If the model doesn't meet the requirements, adjust the model. But the key is to get the structure correct before you start programming.

I have acc2003 and can not open an accdb file.

Good luck with your project.

[RemyO]

Same db now in Access 2000 format