Linking tables to select multiple records in a form

[rbtkoz]

I'm trying to create a form that will automatically enter multiple records in a form when I select a name in a Lookup box. The database has 3 tables (OWNER_INFO, PROPERTY_INFO, and TREATMENT_RECORDS). The OWNER_INFO contains owner names, addresses, etc. The PROPERTY_INFO contains different properties the owners have. The TREATMENT_RECORDS contains fields of products used to treat the properties. I have created 2 fields in the TREATMENT_RECORDS table set up as Lookup field. they are linked to the owner's name field in the OWNERS table and the property name in the PROPERTY table. All tables have a primary Key field and the PROPERTY table has a foreign key. There is a master/child relationship between the OWNER and PROPERTY. What I want to do is set the form up so that when I select the owner name from the Lookup list just the properties that belong to the owner will appear on the form. Then I would select one of the properties and record the treatment for it. Is there a way that I can link the owner Lookup field to the property name Lookup field?

[alansidman]

You can use subforms. Here is an example

http://www.datapigtechnologies.com/f...subforms1.html

[ssanfu]

I have created 2 fields in the TREATMENT_RECORDS table set up as Lookup field

I would get rid of the look up FIELDS. (different than look up tables). See http://access.mvps.org/access/lookupfields.htm

Care to post your dB? No records needed......

[rbtkoz]

The link you posted does not respond when I click on it. I tried a sub form before. I'm trying to view about 10 to 15 fields and this requires a lot of scrolling in a sub form. I prefer to be able to see all the fields on the main form.

[ssanfu]

Both links open for me.

Would still need to see your dB to understand your concept.

Or images of your table relationships and images of what you want your form to look like.

[rbtkoz]

What would you recommend I do if I get rid of the Look up Fields? What I'm trying to do is either click on or enter an owner's name in a form. The owner's property information from another table would populate the form. I would then select the property that is being treated. Then I would enter the treatments.
I'm new to this forum. How do I post a database?

[rbtkoz]

I used Microsoft Edge to try to open the link and upload the database. That browser doesn't work well with this web site. I'm using Firefox now and was able to view the link. I wasn't able to reduce the size of the database file to under 500KB to upload it so I took screenshots of the tables and main form.

[orange]

How are these tables related? Do you have a relationships diagram in your relationships window?
Can 1 property have more than 1 treatment?

Best to use field/column names that do not have embedded spaces and do not use non alpha characters. Exception is the underscore "_" which is acceptable to Access.

A Date/Time data type can store date and time in a single field. You do not need separate Date and Time fields.

ID is a default by Access, but most will tell you to associate the name with the table, such as
Treatment_ID or TreatmentId
Owner_ID
Property_ID

Rationale:
Once you have >10 tables or so, distinguishing 1 ID from another can be confusing.

[rbtkoz]

The field names are being corrected. The screenshots are from an older version of the database that is being updated. the relationship screenshot shows the current relationships. the scenario for this database would be: Each owner may have more than one property and each property has treatment records. Ideally I'm trying to set the form up so that employees don't enter treatment records for the wrong property.

[ssanfu]

What would you recommend I do if I get rid of the Look up Fields?

Use a combo box on the form.

I don't have all of the fields, but I would create relationships like

(PK = Primary key
FK = Foreign key)

How do I post a database?

Do a "Compact and Repair". Then Zip it.
Click on the "Advanced" button.
Scroll down/ click the "Manage Attachments" button.

[orange]

Which of the tables represents your Property table that you mentioned in the beginning?

If the treatment refers to a property, your table structure could be as follows.

Owner---->Property--->PropertyWasTreated<---Treatment



In this set up

There are many Owners
There are many Treatments
An Owner can own 1 or many Property(s)
A Property can be treated with 0,1 or many Treatments

But we need to hear more about your tables and your business.

[rbtkoz]

I probably shouldn't have called it a property. It is a body of water such as a lake or pond, which may have it's own unique address or location. the database has over 600 owners with their info in the OWNWER_INFO table. the names of the waterbodies and corresponding data is in the Waterbody_information table. it has over 1000 records. the Treatment_Records contains materials used and conditions at time of treatment. at the current time the treatment records has over 27000 records. I'm wiping the treatment records clean except for the field names and importing the owner info and waterbody info into a new database. the old database wasn't set up correctly. I'm trying to streamline the new database to make it work more efficiently, but when you mix old with new you get bumps in the road.

[rbtkoz]

I will give the relationships and combo boxes another try. I had created a combo box but I saw that it was recording numbers instead of actual data so I discarded it. Perhaps I set it up incorrectly.

[orange]

It might be the right time to tell readers about your "business". That is a description of what you are trying to do in simple terms. And what exactly the things are that you are dealing with. I say this because your business rules will determine your relationships. And getting your tables and relationships designed to meet your requirements is critical.

I don't understand Owner of a Waterbody?
If the database wasn't set up correctly, then take the time to design it to match your "business".

You may find that working through this tutorial will help with your database. It leads you through a process to design and check your tables, fields and relationships to make a blueprint for your database. It has a problem and solution and details the procedure to identify and create the tables and relationships. You have to work through the tutorial (40-60 minutes), but you will learn things that you can use with any database.