Query works for 2 out of 4 status' help

yeh I added outstanding and tweaked the others.

I am using insert chart function then selecting the appropriate query, but it comes up with object variable nnot set or something so I added it to a normal form and copied the active x object in i think it uses ole automation. this is where I struggle as any changes i make afterwords do not get saved it returns to how it was on the orginal form including size, I will give it a go over the weekend and surely post again if I have no luck.

Thanks again
 
I'm trying to remember whether Access 2000 included Pivot Charts. (Isn't it time to upgrade?) They're much easier to work with than the Chart Object (Chart Wizard).
 
Hi Texan need more help, trying to modify your query and I am stuck I am trying to setup a date filter by year only and I am getting an error problem is where the blue bit I highlighted. I am trying to only use the year from the Date logged based on how you coded the rest. Thanks In advance.

Private Sub ApplyFilters(strFilter As String)
Dim strComboFilter As String
' Fix up all the other row sources
Me.No.RowSource = "SELECT [tblInvestigations].[No] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [No];"
' If there is a value in No,
If Not IsNull(Me.No) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("No", "qryInvestigations", _
strFilter & _
" And ([No] = " & Me.No & ")")) Then
' Clear the No box
Me.No = Null
End If
End If
Me.Investigator.RowSource = "SELECT DISTINCT [tblInvestigations].[Investigator] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [Investigator];"
' If there is a value in Investigator,
If Not IsNull(Me.Investigator) Then
' If the current value of the Investigator control is no longer in the filtered set
If IsNull(DLookup("Investigator", "tblInvestigations", _
strFilter & _
" And ([Investigator] = '" & Me.Investigator & "')")) Then
' Clear the No box
Me.Investigator = Null
End If
End If
Me!Section.RowSource = "SELECT DISTINCT [tblInvestigations].[Section] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [Section];"
' If there is a value in Section,
If Not IsNull(Me!Section) Then
' If the current value of the Source control is no longer in the filtered set
If IsNull(DLookup("Section", "tblInvestigations", _
strFilter & _
" And ([Section] = '" & Me!Section & "')")) Then
' Clear the No box
Me!Section = Null
End If
End If
Me.Source.RowSource = "SELECT DISTINCT [tblInvestigations].[Source] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [Source];"
' If there is a value in Source,
If Not IsNull(Me.Source) Then
' If the current value of the Source control is no longer in the filtered set
If IsNull(DLookup("Source", "tblInvestigations", _
strFilter & _
" And ([Source] = '" & Me.Source & "')")) Then
' Clear the No box
Me.Source = Null
End If
End If
Me.Method.RowSource = "SELECT DISTINCT [tblInvestigations].[Method] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [Method];"
' If there is a value in Method,
If Not IsNull(Me.Method) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("Method", "tblInvestigations", _
strFilter & _
" And ([Method] = '" & Me.Method & "')")) Then
' Clear the No box
Me.Method = Null
End If
End If
Me.Ref1.RowSource = "SELECT DISTINCT [tblInvestigations].[Ref1] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [Ref1];"
' If there is a value in Ref1,
If Not IsNull(Me.Ref1) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("Ref1", "tblInvestigations", _
strFilter & _
" And ([Ref1] = '" & Me.Ref1 & "')")) Then
' Clear the No box
Me.Ref1 = Null
End If
End If
Me.YearRange.RowSource = "SELECT DISTINCT Year([tblInvestigations].[Date Logged]) " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY Year([Date Logged]);"
' If there is a value in Ref1,
If Not IsNull(Me.YearRange) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("Date Logged", "tblInvestigations", _
strFilter & _
" And Year([Date Logged] = '" & Me.YearRange & "')")) Then
' Clear the No box
Me.YearRange = Null
End If
End If
' Add back the combo box filters, if any
If Not IsNull(Me.No) Then
strComboFilter = " And ([No] = " & Me.No & ")"
End If
If Not IsNull(Me.Investigator) Then
strComboFilter = strComboFilter & " And ([Investigator] = '" & Me.Investigator & "')"
End If
If Not IsNull(Me!Section) Then
strComboFilter = strComboFilter & " And ([Section] = '" & Me!Section & "')"
End If
If Not IsNull(Me.Source) Then
strComboFilter = strComboFilter & " And ([Source] = '" & Me.Source & "')"
End If
If Not IsNull(Me.Method) Then
strComboFilter = strComboFilter & " And ([Method] = '" & Me.Method & "')"
End If
If Not IsNull(Me.Ref1) Then
strComboFilter = strComboFilter & " And ([Ref1] = '" & Me.Ref1 & "')"
End If
If Not IsNull(Me.YearRange) Then
strComboFilter = strComboFilter & " And Year([DateLogged]) = '" & Me.YearRange & "')"
End If
' Set the subform filter
Me.frmInvestigations.Form.Filter = strFilter & strComboFilter
Me.frmInvestigations.Form.FilterOn = True
' Done
End Sub
 
Russ-

Are you talking about this line?

If Not IsNull(Me.YearRange) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("Date Logged", "tblInvestigations", _
strFilter & _
" And Year([Date Logged] = '" & Me.YearRange & "')")) Then

What's in YearRange? A Date/Time value? If so, then you need to surround it with the # character, like this:

If Not IsNull(Me.YearRange) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("Date Logged", "tblInvestigations", _
strFilter & _
" And Year([Date Logged] = #" & Me.YearRange & "#)")) Then

If you're on a non-US machine, you may also need to convert the date to mm/dd/yyyy format that Access SQL requires, like this:

If Not IsNull(Me.YearRange) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("Date Logged", "tblInvestigations", _
strFilter & _
" And Year([Date Logged] = #'" & Format(Me.YearRange, "mm/dd/yyyy") & "#)")) Then
 
hi Texan, YearRange is a combobox with a list of years created from the Year([tblInvestigations].[Date Logged])
would the same still apply? and is the code right? because I think it still shows me the list of years 2010,2011 and 2012 as all date logged fields match either one of these years, this is so that the user only sees the years the data is for. but it keeps giving me a error for this bit I think

If IsNull(DLookup("Date Logged", "tblInvestigations", _

Thanks
 
Whoa. I just looked at your code more closely. Why in the world are you changing the Row Source of parameter combo boxes when you're trying to assemble a filter? For example,

' Fix up all the other row sources
Me.No.RowSource = "SELECT [tblInvestigations].[No] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [No];"
' If there is a value in No,
If Not IsNull(Me.No) Then

If you change the RowSource, then whatever was selected before you did that may become invalid and there will be nothing there!

As for this block of code:

Me.YearRange.RowSource = "SELECT DISTINCT Year([tblInvestigations].[Date Logged]) " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY Year([Date Logged]);"
' If there is a value in Ref1,
If Not IsNull(Me.YearRange) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("Date Logged", "tblInvestigations", _
strFilter & _
" And Year([Date Logged] = '" & Me.YearRange & "')")) Then
' Clear the No box
Me.YearRange = Null
End If
End If

The value returned by the Year function is an integer, so get rid of the quotes in your DLookup:

If IsNull(DLookup("Date Logged", "tblInvestigations", _
strFilter & _
" And Year([Date Logged]) = " & Me.YearRange) Then

But that still doesn't clear up why you're resetting the Row Source of the criteria combo boxes.
 
Hi it still says syntax error(missing operator) in query expression 'Date Logged'

If IsNull(DLookup("Date Logged", "tblInvestigations", _
strFilter & _
" And Year([Date Logged]) = " & Me.YearRange)) Then

any ideas?

As for your other question thats an error as I have been playing around with it. it should say SELECT DISTINCT
thanks
 
It could be something messed up in what you've built in strFilter. Try a Debug.Print strFilter just before this statement, then look at the Immediate Window (CTRL+G).

As for setting up a filter, I NEVER reset the Row Source of a control containing a filter value as I'm building the filter. I have no clue what you're doing.
 
Hi Texan sorry for not replying, I have corrected the code, used your one corrected for the status' added extra status "INVESTIGATED". Added a Year filter and a Issue filter which is "IS LIKE". All of this works and is fine, currently when I change the status filter the other filters dont repopulate with data from the filtered list i.e if I select Overdue, the No filter should only show me no which are overdue. However, What I also wanted was that say if I select two filters e.g. Section and source, I want all the filters to repopulate with records that match this, of course my main screen is already showing these records, but I dont understand why the filters do not. I can remove some information and upload the db if required. Thanks for your help it is much appreaciated. I have attached all the code on the main form below.

Option Compare Database
Option Explicit
Dim strFilter As String
Private Sub Form_Load()
'On open Maximise form
DoCmd.Maximize
'Adjust form to Monitor Resolution
ReSizeForm Me
' Initialize the "OPEN" filter (default value of the Status combo)
strFilter = "(True) And (IsNull([Closed]))"
' Build the filters
ApplyFilters strFilter
End Sub
Private Sub Investigator_AfterUpdate()
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Investigator_DblClick(Cancel As Integer)
Me.Investigator = Null
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Issue_AfterUpdate()
ApplyFilters strFilter
End Sub
Private Sub Issue_DblClick(Cancel As Integer)
Me.Issue = Null
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Method_AfterUpdate()
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Method_DblClick(Cancel As Integer)
Me.Method = Null
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub No_AfterUpdate()
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub No_DblClick(Cancel As Integer)
Me.No = Null
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Ref1_AfterUpdate()
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Ref1_DblClick(Cancel As Integer)
Me.Ref1 = Null
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Section_AfterUpdate()
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Section_DblClick(Cancel As Integer)
Me!Section = Null
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Source_AfterUpdate()
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Source_DblClick(Cancel As Integer)
Me.Source = Null
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Status_AfterUpdate()
' Reset all filters based on the status
Select Case Me.Status
Case "ALL"
' User wants to remove all filters
' Set the filter to include all rows
strFilter = "(True)"
' Call the common code to apply the filter to
' all the combo boxes and the subform
ApplyFilters strFilter
Case "ALL-CLOSED"
' User wants to remove all filters
' Set the filter to include all rows
strFilter = "(True) And (IsNull([Closed]))"
' Call the common code to apply the filter to
' all the combo boxes and the subform
ApplyFilters strFilter
Case "OPEN+OVERDUE"
' Need a definition for this
strFilter = "((IsNull([Authorised])) And (IsNull([Closed])) And (IsNull([Investigated])))"
ApplyFilters strFilter
Case "OPEN"
' User wants only OPEN cases (No Investigated assigned, due date in the future)
' Set the filter
strFilter = "(IsNull([Investigated])) And ([Due Date]>=Date())"
' Call the common code to apply the filter to
' all the combo boxes and the subform
ApplyFilters strFilter
Case "OVERDUE"
' User wants only OVERDUE cases (No Investigated assigned, and due date is in the past)
' Set the filter
strFilter = "(IsNull([Investigated]) And (IsNull([Authorised]) And (IsNull([Closed]) And ([Due Date]<Date()))))"
' Call the common code to apply the filter to
' all the combo boxes and the subform
ApplyFilters strFilter
Case "INVESTIGATED"
' User wants only INVESTIGATED cases (No Investigated, not Closed, and no Authorised and actions open)
' Set the filter
strFilter = "Not (IsNull([Investigated])) And (IsNull([Authorised])) And (IsNull([Closed])) And Not (IsNull([qryActions3.CountOfNo]))"
' Call the common code to apply the filter to
' all the combo boxes and the subform
ApplyFilters strFilter
Case "COMPLETE"
' User wants only COMPLETE cases (No Investigated, not Closed, and no Authorised)
' Set the filter
strFilter = "Not (IsNull([Investigated])) And (IsNull([Authorised])) And (IsNull([Closed]))"
' Call the common code to apply the filter to
' all the combo boxes and the subform
ApplyFilters strFilter
Case "AUTHORISED"
' User wants only AUTHORIZED cases (Investigated assigned, not closed)
' Set the filter
strFilter = " Not (IsNull([Investigated])) And Not (IsNull([Authorised])) And (IsNull([Closed]))"
' Call the common code to apply the filter to
' all the combo boxes and the subform
ApplyFilters strFilter
Case "CLOSED"
' User wants only CLOSED cases (Closed is set)
' Set the filter
strFilter = "Not (IsNull([Investigated])) And Not (IsNull([Authorised])) And Not (IsNull([Closed]))"
' Call the common code to apply the filter to
' all the combo boxes and the subform
ApplyFilters strFilter
End Select
End Sub
Private Sub ApplyFilters(strFilter As String)
Dim strComboFilter As String
Me.YearRange.RowSource = "SELECT DISTINCT [qryYear].[YearA] " & _
"FROM qryYear " & _
"ORDER BY [qryYear].[YearA];"
' If there is a value in No,
If Not IsNull(Me.YearRange) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("YearA", "qryYear", _
"([qryYear].[YearA] = " & Me.YearRange & ")")) Then
' Clear the No box
Me.YearRange = Null
End If
End If
' Fix up all the other row sources
Me.No.RowSource = "SELECT DISTINCT [tblInvestigations].[No] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [No];"
' If there is a value in No,
If Not IsNull(Me.No) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("No", "tblInvestigations", _
strFilter & _
" And ([No] = " & Me.No & ")")) Then
' Clear the No box
Me.No = Null
End If
End If
Me.Investigator.RowSource = "SELECT DISTINCT [tblInvestigations].[Investigator] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [Investigator];"
' If there is a value in Investigator,
If Not IsNull(Me.Investigator) Then
' If the current value of the Investigator control is no longer in the filtered set
If IsNull(DLookup("Investigator", "tblInvestigations", _
strFilter & _
" And ([Investigator] = '" & Me.Investigator & "')")) Then
' Clear the No box
Me.Investigator = Null
End If
End If
Me!Section.RowSource = "SELECT DISTINCT [tblInvestigations].[Section] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [Section];"
' If there is a value in Section,
If Not IsNull(Me!Section) Then
' If the current value of the Source control is no longer in the filtered set
If IsNull(DLookup("Section", "tblInvestigations", _
strFilter & _
" And ([Section] = '" & Me!Section & "')")) Then
' Clear the No box
Me!Section = Null
End If
End If
Me.Source.RowSource = "SELECT DISTINCT [tblInvestigations].[Source] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [Source];"
' If there is a value in Source,
If Not IsNull(Me.Source) Then
' If the current value of the Source control is no longer in the filtered set
If IsNull(DLookup("Source", "tblInvestigations", _
strFilter & _
" And ([Source] = '" & Me.Source & "')")) Then
' Clear the No box
Me.Source = Null
End If
End If
Me.Method.RowSource = "SELECT DISTINCT [tblInvestigations].[Method] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [Method];"
' If there is a value in Method,
If Not IsNull(Me.Method) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("Method", "tblInvestigations", _
strFilter & _
" And ([Method] = '" & Me.Method & "')")) Then
' Clear the No box
Me.Method = Null
End If
End If
Me.Ref1.RowSource = "SELECT DISTINCT [tblInvestigations].[Ref1] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & " ORDER BY [Ref1];"
' If there is a value in Ref1,
If Not IsNull(Me.Ref1) Then
' If the current value of the No control is no longer in the filtered set
If IsNull(DLookup("Ref1", "tblInvestigations", _
strFilter & _
" And ([Ref1] = '" & Me.Ref1 & "')")) Then
' Clear the No box
Me.Ref1 = Null
End If
End If
' Add back the combo box filters, if any
If Not IsNull(Me.No) Then
strComboFilter = " And ([No] = " & Me.No & ")"
End If
If Not IsNull(Me.YearRange) Then
strComboFilter = strComboFilter & " And (Year([Date Logged]) = '" & Me.YearRange & "')"
End If
If Not IsNull(Me.Issue) Then
strComboFilter = strComboFilter & " And ([Issue] Like '*" & [Forms]![frmMain]![Issue] & "*')"
End If
If Not IsNull(Me.Investigator) Then
strComboFilter = strComboFilter & " And ([Investigator] = '" & Me.Investigator & "')"
End If
If Not IsNull(Me!Section) Then
strComboFilter = strComboFilter & " And ([Section] = '" & Me!Section & "')"
End If
If Not IsNull(Me.Source) Then
strComboFilter = strComboFilter & " And ([Source] = '" & Me.Source & "')"
End If
If Not IsNull(Me.Method) Then
strComboFilter = strComboFilter & " And ([Method] = '" & Me.Method & "')"
End If
If Not IsNull(Me.Ref1) Then
strComboFilter = strComboFilter & " And ([Ref1] = '" & Me.Ref1 & "')"
End If
' Set the subform filter
Me.frmInvestigations.Form.Filter = strFilter & strComboFilter
Me.frmInvestigations.Form.FilterOn = True
' Done
End Sub
Private Sub Status_BeforeUpdate(Cancel As Integer)
strFilter = "(True)"
End Sub
Private Sub AddNewRecords_Click()
On Error GoTo Err_AddNewRecords_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmInvForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_AddNewRecords_Click:
Exit Sub
Err_AddNewRecords_Click:
MsgBox err.Description
Resume Exit_AddNewRecords_Click
End Sub
Private Sub YearRange_AfterUpdate()
' Rebuild the filters
ApplyFilters strFilter
End Sub
Private Sub Exit_DB_Click()
On Error GoTo Err_Exit_DB_Click
DoCmd.Quit
Exit_Exit_DB_Click:
Exit Sub
Err_Exit_DB_Click:
MsgBox err.Description
Resume Exit_Exit_DB_Click
End Sub
 
Ruzz-

Might be easier to figure out if you can post a sample of the database.
 
Hi Texan, please find sample of db. login = texan.paris password = test.
should look familiar
Thanks in advance.
 
Ruzz-

After looking at your database, I still can't see what you're trying to do. You seem to be resetting strFilter whenever the Status changes. What else do you want to happen, and what should the code do about filters already set for things like Investigator and Section?

Why aren't you just including the current Status setting in your ApplyFilters code?
 
Maybe it would help if you wrote out what you expect to happen when the user updates each combo box.
 
For example if you select Amy Hardie as the investigator, I want the other filters, Section, Source, Method and Ref1 to show the same as the filtered records e.g. the Source filter should now only have "AQC Review", and section "Inorg1", method "30" etc hope that makes sense.
 
just had a thought should be refering these to the query rather than the table as you originally set up for me? would that do the trick?
The year and status filters are primary filters the user has to select these or is set by deafult, whereas the others are secondary i.e based on what is selected in Year and Status will depend on what the other filters will show but also what is selects in each of those secondary filters should effect each other, i.e getting a very narrowed down list. If it still does not make sense I could explain by laying down various examples which might help
 
Ruzz-

So, you want the other filters to reflect only values valid for "Amy Hardie" once Investigator is selected? What do you do if there is an existing filter, for example, for Section that doesn't match any Amy Hardie records? Clear it?

I think you need to lay it all out for yourself more than me. When user picks "X", "Y" should happen. You'll need to cover all the permutations, including the cases where the user has already selected some values in other filters. Once you lay that all out, I think it'll be clear to you what you need to do.
 

Users who are viewing this thread

Back
Top Bottom