By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,152 Members | 1,742 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,152 IT Pros & Developers. It's quick & easy.

Basic Access user: Link field in one table to field in other?

P: n/a
Hi folks,

I have Access 97 and have set up four tables, each with a Primary Key
with a file name manually entered. For example, the four tables relate
to information stored in a paper file, on four subjects, the paper file
is indexed WM/01/05. This number, a unique identifier in the database,
is the primary key and the same content in each table.

Table 1 is called "Driver Data"
Table 2 is called "Vehicle Owner"
Table 3 is called "Vehicle Expenses"
Table 4 is called "Legal Costs"

The four tables have four corresponding Data Entry Forms, similarly

I have command buttons in each form, each button linking to the
appropriate three other forms.

The tables are all linked in the same Primary Key using the
Relationships tool, in order Table 1 to Table 4.

As I add to the database, these four stacks of cards don't behave in
the manner I would like. Once all four Forms have the same file number
manually entered, I would like the current file number to go to the
exact same file number when the command button is pressed, so that each
Form in the "chain" works seamlessly between all the records. Each
form should jump from WM/09/05 to WM/09/05 to WM/09/05 to WM/09/05
between forms.

Presently, when the database is first switched on, WM/01/05 comes to
the fore, which is fine. I can use the Search function to locate
WM/09/05, which is fine. However, my linear navigation design is
lacking something. It doesn't all "line up".

I'm a fairly basic user, so be gentle.... ;-)

How (if it's possible) could I get, in effect, the database equivalent
of a Bookmark in each form so that they navigate only between related
file numbers? Or is there a better way of doing this?

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
It's hard to tell exactly what's going on without actually being there,
but it does look like you may have your tables set up incorrectly by
simulating how your file cabinet stored the information. Databases work
very differently.

Check in your relationships. If your relationships are showing a 1-to-1
relationship rather than a 1-to-many relationship, you added an
unnecessary table. You can check this by seeing if there is a 1 or a
symbol for infinity next to the arrows connecting the tables. If the
relationship is only 1 to 1 then there really is no reason not to
simply combine the two tables.

If you don't want to change the setup of your tables, you could use a
query to combine the information from all of those tables into a single
record. By doing this you can display all of the information on a
single form. You can use a multitude of different design tools to help
you separate out different types of information (lines, tabs, linked
forms, sub forms etc).

If you don't want to mess around with queries or with redesigning
your table, you may wish to set up your linking buttons differently.
It's possible to have the button look up specific information on the
form that it is opening.

If I remember correctly, Access 97 has a wizard to walk you through how
to link to specific data in the next form. However, there are some
situations in which that wizard may not be able to cater to your
specific need. If that's the case, you will need to build code to do
it for you.

You can find detailed info on the code in the help file of Access 97.
Lookup the keyword 'openform'

The first example is the code with instructions on what to put where.
The second example shows what the line of code would look like in
practice. To insert the code, right click on the command button in
design time, select "build event", and insert the code between the
"private sub..." and "end sub" Type in everything (other than
the < and >)exactly as you see it, do not omit any ", ', [], or ,,,
you see.

DoCmd.OpenForm "<insert name of form to open here>", , , "[<insert name
of the field in the table or query that is the data source of the form
you are opening >] = '" & forms!<insert name of the referring form
here>!<insert name of the field in the referring form that holds the
information here> & "'"

DoCmd.OpenForm "frmtest", , , "[id] = '" & forms!frmmain!fldID & "'"

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.