Retrieve Result From SQL (1 Viewer)

lhooker

Registered User.
Local time
Today, 02:01
Joined
Dec 30, 2005
Messages
399
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

Super Moderator
Staff member
Local time
Today, 02:01
Joined
Jan 23, 2006
Messages
15,364
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

Registered User.
Local time
Today, 02:01
Joined
Dec 30, 2005
Messages
399
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.
 

Attachments

  • Test.mdb
    1.2 MB · Views: 79

June7

AWF VIP
Local time
Yesterday, 22:01
Joined
Mar 9, 2014
Messages
5,424
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

Registered User.
Local time
Today, 02:01
Joined
Dec 30, 2005
Messages
399
June7,

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:01
Joined
May 7, 2009
Messages
19,169
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:01
Joined
Feb 19, 2002
Messages
42,981
I fixed the form so that when you click on the listbox, the other fields populate.

I don't know where you are going with the rest of the code so I didn't fix it.

I also cleaned up the names. ALWAYS give controls a proper name before you use them. Leaving names such as list7 is poor practice.
 

Attachments

  • Test_Pat.mdb
    300 KB · Views: 87

lhooker

Registered User.
Local time
Today, 02:01
Joined
Dec 30, 2005
Messages
399
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

Registered User.
Local time
Today, 02:01
Joined
Dec 30, 2005
Messages
399
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:01
Joined
Feb 19, 2002
Messages
42,981
As I said, the process you described doesn't make sense to me so I didn't fix up all the code.

In order to make the form work as you wanted it to, I had to use a non-updateable query and that is why you can't type anything in the controls.

Since you don't really want the form to be updateable, that shouldn't be a problem. Just add an unbound control to hold the new date. Then your append query can take data from both the bound and unbound forms to insert a new record.
 

lhooker

Registered User.
Local time
Today, 02:01
Joined
Dec 30, 2005
Messages
399
Pat Hartman,

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

lhooker

Registered User.
Local time
Today, 02:01
Joined
Dec 30, 2005
Messages
399
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

..forever waiting... waiting for jellybean!
Local time
Today, 14:01
Joined
May 7, 2009
Messages
19,169
Here is another version
 

Attachments

  • Test.zip
    126.2 KB · Views: 76

Users who are viewing this thread

Top Bottom