ADODB Retrieve Value with SQL Help

[kawi6rr]

I'm trying to read a value from a table using ADODB below.

Private Sub Form_Load()
Dim conn As ADODB.Connection
Dim strSQL As String
Set conn = CurrentProject.Connection

strSQL = "SELECT CFS FROM loadDateCFS"

conn.Execute strSQL

Me.txtCFS.value = strSQL

conn.Close
Set conn = Nothing

Me.Dirty = False
Me.Requery


End Sub

When I try to assigne the strSQL value to my txt field the txt field simply displays my select statement.

txt field shows "SELECT CFS FROM loadDateCFS"

How can I get it do display the actual date in the tables CFS field?

Thanks in advance for any help.

[ajetrumpet]

you can't use SELECT queries in vba for anything, bud. Not within access, anyway. now, for ado connection to excel sheets, named ranges, and things like that, it serves a different purpose. but what you're trying to do is impossible. use a DLOOKUP() function instead.

[June7]

DLookup would work and don't have to bother with the connection and recordset code. But if you really want to use SQL then you need a recordset and set box to the value of field in the query.

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset
 
rs.Open "SELECT CFS FROM loadDateCFS;", conn, adOpenStatic, adLockPessimistic
 
Me.txtCFS = rs!CFS
 
rs.Close

That's how I do it. This query will retrieve all records but the textbox is populating with value from the first record.

The real question is why do you need to do this, what are you really trying to accomplish? Can this field be a part of the form's RecordSource?

[kawi6rr]

Thanks I'll give it a try!