Retrieve Result From SQL

[lhooker]

How can I retrieve the results of the below SQL code ? Also, How can I replace the hard coded 'Target' value with the variable named 'Account' ?


Dim strSQL As String
Dim rs As DAO.Recordset

Account = [List7].[Column](0)

strSQL = "SELECT Bill_Name, Last_Paid, NextPaymentDate FROM Bills " & _
"WHERE [Bill_Name] = 'Target'"

Debug.Print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL)

If Not (rs.EOF) Then
Debug.Print rs![Bill_Name]
Else
Debug.Print "Nothing found"
End If

Set rs = Nothing

 

[jdraw]

Do you get an error message? error number? error description?
What exactly did you get with the debug.print?
Where exactly are you running this code?
What is Account, where is it defined?

Please show all relevant code and use code tags when posting.

 

[lhooker]

JDraw,

I'm not getting any errors. Attached is a 'Test' database. When I click on any name in the 'Listbox' I expected to retrieve the latest 'Last_Paid' date in the 'Bills' table.

 

[June7]

Concatenate variables.

strSQL = "SELECT Bill_Name, Last_Paid, NextPaymentDate FROM Bills WHERE [Bill_Name] = '" & Account & "'"

Don't really need the variable.

strSQL = "SELECT Bill_Name, Last_Paid, NextPaymentDate FROM Bills WHERE [Bill_Name] = '" & Me.List7 & "'"

What do you want to do with the recordset besides just Debug.Print? If you only want to get a single value, then can use DLookup() instead of Recordset.

 

[lhooker]

June7,

I want the results of the recordset (for 'Last_Paid' from SQL) to be put into the 'Last_Paid' 'Textbox.

 

[arnelgp]

Dim strSQL As String
Dim rs As DAO.Recordset

If [list7].listindex > -1 then
Account =[List7]

strSQL = "SELECT Bill_Name, Last_Paid, NextPaymentDate FROM Bills " & _
"WHERE [Bill_Name] = """ & Account """"

Debug.Print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL)

If Not (rs.EOF) Then
rs.movefirst
Me.last_paid = rs!last_paid
Debug.Print rs![Bill_Name]
Else
Debug.Print "Nothing found"
End If
End if

Set rs = Nothing

 

[lhooker]

Arnelgp,

I had to modify your code a little bit (missing '&' - see below). Still, the code does not put he the 'Last_Paid' date in the 'Textbox'. Also, the original 'NextPaymentDate' and 'Last_Paid' dates are not put in their textboxes. What I'm trying to do is load the these textboxes with the original dates (lastest dates for the Bill_Name in the 'Bills' table), modify the 'NextPaymentDate' with a new date (from 'Date Picker'), then add a new record to the 'Bills' table.

Dim strSQL As String
Dim rs As DAO.Recordset

If [List7].ListIndex > -1 Then
Account = [List7]

strSQL = "SELECT Bill_Name, Last_Paid, NextPaymentDate FROM Bills " & _
"WHERE [Bill_Name] = """ & Account & """"

Debug.Print strSQL
Set rs = CurrentDb.OpenRecordset(strSQL)

If Not (rs.EOF) Then
rs.MoveFirst
Me.Last_Paid = rs!Last_Paid
Debug.Print rs![Bill_Name]
Else
Debug.Print "Nothing found"
End If

End If

Set rs = Nothing

 

[lhooker]

Pat Hartman,

I can't select a new date for 'NextPaymentDate'. I want to be able to modify the 'NextPaymentDate' (from 'Date Picker'), then add this new record (with 'Bill_Name' and 'Last_Paid') to the 'Bills' table.

 

[lhooker]

Pat Hartman,

Thank you very much ! ! ! I'll try what you said.

 

[lhooker]

Pat Hartman,

One more question . . . why am I getting the 'This Recordset is not updateable.' error when trying to clear the textboxes in the form ? The error appears for both "" and Null.

Me.[Bill_Due_Date] = ""
Me.[LastPaid] = ""
Or
Me.[Bill_Due_Date] = Null
Me.[LastPaid] = Null

 

[arnelgp]

Here is another version

 

[jdraw]

lhooker,

Here is an article by Allen Browne.
Why is my query read only?