Carrying data forward between forms in Add mode

[ProvPC]

I'm involved in a project that's beyond my depth, so I'm hoping someone can point me in the right direction and I'll (hopefully) learn a bit as I go.



I have a rather large table. It contains information regarding families, along with a couple of calculated fields, and innumerable yes/no fields - enough that I'd like to split the data entry form up a bit. To that end, I've created a "Household" form, where names and addresses and the like are entered, and a "SkillSet" form, where some yes/no options are selected, and a "Resources" form, where some additional yes/no options are made.

One of the fields available is a calculated field that takes the elements of the names entered and joins them in a string (so the individual names become "Mouse, Mickey M. and Minnie P.", representing the household). What I would like is for the button at the bottom of the Household form to open a new form, display the name from the calculated field, and continue editing the same record, adding the various yes's and no's to the record that was begun in the Household form.

Phew. I hope the explanation makes sense - I barely understand it, and I wrote it!.

[ranman256]

the button will open the form, say : frmNew
then afterwards fill in the boxes

docmd.openform "frmNew"
forms!frmNew!txtHouseHold= forms!frmOld!txtHouseHold

[ProvPC]

Thank you!

At present, thanks to some advice I was given in another thread, I'm in the process of renaming numerous fields within the table. I hope to have that wrapped up later today, then redo the forms, and give this a try!

I assume I'll need to add the field to the old form (it's in the same table, but not on the form itself). I'll post back and let you know how things went.

Thanks for your help!

[John_G]

With a lot of data going into one table record, you could use a tab control to separate your fields into related groups, with each tab containing one group. Tab controls allow one form to contain a lot of data, but only display some of it at any given time.

[ProvPC]

Tab control... Do you mean, for example, I could create the form with tabs across the top, and have, say, household information on the first tab, click the second tab to enter skillsets, then a third tab to enter resources? And the data is all related to the same record - it's just sort of continued on one long form, which happens to be broken into sections chosen by the tabs?

If so... that sounds SO much better than my original idea!

[John_G]

Yes, that's exactly how the tab control works. It does not have to be for the entire form, though - you can have some data outside the tab, so it is always visible, with tabs for groups of other data in the same record.

Tabs can also have subforms on them - very useful if you have a main table and several different related tables.

Here is a screenshot of one of my forms so you can see what the layout might look like:

[orange]

PMFJI, but yes that is the concept. However, you may want to break the subject matter into separate tables and have appropriate joins between then such that info about the same household an be "pulled together".

What is the basic data you are dealing with?

Household Skillset Resources

You may need a structure that says

ThisHouseholdHasThisSkillset

Household-->thisHouseholdHasThisSkillset<--Skillset

similarly

ThisHouseholdHasThisResource

Household-->ThisHouseholdHasThisResource<--Resource

Is is rare that a well structured database has 1 table.

Good luck.

OOOops: I see John responded while I was typing. Nice example.

[ProvPC]

Please, jump in all you like! I've a lot of learning to do, and the more voices the better, so far as I can see!

Here's the complete story:
I was asked by a relative to take a look at a booklet produced by an organization of which he is a member. It's a sort of directory which includes participating members of their organization, along with skillsets they may possess, or resources they may own that could be brought to bear in the event of an emergency. The problem they had was that the data was pretty nearly always out of date by the time the book was published, and there was no real way of keeping track of changes when members come or go, or people acquire new skills, or assets are sold or disposed of.

The data, as I alluded to, consists primarily of three elements:
1) Households - Most of these are simple text fields: names, addresses, phone numbers, emails, local chapter, languages spoken, and number of people that could be sheltered by the household in an emergency.
2) Skillsets - Most of these are simple yes/no fields, asking whether a member of the household has skills as (for example) a plumber, nurse, EMT, counselor, IT, electrician, etc.
3) Resources - Much the same as Skillsets, most of these are simple yes/no fields, asking whether the household owns or has access to (for example) a truck, welder, backhoe, wet/dry vacuum, extensive first aid kits, ATV's, walkie talkies, etc.

Originally, my idea was to put all the data into an Excel spreadsheet, create a form on the first page to enter or search for data, and a few lookup fields (select the city, all applicable zip codes are displayed, etc.) This worked quite well, actually (thanks in no small part to the assistance I got from a forum very similar to this one). However, a problem arose: many of those who will be entering the data will not have Excel, and the various macros and formulas did not work in any of the "free" spreadsheet programs I tried. So I thought my best bet would be to put the whole thing into a Access database, and save it as a standalone application.

There are more hurdles to cross, still. I have to figure out a way of importing the data from the spreadsheet (roughly 3000 records) into the new database and how to prevent record duplication. And I have hopes of converting the database for use on the web (or using what I learn here to recreate it). Otherwise, I'm going to have to figure out a way of creating an import/export process so that the data can be occasionally synchronized among the users.

I originally created three tables, corresponding to the three elements above, and formed a many-to-many relationship using a join table. When I created a query to look at the data as I entered test records, it seemed to be working fine, but I thought that a single table might be easiest to import to and/or convert to web use; but I really don't know what advantages there might be to separating them, and I definitely not opposed to the idea of doing what's best in the long run.

[ProvPC]

Hm.. I thought I posted this already, but something may have gone wrong at my end.

Before I go any further down what may well be the wrong path, should I return to splitting up the tables? I'm early enough in the project to make this change fairly easily - but novice enough to realize that it will add complications that will probably require that I pester the forums to learn how to resolve.

I do want to do it in the way that makes most sense for the project. I'm just inexperienced enough to not necessarily understand what that way might be.

[John_G]

Based on your description in post#8, you do need to take another look at your data structure. Your 1 main table had fields ( = questions) for each of a number of skills and resources. Now what happens if you want to add a new skill or resource (which to me seems almost certain)? Your tables and forms now all need to be changed, as do all your search procedures.

Note Orange's post above; your data structure should be normalized to remove the fields for multiple skills / resources, and replace them with the 4 that orange describes. Adding new skills or resources to the list then becomes trivial.

There are dozens of websites describing how normalization works - just put "database normalization" into a search engine.

Here is one: http://holowczak.com/database-normalization/

Good luck with your project.

[ProvPC]

Thanks! That link was very helpful. I'm not sure I've done it completely correctly, but I'm confident of being a lot closer that I was when I began!

[orange]

Using pencil and paper---You can take your tables and relationships; and some test data; and some scenarios for getting info out of the database (forms/queries/reports..).....test the model, adjust as necessary-----until you can get the info you need. Then, you'll have a blueprint/design for your database that you know works.

Good luck.