Solved: Displaying a generated recordset in a subform

[Touni102]

What do you set the subform's source to in order to load a recordset created in VBA?

[orange]

What do you set the subform's source to in order to load a recordset created in VBA?

How is your mainform linked to the subfrom?

Typically there is a mainform with a record source, and there are related data that are presented on the subform. There is an option to link the related parent and child fields.

[Touni102]

As of now, I dragged a query onto the Mainform and it created a Subform to display the query. Currently, the subform's Source Object is set to one of my query objects I have in My access file. However, I want to dynamically create SQL and run the sql in vba (using DoCmd.RunSQL) and set the results to display in the subform instead.

The other way I would be able to do it, but I dont know how, is to have parameters in my SQL string to set conditions in the WHERE clause, but I don't know how to do that, or if it's possible.

Right now in my vba code, i'm referencing the subform using:
Forms![Software Search]![tblResult]

"Software Search" is the name of the Mainform, "tblResult" is the name of the Subform.

[orange]

As of now, I dragged a query onto the Mainform and it created a Subform to display the query. Currently, the subform's Source Object is set to one of my query objects I have in My access file. However, I want to dynamically create SQL and run the sql in vba (using DoCmd.RunSQL) and set the results to display in the subform instead.

The other way I would be able to do it, but I dont know how, is to have parameters in my SQL string to set conditions in the WHERE clause, but I don't know how to do that, or if it's possible.

Right now in my vba code, i'm referencing the subform using:
Forms![Software Search]![tblResult]

"Software Search" is the name of the Mainform, "tblResult" is the name of the Subform.
I'm not exactly sure of what you're trying to do. Perhaps you could explain more.
I am attaching a file of a typical form and subform - but this does not sound like your situation.

Photo attached is an Order form, with an OrderDetails sub form.

Sites that may help : See Martin Green's examples of setting up forms to adjust SQL etc.
Martin Green http://www.fontstuff.com/access/acctut18.htm

Crystal http://www.allenbrowne.com/casu-22.html

[orange]

What do you set the subform's source to in order to load a recordset created in VBA?

Are you asking how to use a Form/SubForm to display the data from a query?

[Touni102]

Thanks for the link, that has what I need to do. I already knew how to set up a subform to display data from a stored query, by just setting the Source Object to the query desired, however, that only works for stored queries. I created a sqlString dynamically and used DoCmd.RunSQL to get the recordset. I wanted to have the subform display that information. However, the link shows that you can have the subform set to a stored query, and change that query's SQL dynamically instead.

[Touni102]

SQL is working and successfully updating, but for some reason when I requery, nothing changes. When I restart the form the new data appears but what should I do to get it to update right away? I've used requery before on other cases, but now that I'm changing the query's sql command does that stop the display of the query? i dunno

[orange]

You could try repaint.

[Touni102]

Ok.... i thought this problem was solved. I got far though. My sql string is generating correctly and the data displays when you open the Query directly. However in my form when I interact with the controls, that's when the Sql is Updated, and it should also update the subform too. I have done a requery on the subform before, leading me to think that once I get the sql down this is solved.

Anyway this is what i have:
Function UpdateSQL()
Dim varItem As Variant
Dim whereClause As String
Dim negator As String
If cbNOT.Value = 0 Then
negator = ""
Else
negator = "NOT "
End If
For Each varItem In lbContains.ItemsSelected
whereClause = whereClause & " OR [Computer Software].ProgramName='" & lbContains.ItemData(varItem) & "'"
Next varItem
If Len(whereClause) = 0 Then
whereClause = ""
Else
whereClause = " AND " & negator & "(" & Right(whereClause, Len(whereClause) - 4) & ")"
End If
Dim sqlString As String
sqlString = "SELECT [Computer Summary].ComputerName, [Computer Summary].Info, [Computer Summary].Active, [Computer Software].ProgramName, [Computer Software].Version, [Computer Software].InstallDate "
sqlString = sqlString & "FROM [Computer Summary], [Computer Software] WHERE [Computer Summary].ComputerName=[Computer Software].ComputerName" & whereClause
MsgBox sqlString

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("Advanced Query")
qdf.SQL = sqlString
Set qdf = Nothing
Set db = Nothing
Forms![Advanced Search]![tblResult].Requery
End Function
The subforms data source is "Advanced Query" which gets it's SQL updated from this code here, with the help of the link supplied above - thanks. When I change the checkbox or select a new selection this function is called. However, the Requery does nothing. I just want to update the query in the subform. Also, i dunno why but there is a bug - when I select only one item in the list box lbContains, lbContains.SelectedItems shows up with nothing...

I'm so close to finishing this but so far from knowing what to do.... Any help again is appreciated.

I just tried the Repaint, and it also didn't work.