Query To Textbox

[modest]

How do you go about setting a textbox on a form = a return value of a query?
Let's say I have a customer table.

Customer
Customer ID
Customer Name
Customer Phone
Customer Address

And on my form I have 2 text boxes (& a submit button).
One text box is a search field to look up the customer by ID.
When I click a button to look it up, how do I put [Customer].[Customer Name] in the other textbox?
Do I have to do something like:

qd = db.QueryDefs(queryName)
strSQL="SELECT [Customer Name] FROM Customer WHERE [Customer ID] = textbox1;"
qd.SQL = strSQL
textbox2 = docmd.openQuery (queryName,acViewNormal, acReadOnly)

because while that sounds good openQuery has no return value... it opens a query window..but doesn't return anything. So ARGH...I'm stuck.

 

[modest]

This was posted from a different forum by Kenny Alligood. Would anyone like to translate to DAO?

"This is the method that I use...

Dim cxn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

Set cxn = New ADODB.Connection
Set rst = New ADODB.Recordset

With cxn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"Absolute Path to your DB"
.Open
End With

strSQL = _
"SELECT Title " & _
"FROM Book " & _
"WHERE book_id=1;"

rst.Open strSQL, cxn, adOpenStatic, adLockOptimistic

MyForm.MtTextBox.Text = rst!Title

rst.Close
Set rst = Nothing
cxn.Close
Set cxn = Nothing

I used 'MyForm.MtTextBox.Text = rst!Title' because I have this code in a seperate module named RecordSets and call it from the Form_Load sub that use it. It is up to you where you put I just prefer my code modules to look cleaner. Be sure to included references to Microsoft ActiveX Data Objects 2.5 Library (or Later) and Microsoft Data Binding Collection VB 6.0 SP4 (or later.

Hope this helps......"

For anyone who may use this, please notice that this was in reference to a book and it's title. The premise of the answer is still the same. You may view the person's actual post here:
http://p2p.wrox.com/topic.asp?TOPIC_ID=1521

 

[gecko_1]

Try this one out, it should do what you are after. your version might work but just modify ID] = textbox1;" , to ID] = " & textbox1 & ";"
if Customer ID is a text field you will need to put ' ' in as in the example below

   Dim db As Database, rs As Recordset, sql as String
    Set db = CurrentDb

   sql = "SELECT [Customer Name] FROM Customer WHERE [Customer ID] = '" & textbox1.value & "';"
   set rs = db.OpenRecordset(sql)
   if not rs.EOF Then
     textbox2.value = rs.Fields("Customer Name")
   End If

  set rs = nothing
  db.close
  set db = nothing

 

[Pat Hartman]

If all you need is one field, DLookup() will work. You're doing a lot of work for nothing. The best way though is to join the lookup table to the main table and base the form on that query. Then you can select columns from both tables.
See this Sample db

 

[modest]

Thank you all for responding, when I went home I looked up the DAO.chm file and made a solution very similar to Gecko's. The worst part of all is that I have done this in a database some time ago --- how the memory fades.

Pat, I don't have time to look into your solution at the moment, but I assure you I will because I know you're very credible.


This is my objective (minus the non-significant fields):
1) I have a form with two input boxes that you input a city name into (an origin city and a destination city).
2) I have a table that the form references consisting of a city and zipcode information.
3) The zipcodes are used to look up information about from another table


So to put it in a picture:
Form: (enter cities and click a submit button)

To:____
|______|

From:__
|______|


Table 1:
City Zipcode
Miami 22220
Tampa 22220
Atlanta 12345
Balitimore 33322
Annapolis 33322


Table 2:
Zip1 Zip2 Description
22220 12345 Blah Blah Blah
22220 33322 22220 to 33322
33322 12345 --------------
33322 22220 adkfhasdlfdsjks


So if I entered Miami and Baltimore in the form i would get "22220 to 33322"
Same thing if I entered Tampa and Baltimore (or Annapolis)


------
No need for comment i already have the solution.
I was just posting what my objective was for future readers
that may have the same problem