Corrupt Code?

[Krystoff]

Hello all,

I have some code that seems to corrupt my Access Project everytime I execute it. Everything in my database runs fine before I run the code but afterwards, every button I have gives me a "You can't carry out this action at the present time." Am I not declaring something right?

Here is the code:

*****************
Private Sub Do_Search_Click()
On Error GoTo HandleErr
Dim strSQL As String

If IsNull(Search1) Then
MsgBox "There is no Search Criteria."
Exit Sub
End If

Select Case Search1
Case "PriSSN"
strSQL = "SELECT Data_Cust.CustID, Data_Cust.PriSSN, Data_Cust.PriFName, Data_Cust.PriLName, Data_Cust.Snp_Num, Data_Cust.Cre_User, Data_Cust.Cre_Date, Data_Cust.SecSSN, Data_Cust.SecFName, Data_Cust.SecLName, Tbl_PB_Full.PB_Full , Data_Cust.B_TaxID FROM Data_Cust INNER JOIN Tbl_PB_Full ON Data_Cust.PB_SSN = Tbl_PB_Full.PB_SSN WHERE Data_Cust.PriSSN = " & Me!Search_Field1
Me!Search_List.Visible = True
Me!Search_List.Form.RecordSource = strSQL
Me!Search_List.Form.Requery
Case "PriLName"
With CodeContextObject
strSQL = "SELECT Data_Cust.CustID, Data_Cust.PriSSN, Data_Cust.PriFName, Data_Cust.PriLName, Data_Cust.Snp_Num, Data_Cust.Cre_User, Data_Cust.Cre_Date, Data_Cust.SecSSN, Data_Cust.SecFName, Data_Cust.SecLName, Tbl_PB_Full.PB_Full , Data_Cust.B_TaxID FROM Data_Cust INNER JOIN Tbl_PB_Full ON Data_Cust.PB_SSN = Tbl_PB_Full.PB_SSN WHERE Data_Cust.PriLName = " & Me!Search_Field1
Me!Search_List.Visible = True
Me!Search_List.Form.RecordSource = strSQL
Me!Search_List.Form.Requery
End With
Case Else
MsgBox "The Else portion of the CASE statement ran"
End Select

ExitHere:
Exit Sub
HandleErr:
MsgBox Err.Description
Resume ExitHere

End Sub
*******************************

Any help is appreciated

 

[mp9]

Without knowing too much, for example where you get

Search1

, I'd try:

*****************
Private Sub Do_Search_Click()
On Error GoTo HandleErr
Dim strSQL As String

If IsNull(Search1) Then
    MsgBox "There is no search criterion."
Else
    Select Case Search1
    Case "PriSSN"
        strSQL = "SELECT Data_Cust.CustID, Data_Cust.PriSSN, Data_Cust.PriFName, Data_Cust.PriLName, Data_Cust.Snp_Num, Data_Cust.Cre_User, Data_Cust.Cre_Date, Data_Cust.SecSSN, Data_Cust.SecFName, Data_Cust.SecLName, Tbl_PB_Full.PB_Full , Data_Cust.B_TaxID FROM Data_Cust INNER JOIN Tbl_PB_Full ON Data_Cust.PB_SSN = Tbl_PB_Full.PB_SSN WHERE Data_Cust.PriSSN = " & Me!Search_Field1 & ";"
        Me!Search_List.Visible = True
        Me!Search_List.Form.RecordSource = strSQL
        Me!Search_List.Form.Requery
    Case "PriLName"
        strSQL = "SELECT Data_Cust.CustID, Data_Cust.PriSSN, Data_Cust.PriFName, Data_Cust.PriLName, Data_Cust.Snp_Num, Data_Cust.Cre_User, Data_Cust.Cre_Date, Data_Cust.SecSSN, Data_Cust.SecFName, Data_Cust.SecLName, Tbl_PB_Full.PB_Full , Data_Cust.B_TaxID FROM Data_Cust INNER JOIN Tbl_PB_Full ON Data_Cust.PB_SSN = Tbl_PB_Full.PB_SSN WHERE Data_Cust.PriLName = " & Me!Search_Field1 & ";"
        Me!Search_List.Visible = True
        Me!Search_List.Form.RecordSource = strSQL
        Me!Search_List.Form.Requery
    Case Else
        MsgBox "The Else portion of the CASE statement ran"
    End Select
End If

ExitHere:
    Exit Sub
HandleErr:
    MsgBox Err.Description
    Resume ExitHere

End Sub
*******************************

http://www22.brinkster.com/accessory/

 

[Krystoff]

Sorry but that didn't help. I left the ; in just in case though.

What happens is every button on my form gives me the:
"You can't carry out this action at the present time."

I can't close out of the form normally and I can't design my form either. I have to close out of the entire database to get it to work again.

Any ideas?

 

[Krystoff]

Ok I figured out the problem but I still have no idea why.

The Me!Search_List.Form.Requery was messing everything up.

I ran through my code taking stuff out and that was the problem. Now everything seems to work great.

Thanks for the help MP9!

Chris