Solved

Populate text box based on combobox selection.

Posted on 2003-12-09
10
333 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

734 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