Combining several fields from different tables in one form

[sueviolet]

Two questions:

I have read posts where users refer to using 'codes" in forms - what are they talking about and how do you use them (i am just using form wizard in access 97)

I am trying to create a form that contains fields from different tables (about 4 or 5 tables) - i am running into problems. how are tables and/or linked together in forms? Form wizard doesn't ask me about subforms, though the fields i select are from different tables.

How do i create a form with fields from several different tables?

Please help - I have been reading about creating forms - all that i have read so far is either too basic or to complicated for what i need to do.

Let me know if you want me to send you more info on what i an trying to do

thanks for you time in reading this

sue

 

[Alexandre]

Sue,
The answer depends much on how are your tables related, and whether you want your form to be updatable or not (read only).
Depending on this, the three main solutions are to base your form on a query gathering the info from your various tables; And/Or use subforms; And/or make separate forms (!). It is not always a a good idea to try to put 'all' data on a same for for a variety of reasons (slowness, complication for setting up and using the forms...)
Tell more about your struture and what info you want to have and use on your form.

 

[Chaotix]

I'm in the same boat

I'm just learning Access as well, and am having difficulties creating a form that displays linked table data. Any pointers? I have a main table, that has relationships with 3 other tables. I have attached a screenshot. I keep getting #Name? on my Agencies and Brochure entries. What I want it to do is have a dropdown box display a list of agencies, and the users can select what agency this particular program belongs to (same with brochures). I have the subform working fine, for the services they provides, but am confused. thanks for any help
~~Chaotix
EDIT: please see this post for more screenshots, and info on my problem. I'd really like to get to know this stuff
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30538

 

[sueviolet]

More info for alex

I figured out how to base a form on a query (which has fields from different tables) - i got it to work once or twice - but sometimes form wizard doesn't ask about subforms - and when i open the form it is blank..any advice on setting up queries?

anyways, back to what i want on my form: the following is a list of how my tables relate to one another


Table 1:


MAIN_ID


ABLE 2:


FISH_ID
MAIN-ID

TABLE 3:


IND_FISH_ID
FISH_ID

TABLE 4:


FISH_ID
ADULT_ID



I would like to have one or two fields (from table 4,3, and 1) ..what is the best way to do this?

Also, why doesn't form wizard give me the subfrom options when i select 2 or 3 tables?

Can you add data for the same record when using separate forms for data entr?

if you create several separate forms, how do you combine them into one form?

I know, so may questions!

Thanks for your help alex

sue

 

[sueviolet]

one more thing...

i need my forums for data entry

thanks

 

[Alexandre]

Sue,
I have concerns about the strucure you provided.
However your field names are not explicit enough for me to make a guess and propose changes. I suggest that you explain what is the purpose of your db, and what are the data.

As an example, tables with only an ID like your table 1 are likely to be a design mistake; as well as table with only two ID like your Table 2...

 

[sueviolet]

alex

the previous post didn't include the fields that were in the tables - i was just trying to illustrate how the tables related to one another.


I am trying to create a form (s) where I can enter data from fisheries-related paper records - the problems is is that the records contain data that relate to fields in different tables.( it would be so nice if the records perfectly matched ONE table!) For example, the records i would like to enter are concerned with: origin, field work date, location, and # of fish captured.

The fields where these records belong are in different tables. Some of the tables are related - for example data source and location fields are in related tables, as are # of fish captured and origin. but data source is not related to origin.

i am working with a db that has MANY tables and it has just been given to me, a newbie of all people, to create forms for data entry

any suggestion on going about this would be greatly appeciated - how should i approach this problem?

when i try to create a query to base my form on containing all the fields i require - query comes out blank...what is happening?


need help!

 

[doulostheou]

You said you wanted to link tables 1,3,&4. From what you supplied, I would guess the problem is that there is no relationship between some of the tables. You cannot link all the fields together into one query if there is no common denominator between them. However, if all you are concerned with is that you are able to add new data to the various tables, I can think of an awkward solution. You can use subforms without defining a relationship. Your main form could just house subforms that contain data from the fields that do have a valid relationship. The user would then have to make new entries in each of the subforms. You might run into some weird side effects. I seem to recall that Access doesn't allow you to tab outside the subform once entered.