J
Joris Dobbelsteen
I'm currently developing something in Access 2003. This is an extention to an earlier project I developed in Access 2000 and maintained. It still works under Access 2003.
Currently I'm having some issues and I don't seem to get why:
I have a form. In order to reduce load-time information is put in with a query to reduce the amount of data collected.
Basically the form has the following code, triggered when a combobox is updated...
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Public Sub RefreshData()
Dim Query As String, QFilter As String
Query = "SELECT * FROM ModuleAndOrders "
Select Case fraOption.Value
Case 1: 'Name/PR/MR
If IsNull(cmbProductName) Then
Query = Query & "WHERE ModuleAndOrders.ProductNameID = 0" ' selects none...
Else
Query = Query & "WHERE ModuleAndOrders.ProductNameID = " & cmbProductName.Value
' continue with the (optional) PR
If Not IsNull(cmbPR) Then
' this origionally added and AND to the where part...
' in the Query variable
' Because it simply doesn't work, this was the only
' possible workarround I could think of. It sets the filter
' for the form
' It is also better because it saves a requery against
' the data (however it shouldn't care).
QFilter = "((ProductReleaseID=" & cmbPR.Value & "))"
' don't try the line below, it simply fires error: violating a validation field,
' however I can't find none at all!!!
' Query = Query & " AND ProductReleaseID=" & cmbPR.Value
' continue with the (optional) MR
If Not IsNull(cmbMR) Then
QFilter = QFilter & " AND ModuleAndOrders.ModificationRelease = " & cmbMR.Value
End If
End If
End If
Case 2: 'SysteemType
' unsupported of yet
End Select
If Query <> Me.RecordSource Then
Me.AllowFilters = True
' Me.RecordSource = "" ' stupid can't because of violation property something...
Me.RecordSource = Query
End If
FilterForm Me, QFilter, True
End Sub
Public Sub FilterForm(frm As Form, FilterBy As String, Optional Force As Boolean = False)
On Error Resume Next
If Force Or (Not frm.FilterOn) Or frm.Filter <> FilterBy Then ' could save some nice CPU cycles
frm.FilterOn = (FilterBy <> "") ' reversed FilterOn and Filter order, maybe this does work correctly
' Microsoft suggest setting FilterOn in the Form_Load routine.
frm.Filter = FilterBy
End If
End Sub
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Now I'm trying to get the damn filter running. First I set cmbProductName, which work fine as says (in this case):
record 1 of 477.
Setting cmbPR, nicely shows up below:
record 1 of 477 (filtered)
So far so bad: The ... filter is on, yes, but it doesn't filter anything. All my records are still shown here, though they shouldn't!!!
Doing a "Filter From Selection" out of the menu instantly hides all the records.
Back to set cmbProductName again, and we have the records again...
Now do a "Filter From Selection" and it works....
Next attempt: Copy-Paste the existing filter into the code, but replace the value by " & cmbPR.Value
It seems I'm running out of luck or missing something completely.
Please help me fast. The work should be finished by long, but I'm wasting already several hours on this spupid issue...
Thank you...
- Joris
Currently I'm having some issues and I don't seem to get why:
I have a form. In order to reduce load-time information is put in with a query to reduce the amount of data collected.
Basically the form has the following code, triggered when a combobox is updated...
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Public Sub RefreshData()
Dim Query As String, QFilter As String
Query = "SELECT * FROM ModuleAndOrders "
Select Case fraOption.Value
Case 1: 'Name/PR/MR
If IsNull(cmbProductName) Then
Query = Query & "WHERE ModuleAndOrders.ProductNameID = 0" ' selects none...
Else
Query = Query & "WHERE ModuleAndOrders.ProductNameID = " & cmbProductName.Value
' continue with the (optional) PR
If Not IsNull(cmbPR) Then
' this origionally added and AND to the where part...
' in the Query variable
' Because it simply doesn't work, this was the only
' possible workarround I could think of. It sets the filter
' for the form
' It is also better because it saves a requery against
' the data (however it shouldn't care).
QFilter = "((ProductReleaseID=" & cmbPR.Value & "))"
' don't try the line below, it simply fires error: violating a validation field,
' however I can't find none at all!!!
' Query = Query & " AND ProductReleaseID=" & cmbPR.Value
' continue with the (optional) MR
If Not IsNull(cmbMR) Then
QFilter = QFilter & " AND ModuleAndOrders.ModificationRelease = " & cmbMR.Value
End If
End If
End If
Case 2: 'SysteemType
' unsupported of yet
End Select
If Query <> Me.RecordSource Then
Me.AllowFilters = True
' Me.RecordSource = "" ' stupid can't because of violation property something...
Me.RecordSource = Query
End If
FilterForm Me, QFilter, True
End Sub
Public Sub FilterForm(frm As Form, FilterBy As String, Optional Force As Boolean = False)
On Error Resume Next
If Force Or (Not frm.FilterOn) Or frm.Filter <> FilterBy Then ' could save some nice CPU cycles
frm.FilterOn = (FilterBy <> "") ' reversed FilterOn and Filter order, maybe this does work correctly
' Microsoft suggest setting FilterOn in the Form_Load routine.
frm.Filter = FilterBy
End If
End Sub
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Now I'm trying to get the damn filter running. First I set cmbProductName, which work fine as says (in this case):
record 1 of 477.
Setting cmbPR, nicely shows up below:
record 1 of 477 (filtered)
So far so bad: The ... filter is on, yes, but it doesn't filter anything. All my records are still shown here, though they shouldn't!!!
Doing a "Filter From Selection" out of the menu instantly hides all the records.
Back to set cmbProductName again, and we have the records again...
Now do a "Filter From Selection" and it works....
Next attempt: Copy-Paste the existing filter into the code, but replace the value by " & cmbPR.Value
It seems I'm running out of luck or missing something completely.
Please help me fast. The work should be finished by long, but I'm wasting already several hours on this spupid issue...
Thank you...
- Joris