Auto Pop text in box, from combobox click

[Mfouly]

Hey Folks,

I've found your forum through my searches and decided that you would be the right folks to answer my question. I'm building a database that includes client names, addresses, contact info, etc. Please also note that i'm using Access 2007(don't know if that makes a difference). Alot of my information is coming from excel sheets, so there has been alot of importing of info. I've made a form that will have users input information regarding calls that are being accepted. Now to minimize error as much as i can and make it efficient as i can, i've added a combo box with the names of the clients. What i would like to happen is as soon as one of the clients is choosen in the drop down i would like to have the address of the client populate in a text box in the same form to make things easier on the user. Any help would be appreciated.

Thanks in Advance.

 

[CameronM]

One popular way is to add the fields you want into the RowSource query of the combobox.

For example, your query might be "SELECT ID, cName, cAdd1, cAdd2 FROM tblCustomers"

Set the ColumnCount=4 (match the number of columns in your query)
Set the ColumnWidth=0;4;0;0; (set to zero for those you don't want the user to see)

On the combobox After_Update event, add the values to the textboxes

Example (remember the columns collection is zero based)

txtAdd1 = cboCustomer.Column(2)
txtAdd2 = cboCustomer.Column(3)

 

[Mfouly]

Hey there,
I've tried what you said and nothing populated in the address box. I just added in to the VB section. Also my query looks a little different then yours.

SELECT [Table1].[ID], [Table1].[Site] FROM Table1 ORDER BY [Site];

So, on the afterupdate event for my combo box this is what i had:

[Address]=[AddressLook].[Column](5)

I don't know what i'm doing wronge?????

 

[CameronM]

You only have 2 fields in your query, so column 5 does not have anything in. You want [Address]=[AddressLook].[Column](1) - which would return the Site field.

 

[Mfouly]

I've tried it with column (1) and what ended up happening is the name of the company came up which would be the same one selected from the list. So i tried column(2) and nothing happened???? Sorry about this, i just really want this to work. Let me know if you need more info.

 

[John Big Booty]

You only have 2 fields in your query, so column 5 does not have anything in. You want [Address]=[AddressLook].[Column](1) - which would return the Site field.

Columns in Combo boxes are numbered from zero up, so given that you only have two columns in your combo currently, you will have to modify the underlying query to select the additional column/s you wish to show in your text boxes.

 

[Mfouly]

Thank You very much, that worked. I've also managed to get the information in more then one box which is fantastic. I had one other question though (Don't know if i should start a new thread for this, and if i do please say so) I would like to have the form auto stamp the time and date of the entry and have that information also show on the save table. I already have the time and date showing up on the form using the "= Time()" and "=Date()". Any Suggestion are welcome, and thanks again for all the great help!

 

[John Big Booty]

Glad you got that sorted

To put a date and time on the entry there are probably a number of ways of doing this.

The simplest would just be to add a field to your table and format it as Date/Time, then in the field Default Value put Now(). This will give you a single field that has the time and the date when each record was saved. If you don't want these values in the one field you will need to add two fields one for date and one for time. The default value for the date field would be Date() and the default for time Time()

Hint: Do not call either of these new fields simply Date or Time. As both those word are reserved words and represent inbuilt Access functions, and to do so would cause no end of confusion and grief. Use something meaningful like DateIn or TimeOut.​

 

[missinglinq]

To answer your side question, yes, please always note in your threads that you're using Access 2007, especially if you're attaching your database fiel! Many things and ways of doing things changed with this version and it can make a big difference in the answers, and anyone not running 2007 would be wasting their time downloading a 2007 database as they can't run the new file format.