Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    39

    Unanswered: Debug code for 424 object / syntax error

    Hi guys, not sure where im going wrong with this line of code. It's being used as part of a search form, however, 99% of the time it keeps coming up with the 424 error, then when i think i have it fixed, i get a syntax error. Any suggestions would be gratefully received

    Form_frmSearch1.RecordSource = "select * from BACKUPQUERY_TBL where " & GCriteria"

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Me.RecordSource = "SELECT tableName.* FROM tableName WHERE(((tableName.fieldName)= ???what));"

  3. #3
    Join Date
    Jan 2010
    Posts
    39
    Ok, so I've managed to get rid of the runtime errors, however my search form isnt actually filtering the data!
    Could someone have a look over the code for me please and point me in the right direction, its not my fortey coding, so if its messy, im sorry!

    Code:
    Private Sub cmdSearch_Click()
    
        Dim LCaption As String
    
        'First search condition is mandatory
        If Len(cboSearchField1) = 0 Or IsNull(cboSearchField1) = True Then
            MsgBox "First search condition:  You must select a field to search."
            
        ElseIf Len(cboSearchOperation1) = 0 Or IsNull(cboSearchOperation1) = True Then
            MsgBox "First search condition:  You must select a search operation."
            
        ElseIf Len(txtSearchValue1) = 0 Or IsNull(txtSearchValue1) = True Then
            MsgBox "First search condition:  You must enter a search value."
            
            
        'Second search condition must be completed if started
        ElseIf Len(cboSearchField2) > 0 And (Len(cboSearchOperation2) = 0 Or IsNull(cboSearchOperation2) = True) Then
            MsgBox "Second search condition:  You must select a search operation."
            
        'Second search condition must be completed if started
        ElseIf Len(cboSearchField2) > 0 And (Len(txtSearchValue2) = 0 Or IsNull(txtSearchValue2) = True) Then
            MsgBox "Second search condition:  You must enter a search value."
            
        'Third search condition must be completed if started
        ElseIf Len(cboSearchField3) > 0 And (Len(cboSearchOperation3) = 0 Or IsNull(cboSearchOperation3) = True) Then
            MsgBox "Third search condition:  You must select a search operation."
            
        'Third search condition must be completed if started
        ElseIf Len(cboSearchField3) > 0 And (Len(txtSearchValue3) = 0 Or IsNull(txtSearchValue3) = True) Then
            MsgBox "Third search condition:  You must enter a search value."
            
        Else
        
            'Generate search criteria for first condition
            Select Case cboSearchOperation1.Value
                Case "contains"
                    GCriteria = cboSearchField1.Value & " LIKE '*" & txtSearchValue1 & "*'"
                    LCaption = "BACKUPQUERY_TBL (" & cboSearchField1.Value & " contains '*" & txtSearchValue1 & "*'"
                    
                Case "is greater than"
                    GCriteria = cboSearchField1.Value & " > " & txtSearchValue1
                    LCaption = "BACKUPQUERY_TBL (" & cboSearchField1.Value & " > " & txtSearchValue1 & ""
                
                Case "is less than"
                    GCriteria = cboSearchField1.Value & " < " & txtSearchValue1
                    LCaption = "BACKUPQUERY_TBL(" & cboSearchField1.Value & " < " & txtSearchValue1 & ""
                
                Case "is equal to"
                    GCriteria = cboSearchField1.Value & " = " & txtSearchValue1
                    LCaption = "BACKUPQUERY_TBL (" & cboSearchField1.Value & " = " & txtSearchValue1 & ""
            End Select
            
            'Generate search criteria for second condition
            If Len(cboSearchField2) > 0 And Len(cboSearchOperation2) > 0 And Len(txtSearchValue2) > 0 Then
                Select Case cboSearchOperation2.Value
                    Case "contains"
                        GCriteria = GCriteria & " and " & cboSearchField2.Value & " LIKE '*" & txtSearchValue2 & "*'"
                        LCaption = LCaption & " and " & cboSearchField2.Value & " contains '*" & txtSearchValue2 & "*'"
                        
                    Case "is greater than"
                        GCriteria = GCriteria & " and " & cboSearchField2.Value & " > " & txtSearchValue2
                        LCaption = LCaption & " and " & cboSearchField2.Value & " > " & txtSearchValue2 & ""
                    
                    Case "is less than"
                        GCriteria = GCriteria & " and " & cboSearchField2.Value & " < " & txtSearchValue2
                        LCaption = LCaption & " and " & cboSearchField2.Value & " < " & txtSearchValue2 & ""
                    
                    Case "is equal to"
                        GCriteria = GCriteria & " and " & cboSearchField2.Value & " = " & txtSearchValue2
                        LCaption = LCaption & " and " & cboSearchField2.Value & " = " & txtSearchValue2 & ""
                End Select
            End If
            
            'Generate search criteria for third condition
            If Len(cboSearchField3) > 0 And Len(cboSearchOperation3) > 0 And Len(txtSearchValue3) > 0 Then
                Select Case cboSearchOperation3.Value
                    Case "contains"
                        GCriteria = GCriteria & " and " & cboSearchField3.Value & " LIKE '*" & txtSearchValue3 & "*'"
                        LCaption = LCaption & " and " & cboSearchField3.Value & " contains '*" & txtSearchValue3 & "*'"
                        
                    Case "is greater than"
                        GCriteria = GCriteria & " and " & cboSearchField3.Value & " > " & txtSearchValue3
                        LCaption = LCaption & " and " & cboSearchField3.Value & " > " & txtSearchValue3 & ""
                    
                    Case "is less than"
                        GCriteria = GCriteria & " and " & cboSearchField3.Value & " < " & txtSearchValue3
                        LCaption = LCaption & " and " & cboSearchField3.Value & " < " & txtSearchValue3 & ""
                    
                    Case "is equal to"
                        GCriteria = GCriteria & " and " & cboSearchField3.Value & " = " & txtSearchValue3
                        LCaption = LCaption & " and " & cboSearchField3.Value & " = " & txtSearchValue3 & ""
                End Select
            End If
            
            LCaption = LCaption & ")"
            
            'Filter frmCustomers based on search criteria
            Me.RecordSource = "SELECT BACKUPQUERY_TBL.* FROM BACKUPQUERY_TBL WHERE(((BACKUPQUERY_TBL.CLIENT) ));"
            Form_Search1.Caption = LCaption
            
            'Close frmSearch
            DoCmd.Close acForm, "Search2"
            
            MsgBox "Results have been filtered."
            
        End If
        
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •