strange form behavior

[m_elect]

I built 2 tables: Complex_table and ManagmentCompanies_table. Complex_table has these fields: complex, ManagementCompany and ManagementPhoneNumber. ManagmentCompanies_table has these fields: ManagementCompany and PhoneNumber. There are several rows in Complex_table that have a value for ManagementCompany but do not have a value for ManagementPhoneNumber.

I made a form for Complex_table that has a combo fox for ManagementCompany. Then I decided that it would be nice for the ManagementPhoneNumber field to get automatically populated when I select a managment company. So I made ManagementPhoneNumber a combo box with the following row source:

SELECT ManagementCompanies_table.PhoneNumber FROM ManagementCompanies_table WHERE ManagementCompanies_table.ManagementCompany=Forms!EnterComplex!ManagementCompany;

I also added this code for ManagementCompany for the after_update event:

Private Sub ManagementCompany_AfterUpdate()
Me.ManagementPhoneNumber = Null
Me.ManagementPhoneNumber.Requery
Me.ManagementPhoneNumber = Me.ManagementPhoneNumber.ItemData(0)
End Sub

This works great when I create new records. If, after creating a new record, I scroll back through old records without phone numbers the only value in the phone number combo box is the last phone number from the new record. What am I missing?

 

[pbaldy]

I'd use this technique, noting the fact that you probably shouldn't be saving the phone number in the complex table:

http://www.baldyweb.com/Autofill.htm

 

[m_elect]

Thanks, that works exactly how I want it to.

 

[pbaldy]

Happy to help, and welcome to the site by the way.