Auto Lookup Query

[cbearden]

I'm having trouble with a form. I believe the Auto Lookup Query is the way to go...but I may be wrong.

Here's my Form and Subform
Form is based on a table called Warranty
SubForm is based on a table called Dealer
They are linked by Dealer# field.

The MainForm consists of a person's information such as:

Name
Account #
Cost1
Refund1
Cost2
Refund2
Dealer#
Comments

The subform consists of Dealer information. I have a relationship between the Dealer and Warranty Table. It is One-to-Many. One Dealer #(ID) can match several people.
Here is the controls on the subform:

Dealer#
Contact
Phone
Fax
LastUpdated

I'm trying to allow the user to type in a Dealer# and when they tab out of the control, it will auto-fill the rest of the fields(contact, phone, fax, lastupdated).

If anyone can help, I'd appreciate it.
Thanks

 

[Eupher]

Hi, cbearden,

Your form/subform arrangement is the reverse of the normal way of handling such things - usually the main form is on the one side of the one-to-many. This example illuminates one of the difficulties. Normally this situation can easily be handled by the combo wizard "Find a record in my form based on the selection in my combo". But in this case, what happens on the main form when you select a new Dealer in the subform?

Ken S.

 

[cbearden]

The reason for the arrangement is to be able to look at the accts, not the dealers and to be able to add new accts. With adding/editing accts, this way seems to be easier. Please give suggestions if you ahve any.

not sure what your asking...
1) If there is not a number, it is added to the combo box. But there again is another problem, if its not in the combo box, i need it to be added to the combo box as well as all the other fields in the subform to be updated in the table.

2) If I choose one that is in the combo box, it will allow me to select it but... a) It says its a duplicate value and etc etc. I'm guessing for the Dealer # control...I should use the one in the Warranty table and not the Dealer Table.
Warranty Table allows duplicates (many)
Dealer Table doesn't allow duplicates (one)
b) It does not fill in the other fields, it just chooses the one dealer #.

 

[cbearden]

I got the fields to be filled in when someone chooses a number from teh combo box. But when I enter a number in the combo box that is not in the list, it will not add it to the Dealer Table. Also, it will not add the new information for the fields(Contact, Phone, etc) to the Dealer Table either.

Any suggestions?
Thanks

 

[cbearden]

additional info:

The tables are Cancel and Deal.

Ex:

#: 120000
Contact: Bob
Phone: 123-456-7890
Updated: =Date()

I would need that to be updated in the Deal table.

I think I might know what the problem is but if I fix that, it screws me up some other way.

1) If i change the combo box to the one in the Deal table, I suppose it would add that to the Deal Table, BUT, since those numbers are not to be duplicated, it will give me a message saying its a duplicate value.

2)If I have the combo box from the Cancel table, it won't give me the duplicate value message but it won't add the info to the Deal table.

 

[PHV]

Have a look at the NotInList event procedure of the ComboBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cbearden]

Thanks, I'll take a look