Auto fill text boxes based on text box input

[zrawe16]

Hello,

I am creating a form that will auto fill text boxes based on a unique ID entered into another text box. There are also check boxes on this form from another table. So this form will be created based on two tables. One table is already filled with information the other is blank... I am a novice so this maybe easy and something I am over looking. I created a form using the wizard and chose the unique ID, Location and Owner from the populated table and chose the check box items from the setup but black table. I'd like to enter the unique ID and the other field populate and then the user would check the appropriate text boxes and then the form would create a new table with the information filled in....When I enter the unique ID the other fields don't populate and the information is saved to the original tables in a new row...Thanks for any input....

[jzwp11]

What type of relationship do you have between the two tables? Can you provide a little more detail (fieldnames) of the two tables? What type of information is in each table?

[zrawe16]

Sure. Thanks for the response. I am creating a form for inpsections of stormwater ponds in a City. We have a table that contains all of the information of each pond and each pond has a unique ID. Inspections were performed using an inspection sheet and I'd like to make it digital so they can input the data in there tablets in the field. I have two tables. A 'Master Pond' table that has Unique IDs for each stormwater management pond and fields containing; location, owner, last inspected. The other table is empty but has fields regarding inpsections we have to do on each pond. Erosion (Y/N), excessive sediment (Y/N), rip rap failure (Y/N). These are linked by unique ID. I'd like to make a form that contains field from both tables. The unique ID, location and owner from the Master Pond table and all of the inspection information from the other. I'd like the inspector to enter the UniqueID into a text box and then the location and owner to autofill with the information from the master pond table. Then fill out the rest of the form and check boxes. Once the form is filled I'd like that information to be saved into a new table with each row being a stormwater pond inspection. Hope this makes sense I'm learning as I go.. Thanks!

[jzwp11]

You are actually short a couple tables. You describe a one-to-many relationship between the MasterPond table and the inspections, but each inspection has many items to inspect (erosion, sediment level, etc.). This is another one-to-many relationship. I assume that you inspect the same items at each MasterPond at each inspection. This implies that you have another one-to-many relationship, 1 item to many inspections. So we have 2 one-to-many relationships between inspections and items which is a many-to-many relationship.

With your current table structure, what would happen if you need to add a new item to inspect in addition to Erosion, excessive sediment, rip rap failure etc.? You would have to alter your table structure which in turn would require you to redesign every related form, query and report!

To properly structure this, the tables should look something like this

tblMasterPond
-pkMasterPondID primary key, autonumber
other fields related to the pond

tblInspections
-pkInspectID primary key, autonumber
-fkMasterPondID foreign key to tblMasterPond
-dteInspection (date of inspection)
other fields related to the inspection event

tblItems (this table will hold just a simple list of all items that can be inspected: erosion, sediment etc as well as any you may add in the future)
-pkItemID primary key, autonumber
-txtItemName


Now tie the items to the inspection

tblInspectionItems
-pkInspectionItemID primary key autonumber
-fkInspectID foreign key to tblInspections
-fkItemID foreign key to tblItems
-inspectresult

In terms of forms, you would have your main form based on tblMasterPond and within that you would have a subform based on tblInspections and within that another subform based on tblInspectionItems that would have a combo box based on tblItems.

[zrawe16]

Thanks. This is very helpful w. setting up the tables. How do I set the text boxes in the form to auto populate with information from the Master Pond table after a uniqueID for the stormwater pond is entered into the text field. i.e. I enter the uniqueID for the stormwater pond and the location, owner and type of pond auto populates the respective text boxes. Thanks again.

[jzwp11]

You would base the form on the masterpond table and include the fields you want to show on the form.

I enter the uniqueID for the stormwater pond and the location, owner and type of pond auto populates the respective text boxes.

Don't you have to assign the pond type and the owner? Or is this information stored in other tables? I guess I'm not following... Can you provide more detail on your table structure (i.e. additional fields, tables)?

[zrawe16]

This information is in the master pond table which has all the information filled in already. I'd like it to auto populate the respective text boxes on the form after the unique ID for the stormwater pond is entered so the inspector doesn't have to enter it in manually. And then manually fit out the rest of the form.

[jzwp11]

If set up properly, you should be able to see all the records in the masterpond table via the form. What it sounds like you need is a search box that will take you to the specific record of interest. This is typically done with a combo box in the header of the form, that box would show the uniqueID you mention, when someone selects the uniqueID they want, the form will automatically go to the records and all information for that pond will be displayed.

I have attached an example database that I created that illustrates the technique. It is basically set up to search for people but the principle to search for a specific pond would be the same. I don't show a subform in this example as you would need to show your inspections.

[zrawe16]

Thanks. I am unable to open the example database because of network security, but I think I'm making some progress based on your input. I've created a subform that looks like a form in design view,but when I go to form view it looks like a table. I attached a image clip of what it looks like.

Reading through one of the first messages - I am actually trying to have one record for each stormwater structure in the master pond table so I think it is a one-to-one relationship. The user selects the uniqueID for the pond that's being inspected and then the rest of the information in the main for is auto populated. Then the user will fill out the subform (inspection form) and then I'll have a new record for that pond. Then I can recreate the Master Pond table through a query pulling the fields I need from the master pond and pond inspection tables. Thanks!

[jzwp11]

..subform that looks like a form in design view,but when I go to form view it looks like a table

The datasheet view looks similar to the table. You can force Access to show the subform in form view using the format properities of the form.

I am actually trying to have one record for each stormwater structure in the master pond table

You did not mention the stormwater structures. If a pond has many stormwater structures, then it would be a one-to-many relationship which would require a separate but related table. If there is only 1 stormwater structure per pond then you would indeed have a 1-to-1 relationship. It is your call whether to have this information in a separate table. I have never had a separate table for a 1-to-1 relationship. I just included it with the other information in 1 table. If you go with the 2 table approach, then I think it would be best to use a query that joins the masterpond and structures table and base the main form on that query.

[zrawe16]

Here is what I'd like it to look like in form view

[jzwp11]

The form you show will not work with the structure I proposed earlier since each inspection has many items to be inspected, the actual inspection information must be in a subform within the subform. It is more important to have the correct table structure and then manipulate the forms to work with the structure rather than forcing the table structure to match the way you want the form to look.

[zrawe16]

I understand. There are a few things that I'm trying to get figured out with creating these forms and table before I go and restructure everything. Some of these tables reside within GIS. Any idea why I cannot get the subform to look like a form in form view? thanks again...

[jzwp11]

Go into design view of subform and then the property sheet for the form. Go to format-->Allow Datasheet View and set it to No. Also if the default view property is set Datasheet View change it to Single Form

[zrawe16]

I thought I'd attach an image of the 'Master Pond' table and the hardcopy inspection sheet I'm trying to recreate. In the end I would like a new Master Pond table with: Previous Inspection, Insp Name, Status and Current Inspection updated with the new info. I think I can achieved this through a query. I would also like a table that contains all of the information on the SWM Pond Inspection list. I do not want the original Master Pond table to be altered at all, but I do want the Top fields on the inspection form to be auto populated with information from the Master Pond table so the inspector only has to either enter or pick a UniqueID from a drop down list. When the form is complete I'd like all the info to be saved in a table.. One row for each pond structure.....