Combo Box Problem

[Sue]

I've building a form with a subform. The subform is based on a query that
draws info from several tables. Each of these tables relates to the others
using foreign keys.

For ease of reading the fields in the query, I didn't use the numeric values
(foreign fields) but rather the main "point" of the table, in this case the
name of a protocol. The query works fine, but the problem lies in
constructing the form using the query as the record source.

The wizard creates a subform that contains the right entry, but if data
entry is required, the user has to free-text in the name of the protocol on
the subform - the wizard uses a text box rather than a combo box. When I
create a combo box, It "reads" correctly until I try to enter data into the
subform or move to the next record in the main form - then I get a message
which says "The value you entered isn't valid for this fiels. For example,
you may have entered text in a numeric field or a number that is larger than
the FieldSize setting permits.

I've tried doing a variety of things on the original subform. I've blown up
the subform and started over. I've recreated the query. I've double-checked
everything I can think of. I would be endlessly grateful if someone could
help me with this. It's driving me nuts.

 

[Wayne-I-M]

Hi Sue

I think you may have misunderstood they way access deals with relational data.

Say you have a table (tblNames)

ID = 1
Name = John

ID = 2
Name = Jim

ID = 3
Name = John

In your post you say

For ease of reading the fields in the query, I didn't use the numeric values
(foreign fields) but rather the main "point" of the table, in this case the
name of a protocol.

This is where your problem is. You should bind the reord to the primary
field of the table not the "> name of a protocol." You can set the column
widths to allow this. So you could work with record 2 and "see" Jim
displayed for the user or record 3 you would see John.

Access can not see the difference between your records 1 and 3 if you only
enter John - so you need to use the primary field to differentitate between
the reocrds.