Assigning a value to an unbound textbox

[Guest]

I am trying to run a SQL query and apply the recordset to an unbound textbox.
I am doing this primarily because I want to run multiple queries on one form.

So far I have this down:

Set conn = CurrentProject.Connection

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = conn
.Source = "SELECT Client.client_name, Client.street_addr,
Client.city, Client.state, Client.zip, Client.main_phone, People.people_id
FROM Client INNER JOIN People ON Client.client_id = People.client_id WHERE
(((People.people_id)=" & Combo40.Value & "));"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

Set Me.Recordset = rst
rst.Close

How do I, for example, tell Access to display the client_name to
txtClient_name on the form?

Thanks in advance!

 

[Dirk Goldgar]

I am trying to run a SQL query and apply the recordset to an unbound
textbox. I am doing this primarily because I want to run multiple
queries on one form.

So far I have this down:

Set conn = CurrentProject.Connection

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = conn
.Source = "SELECT Client.client_name, Client.street_addr,
Client.city, Client.state, Client.zip, Client.main_phone,
People.people_id FROM Client INNER JOIN People ON Client.client_id =
People.client_id WHERE (((People.people_id)=" & Combo40.Value & "));"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

Set Me.Recordset = rst
rst.Close

How do I, for example, tell Access to display the client_name to
txtClient_name on the form?

Thanks in advance!

Wouldn't it be easier just to assign your SQL string to the form's
RecordSource property? As in ...

Me.RecordSource = _
"SELECT " & _
"Client.client_name, Client.street_addr, " & _
"Client.city, Client.state, Client.zip, " & _
"Client.main_phone, People.people_id " & _
"FROM Client INNER JOIN People " & _
"ON Client.client_id = People.client_id " & _
"WHERE People.people_id=" & Combo40.Value & ";"

If you will be selecting different fields in different queries, you'd
need to dynamically set the ControlSource properties of various controls
to the names of the fields you selected; e.g.,

Me!txtClient_name.ControlSource = "client_name"

However, if you're just selecting a different set of records, but with
the same fields, you could just set the ControlSource properties in the
form's Design View, and not change them.

 

[Guest]

Wouldn't it be easier just to assign your SQL string to the form's
RecordSource property? As in ...

Me.RecordSource = _
"SELECT " & _
"Client.client_name, Client.street_addr, " & _
"Client.city, Client.state, Client.zip, " & _
"Client.main_phone, People.people_id " & _
"FROM Client INNER JOIN People " & _
"ON Client.client_id = People.client_id " & _
"WHERE People.people_id=" & Combo40.Value & ";"

If you will be selecting different fields in different queries, you'd
need to dynamically set the ControlSource properties of various controls
to the names of the fields you selected; e.g.,

Me!txtClient_name.ControlSource = "client_name"

However, if you're just selecting a different set of records, but with
the same fields, you could just set the ControlSource properties in the
form's Design View, and not change them.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks for the quick reply.

I can't define the form's RecordSource because there are multiple queries
being run on the same form. If I write one query after the other (using
Me.RecordSource), only the results of the last query show up... I think this
is because the results are being overwritten by the earlier query.

I would hope there is an easy way to run a query, show the results in
textbox A B C, run another query, show the results in textbox D E F, etc
without having the first query results being overwritten.

Thanks again!

 

[Dirk Goldgar]

I can't define the form's RecordSource because there are multiple
queries being run on the same form. If I write one query after the
other (using Me.RecordSource), only the results of the last query
show up... I think this is because the results are being overwritten
by the earlier query.

I would hope there is an easy way to run a query, show the results in
textbox A B C, run another query, show the results in textbox D E F,
etc without having the first query results being overwritten.

If that's what you're trying to do, then I was misled by your original
code, which was setting the form's Recordset property to the recordset
you opened. I thought you wanted to dynamically bind the form. But I
think now that what you want is more like this:

'----- start of example (air) code -----
Dim rst As ADODB.Recordset

Set conn = CurrentProject.Connection

Set rst = New ADODB.Recordset
With rst

Set .ActiveConnection = CurrentProject.Connection

.Source = _
"SELECT " & _
"Client.client_name, Client.street_addr, " & _
"Client.city, Client.state, Client.zip, " & _
"Client.main_phone, People.people_id " & _
"FROM Client INNER JOIN People " & _
"ON Client.client_id = People.client_id " & _
"WHERE People.people_id=" & Combo40.Value & ";"

.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open

If Not .EOF Then
Me!Client_Name = !client_name
Me!Street_Addr = !street_addr
Me!City = !city
Me!State = !state
Me!Zip = !zip
Me!Main_Phone = !main_phone
End If

.Close
End With

set rst = Nothing

Set rst = New ADODB.Recordset
With rst

Set .ActiveConnection = CurrentProject.Connection

.Source = _
"SELECT SomeField FROM SomeTable " & _
"WHERE SomeCondition=True;"

.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open

If Not .EOF Then
Me!SomeField = !SomeField
End If

.Close
End With

set rst = Nothing
'----- end of example code -----

 

[Dirk Goldgar]

YES!! Thank you so much!

You're welcome.

One more question... how could I dynamically change the values of a
combo box depending on the value of another combo box.

For example:
Scenario 1 -> Select the value "Colors" from combo box A. Combo box B
should now have "Red", "Green", and "Yellow". The values Red, Green,
and Yellow are coming from a table in the database. So I guess the
OnClick event of combo box A should run a query pulling the values of
colors.

Scenario 2 -> Select the value "Fruits" from combo box A. Combo box B
should now have "Apple", "Orange", "Grape". Again, the fruits should
be coming from a table.

Thanks!

It depends on how your tables are set up. If the values for combo B's
rowsource, both Colors and Fruits in your example, are drawn from the
same table (but with some field that distinguishes the colors from the
fruits), then you would normally set the rowsource of B to a query that
selects from that table and uses a form-control reference to combo A as
a criterion in the WHERE clause. Then all you have to do is requery
combo B in the AfterUpdate event of combo A.

If, on the other hand, the selections of Colors and Fruits come from
different tables, then you'd have to rewrite the rowsource of combo B
completely in combo A's AfterUpdate event. That's not much harder.

The article at this link shows both methods:

http://www.mvps.org/access/forms/frm0028.htm
Forms: Limit content of combo/list boxes