Assigning a value to an unbound textbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MS Access Newbie - 12 Jan 2007 03:30 GMT

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!

Reply to this Message

Dirk Goldgar - 12 Jan 2007 03:47 GMT

> 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
[quoted text clipped - 23 lines]
>
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

MS Access Newbie - 12 Jan 2007 04:19 GMT

> > 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
[quoted text clipped - 45 lines]
> the same fields, you could just set the ControlSource properties in the
> form's Design View, and not change them.

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!

Reply to this Message

Dirk Goldgar - 12 Jan 2007 04:52 GMT

> 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
[quoted text clipped - 5 lines]
> 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 -----

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message