Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Populate text box based on combobox selection.

Posted on 2003-12-09
10
Medium Priority
?
341 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 100 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

715 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