Problems Dealing with Forms

[MichaelTaylor]

I apologize for this, however. . . I am trying to put together an access database, and want to populate one table/form with data from another table/form. Table 1 has an event, described in part by a drop-down from Table 2. Now, Table 2 contains additional info (two lots of hyperlinks) which are associated with each individual event. How do I subsequently populate other fields in Table 1 with the appropriate hyperlinks to the event already chosen from Table 2.

Strikes me either a) this should be easy or b) I've made a category error. I've tried working through the 'relationships' bit of the Access help videos, but they haven't helped much.

[patjones]

In order for us help you, I think we need first to clarify the structure of your database. The first thing is to make a distinction between tables and forms. They are two different things. The table stores the data, and the form displays it.

It sounds to me as though table 2 is what we call a "look-up" table. This is a table with a set of pre-defined records that might populate a combo box or list box on your form, thus allowing you to restrict what the user can select when entering a new record for table 1, which is where user entered data is stored.

In your case, it sounds like you need to populate a combo box on your form using table 2 as a recordsource, and when the user makes a selection from the combo box, that selection becomes part of the record that will be saved in table 1.

Does this reasonably describe your situation?

Pat

[MichaelTaylor]

You know, it sounds suspiciously as if you've got it there. Table 2 did indeed start life as a lookup for Table 1, but then it sort of expanded its fields to incorporate other relevant data, which, as you say, I now want to populate a form for Table 1. Thanks for getting me this far. I'll let you know how I manage to mess it up in due course!

[patjones]

That's no problem; let us know how it goes.

I should add that when you use a look-up table and combo box in this manner, it's commonplace to simply drop the bound value into the stored record (which in your case will go in table 1). What do I mean by this?

Suppose I build a form to track a vehicle fleet. I might have a look-up table for vehicle make (Ford, Honda, Nissan, etc.) and base a combo box upon that table, which the user can then select from.

Now, you can store the actual name of the vehicle make in your record in table 1, but usually what we do is to associate each entry in the look-up table with a code - and when a selection is made from the combo box we then just store the code for that selection in the entered record for table 1.

So perhaps our look-up table is something like:

231 Honda
035 Nissan
542 VW

Then, when the user selects "Nissan" in the form while entering a record, just "035" will get stored in that record. This is one of the basic principles of relational database design: avoid duplication of information. In this case, the vehicle makes get stored just once in the look-up table, and get coded everywhere else in the database.

Pat