June7 has another valid method. Since there are so many ways to do just about anything, part of the learning process is what is neater, or more buggy or sloppier or just plain not right for the situation over some other method. We all have our favouites that we seem to remember best as well. So here's something I put together. You cannot use it as code verbatim - it is missing the error line label that I put in at the beginning and who knows what else. No sense in coding it all for you as I don't know which approach you will settle on. It is also full of comments not intended to be part of a code module.
You are testing for empty string "" which is likely OK for current use, but "" is not Null, so unless you test for both or know for sure it can be one and not the other, why not test for both by calling a function any time you want to know, and only write the code that tests one time? I recommend this snippet in a standard module called mdlCommonFunctions for stuff that gets used frequently. See the Select Case part below for how it is called and how to build a list of control names that are missing data.
Function HasNoData(vCheckVal As Variant) As BooleanHasNoData = False
If IsNull(vCheckVal) Or vCheckVal = "" Then HasNoData = True
End Function
Dim ctl As Control
Dim svList as string, svList2 as string, svFilter as string
Dim svCanRfiNum as string, svWebRfiNum as string, svDesc as string
on error goto errHandler
svList = "Please enter a value for: " & vbCrLf
svlist2 = ""
For Each ctl In Me.ControlsSelect Case ctl.ControlType
'you can separate cases and customize the message if needed
Case acTextBox, acComboBox 'add more comma separated if needed
If HasNoData(ctl) Then svlist2 = svlist2 & ctl.Controls.Item(0).Caption & vbCrLf
End Select
Next
If svlist2 <> "" ThenMsgBox svList & svlist2, vbOKOnly, "Missing Information"
Exit Sub
End If
don't think you need to test if Cancel = False but doesn't hurt
You appear to be working with listboxes, but I don't follow their relevance. The filter criteria seems to be coming from Me.controlSomething that is not a listbox. If so, why not reference the listbox item that has been selected if that's what you're using as filter criteria? An example:
svJobId = Me.lstJobList.Column(1)
svDept = Me.lstJobList.Column(0)
svFilter = "JobID=" & svJobId & " AND Dept='" & svDept & "'"
'I'd assign the variables here, not before the possible exit sub statement
'Using your code as a guide:
svCanRfiNum = Me.CAN_RFI_Number
svWebRfiNum = Me.Webcor_RFI_Number
svDesc = Me.Description
'BTW, why are you insinuating the values are numbers [CAN_RFI_Number] if they are text (strings)? Your fields, control names and variables should indicate the data type as much as is practical and you should read up on naming coventions for Access if you have not already. It helps to debug code if you name a varable intSomething - you are looking for an integer type and if debugging shows its value is text, it may immediately solve a problem. Also, I NEVER,NEVER,EVER use spaces or special characters (-) in naming anything. You are just asking for more work at best, and at worst, more problems.
svFilter = "CAN_RFI_Number = '" & svCanRfiNum & "' AND Webcor_RFI_Number = '" & svWebRfiNum
svFilter = svFilter & "' AND Description = '" & svDesc & "'"
This next part as you have written, closes your form and passes the string as the open args in that form, specify that the filter = the string and apply the filter. I believe it is good form to always reference the object to be closed. Your way closes the active object which 99% of the time will be safe.
I use Docmd.close acform, "MyForm",acsaveno (or whatever option is desired). Note that if you use acsaveno and allow code to close a form whose design you have edited, it will not save your unsaved changes
If Me.myListBox.Value = 1 ThenDoCmd.OpenForm "New Change Estimate - RFI", , , , , , svFilter 'this is passing the filter string to the form as an open arg
DoCmd.Close
ElseDoCmd.Close
End If
OK, with respect to your question, here is the method I was referring to. When you get to the part where you open the second form (hidden)
docmd.open "frmMyForm",,,,acHidden & whatever else you might need
reference the database forms collection and a specific form & set the filter property that was created as before and apply it
with CurrentProject.Allforms "frmMyForm".filter = svFilter
.filteron = true
.visible = True
end with
Consider using the allforms reference in cases where a prior exit statement may have prevented opening it. You will get an error if you try to close a form that is not open:
If currentproject.allforms ("frmMyForm").isloaded then docmd.close "frmMyForm" etc.
P.S. not sure about the bracketing. May be required here because of the referende to the isloaded property whereas I didn't use them above.
Hope some of this gets you where you want to be.
Windows 8 
Access 2013
Reply With Quote
A simple spelling mistake will cause errors that you may not even notice right away. In the VBE editor, go to Tools \ Options and check "Require Variable Declaration". It's beyond me why this is even an option. When you do this and compile the code, you will be surprised as to how many potential problems it finds in your code.
