Select Case doesn't work after change 1

[lastout]

I am trying to develop a search form that allows the user to search for records based on various criteria of their own choice. Right now, I'm testing a Select Case block with some If..Then. statements. Yesterday the Select Case block was working for the most part. Then, to take care of a couple of glitches, I added some code after the Select Case block. Now the Select Case block doesn't work right and I can't, for the life of me, figure out why.

Here's where I'm at. I have a main form with three unbound comboboxes, a Search (OnClick) button, and a subform that shows the results of the search. The comboboxes are cmbProjectID, cmbAgentID, and cmbZipCode. If the user selects a value from cmbProjectID the search will return only the Project record with that ProjectID (i.e. a single record). If the user selects a value from cmbAgentID, it will return all the Projects for that Agent. Likewise for ZipCode. But, if the user selects an Agent AND a ZipCode, it returns all the Projects for that Agent in that ZipCode.

When I open the form anew and do any of the above, the results return correctly. But if I change the selections in the comboboxes, it no longer works (yesterday I was able to make any changes I wanted and the results changed accordingly).

Below is the code following the variable definitions. The MsgBoxes are there so that I can see exactly what case is being selected and what the values of the comboboxes are in each case. Following the code, is a description of what happens at run-time:
---------------------------------------------------------------------------
Select Case True
'THIS CASE IF ONLY PROJECTID HAS VALUE
Case Not IsNull(varProjectID)
MsgBox "ProjectID is " & varProjectID & "AgentID is " & varAgentID & "ZipCode is " & strZipCode
strWhere = ""
strWhere = "(((tblProject.ProjectID)=[forms].[frmSearchProjectTest1].[cmbProjectID]))"
strSQL = strSQL & strWhere & ";"
MsgBox (strSQL & " Case ProjectID only&quot
Me![subfrmqryProjectandZip].Form.RecordSource = strSQL
Case Not IsNull(varAgentID)
'THIS CASE IF ONLY AGENTID HAS VALUE
If IsNull(strZipCode) Then
MsgBox "ProjectID is " & varProjectID & "AgentID is " & varAgentID & "ZipCode is " & strZipCode
strWhere = ""
strWhere = "(((tblProject.AgentID)=[forms].[frmSearchProjectTest1].[cmbAgentID]))"
strSQL = strSQL & strWhere & ";"
MsgBox (strSQL & " Case AgentID only&quot
Me![subfrmqryProjectandZip].Form.RecordSource = strSQL
'THIS CASE IF AGENT AND ZIPCODE HAVE VALUES
ElseIf Not IsNull(strZipCode) Then
MsgBox "ProjectID is " & varProjectID & "AgentID is " & varAgentID & "ZipCode is " & strZipCode
strWhere = ""
strWhere = "(((tblProject.ZipCode)=[forms].[frmSearchProjectTest1].[cmbZipCode]) AND ((tblProject.AgentID)=[forms].[frmSearchProjectTest1].[cmbAgentID]))"
strSQL = strSQL & strWhere & ";"
MsgBox (strSQL & " Case Agent + ZipCode&quot
Me![subfrmqryProjectandZip].Form.RecordSource = strSQL
End If
'THIS CASE IF ONLY ZIPCODE HAS VALUE
Case Not IsNull(strZipCode)
If IsNull(varAgentID) Then
MsgBox "ProjectID is " & varProjectID & "AgentID is " & varAgentID & "ZipCode is " & strZipCode
strWhere = ""
strWhere = "(((tblProject.ZipCode)=[forms].[frmSearchProjectTest1].[cmbZipCode]))"
strSQL = strSQL & strWhere & ";"
MsgBox (strSQL & "Case ZipCode only&quot
Me![subfrmqryProjectandZip].Form.RecordSource = strSQL
End If
'THIS CASE IF ALL THREE COMBOBOXES ARE NULL
Case Else
MsgBox "Does not fit any of the cases."
MsgBox (strSQL)

End Select

'CLEAR COMBOBOXES AFTER RESULTS ARE RETURNED
Me.cmbProjectID = ""
Me.cmbAgentID = ""
Me.cmbZipCode = ""
------------------------------------------------------------------------------------------
Like I said above, when the form is opened anew, the code returns the correct results for the first selection that is made, no matter which combox(es) I fill in. If I then change a combobox, for instance, cmbProjectID, the correct results are still returned. But if I change the selection in cmbAgentID and/or cmbZipCode, the sub seems to default to the first case where cmbProjectID's value is NOT Null. The strSQL I see in the message box is the strSQL for the first case. But the other message box, the one that shows the values of the comboboxes, has just shown me that cmbProjectID's value IS Null.

I don't understand how it can be taking the first case to be true when it is showing me that the ProjectID is null???

The last thing I tried before posting this was to add another button that clears the RecordSource of the subform. I tried clicking this button before making a new selection. Didn't make a difference. The problem persists.

Also, when I said I that this form worked yesterday but then made some changes, the change I made was to add the last three lines that clear the comboboxes. After that, the form didn't work. I have tried taking those lines out, saving the form, closing it, even closing the database, reopening it and doing a compact/repair, but the form has not worked again. lastout (the game's not over till it's over)

 

[ClydeDoggie]

Your combo boxes aren't Null, they are ="" hence the Case Not IsNull(varProjectID) evaluates to True (it's showing an empty string in the messagebox, which looks the same as Null). Try setting them to Null (or vbNull keyword) instead of "".

 

[lastout]

ClydeDoggie,

Thanks SO MUCH! I set them to Null and now it works again! You're a lifesaver.

lastout (the game's not over till it's over)