Regarding Subform update

[kris]

I have a subform whose source is a query. The SQL statement for the query is
obtained from the code given below .

Function s()

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms![ROC-Home1]
Set ctl = frm![control-list]
strSQL = "SELECT Table3.* FROM Table3 WHERE [Controls]="
For Each varItem In ctl.ItemsSelected
If ctl.ItemData(varItem) = "(All)" Then 'added
strSQL = "SELECT Table3.* FROM Table3 OR [Controls]="
Exit For
End If
strSQL = strSQL & "'" & ctl.ItemData(varItem) & "'" & " OR
[Controls]="
Next varItem
'Trim the end of strSQL
strSQL = Left(strSQL, Len(strSQL) - Len(" OR [Controls]="))
strSQL = strSQL & ";"
CurrentDb.QueryDefs("Query3").SQL = strSQL
End Function

I have a button which executes a macro with following actions
• Run code s()
• Sets the visible property of query3 subform to -1;
• Requery

When I change the selection in the list box and run the macro, the query
output should change (that is subform contents should change)but it doesn’t.
The changes are reflected when I close the form and open it again.
I use 2007 MS access and I'm very new to Vb coding.

 

[June7 via AccessMonster.com]

First, don't think you need the procedure as a Function, just make it a Sub.
Use functions when you want to return a value. Unless you are calling this
procedure from several places, just put it in the AfterUpdate event of the
list box along with the lines for setting subform visible property and
requery. You don't show those lines so can't evaluate. No button and no
macro needed with this event. I don't use macros, all VBA. Since you are
already using VBA to build function, suggest follow through and use VBA code
for all. Select [Event Procedure] for the AfterUpdate property on property
box Events tab, click the ellipses (3 dots) button and that will take you to
the VBA code, paste your code from the function into the Sub.

I have a subform whose source is a query. The SQL statement for the query is
obtained from the code given below .

Function s()

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms![ROC-Home1]
Set ctl = frm![control-list]
strSQL = "SELECT Table3.* FROM Table3 WHERE [Controls]="
For Each varItem In ctl.ItemsSelected
If ctl.ItemData(varItem) = "(All)" Then 'added
strSQL = "SELECT Table3.* FROM Table3 OR [Controls]="
Exit For
End If
strSQL = strSQL & "'" & ctl.ItemData(varItem) & "'" & " OR
[Controls]="
Next varItem
'Trim the end of strSQL
strSQL = Left(strSQL, Len(strSQL) - Len(" OR [Controls]="))
strSQL = strSQL & ";"
CurrentDb.QueryDefs("Query3").SQL = strSQL
End Function

I have a button which executes a macro with following actions
• Run code s()
• Sets the visible property of query3 subform to -1;
• Requery

When I change the selection in the list box and run the macro, the query
output should change (that is subform contents should change)but it doesn’t.
The changes are reflected when I close the form and open it again.
I use 2007 MS access and I'm very new to Vb coding.

 

[kris]

Hello,
As suggested I put my code into after update event and still have no
luck
my code looks like..

Private Sub control_list_AfterUpdate()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms![ROC-Home1]
Set ctl = frm![control-list]

strSQL = "SELECT Table3.* FROM Table3 WHERE [Controls]="

For Each varItem In ctl.ItemsSelected
If ctl.ItemData(varItem) = "(All)" Then
strSQL = "SELECT Table3.* FROM Table3 OR [Controls]="
Exit For
End If
strSQL = strSQL & "'" & ctl.ItemData(varItem) & "'" & " OR
[Controls]="
Next varItem

'Trim the end of strSQL
strSQL = Left(strSQL, Len(strSQL) - Len(" OR [Controls]="))
strSQL = strSQL & ";"
CurrentDb.QueryDefs("Query3").SQL = strSQL

CurrentDb.QueryDefs("Query3").Close
' DoCmd.OpenQuery "Query3"
DoCmd.SetProperty "Query3 Subform1", acPropertyVisible, "-1"
DoCmd.Requery ""
End Sub

But still the problem remains.The subform doesnt get updated even after i
changed the selection.

First, don't think you need the procedure as a Function, just make it a Sub.
Use functions when you want to return a value. Unless you are calling this
procedure from several places, just put it in the AfterUpdate event of the
list box along with the lines for setting subform visible property and
requery. You don't show those lines so can't evaluate. No button and no
macro needed with this event. I don't use macros, all VBA. Since you are
already using VBA to build function, suggest follow through and use VBA code
for all. Select [Event Procedure] for the AfterUpdate property on property
box Events tab, click the ellipses (3 dots) button and that will take you to
the VBA code, paste your code from the function into the Sub.

I have a subform whose source is a query. The SQL statement for the query is
obtained from the code given below .

Function s()

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms![ROC-Home1]
Set ctl = frm![control-list]
strSQL = "SELECT Table3.* FROM Table3 WHERE [Controls]="
For Each varItem In ctl.ItemsSelected
If ctl.ItemData(varItem) = "(All)" Then 'added
strSQL = "SELECT Table3.* FROM Table3 OR [Controls]="
Exit For
End If
strSQL = strSQL & "'" & ctl.ItemData(varItem) & "'" & " OR
[Controls]="
Next varItem
'Trim the end of strSQL
strSQL = Left(strSQL, Len(strSQL) - Len(" OR [Controls]="))
strSQL = strSQL & ";"
CurrentDb.QueryDefs("Query3").SQL = strSQL
End Function

I have a button which executes a macro with following actions
• Run code s()
• Sets the visible property of query3 subform to -1;
• Requery

When I change the selection in the list box and run the macro, the query
output should change (that is subform contents should change)but it doesn’t.
The changes are reflected when I close the form and open it again.
I use 2007 MS access and I'm very new to Vb coding.

 

[June7 via AccessMonster.com]

Have you tried step debugging? That way you can identify which lines fail.
You are using some code I am not familiar with (QueryDefs). And for the DoCmd
statements I would use:
Me.subformContainerName.Form.RecordSource = strSQL
Me.subformContainerName.Form.Requery
Me.subformContainerName.Visible = True
Here is example from my project: Me.ctrSampleList.Form.RecordSource = strSQL
And I don't even use the Requery statement and my form's recordset is
modified.

Hello,
As suggested I put my code into after update event and still have no
luck
my code looks like..

Private Sub control_list_AfterUpdate()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms![ROC-Home1]
Set ctl = frm![control-list]

strSQL = "SELECT Table3.* FROM Table3 WHERE [Controls]="

For Each varItem In ctl.ItemsSelected
If ctl.ItemData(varItem) = "(All)" Then
strSQL = "SELECT Table3.* FROM Table3 OR [Controls]="
Exit For
End If
strSQL = strSQL & "'" & ctl.ItemData(varItem) & "'" & " OR
[Controls]="
Next varItem

'Trim the end of strSQL
strSQL = Left(strSQL, Len(strSQL) - Len(" OR [Controls]="))
strSQL = strSQL & ";"
CurrentDb.QueryDefs("Query3").SQL = strSQL

CurrentDb.QueryDefs("Query3").Close
' DoCmd.OpenQuery "Query3"
DoCmd.SetProperty "Query3 Subform1", acPropertyVisible, "-1"
DoCmd.Requery ""
End Sub

But still the problem remains.The subform doesnt get updated even after i
changed the selection.

First, don't think you need the procedure as a Function, just make it a Sub.
Use functions when you want to return a value. Unless you are calling this

[quoted text clipped - 42 lines]