Solved: Update a subform, Requery doesn't work

[Touni102]

Reposting this in a new thread - old thread was marked as solved but this is still a problem.

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.

[OBP]

Is there a whereclause missing from this line

whereClause = " AND " & negator & "(" & Right(whereClause, Len(whereClause) - 4) & ")"

Or is starting the line with an AND intentional?

[Touni102]

the variable whereClause is attached to the end of sqlString, which already has "WHERE" included in it. The sql string is correct, and the correct data is pulled when you open the query manually, but I would like the query to show in the subform...

[OBP]

Well you have 2 options.
1. set the Subform's record Source to the SQL Statement in the Form.
2. Use a QueryDef to to replace the Subform's Query Record Source with the SQL Statement. 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 ")

End With
End Sub


Local Time: 04:31 PM
Local Date: 05-13-2009
Location: http://www.vbaexpress.com/forum/images/flags/United%20Kingdom%203D.gif http://www.vbaexpress.com/forum/images/flags/provinces/Wales%203D.gif

http://www.vbaexpress.com/forum/images/buttons/quote.gif (http://www.vbaexpress.com/forum/newreply.php?do=newreply&p=178233)

[Touni102]

how do I update the subform's record source through vba?

[Touni102]

I was able to create a new query definition with the code you supplied, but i still cannot update the subform with the new data. I have to close the form and re-open it just for it to show the new data...

[OBP]

Is the query definition name the same as the original Form's Query name?

[OBP]

This works for me.
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 "NewQueryDef", acViewPreview
DoCmd.TransferSpreadsheet acExport, , "NewQueryDef", "c:\Access\TEST.xls", False
End With

Me.Categories.Form.RecordSource = "NewQueryDef"
Me.Categories.Requery

[Touni102]

It works!!!! I had to update the RecordSource for some reason. I don't know why... I was so confused with what was going on because on a form before i've used only a Requery to get the form to update.... Now the Record source must be changed?

[OBP]

Yes I found the same thing, my original code was written for a Report, not a form. The report didn't need it of course.
Can you mark the Thread as solved please?

[Touni102]

Done.

Btw, as for the ItemsSelected bug, It was from the function being called as the list was being clicked on, which at that point the ItemsSelected was not populated yet it seems. I solved that by calling the update function through a button instead of through changing the selection. If there is one item selected and you click a button to update the item shows up in the list.