Setting text box contents from SQL statement

  • Thread starter Thread starter dockeradz
  • Start date Start date
D

dockeradz

I have a text box and I want to set the contects from a select statment like
this:
txtBox = select * from table where...

Is this possible and how would I accomplish this?
 
I have a text box and I want to set the contects from a select statment like
this:
txtBox = select * from table where...

Is this possible and how would I accomplish this?
Click to expand...

No, it's not possible. You cannot use a SQL statement as the control
source of a text control.
Now if you tell us what data you wish to place in the text box, and
what you intend to do with the data, perhaps someone can tell you how.
 
Basically I have a table with machineName,macAddress1,macAddress2 and I want
it to output text somehow in exactly the format:

machineName,macAddress1
machineName,macAddress2
etc.

This is for importing into a radius database. So there would be many
machine names and each machine name would require 2 rows, one for each mac
address.

Thanks,
Andy.
 
You can set the data source of a text box equal to a function that returns
the result.

Along the lines of (the function is put in a standard code module)

Public Function MyFunction(yMachineName) As Variant
Dim db As DAO.Database
Dim r As DAO.Recordset

Dim mSQL As String

MyFunction = Null

On Error GoTo TrapIT

mSQL = "SELECT ..."
mSQL = mSQL & " FROM ..."
mSQL = mSQL & " WHERE .... " & ";"

Set db = CurrentDb
Set r = db.OpenRecordset(mSQL)
With r
If Not .EOF Then
MyFunction = Nz(!mac1) & vbcrlf & Nz(!mac2)

Else
MyFunction= Null

End If
End With

EnterHere:

On Error Resume Next
r.Close
Set r = Nothing
Set db = Nothing

Exit Function

TrapIT:
'error reporting stuff here
Resume Enterhere

End Function


The datasource for the text box then looks something like this:

=MyFunction([MachineName])

This assumes that MachineName is a text box on the form, or a fieldname in a
query.

Using this in a continous form, or a query that returns many rows, can be a
bit slow.
 
Back
Top