Changing a form's recordsource through code

[Santiago Gomez]

Hi guys, these groups are amazing, thanks for taking the time.

I have a form based on a saved query (qryfrmActiveRecords). I want to add a
checkbox to the form that shows all records, ie. active and inactive
(qryfrmAllRecords).

How can I change the recordsource of the form using code?

Ideally it would look at another saved query.


Private Sub chkShowAll_Loads_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open ("qryfrmAllRecords"), , adOpenDynamic, adLockOptimistic
Me.Recordset = rst
Me.Requery
end sub

Thanks

 

[Dirk Goldgar]

Hi guys, these groups are amazing, thanks for taking the time.

I have a form based on a saved query (qryfrmActiveRecords). I want to
add a checkbox to the form that shows all records, ie. active and
inactive (qryfrmAllRecords).

How can I change the recordsource of the form using code?

Ideally it would look at another saved query.


Private Sub chkShowAll_Loads_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open ("qryfrmAllRecords"), , adOpenDynamic, adLockOptimistic
Me.Recordset = rst
Me.Requery
end sub

Thanks

That's more complicated than you need. Try something like this:

Private Sub chkShowAll_Loads_AfterUpdate()

If Me!chkShowAll_Loads = True Then
Me.RecordSource = "qryfrmAllRecords"
Else
Me.RecordSource = "qryfrmActiveRecords"
End If

End Sub

 

[Santiago Gomez]

very good to know, thanks a million

That's more complicated than you need. Try something like this:

Private Sub chkShowAll_Loads_AfterUpdate()

If Me!chkShowAll_Loads = True Then
Me.RecordSource = "qryfrmAllRecords"
Else
Me.RecordSource = "qryfrmActiveRecords"
End If

End Sub

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)