Auto Population

[jbento]

All,
I have a table with the following fields: id (autonumber field), name, position, & phone.

I have a query is used as a value list for the name field.

I have a form that it is associated with that table. I have an unbound text field that is associated with the query for the values for the name field.

I also have code that if the name is not in the table, it will prompt the user to either add or not add the new value.

I have hardcode on the AfterUpdate event for the unbound field (poc_name), to automatically populate the name, position, and phone fields.

The code is as follows:

Private Sub poc_name_AfterUpdate()
If poc_name = "Doe, John" Then
name = "Doe, John"
position = "Supervisor"
phone = "777-9311"
End If

I would like to not have to do that for everybody that gets added to the table. I would like to be able to if someone gets added to the table, it would automatically fill in the name, position, and phone fields.

For example if I use the pulldown that has several names in it, and I chose one which is Bell, Sylvia, I would like for it to automatically fill in the name, position, & phone fields without me having to go in and hardcode like I have above.

Is there a easy way to do this?

Any help will be much appreciated?

Thanks in advance,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[yamafopa]

If you already have a table (let's call it 'tblNames') and that table already has a record that looks like...

Name Position Phone
Bell, Sylvia Sales 111-1111

Why would you want to re-add this information. The idea is to put it in once, uniquely identify it (perhaps using your autonumber) then just reference the records parts using the UID. Unless you are re-adding to tblNames. If this is so, I think you may have design issues...

As an aside, you can include the other fields of the Name record in your combo box. Then when you want to add the Name, etc, you can use the Column Property of the combo.

HTH

yamafopa!

 

[jbento]

Let me explain further:

I have a combo which has the following values:

Bell, Sylvia
Jones, Don
Smith, Bob

Sylvia Bell's position is Supervisor and her phone is 111-1111

Don Jones' position is Electrician and his phone is 111-1112

Bob Smith's position is Technician and his phone is 111-1113

If I were to use the drop down, I am going to choose Jones, Don

Now when I choose Jones, Don, AfterUpdate I want the position and phone field to automatically populate with Electrician in the position field and 111-1112 in the phone field.

Did that explain it a little better of what I am trying to do?

Do you know of an easy way to do this?

Please help. Thanks so much.

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[Eupher]

Jerome, yamafopa has it right. Include the position and phone fields in the row source (query grid) for the combo and adjust the column count property accordingly - I presume the id field is already there, and is the bound column, and is hidden. If you don't want the position and phone number to display in the drop-down, just set the column widths for those columns to 0. Then you can refer to them using column notation. So:

1) Add position and phone fields to the combo's row source
2) Change the combo's Column Count property from 2 to 4
3) Set the Column Widths property for the 3rd and 4th columns to 0 - so the field will end up looking something like: 0";1";0";0"
4) Then in the event procedure for the After Update event, put in code that looks like this:

Me![name] = Me![poc_name].Column(1)
Me![position] = Me![poc_name].Column(2)
Me![phone] = Me![poc_name].Column(3)

Remember that column notation is zero-based, so the first column is column 0, the second is column 1, etc.

HTH...

Ken S.

 

[Eupher]

p.s. yamafopa is also right that you should not be re-writing the data to these fields - unless they are unbound fields, in which case you aren't really writing any data, you're just displaying it on the form. If the fields are bound, then your combo is setup improperly, and should be removed and replaced with a new combo using the combo wizard, and selecting "Find a record on my form based on the selection...".

Ken S.

 

[jbento]

All,
I will try Eupher's example tomorrow at work, and I will let you all know how it turns out.

Thank you all so much for responding. Have a good night!!!

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!