WojtekKowalski
New member
- Local time
- Today, 13:27
- Joined
- Jan 17, 2021
- Messages
- 5
Hi
I have one main form(place where I filter content in subform by comboboxes) and one subform(datasheet where data are being filtered).
I have to work on data what come from sql server. I don't wont to use standard query based on linked tables as subform's Record Source, because it doesn't work that fast as in case where I have linked tables from MS Access database stored on same PC.
I found this code and placed in on "Open event" in subform.
It works perfectly fine. But I'm wondering how am I suppose to filter subform based on something like this from my main form.
I thought about running same code with small modification on event "After Update" in combobox
But unfortunately it seems I can not set subform recordset from main form and secondly doing this I would need to get data from sql server each time I set some filter.
Setting filter on subform also doesn't work - it seems to be not possible to use it once there is Recordset in Subform Record Source.
What's the best way to filter subform from comboboxes on main form, taking into consideration there is Recordset set in Record Source Subform always on "Open form" event.
I have one main form(place where I filter content in subform by comboboxes) and one subform(datasheet where data are being filtered).
I have to work on data what come from sql server. I don't wont to use standard query based on linked tables as subform's Record Source, because it doesn't work that fast as in case where I have linked tables from MS Access database stored on same PC.
I found this code and placed in on "Open event" in subform.
Code:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
'Use the ADO connection that Access uses
Set cn = CurrentProject.AccessConnection
'Create an instance of the ADO Recordset class,
'and set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM dbo_Stan"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
It works perfectly fine. But I'm wondering how am I suppose to filter subform based on something like this from my main form.
I thought about running same code with small modification on event "After Update" in combobox
Code:
...
rs.Filter = "TValue=" & Me.MyComboBox
Set Me.MySubform.Recordset = rs
...
Setting filter on subform also doesn't work - it seems to be not possible to use it once there is Recordset in Subform Record Source.
What's the best way to filter subform from comboboxes on main form, taking into consideration there is Recordset set in Record Source Subform always on "Open form" event.