Put the result of a query into textbox (1 Viewer)

[jpl458]

I have a data entry form that logs phone calls. I also have a zip code table with all the zip codes in the US. from the phone number textbox I can get the area code using:


Private Sub callingnumber_LostFocus()
Dim acode As Variant
acode = Mid(callingnumber, 1, 3)
Me.callorigin = acode

That puts the area code into a textbox. I want use the data in that textbox in a query to get additional; info into 2 other text boxes; Region and Description.

the query(luZipcode) in SQL is:

SELECT areacodetbl.Region, areacodetbl.Description
FROM areacodetbl
WHERE (((areacodetbl.Region)="210"));


DoCmd.OpenQuery "luZipcode", , acReadOnly

how do I get the results of that query into text boxes?(Region and Description)?

Thanks

 

[theDBguy]

You could try using DLookup().

 

[plog]

First, will you be storing [Region] and [Description] in a table other than areacodetbl? If so, that is incorrect, you shoudl not do that.

If you simply want to display those values on the form and not store them in a table other than areacodetbl, then you should use a DLookup for each of the text boxes:

MS Access: DLookup Function

This MSAccess tutorial explains how to use the Access DLookup function with syntax and examples. The Microsoft Access DLookup function returns a value from an Access table (or domain).

www.techonthenet.com

Give that link a read, try it with your data and if things don't work, post what you tried back here.

 

[Pat Hartman]

Use a combo for the area code. Include the other two columns in the RowSource of the combo.

Select AreaCode, Region, Description From YourTable, Order by AreaCode;

To reference the other fields, use an expression in the ControlSource.

=cboAreaCode.Column(1) '''' for Region
=cboAreaCode.Column(2) '''' for Description.

The fields of the RowSource are a zero based array. So, the first one, which is AreaCode is the default and is referenced as Me.cboAreaCode but you could use Me.cboAreaCode.Column(0). The second field is Region so that index is (1) and the third is Description which is referenced as (2)