Solved

Populate text box based on combobox selection.

Posted on 2003-12-09
10
327 Views
Last Modified: 2012-05-04
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

0
Comment
Question by:williery10
10 Comments
 
LVL 8

Expert Comment

by:NowaY
ID: 9905364
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
 
LVL 5

Expert Comment

by:DarkMor
ID: 9905412
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
 

Author Comment

by:williery10
ID: 9905629
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 5

Expert Comment

by:DarkMor
ID: 9905721
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
 

Author Comment

by:williery10
ID: 9906535
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
 
LVL 5

Expert Comment

by:DarkMor
ID: 9906606
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
 

Author Comment

by:williery10
ID: 9911219
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
 

Accepted Solution

by:
Rob-QLD earned 25 total points
ID: 9919359
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
 
LVL 32

Expert Comment

by:jadedata
ID: 10271829
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question