Solved: Subforms (Datasheet):Passing the "Source Object" parameter from code

[afh110]

Hi all,

using VBA code, im trying to populate a subform (Datasheet) using an SQL query. My understanding is that i need to open the DB do the query and pass it to the "Source Object" line in the subform property window. So i wrote the code below. I tested the query itself and the code seems to work till it reaches the "SourceObject=" line of code and gives me "Object Required" error. I understand that the code is expecting an object, but im not sure how to do that.
Private Sub DS_HmActivity_Enter()
Dim User As String
Dim db As DAO.Database
Dim Tb As DAO.TableDef
Dim rs As DAO.recordset
Dim strResult As String
Dim Obj As Object

User = DD_Team_Name_Fld.Value
strResult = "SELECT Activity_Log.Activity_Type AS ['Type'], Activity_Log.Descrp AS ['Description'], SH_Def.First_Name+' '+SH_Def.Last_Name AS ['Shareholder'], Activity_Log.Planned_Date AS ['Scheduled Date'], Activity_Log.Actual_Date AS ['Actual Date'], Activity_Log.Log_Date AS ['Log Date'] FROM Activity_Log INNER JOIN SH_Def ON Activity_Log.SH_Key = SH_Def.StrKey WHERE (Activity_Log.Creation_User='" + User + "');"

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

DS_HmActivity.SetFocus
Project_Management.DS_HmActivity.SourceObject = rs ' HERE IS MY PROBLEM!!
rs.Close
End Sub

[OBP]

Do not set the SQL Statement to a Recordset, set it directly to the Subform's Record Source.

[afh110]

Hi,

I set it to the Source object

SourceObject = "SELECT Activity_Log.Activity_Type AS ['Type'], SH_Def.First_Name+' '+SH_Def.Last_Name AS ['Shareholder'], Activity_Log.Planned_Date AS ['Scheduled Date'], Activity_Log.Actual_Date AS ['Actual Date'], Activity_Log.Log_Date AS ['Log Date'] FROM Activity_Log INNER JOIN SH_Def ON Activity_Log.SH_Key = SH_Def.StrKey WHERE (Activity_Log.Creation_User='" + User + "');"\0

when i run it, i get no error but i get no data displayed in my Subform(Datasheet)!! any ideas?

[OBP]

If you set it to a Recordset and then interrogate the recordset with VBA do you get any Records or data?
It sounds like you are not getting any records returned

[afh110]

Nope i get no record return, i get an error!
i guess the question is what does SourceObject require? a SQL statement string or an object? if an object then of what kind? i know that SourceObject accepts tables and queries. When i save a Query using the Query Builder and go to the SourceObject cell in the property menu, i can see the query i made and select it. It works fine, but the problem i need that query to read in from the DD_Team_Name_Fld combo control on the form, and i dunno how and if it is possible to pass the value in a control to a called saved query! Any ideas if that is possible?

Thanks again

[OBP]

You can rewrite a Query on demand, so you could establish your subform's query, rewrite the Query and the just requery the Subform.
You can use the QueryDef to rewrite the query and the vba command
me.subformname.requery
to re-query it, where subformname is the name of the subform and the control (Button) that runs the vba is on the Mainform.
The QueryDef code looks like this.

Private Sub Command2_Click()
Dim rs As Object, sql As String, qdfNew As Object
With CurrentDb
.QueryDefs.Delete "NewQueryDef"

Set qdfNew = .CreateQueryDef("NewQueryDef", _
"SELECT * FROM Categories WHERE [CategoryID]> 10 ")
DoCmd.OpenReport "Categories Query", acViewPreview ' you don't need this line, it is for a report.
End With
End Sub

[afh110]

Ok i tried making a temporary query, but again i dunno how to pass the query results to the SourceObject property of the Subform! here is the code. Any ideas ?

Private Sub SF_HmGetActivity_Enter()
Dim User As String
Dim db As DAO.Database
Dim Qry As DAO.QueryDef
Dim strResult As String

User = DD_Team_Name_Fld.Value
Set db = CurrentDb
Set Qry = db.CreateQueryDef("")

Qry.sql = "SELECT Activity_Log.Activity_Type AS ['Type'], SH_Def.First_Name+' '+SH_Def.Last_Name AS ['Shareholder'], Activity_Log.Planned_Date AS ['Scheduled Date'], Activity_Log.Actual_Date AS ['Actual Date'], Activity_Log.Log_Date AS ['Log Date'] FROM Activity_Log INNER JOIN SH_Def ON Activity_Log.SH_Key = SH_Def.StrKey WHERE (Activity_Log.Creation_User='" + User + "');"
Qry_HmGetActivity.SourceObject = Qry.Execute
Qry.Close
db.Close
End Sub\0


[OBP]

Set your Subform Recordsource to the new Query, which you do not appear to have named as I did.

[afh110]

Doesnt work either ! gives me Object Required error!
Notice that i didnt give the query a name cuz i want it to be a temporary query (not saved) HELP!!!

Private Sub SF_HmGetActivity_Enter()
Dim User As String
Dim db As DAO.Database
Dim Qry As DAO.QueryDef
Dim NewQry As Object
Dim strResult As String

User = DD_Team_Name_Fld.Value
With CurrentDb
Set db = CurrentDb
Set NewQry = db.CreateQueryDef("", "SELECT Activity_Log.Activity_Type AS ['Type'], SH_Def.First_Name+' '+SH_Def.Last_Name AS ['Shareholder'], Activity_Log.Planned_Date AS ['Scheduled Date'], Activity_Log.Actual_Date AS ['Actual Date'], Activity_Log.Log_Date AS ['Log Date'] FROM Activity_Log INNER JOIN SH_Def ON Activity_Log.SH_Key = SH_Def.StrKey WHERE (Activity_Log.Creation_User='" + User + "');")
Project_Management.SF_HmGetActivity.SourceObject = NewQry
End With
End Sub

[OBP]

Why do you want it to be a Temporary Query?
Which line of code gives the error?

[afh110]

Well i just dont need to save it, but if i give it a name in the code above, it gives me a "Field not found" error.

but the current error is on the "...SourceObject=NewQry" line

[OBP]

Try
Me.Project_Management.SF_HmGetActivity.SourceObject = NewQry

[afh110]

a new error " Method or data member not found"

Im so confused :S

[afh110]

Ok i finally figured it out! OBP you were so close. I had to give the query a name and equate the name of the new query in quotes for it to work! here is the final code. of course for me to delete this created temp qry i just set a delete command on on the close form event. Thanks for everything OBP :)
Private Sub SF_HmGetActivity_Enter()
Dim User As String
Dim db As DAO.Database
Dim Qry As DAO.QueryDef
Dim NewQry As QueryDef
Dim strResult As String

User = DD_Team_Name_Fld.Value
With CurrentDb

Set db = CurrentDb
Set NewQry = db.CreateQueryDef("Qry", "SELECT Activity_Log.Activity_Type AS ['Type'], SH_Def.First_Name+' '+SH_Def.Last_Name AS ['Shareholder'], Activity_Log.Planned_Date AS ['Scheduled Date'], Activity_Log.Actual_Date AS ['Actual Date'], Activity_Log.Log_Date AS ['Log Date'] FROM Activity_Log INNER JOIN SH_Def ON Activity_Log.SH_Key = SH_Def.StrKey WHERE (Activity_Log.Creation_User='" + User + "');")
Forms!Project_Management!SF_HmGetActivity.SourceObject = "Query.Qry"

End With
End Sub

[OBP]

Great. :thumb VBA Syntax is tough, isn't it?

[afh110]

Tell me about it ! But getting there , slowly but surly ;)

[geekgirlau]

Just to confuse you even further :tongue: ...

You can set the criteria in your query to look at the value of a control in a form. You simply set the criteria for the field to Forms![MyForm]![MyControl], and the recordsource for your sub form is the query name - no code required!