DLookup seems to be the way to go, but....

[mpmoore]

OK Bear with me here, this could get hairy.
I have a form, TheBigForm. On TheBigForm I have a control box called DealerName. This control is tied to the table DealerList. In the table, DealerList, there is a field, DealerGroup. TheBigForm needs to know what the DealerGroup is for each DealerName. This information will be used to determine the BranchID and perform multiple calculations on TheBigForm. I have agonized on how to phrase this question so somebody reading it may know what is going on in my twisted head but I am sure there are more questions that need to be answered before I can be helped. Please just ask. Assistance will be most helpful.

 

[ImpMan]

This is a simple solution but maybe isn't quite what you want.

Place a list box or a combo box on the bigform
Set the row source for the list/combo to the tableDealerList
Make the bound column the dealer group.

Then when the user looks at the combo/list box they actually see only the dealer name bit, but the box returns the dealer group. Very simple.

btw, if columns are not required to be shown in the final pull down, set the width of that column to 0.

 

[slaman]

I am not by any means a professional, but I would try using DLookup.

DLookup("[DealerGroup]", "DealerList", "[DealerName] = Forms![TheBigForm]![DealerName]&quot

This will return the DealerGroup of any fields that match the criteria ("[DealerName] = Forms![TheBigForm]![DealerName]&quot I assume that DealerName is unique. Try that - good luck.

 

[mpmoore]

DLookup is the way to go. Where would be your suggestion to put the DLookup function. This form is basically data entry, loading directly on a table with no query. Should I create a control that returns the value of this function? We are on the right track. Thanks for your help.

 

[slaman]

Well, from what I understand - you enter in a DealerName in the text box... then you want to fill ANOTHER field with DealerGroup based on this input name? I would simply put the DLookup code in the Exit Event of the DealerName control box. That way you can trap any errors (Cancel = True) that occur and force the user to enter a valid DealerName. I hope this helps.