Autofilling Textboxes with Table data based on a Combobox. (1 Viewer)

[Ross Hurrell]

I am creating an Access program for logging in Parcels that are delivered and I am having some problems.

I have a Table called 'Contacts' and within this there are 4 Columns: 'Names', 'Departments', 'Extensions' and 'Emails'.

I also have a Form and there is 1 Combobox and 3 Textboxes relating to this Table: 'Addressed To', 'Department', 'Ext Number' & 'Email'. (see http://c0ld.homeip.net/parcel_log.jpg for details).

What I would like to do is select the Name from the Combobox (Addressed To) and the have the other Textboxes (Department, Ext Number & Email) get auto-filled with the corresponding data that’s within the Table for that Name.

If anyone would like the .mdb for this, please PM me.

Thanks for your help in advance.
Ross Hurrell

 

[gemma-the-husky]

in the combo box afterupdate you can

textbox1 = mycombobox.column(1) etc to get at the column info

note that in a cbobox, the first column is column(0). note also that the column ordinals follow the underlying query, even if the columns are hidden, because their width is 0

 

[rburna904]

You can also bind the text box to the column of the cboBox, this is only good if you do not plan on letting the user update the fields that get populated.

 

[Ross Hurrell]

in the combo box afterupdate you can

textbox1 = mycombobox.column(1) etc to get at the column info

note that in a cbobox, the first column is column(0). note also that the column ordinals follow the underlying query, even if the columns are hidden, because their width is 0

Thanks for your help, I tried this and i get the error message:

"The macro (or its macro group) doest exist, or is new and hasnt been saved"
Click here: http://c0ld.homeip.net/error.jpg

Sorry if im doing something totally stupid, all that I know is self-tault.

 

[ansentry]

Ross,

Have a look at the attached sample, it will do what you want.

Have a look in the tblData and you will see that only the key is stored for the data (fkCustomerID).

Have a look at the query qryDataDetail and you will see how the foreign key link is used to turn the key back into data. You can also see the result in frmDataDetail.

 

[statsman]

With the form in design view, right click the combo/list box and select
Build Event, then Code Builder.

Select BeforeUpdate from the top right corner.

Enter the following code between the 2 existing lines.

Private Sub Combo55_BeforeUpdate(Cancel As Integer)

[fieldname] = ([Combo55],#)

End Sub

The [fieldname] is the name of the field you want to copy the data to.
Combo55 is the number of the combo box you're using.
The # refers to the column in the combo box. The columns start with number 0 (zero) in the left most column and increases by 1 for each column you move to the right. The third column from the left would therefore be column 2.

Each column you wish to copy when you select a record in the combo box needs its own line of code.

When you created the combo box using the wizard, you had the option of saving one of the columns into a field at that time. If you did this, you don't have to use the code to save that column, it’s already done.

 

[ansentry]

Statsman,

If you use the method in my sample you don't need code.

Maybe I'm wrong but I though that linking tables with keys was the way to go.

Someone may be able to enlighten me if I am wrong.

 

[Ross Hurrell]

Many thanks to everyone, I got it working.