Query works for 2 out of 4 status' help

[Ruzz2k]

yes if the result is nothing then I would like blank, I will write something out and get back to you. Might not be today but I really would like to see this working so I will try my best.

I do have a second issue, on the main form, there is a add new investigation button, "looks like a blank page".

I have 3 buttons, one is save the new investigation, one is exit the form.

The third the "hand" is one which I have not figured out. I want to add the new investigation,(that part is fine), but then I want to close the form and open frmInvForm1 and set it to the investigation I just logged. However currently this form only works for when the user double clicks the investigation number in the main list. Is the only way around this to create an additional form/query. I intend to use this in the multi user environment would it be better to call records directly from the table rather than using a query or is that not an issue?
Thanks for your knowledge. As for the filters I will get back to you as soon as I can.

 

[Ruzz2k]

Hi Texan, the following are all the filters
"Status Filter" + "Year Filter" + "No Filter" + "Investigator Filter" + "Section Filter" + "Source Filter" + "Method Filter" + "Ref1 Filter" + "Issue Filter"
Year and Status are not designed to be empty the others if they are empty should be ignored, however the following could be all populated at the same time or a mixture populated/unpopulated
i.e. every permutation
"No Filter" + "Investigator Filter" + "Section Filter" + "Source Filter" + "Method Filter" + "Ref1 Filter" + "Issue Filter"
So I want the below filters to refresh when the user selects something from any filter or adds anything into the issue filter, producing a narrowed down list.
the only filters that needs refreshing are the following as they are lists

"No Filter" + "Investigator Filter" + "Section Filter" + "Source Filter" + "Method Filter" + "Ref1 Filter"
I could list out every permutation but I dont see how I would code that and also account for when the fields are empty e.g.
bring up All the No. where Status is overdue, year is 2012, Investigator is Amy Hardie, ignore section,source,method, ref1,issue as they are blank.
I think part of it is because I dont fully understand this bit of code you have written for each of the filters. I understand that the first bit pulls a distinct list of records from the table, however Im not confident manipulating this as I always get errors.
If you could show me what I should be doing with correct syntax then Im sure I can write this as I was able to manipulate bits of the code to my needs.
If you require anything from me just let me know I am really pulling my hair out on this one.
' Fix up all the other row sources
Me.No.RowSource = "SELECT DISTINCT [tblInvestigations].[No] " & _
"FROM tblInvestigations " & _
"WHERE " & strFilter & string2 & " 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
Thanks