Automatically Update Form Fields from Combo Box Selection

[nikik]

I am using Access 2007 and would like my subform to fill in the remaining text boxes in my form once I choose an item from the drop down combo box. Meaning if my table has "Fred, accounting, 555-5555". In the form, I'd like to be able to choose Fred from the drop down box and his department and phone number fill in automatically.

I've tried Me!TextBoxName=Me!ComboBoxName.Column(#) to no avail.

It seems like this would be easy to do and it DOES fill in the text boxes the very first time I choose something, but then the next thing I choose, it sends the data already there back to my table and screws up all my info. Perhaps I should be using a report or filter?

Any help would be greatly appreciated - Thanks!

 

[Montez659]

Nikik,

It sounds like the text boxes are bound and thus, if the information will change in your form, it will change the info in the db. If you are just wanting to select a name and fill in the rest of the info, you could try to populate the phone number box and department via a DLookup in VBA. It might not be the best way to do it, but this is how I have done it to get around the problem you are having. Google DLookup and you will get plenty of info on how to do that.

 

[alansidman]

Is this what you had in mind?

http://www.datapigtechnologies.com/flashfiles/recordsetclone.html

Alan

 

[nikik]

It is, but I'm in Access 2007 and it's giving me a bunch of error messages. I altered it a bit since it wouldn't let me in DAO.

Private Sub Food_Name_AfterUpdate()
Dim MyName As Recordset
Set MyName = Me.RecordsetClone
Set MyName.FindFirst = "[FoodName] = '" & Me.qFoodName & "'"
If MyName.NoMatch Then
MsgBox "Name Not Found"
Else
Me.Bookmark = MyName.Bookmark

End If
End Sub

Also, I have about 10 things I'd like to fill, not just the name & phone.

Where's the emoticon with tears and hair pulling? That's the one for me...

 

[alansidman]

From your original post, it sounded like you were attempting to find one field in a record. Now it sounds like you are looking to filter one record out and have multiple fields presented to you. If this is the case, then perhaps the following may be more applicable.

http://www.datapigtechnologies.com/flashfiles/buildcustomfilter.html

Alan

 

[nikik]

There must be some major issues btwn 2003 & 2007, because I followed EXACTLY what this guy said and it still didn't work. Perhaps I am the one with issues...

Thanks so much for your replies - I'll keep trying, but if you find a simple 2007 solution, please post.

Thanks again!

 

[alansidman]

I run 2007 and use these tutorials and they work fine for me. What types of issues are you encountering.

Alan

 

[Norie]

nikik

Are you saying you aren't allowed to use DAO in your database?

You should be able to whatever version it is.

Perhaps you need to fully qualify objects like Recordset to make sure you are using the DAO one.

Something like this perhaps.

Dim MyName DAO.Recordset

 

[boblarson]

It is, but I'm in Access 2007 and it's giving me a bunch of error messages. I altered it a bit since it wouldn't let me in DAO.

DAO works perfectly fine in Access 2007. However, you have to realize that it isn't named DAO in Access 2007 if you are in an ACCDB file. You would be looking for the

Microsoft Office 12 Access Database Engine Object Library

for which to reference.

 

[nikik]

When I try to select Microsoft DOA 3.6 Object Library it says:

"Name conflicts with existing module, project or object library"

I'm not sure why the second mulitiple lookup option isn't working. Everything is going along fine and then at the end when I change my drop down selection nothing happens.