Auto Populate fields (B & C) from a Lookup query in field A

[karl05]

Hi

In a form, I want to autopopulate two fields of contact details after the user selects a contact name from the another field using a lookup query. For example, the user selects the contact name in field A. Using the afterUpdate feature of this field I want to automatically populate two other fields (phone number, email address).

The query looks like this:-

SELECT CSContactDetails.CSContactName, CSContactDetails.CSContactNumber, CSContactDetails.CSContactEmail
FROM CSContactDetails
ORDER BY CSContactDetails.CSContactName;

And my current vba code looks like this:-

Private Sub CSContactName_AfterUpdate()
    Me!CSContactNumber = Me![CSContactName].Column(1)
    Me!CSContactEmail = Me![CSContactName].Column(2)
End Sub

The phone number works fine but for some reason the email will not work. Can someone help me fix this or advise on easier way to achieve my goal. Help is greatly appreciated, thanks.

 

[workmad3]

not sure why its not working, but heres a slightly nicer way of doing your query

SELECT c.CSContactName, c.CSContactNumber, c.CSContactEmail
FROM CSContactDetails c
ORDER BY c.CSContactName;

Also try refering to the fields by name rather than number (not sure if it will work like this so just kind of taking a shot in the dark), i.e.

Me!CSContactNumber = Me![CSContactName].Column("c.CSContactNumber")
Me!CSContactEmail = Me![CSContactName].Column("c.CSContactEMail")

 

[karl05]

reply

Thanks workmad3 but that didnt fix the problem. I rearranged my vba code a little to get the column count returned by the query (as below). The column count query returns 1?? But when I run the query normally in access it returns all 3! This is very weird

 

[karl05]

Fixed it but am not happy with the fix. The Column Count property of the Name field on the form needed to be 2 for the email address to be picked up.
Now when the user selects the drop down box on the name field it shows both name and number columns. Not great but at least I can move on