How do I make the DLookup function update fields with new selection

[Timsb]

I have been searching for 2 weeks on how to mkae this work.

I have a form with a dropdown list. In the afterupdate event I have the following code:

Private Sub BreederID_AfterUpdate()
BreederFirstName = DLookup("[BreederFirst]", "Breeder", "BreederID=" & BreederID)
BreederLastName = DLookup("[BreederLast]", "Breeder", "BreederID=" & BreederID)
BreederAddress = DLookup("[BreederAddress]", "Breeder", "BreederID=" & BreederID)
BreederCity = DLookup("[BreederCity]", "Breeder", "BreederID=" & BreederID)
BreederState = DLookup("[BreederState]", "Breeder", "BreederID=" & BreederID)
BreederZip = DLookup("BreederZip", "Breeder", "BreederID=" & BreederID)
BReederPhone = DLookup("BreederPhone", "Breeder", "BreederID=" & BreederID)
End Sub

The list is populating the fields for the contact information for each breeder on the form. It works fine, the problem is that if a different selection is made in the dropdown list, it doesn't update the fields. It keeps the original selection.

Is there something I am doing wrong? Can anyone help?

Thanks,
Tim

 

[MajP]

Seems like a lot of work.
Simply put a combo with breeder ID on the main form. put a subform on the main form bound to you a table or query with your information.
link MasterFields: [yourComboNameHere]
link childFields: BreederID

Change the combo, and the proper information appears in the subform. You can format the subform to look as if it is just fields on the main form.

 

[traingamer]

MajP beat me to it. The DLookups seem like overkill.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Timsb]

Thank you for your help. I am not sure of how to make your suggestion work. Do I put the code in the afterupdate event for the combo box? I tried and I just got a compiler error. I am new to access programming so I apologize if I don't understand what to do.

Tim

 

[MajP]

the beauty is that there is no code needed.

Make a main form and put your combo box on it. The main form does not have to be bound. build a subform and bind it to a table/query that has all of your other fields that you show.

When you put a subform control on a main form a wizard will probably come up, and you may need to cancel.

in the properties of the subform control
link MasterFields: [yourComboNameHere]
link childFields: BreederID

There is no code needed once you make this link. If you change the value of the combo box it will automatically filter the records of the subform to the linked value of the combo.