Creating a Form To Update a Database

[R Tanner]

Hi,

If I have one child table with a relationship between 7 parent tables
and I want to use a form to add new records to my child table, would I
want to have my form linked to a query or would I want to have my form
linked to the child table with individual fields from my parent tables
using a lookup function or something?

 

[John W. Vinson]

Hi,

If I have one child table with a relationship between 7 parent tables
and I want to use a form to add new records to my child table, would I
want to have my form linked to a query or would I want to have my form
linked to the child table with individual fields from my parent tables
using a lookup function or something?

If the seven tables are "lookup tables" (as I gather from previous
discussions) you would use a simple form based on the "child" table with seven
combo boxes on it, each based on one of the lookup tables.

 

[R Tanner]

If the seven tables are "lookup tables" (as I gather from previous
discussions) you would use a simple form based on the "child" table with seven
combo boxes on it, each based on one of the lookup tables.

This is what I read in a book that it seems I should do...

Have a combo box that looks up the values from the table, both the ID
and the actual value, so 2 columns:

Column1 Column2
1 Client Misunderstanding
2 System Error

Then make the width of column1 0 but have that column entered in the
child table. It seems like it is a work around and I don't know if it
works yet, but that's what I'm doing..

And maybe this is a big problem, but I'm not sure what you mean by
lookup tables. I no longer have any lookup columns in my tables. I
changed that. I used an SQL statement to identify what my foreign
keys were so I didn't have to...

 

[John W. Vinson]

This is what I read in a book that it seems I should do...

Have a combo box that looks up the values from the table, both the ID
and the actual value, so 2 columns:

Column1 Column2
1 Client Misunderstanding
2 System Error

That's good. That's exactly how a lookup table is ordinarily designed.

Then make the width of column1 0 but have that column entered in the
child table. It seems like it is a work around and I don't know if it
works yet, but that's what I'm doing..

It's a universal work around used in every Access database I've ever worked
with. In other words, it WILL work and you're doing it right.

And maybe this is a big problem, but I'm not sure what you mean by
lookup tables. I no longer have any lookup columns in my tables. I
changed that. I used an SQL statement to identify what my foreign
keys were so I didn't have to...

Sorry... should have been clearer. This is a lookup TABLE, used for looking up
data using a combo box on a form, and storing a foreign key into your table;
your main table would have a 1 in the field ErrorReason (or whatever the combo
is bound to), so when you look at the table datasheet it will be pretty much
incomprehensible (unless you've memorized a whole lot of numeric codes). But
that 1 will be a link to the table you display, so that on the Form or on your
Report, you will see "Client Misunderstanding".

You're now firmly on the right track... keep it up, and don't hesitate to post
back if you have trouble!

 

[R Tanner]

That's good. That's exactly how a lookup table is ordinarily designed.


It's a universal work around used in every Access database I've ever worked
with. In other words, it WILL work and you're doing it right.


Sorry... should have been clearer. This is a lookup TABLE, used for looking up
data using a combo box on a form, and storing a foreign key into your table;
your main table would have a 1 in the field ErrorReason (or whatever the combo
is bound to), so when you look at the table datasheet it will be pretty much
incomprehensible (unless you've memorized a whole lot of numeric codes). But
that 1 will be a link to the table you display, so that on the Form or onyour
Report, you will see "Client Misunderstanding".

You're now firmly on the right track... keep it up, and don't hesitate topost
back if you have trouble!

Hey John I am actually having trouble a little. I have made my way a
little further down the road, but have managed to get stuck. I have a
form with one combo box on it and a subform. The form is bound to a
parent table, Trade_Specialists, the combo box looks up the primary
key and value from this same table. The subform is based on a query
called Recent_Tickets which looks up 7 columns from my child table,
called Tickets, and 7 columns from 7 parent tables. In the subform
properties I have the following:

Link Master Fields: ID
Link Child Fields: TradeSpecialist_ID

The master field (ID), I am assuming, is the primary key from my
parent table Trade_Specialists while the TradeSpecialist_ID I know is
the foreign key in my child table. I want to add another combo box
here that would look up values from a different table and further
filter out records. This other parent table I want to use is called
System_Adjustments. I want to put a combo box on my form called
System Adjustments and further filter out records in my subform. When
I try this however, it does not work. I am assuming it is because my
form is bound to my parent table Trade_Specialists. I tried to make
it bound to my query, but that wasn't cutting it either. Any
suggestions?

Thanks John.