refresh recordsource on main form dynamically

[karen scheu via AccessMonster.com]

I have a problem viewing a mainform/subform when setting the rowsource
dynamically .

I have a filter form which allows user to enter begin date and end date range
, a division and a customer. There is a view button on this form that will
create a where string based on the user selections, then open the mainform.
The following code shows example of how I set the recordsource and open the
form
strWhere = "[minOfGl] Between #" & Me!BeginningDate & "# AND #" & Me!
EndingDate & "# " & _
" AND [div_code]= " & txtDiv & " AND [firstOfSDAN8]= " &
txtCust

DoCmd OpenForm "Frm201Main"

With Forms![frm201main]
.RecordSource = "Select * FROM Qrpt201Summary WHERE " & strWhere
Me.Requery
!cboOrder.RowSource = "Select DISTINCT [SDDOCO], [SDDCTO] FROM
Qrpt201Summary WHERE " & strWhere _
& " ORDER BY [SDDOCO]"
!cboOrder.Requery
End With

When the mainform opens, it opens with the first row from the Qrpt201Summary
query result before the where string was applied, then a few seconds later,
the forms are refreshed and the mainform and subform display the filtered
selection. How can I change things so that the forms do not open until the
where clause is applied?

Thanks so much for any assistance with this.

 

[Marshall Barton]

I have a problem viewing a mainform/subform when setting the rowsource
dynamically .

I have a filter form which allows user to enter begin date and end date range
, a division and a customer. There is a view button on this form that will
create a where string based on the user selections, then open the mainform.
The following code shows example of how I set the recordsource and open the
form
strWhere = "[minOfGl] Between #" & Me!BeginningDate & "# AND #" & Me!
EndingDate & "# " & _
" AND [div_code]= " & txtDiv & " AND [firstOfSDAN8]= " &
txtCust

DoCmd OpenForm "Frm201Main"

With Forms![frm201main]
.RecordSource = "Select * FROM Qrpt201Summary WHERE " & strWhere
Me.Requery
!cboOrder.RowSource = "Select DISTINCT [SDDOCO], [SDDCTO] FROM
Qrpt201Summary WHERE " & strWhere _
& " ORDER BY [SDDOCO]"
!cboOrder.Requery
End With

When the mainform opens, it opens with the first row from the Qrpt201Summary
query result before the where string was applied, then a few seconds later,
the forms are refreshed and the mainform and subform display the filtered
selection. How can I change things so that the forms do not open until the
where clause is applied?

Opening a form and setting its record source in the search
form is bound to have some timing issues. You caould
probably avoid the problem you're seeing by leaving the
form's RecordSource and combo box's RowSource blank in
design view.

Generally, I would use the OpenForm method's WhereCondition
argument to filter the form's data while opening the form,
but you want to set the combo box's RowSource as well.

In this case, I think I would use the OpenForm method's
OpenArgs argument to pass the criteria to the form and let
it take care of itself in its Open event procedure.

Code in the view button:
strWhere = " . . ."
DoCmd OpenForm "Frm201Main", OpenArgs:= strWhere

Code in Frm201Main's Open event:
If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = "Select * FROM Qrpt201Summary " _
& "WHERE " & Me.OpenArgs
Me!cboOrder.RowSource = "Select DISTINCT [SDDOCO], " _
& "[SDDCTO] FROM Qrpt201Summary WHERE " _
& strWhere & " ORDER BY [SDDOCO]"
End If

Note that setting the RecordSource or RowSource properties
automatically requeries, so adding a .Requery statement is
redundant and a performance degradation.

 

[karen scheu via AccessMonster.com]

Thank you for your help. I made the changes and my form opens much quicker.
Much appreciated!