Search Form stops working on Exit

[chickalina]

I have a search form with a combo button. There are 6 different combo boxes
to choose from for criteria. Below is the code... it stops working after the
program closes and re-opens. If I cut and paste it works again until I close.
Private Sub cmdRunReport_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strCountry As String
Dim strStructural As String
Dim strCategory As String
Dim strJurisdiction As String
Dim strBenefitType As String
Dim strCurrentStatus As String
Dim strHWContact As String
Dim strExternalContact As String
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qry_planninglookup")


'THIS CODE IS TO BE USED IF YOU MUST HAVE ALL FIELDS SATISFIED

' If IsNull(cbocategory) Or IsNull(cbojurisdiction) Then
' MsgBox "You must choose both one of the criteria." _
' & vbCrLf & "Please try again.", vbExclamation, _
' "More information required."
' Exit Sub
' End If
' DoCmd.OpenQuery "qry_PlanningLookup", acViewNormal, acEdit
' DoCmd.Close acForm, "frm_ideas_bank"
'KEEP IN MIND THAT IF YOU OPEN THE QUERY THROUGH THE DATABASE, IT WILL USE THE
'CRITERIA PREVIOUSLY USED.


'THIS "IF ISNULL" CODE HERE IS USED IF THE USER LEAVES ONE OF THE FIELDS BLANK
'THE RETURN WOULD BE BASED ON ONLY THE OPTIONS CHOSEN, AND USE ALL OF THE
'CRITERIA MET ON THE THIRD.


If IsNull(Me.cboCountry.Value) Then
strCountry = " Like '*' "
Else
strCountry = "='" & Me.cboCountry.Value & "' "
End If

If IsNull(Me.cboCategory.Value) Then
strCategory = " Like '*' "
Else
strCategory = "='" & Me.cboCategory.Value & "' "
End If

If IsNull(Me.cboStructural.Value) Then
strStructural = " Like '*' "
Else
strStructural = "='" & Me.cboStructural.Value & "' "
End If

If IsNull(Me.cboCurrentStatus.Value) Then
strCurrentStatus = " Like '*' "
Else
strCurrentStatus = "='" & Me.cboCurrentStatus.Value & "' "
End If

If IsNull(Me.cboJurisdiction.Value) Then
strJurisdiction = " Like '*' "
Else
strJurisdiction = "='" & Me.cboJurisdiction.Value & "' "
End If

If IsNull(Me.cboBenefitType.Value) Then
strBenefitType = " Like '*' "
Else
strBenefitType = "='" & Me.cboBenefitType.Value & "' "
End If

If IsNull(Me.cboHWContact.Value) Then
strHWContact = " Like '*' "
Else
strHWContact = "='" & Me.cboHWContact.Value & "' "
End If

If IsNull(Me.cboExternalContact.Value) Then
strExternalContact = " Like '*' "
Else
strExternalContact = "='" & Me.cboExternalContact.Value & "' "
End If

strSQL = "SELECT tbl_ideas_bank.* " & _
"FROM tbl_ideas_bank " & _
"WHERE tbl_ideas_bank.countryid " & strCountry & _
"AND tbl_ideas_bank.ideacategory " & strCategory & _
"AND tbl_ideas_bank.structural " & strStructural & _
"AND tbl_ideas_bank.currentstatus " & strCurrentStatus & _
"AND tbl_ideas_bank.ideajurisdiction " & strJurisdiction & _
"AND tbl_ideas_bank.benefittype " & strBenefitType & _
"AND tbl_ideas_bank.externalcontact " & strExternalContact
& _
"ORDER BY tbl_ideas_bank.ideadescription;"

qdf.SQL = strSQL
DoCmd.OpenQuery "qry_PlanningLookup"
DoCmd.Close acForm, Me.Name
Set qdr = Nothing
Set db = Nothing


End Sub
Thanks for your help!

 

[Allen Browne]

That sounds like a corruption in your database.

To solve it, follow this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
and the code syntax is compilable.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

 

[chickalina]

Allen,
Thanks! I'll take those steps....
If I still have any problems, I'll post again.
M

 

[chickalina]

Allen,
Still having problems... Is the code correct? There must be something else.
If I remove the last three items on the SELECT.. WHERE list, then the form
works. I don't see any problems with the code though. They match the tbl and
the form.
Creepy.

 

[Allen Browne]

Obviously I can't test your code, but add:
Debug.Print strSql

When it fails, open the Immediate Window (Ctrl+G) and copy the SQL
statement. Paste it into the SQL View of a new query, and see if it works.
Look for things like missing spaces (so the AND is running into the previous
word), or missing delimiters (quotes wrong.)

Adding some brackets may help too, e.g.:
"WHERE (tbl_ideas_bank.countryid " & strCountry & ") " & _
"AND (tbl_ideas_bank.ideacategory " & strCategory & ") " & _

 

[OldFatherWilliam+]

I had the same problem, but a tech person at EVERYTHINGACCESS found a M-S
Knowedge Base article that said that, with ANSI 92 installed, the RowSource
for a combo box has to start with SELECT DISTINCT, not just SELECT.

....OldFatherWilliam