updating multiple records through a single form

Howdy,

I am working on a soil DBMS. I have individual sample sites with multiple horizons, so we have site A0001 and it has HORIZONs 1,2,3,4. The format of the table is that the first column is the Site_num (non unique) and the second the HORIZ_NO, also non-unique (except within the context of a single sit_num). So in effect I need to update mutliple records with one form, in a common table.There is one table for all the horizon data and I would like to update it with a single form for each site. Thus I would like to enter the site number at the top and then have one text box for each horizon and for each attribute. i.e., for the pH value, I will have one text box for horizon 1, one for 2... Help!

Further, certain attributes, e.g. ground water depth, are common for all horizons of single site. So, although there are four entries for pH, there should be one for ground water depth, which would update all four records.

Any help would be greatly appreciated, and good for your karma!

Karl

In order for you to accomplish what you are wanting to do, you will need one or more subforms. A main form to display each site, a subform to display either in datasheet view or single form view your Horizons. Then another subform to enter your data based on the selections of site and horizon. Trying to set up your form the way you have described cannot be done in a single form view with multiple records. I am not sure what you meant by common table, but typically your tables will contain unique data or information that is related. Example a site table would contain all the information that is unique to the site. A horizon table would contain all the information unique to a particular horizon. Data that is added or records that are maintained related to the horizons in yet another table. This will create the one to many relationships One site has multiple horizons and each horizon would contain multiple records of data. Then build a form with subforms related to each of the tables. Then by selecting a site, all the horizons will be viewed, then select a horizon and all the records related to that horizon for adding or editing data would be viewed.

lwells

Hi lwells, thanks for the repply,and sorry for not getting back to you earlier. In the end i did what you recommended. I have a form with the surface data, with 5 sub forms automatically filed with the sitenum and the appropriate horizon number. These subforms enter data into 5 seperate tables. When the entry is saved, a macro is run that appends these entries to a single table and deletes the entries from these temporary tables using select and delete queries. So I have a bunch of queries, extra tables and a macro, making the process rather unelegant! With my computer it also is slow to work with the form, which is really 6 forms. And they're big.

As further background, I have 35000 sites, with 3-4 horizons each! Each site can't have it's own table! Thanks again lwells!

Well I am glad you found a suitable (and I agree not so elegant or practical) solution, and I still believe setting up just three tables is all that will be necessary regardless of the number of sites or horizons you have. I am still lost as to why you have data in five tables and then turn around and append all that data into one table. It really serves no purpose that I can think of, but I am sure you have your reasons.

One word of caution, adding and then deleting records over and over will bloat the database, which if you are running on a slow machine already, will become very critical in the way of loss of performance and speed.

Re-evaluate your table design, I bet you can set up a one-to-many relationship with one site - multiple horizons - and multiple records of data for each horizon, thus giving you just three tables for all your data. Using simple queries will let you filter the records extremely fast even on the slowest of computers with normalized tables. In the meantime, try compacting the database and see if it will speed things up for you.

lwells