Related Fields in Drop-Downs

[Crispy]

I have a drop-down list in a form, which lists values from a field in a different table (Table1) to the one on which the form is based (Table2). The two tables are linked by a one (Table1) to many (Table2)relationship.

The problem I'm having is that when the user selects from the drop-down list, a message appears saying that "you can't add or change a record because a related record is required in Table1."

This is strange, because the list comes directly from Table1!!!

Any ideas???

 

[David R]

Make sure the Control Source is set to the field in Table2, and the Row Source to the field in Table1.

 

[Crispy]

I've checked this and both the row source and the control source are correctly set. This seems to be happening on all my forms which contain drop-downs based on information in different tables. I'm sure I have dome this before successfully but I can't see where I'm going wrong!!!

 

[David R]

Are you using the combo box wizard? I still do just because it saves on brainpower. Otherwise I'm not sure what's wrong...

The message is saying you have a one-to-many relationship between the two, and that you're not filling the one-side of it. Check your Master/Child fields, perhaps? Are you filling the subform before the main form?

[This message has been edited by David R (edited 05-29-2002).]

 

[Crispy]

I've got it sorted thanks. Yes, I was using the ComboBox Wizard, for some reason, when I selected the field I wanted shown in my ComboBox, it was also selecting the primary key field from that table, this was column 1 in my ComboBox but the properties had defaulted to zero width so I couldn't see it! Simply removing this field from the SQL statement for the ComboBox solved the problem. But many thanks for all your help.

Chris

 

[David R]

Be careful. It is supposed to store that Primary Key, that's the way it links to the other table. If you store the actual value itself then you will not be able to change/update it later, which will possibly lead to corruption of your data. (in the "it's inaccurate" sense, not the computer sense).

There's something else going on. Are you selecting the lookup table as where to look for the value, and the field on your data table to store it in? I've used hundreds of combo boxes and never gotten that problem using the wizard.

 

[Crispy]

Yep - I'm definitely selecting the correct tables - one thought though - I notice that the 'bound column' property defaults to 1. If the Primary Key column comes first in the SQL statement, then should the 'bound column' be 2, thus selecting the column containing the info?

Does this make sense? I've tried it this way and it seems to work...

 

[David R]

Nope, again, you're storing the "text" field, not the PK field, which is the efficient/proper way to do it.

Compact your database in an Access2000 format if possible and email it to me. I cannot visualize what's wrong without seeing this for myself.

 

[Crispy]

Sorry, I only have 97.

 

[David R]

Send it anyway, I can at least look at it and see if I can spot the problem. I'll post back here what I find out and you can make the changes in your Acc97 version.