How to use a Combo Box to autopopulate text box fields?

[HarmonyGirl]

I would really appreciate an answer to my question, as I've spent 2 days looking in various Microsoft knowledge bases and sample databases to find an answer with no success.

I would like to use a combo box to populate some text fields on a single form (no subforms involved). All fields are in the same table, and the table contains records with the information in it.

I know I have to create an Event Procedure on the After Update property, but I cannot seem to get the syntax correct no matter what I try.

I've tried various solutions I found on this board, and I get too many debug errors to list here. If anyone would care to walk me through this, I would greatly appreciate your time.

Thanks very much,
Harmony

 

[chrismcbride]

I suspect that this solution is over-simplified, however here goes.

If you have a combo box that is bound to the UID field of a table, then it is a simple matter to use the RecordsetClone object and Bookmark property to navigate to the record that has been selected by the combo box.
Say that you are talking about an Invoice table and your table has an auto-number field called inv_no and a text field called inv_prefix.
Then you might have a recordSource for the combo box that looked like ...
SELECT inv_no, inv_prefix & "-" & inv_no AS Invoice FROM tblInvoice ORDER BY inv_no;

Then in the AfterUpdate of the combo box you could use...
Me.RecordsetClone.FindFirst "inv_no = " & me.ComboBoxName
Me.Bookmark = Me.RecordsetClone.Bookmark.

This assumes that the fields that you want to populate on the form will get their values from the same record within the table. I hope this is what you mean.
Chris

 

[HarmonyGirl]

Thanks for your prompt reply. Actually, I must confess that I do not fully understand your response.

However, I found a workaround that initially seems to deliver what I need. I used an Event Procedure on the After Update property, with this code:

Private Sub Init_Name_AfterUpdate()

'After selecting an Initiator in the Initiator combo box, populate Organization,
Location, EMail, and Phone fields

Me!Organization = Me!Init_Name.Column(2)
Me!Location = Me!Init_Name.Column(3)
Me!EMail = Me!Init_Name.Column(4)
Me!Phone = Me!Init_Name.Column(5)

End Sub