Error when trying to set the subform recordsource to an sql query

1
votes

I'm trying to change the recordset of a subform to display the query result

So I have a string query : 

sQueryCurrencyRate = "SELECT * FROM Table.Mytable WHERE  As_Of_Date =" & Format(dateField.value, "M/d/yyyy")


 sQueryCurrencyUsDollar = "SELECT * FROM Table.Mytable WHERE  As_Of_Date =" & Format(dateField.value, "M/d/yyyy")

So when I write this , the subform display a result without error : 

 Me.subformName.Form.RecordsourceSourceObject = "Table.tableName"

but When I try all of the below I always receive an error : 

Forms!formName.subformName.Form.RecordSource= sQueryCurrencyRate

I have also tried : 

Me.subFormName.Form.RecordsourceSourceObject= sQueryCurrencyRate

Also , 

 Me.subFormName.RecordsourceSourceObject= sQueryCurrencyRate

No matter what I do , I always end up having this error message :

the expression you have entered refers to an object that is closed or doesn't exist

What I'm doing wrong? and how to properly display the query result in the subform recordsource ?

P.S: the sql queries syntax are fine because I have previously tested them with :

Set db = CurrentDb
Set rs = db.OpenRecordset(sQueryCurrencyRate)

and I received no error , so the problem is how to bind the subform result to the query

EDIT:

The way I'm calling the code is like below : 

Function detectSelectedQuery(sTypeDonnee As String)

sQueryCurrencyRate = "SELECT * FROM Table.Mytable WHERE  As_Of_Date = ' " & Format(dateField.value, "M/d/yyyy") &"'"

 Me![Child8].Form.RecordSource = sQueryCurrencyRate
end function

then when the combobox is changed I call the function : 

Private Sub Combo_descriptionQueries_Change()
detectSelectedQuery (Combo_descriptionQueries.Value)
End Sub
3
ms-accessvba
@ErikvonAsmuth I did also tried simply doing 'me.subformName.Form.RecordSource= sQueryCurrencyRate' and 'me.subformName.RecordSource= sQueryCurrencyRate' without any success , It's weird - user3752718
Where are you calling this code? On the main form? - Erik A
@ErikvonAsmuth I have declared a function called Execute Query and inside of it , I'm calling the code - user3752718
@Mat'sMug the whole RecordSetObject part is wrong, he intends to set RecordSource, which is a string SQL statement that the form uses (there also is a RecordSet property, but that shouldn't be changed unless you want to use an ADO recordset as recordsource) - Erik A
@ErikvonAsmuth I'd trade four fingers and a leg for a properly parameterized ADODB recordset/query over a string-concatenated, begging-for-SQL-injection WHERE clause like this. Sure it makes it "easy" to get something that works, but it's just plain wrong on so many levels. Same holds true for UserForms and their default instance, for example. Too many things are dumbed-down in VBA, it's just crazy. - Mathieu Guindon

3 Answers

2
votes

If you want to do this properly (without any risk of SQL injection), you can use an ADODB recordset:

Dim cmd As New ADODB.Command
cmd.CommandText = "SELECT * FROM Mytable WHERE As_Of_Date = @MyDate"
cmd.ActiveConnection = CurrentProject.Connection
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.Parameters("@MyDate") = cmd.CreateParameter("MyDate", adDate)
cmd.Parameters("@MyDate").Value = datefield.Value
Set Me.Recordset = cmd.Execute

Note that this requires a reference to the ActiveX Data Objects library

1
votes

Use this:

sQueryCurrencyRate = "SELECT * FROM Mytable WHERE As_Of_Date = #" & Format(dateField.value, "yyyy\/mm\/dd") & "#"

To avoid concatenating, you could use TempVars or create a small helper function:

' Public variable:
Dim FilterDate As Date

Public Function GetFilterDate() As Date
    GetFilterDate = FilterDate
End Function

and then an extra line of code:

FilterDate = Me!dateField.Value
sQueryCurrencyRate = "SELECT * FROM Mytable WHERE As_Of_Date = GetFilterDate()"
1
votes

Since this is in Access, why not use the default DAO interface with a parameter query instead of ADODB? No need for an ActiveX Data Objects reference.

Dim qdf As DAO.QueryDef

'For an existing query
Set qdf = CurrentDb().QueryDefs("qryName")

'To create query (Credit to @Erik von Asmuth for pointing this out)
Set qdf = CurrentDb().CreateQueryDef("", "PARAMETERS [@MyDate] DateTime; SELECT * FROM Mytable WHERE As_Of_Date = [@MyDate];")
    qdf.Parameters("[@MyDate]").Value = Date()

Set Me.Recordset = qdf.OpenRecordset()