Solved

Populate text box based on combobox selection.

Posted on 2003-12-09
10
310 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now