combo boxes

[Paolo]

I would like some help on the use of combo boxes.
I have created a form and named it Customers. This form's record
source is a table, also named Customer.
I would now like to add an unbound combo box, name it cbosearch, which
would return a selected record in a textbox, which I will name
txtresult.

The cbosearch record source is another table named AGENCIES which has
two fields: TOWN and ADDRESS.

I would that my cbosearch, will display only one field, TOWN (so that
I can see all Towns in the list) and on the OnChange event, display in
the txtresult, the second field: ADDRESS.

Can any one help me with the code?

[Mike Krous]

Can more than one address be in the same town? If so a simple textbox for
your result wont quite do the trick. If only 1 agency per town then this
would work

Note: This routine makes use of DAO (you will have to create a reference to
this library "Microsoft DAO 3.5". (maybe someone else could post an ADO
equivilent so you dont have to make a reference, my ADO is rusty yet and I
wouldnt want to miss-lead you)

put the following code behind a button that you place on your form, we'll
call it "Search" and we'll name our combo box "cboSearch"
<aircode>
Dim rs as Recordset
Dim strSQL As String

strSQL = ""SELECT ADDRESS FROM AGENCIES WHERE TOWN='" & cboSearch.Value
& "'"
Set rs = CurrentDB.OpenR ecordset(strSQL ,dbOpenSnapshot )
If rs.EOF And rs.BOF Then
Msgbox "No Matching Address for the selected town!",vbOkOnly ,"No
Match"
Else
txtresult = rs.Feilds("ADDR ESS")
End If
'lets be good programmers and clean up our mess after we make it :)
rs.Close
Set rs = Nothing
</aircode>

The idea here is to do it all in code since we are dealing with unbound
controls, first I create an sql query that will select the address from the
agencies table with a matching town to what the user selected. I placed a
little bit of error handling so you could see how to react for bad entries
(just in case your combo box is not set to "limit to list") if the .EOF and
..BOF flags are true then no records were returned. Otherwise we found a
result, we are _assuming_ one agency for one town. At that point simply
retrieve the value of the ADDRESS field and assign it to our txtresults
textbox. Finally, becuase we are dealing with recordsets, I close it and
free it from memory (I believe its better _not_ to get in the habbit of
letting Access clean up memory usage.) Thats it, we now have our results
for the user to see.

HTH

Mike Krous
"Paolo" <jp***@tin.it > wrote in message
news:9f******** *************** ***@posting.goo gle.com...

I would like some help on the use of combo boxes.
I have created a form and named it Customers. This form's record
source is a table, also named Customer.
I would now like to add an unbound combo box, name it cbosearch, which
would return a selected record in a textbox, which I will name
txtresult.

The cbosearch record source is another table named AGENCIES which has
two fields: TOWN and ADDRESS.

I would that my cbosearch, will display only one field, TOWN (so that
I can see all Towns in the list) and on the OnChange event, display in
the txtresult, the second field: ADDRESS.

Can any one help me with the code?