Full Version: Creating a single input form based on multiple tables
UtterAccess Forums > Microsoft® Access > Access Forms
robtorch
Wow is Access humbling.... I'm very new and not having much luck finding a clear answer to this. Charlie and a few others have helped me immensely in designing a normalized table structure (for which I am very grateful!), and now I'm trying to set up a form to input a test data, which will also be the eventual user interface. Well, that brought to light how little I know about forms! I have about 10+ tables that I want to be the source for just one form. I've read about form/subform relationships but I just can't make the connection as to how this should work. I'm sorry if this seems ridiculously easy to anyone, but I'm clearly no guru.
I have an old form that I would very much like to use, because the look and feel mirrors other systems that I use in my business. It also allows almost all of the relevant client info to be inputted on one screen without switching screens. I have attached a screenshot of the form.
So my question is: Is it possible to use the screenshot of the attached form when having so many different tables on the back-end? And if so (now the real question) how?? I can't figure out how to have 10+ record sources and also have all the information change accordingly as I scroll through the records (like it should with an appropriate form/subform set-up). Is this what certain Query's are for? Again, sorry if I sound ridiculous, but even after I've read through about 3 books, I've got no practical hands-on experience with this, so I'm still on the steep part of the learning curve. Any advice would be greatly appreciated - thank you!!!!!
THere are some of my tables - hopefully still normalized! (all PK's are Autonumbers):
tblPeople
PersonID (PK)
LastName
FirstName
MiddleInitial
Nickname
Birthdate
SocialSecurityNumber
Sex
tblPersonRelType
PersonRelTypeID (PK)
PersonRelType
PersonTypeID (FK)
tblPersonType
PersonTypeID (PK)
PersonType
tblAddresses
AddressID (PK)
PersonID (FK)
AddressLine1
AddressLine2
AddressTypeID
ContinentID
CountryID
CityID
tblAddressType
AddressTypeID (PK)
AddressType
tblPhoneNumbers
PhoneNumberID (PK)
PersonID (FK)
PhoneNumberTypeID (FK)
tblPhoneNumberType
PhoneNumberTypeID (PK)
PhoneNumberType
tblCities
CityID (PK)
City
PostalCode
RegionID (FK)
tblContinents
ContinentID (PK)
Continent
tblCountries
CountryID (PK)
CountryAbbr
Country
tblCounties
CountyID (PK)
County
CountryID (FK)
tblRegions
RegionID (PK)
RegionAbbr
Region
fredrisg
Rob,
Ybr />our normalized table structure will help you greatly.
For the most part, the primary table would have Foreign Keys to these tables as selected through cbos if indeed they needed to be tracked to the primary table.
So I'm not sure if your question about connecting to 10 different tables is necessarily the right one in the context you've stated.
Using tables as record sources for cbos isn't the same as binding a form to 10 separate tables per se.
In other words, the primary form could be bound to a since single table with the foreign keys mapped to the same table but selected through cbos on the primary form.
Is this making sense?
Steve
Jack Cowley
You will create forms based on these tables: People, your relationships table, addresses and phones. All the rest of the tables are 'lookups' and will be combo boxes in the forms. For example your Addresses table has ContinentID, CountryID and CityID. These controls on your form will be combo boxes based on the Continents, Country and City tables. When you type in an address you select the Continent, Country, etc. from the combo boxes.
The People form will be your main form and Addresses, Relationships, and Phones will be subforms (related on PersonID) on the People form. This may sound worse than it is, but just dive in and make your forms and then add them to your People form as subforms uising the Wizard and you should be good to go...
It is not as complicated as it seems once you get going... Good luck!
hth,
Jack
robtorch
When I place a combo box on the form, I can get it to link to the appropriate table (cities, for example). But I don't know how to link it to the individual person's who's information I am currently entering into the rest of the form. When I go to the next person, the combo boxes still contain the same value that I chose for the first person, and if I change them for the second person, then they remain the same when I go back to the first person. How do I link them to individual records?
ScottGem
There are two ways to do this. Bound forms vs unbound forms. Bound forms are MUCH easier since Access handles all the interfacing. But with multiple tables you need to use subforms. I just completed initial work on an app that was similar in scope where I had one main form in a tab control and using subforms on the various tabs.
robtorch
Steve, Jack and Scott - I sincerely appreicate your efforts to help, but I think my lack of experience/lingo in causing a gap here. Conceptually, I understand better WHY to do it one way than HOW to actually do it.
On my form, I have one main with the record source set to tblPeople (is that what you mean by "bound"?) Then I have one subform that just shows the street address, linked to tblAddresses. These work fine - the address changes appropriately as I scroll through the people I have in the database. It's exactly what to do from here that I cannot figure out.
Where exactly do I put the combo boxes for city, region, postal code, county, country and continent? Do these go on the main form? The address subform? On their own subform? Do I need one subform for each since they each represent their own table? I have tried most of these methods, but none of the things I've tried link the individual people to a city, region, postal code... etc. There must be something in the properties I need to alter, or an entirely new approach I need to take. I've used the wizard to place the combo boxes, and when that hasn't worked I've been through every property and tried making changes here and there but I've had no luck.
I've attached a new simplified screenshot of what my form looks like now. I'm sorry if I'm being a pest, but Access Help and the pile of books I have here have been no help whatsoever and I am really stuck on what to try next. Any additional help would be greatly appreciated.
ScottGem
Yes setting the recordsource to a table binds the form. Those combobox belong in the subform, since they are components of the address. Comboboxes are just a way to select the FK value to store in a table. They have the added advantage of being able to DISPLAY the text value while storing the FK by setting the first column width to 0.
Ive looked at your screen shots and are you sure you are using subforms? If you are you must be supressing the borders and everything so they appear part of the main form. You can do this, but its not the best idea in my opinion.
fredrisg
Rob,
irst off . . . don't feel too bad about the books not helping you. Most books offer up a lot of info but putting it to work in the correct context comes with time, experience, and yes, beating your head against the wall now and then!
In any event, if you're just starting with access . . . you've taken on a big job to do what you want right off the bat.
I think Scott gave you a great idea in that you could create subforms on the tabs to 'isolate' each recordsource.
But to take a step back, on the orig attachment, you have a list of up to 6 kids on your form.
For me, rather than have 6 kids with 7 txtboxes each, I'd put a listbox that you could add kids to . . . unlimited amounts . . . and then have a Add cmdbutton to add kids to the list. Then when you dbl click a kid in the list, you'd open up a bound form to the kid's information. So now you've isolated one table (tblChild) and can update each child's form.
You could still see all of the attributes in the listbox by using multiple columns.
Likwise, on your employment tab, unless you want to limit the db to only one place of employment . . . which might change, you could do something similar with a listbox for employment that could provide you with the ability to have an employment history if you will . . . by adding employment records.
Just some thoughts . . .
Steve
PS - I've included a very simply db with a form with a listbox that allows a dblclick into a simple bound form. Sadly, an example like this is most often not included with the 'books'! But it will illustrate what I'm talking about as well as using naming conventions and the like . . .
robtorch
Awesome! That was it Scott - now it makes sense, the combo box is actually linked to the FK in tblAddresses, not directly to tblCities, like I had been trying to do all along (duh!) Now that I "see it" it's so obvious!!! And it actually works!! woo hoo!! And yes I formatted the subform to look to the user as if it's all one form - why is that not a good idea? I did it that way because I assumed that this stuff was just back-end development and that the user would not care how it works, just as long as it works. But if it's bad then I'd definitely like to know.
teve thanks for the attached db, I will definitely take a look because you are totally right about having spots for 6 kids, it's just a waste of space. Though I haven't gotten quite that far yet, first I was going to tackle how to use the PERSONTYPE table to define people as Clients, Agents, Spouses, Children, Company Contacts, etc. and I haven't got a clue how to do that yet. But one step at a time....
Thank you both again, your time is much appreciated!!
fredrisg
Just create a table like tblPersonType with fields like:
ersonTypeID (PK)
PersonTypeName (Clients, Agents, etc . . . )
In your primary table, you'll include the PersonTypeID as a FK (foreign key).
Then on your form, you can bound a cbo to the PersonTypeID from the primary table, use a simple 'PickList' qry for your cbo that list the Person Types and you should be good to go.
Steve
robtorch
Ok, just to be absolutely certain I'm on the right track here, I've got one main form bound to tblPeople, and a subform bound to tblAddresses. I added a combo box to the subform so the user can choose a city - after I go through the wizard, the SQL in the Row Source property reads the following:
SELECT tblCities.CityID, tblCities.City FROM tblCities;
It appears to work correctly, but I'm just not sure if this is correct. Does it look right? I added 3 other combo boxes using the same wizard-method to the Addresses subform: for AddressType, Country, and Continent, since they are all foreign keys in my Address table. Does it look right? I've attached another screenshot, and I added the border back so you can see that there is a subform being used.
fredrisg
Yes!!!
It's looking good . . .
Steve
ScottGem
Rob
Its not "bad" to make the subforms look as part of the other forms, but it doesn't make sense in some instances. For example, the children. As Steve indicated you really don't want to waste the space like that. Instead I would use a continuous form mode. With that type of design you can add as many or as few children as applicable. Steve, likes the idea of listing them in a list box. I've seen what he's done and its a nice interface, but I prefer using continuous forms then having to add the extra step of opening a form to add a new record.
also like grouping my data and using subforms creates a visual grouping so the user can identify different groups of data.
robtorch
I agree, it is better to group the data, and I'll add back some more borders. If I don't do that now then I can see how it will get messy later on.
o make the combo box for Region work, I had to insert a subform for tblCities and place the combo box on that. That seems to work ok, however, how should I place the combo boxes for Counties and Postal code? Neither of these have foreign keys in the Address table, they are related differently. However, they are still part of a person's address, so I would like to have them in the general area on that form as the street address and city.
I've attached my relationships window to make it easier to see how these 2 are related. Does the combo box for Postal Code need to go on a tblCities subform? What about Counties, how should that be placed?
ScottGem
Since Region is related to City and County to Country, not directly to Address, then you have to use subforms to select those. However, those subforms can be made to appear part of the address form.
robtorch
Things are working better now, I seem to have gotten the hang of some of this. So far combo boxes work if they source foreign keys. For example, tblAddresses has 4 foreign keys, AddressTypeID, ContinentID, CountryID, and CityID. So in the Addresses subform, it was easy enough to place 4 combo boxes and assign them to the foreign key values in tblAddresses.
blAddresses
AddressID (PK)
PersonID (FK)
AddressLine1
AddressLine2
AddressTypeID (FK)
ContinentID (FK)
CountryID (FK)
CityID (FK)
But I am running into many other types of relationships between my tables that are not the same. I tried to make the combo box for County, and the only other table that tblCounties is related to is tblCountries, so I assume I would have to create a Countries subform, and then add a combo box to choose a county. But the realtionship isn't the same as it was for say creating a combo box for Continent on the Address subform. The wizard doesn't give me an option to link it up correctly. If I go through the wizard it displays correctly, but I can't change the county from the combo box at all, it's stuck. I don't know if this makes any sense at all, but even looking at the relationships I can see they are different:
tblAddresses to tblCountries is many to 1, with a FK in the Addresses table. While tblCountries to tblCounties is 1 to many, and the FK iin the County table.
I just can't make it work right, I'm missing something. Any suggestions? I attached another relationships screenshot. Thanks for your help in advance!
ScottGem
Frankly I think you've gone a bit overboard with normalization here. County is a prime example. County is an attribute of the address. The reason its linked to country is so you can filter counties for the country, but you really don't need a relation or referentail integrity. County is a lookup not a data table. I think you should be using FKs to all those lookups from the address table.
robtorch
It would appear that way, and I think I'm going to re-work the tables to do that and at least try it out. 2 really quick questions though:
) What about Postal Code? The way it's placed within the Cities table I can't figure out how to make that combo box work correctly either. When I create the combo box, it links the zip code to the city, which isn't right. A city will have numerous zip codes, not just one, but it won't let me change it. Should I break this out and make it relate directly to Addresses as well?
2) I liked your idea about setting up phone numbers using continuous form - it worked great! But is there a way to make the forms line up horizontally instead of vertically, so I can scroll left to right instead of up and down?
Thanks again
ScottGem
1) There are two ways to look at this; Either you are using a lookup to insure data integrirty or using one to speed up data entry. If you are using it to insure integrity, then what you want to do is filter the Postal codes offered by the combo, to only those for the selected City. If you want to speed up data entry, then have them select the Postal code first, and have it fill in the City, County, etc. based on the selected code.
) No, records scroll vertically.
robtorch
Thanks Scott, I think I'm gonna take the angle of data integrity. While the other way may take a little longer, we're talking milliseconds, nothing significant. I appreciate your comments, throughout this post. I think I'm all set on this end, many thanks to you and to Steve for the help.
ScottGem
glad to assist
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.