Populate text box based on combobox selection.

Hi there,
I've been playng with this a while now and am getting nowhere. Basically I want to create a form that shows a combo box that has all the clients name in it from the clients table. When the user selects a client, text boxes are filled with their details. That is the corresponding data in the same record. I have been playing with the wizard but cant seem to get a combo box configured to change to the text boxes to the corresponding records. I have been looking at the Northwind orders form which does exactly what I need but can't figure out how they did it. Can anyone help

Cheers inadvance
Williery

williery10Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NowaYCommented:
You could set a filter on the on change even of the combo box. Have the recordsource of the form as the query that pulls all the information you need. The filter action will limit the displayed text to whatever you selected in the combobox.
0
DarkMorCommented:
Willery10,

    A simple approach would be to move the combobox into the header make sure the bound column contains the ClientID Key (you should Hide this key in the step of the wizard so the user can select and dislay the client name).
Next on the form place the textbox controls BOUND to a recordset on the form that has in the WHERE statement
WHERE clientID = cbClient.

On the AfterUpdate of the combobox, requery the form Me.Requery to populate the records.  

I know this is confusing but look in the Event Properties of the combo in northwind to see the actual code.

D
0
williery10Author Commented:
Thanks for the reply guys, I think the problem is that the text boxes are not bound to the combo box so when a change is made and requery is called they dont change. I have been looking at the properties of the text boxes and cant find a property that binds them to the combo box. Maybe this has something to do with how I set up the form? I just used the wizard to map all the fields to textboxes and then when the form was made by the wizard I changed the ClientName text box to a combo box and then use a query in ROW SOURCE property of the combo box to populate it. So I guess the question is how do bind these text boxes to the combo box?

Any help would be great
Williery
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DarkMorCommented:
williery,

You need to bind the form with the data, then set each texbox's conreol source to the proper field.
(The wizard should have that information correct with the controlsource for the textboxes.)

The change needs to be made in the RecordSource of thew Form itself, change it from a link to the table to a query on the table.

SELECT *
FROM <<yourtablename>>
WHERE <<YourCLientIDFieldName>> = [Forms.<<yourFormName>>.<<theComboboxName>>]

Next on the afterupdate event use this code

Sub myComboBoxName_AfterUpdate()
   Me.Requery
End Sub

This sould solve the issue

D

0
williery10Author Commented:
Hi D,
thanks for the reply, think I'm making a mess of this somehow. Can I just run a couple of things past you.

Where should i put the

Me.RecordSource= "SELECT *
FROM <<yourtablename>>
WHERE <<YourCLientIDFieldName>> = [Forms.<<yourFormName>>.<<theComboboxName>>]
"

I've been putting it in the "Sub myComboBoxName_AfterUpdate()" but an getting funny effects even with a query static values.

Also have been having a terrible time trying to access
[Forms.clientForm.cmbClientName]

it keeps telling me it can't find it. When I type in
"Forms." in the code only four options come up (Application, Count, Item and Parent). I tried "Form." and a lot of options came up but none relating to my form name or any of its components.

Any thoughts would be most welcome as I have tried it all I think of.

Cheers
Williery(quite the novice)
0
DarkMorCommented:
Will,
     Ok first click hmm..  outside the form, in the GREY area.  Here you can see the properties box says FORM, on the data tab under recordsource use this format

SELECT * FROM <<yourtablename>> WHERE <<yourtablename.yourfieldname>> = Forms!clientform!cmbClientName


Please notice use ! not . in the Forms section.  If you want email the DB to nbrozik@yahoo.com and I will look at it for you.

D
0
williery10Author Commented:
Hi D
Thanks for the offer, I tried to send you my db but your account is full. The problem is nearly solved but still not right.
Cheers
WIlliery
0
Rob-QLDCommented:
I think this is probably what you want to do, and it took me a reasonable amount of time to tidy it down to two lines 8-)

This is a snippet of code that gets triggered by a ComboBox - The data source for the combobox is a list of part numbers that gets preloaded. The user types in as much of the part number as they want to get close to it, then clicks or pushes Enter.  That then triggers an AfterUpdate, which I then have this in:

Dim rSet as Recordset

sql = "SELECT Description, Manufacturer, Wholesaler, Buy, Sell, Margin FROM Base WHERE PartNbr = """ & PartNo.Value & """ ;"
Set rSet = CurrentDb.OpenRecordSet(sql, dbOpenDynaset)
 TxtDesc.Value = rSet.Fields(0)
 TxtManu.Value = rSet.Fields(1)
 TxtWhole.Value = rSet.Fields(2)

The Txt{Desc|Manu|Whole} are just boring textboxes, that are updated from the sql query.

Note to the unwary - Access is *very* picky about spaces. I spent 2 hours trying to figure out why it was erroring when I put the WHERE clause in there - that's because you *have* to have a space after the closing statement and before the semicolon, otherwise it errors with something like wrong number of arguements. Lovely.

Something else that was mentioned that may or may not have an effect is to ensure that you have DAO as well as ADO turned on - in the VB Script editor, Tools then References, then turn on Microsoft Data Access Objects (whatever version you want).

Hope that helps.

--Rob

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jadedataMS Access Systems CreatorCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: Rob-QLD {http:#9919359}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

jadedata
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.