Requery SubForm

[Exodus]

I'm trying to requry a sub form based either or both text boxes. I need help with the code. I'm trying to rewrite something from a sample but can't seem to get it to use the criteria. It does requery though.

Private Sub cmdSearch_Click()
       Dim sCriteria As String
        sCriteria = "WHERE 1=1 "


       
        If Me![Search] <> "" Then
            sCriteria = sCriteria & " AND MedicalRecordsUpdating.SerialNumber  """ & Search & """"
        End If

        If Me![SearchEvent] <> "" Then
                sCriteria = sCriteria & " AND MedicalRecordsUpdating.Event  """ & SearchEvent & """"
        End If

        
        
        Forms![RecordManagment]![FrmMedicalRecordsUpdating].Form.Requery        
        
End Sub

Can some one steer me in the right direction

 

[Exodus]

Ok I get a syntax error
Missing operator in qury expresion 1=1 And
medicalrecordsupdating.serial "30000" And
medicalrecordsupdating.event "Acceptance Testing"

whats the problem besides the fact I don't know VB

Private Sub Command14_Click()
        Dim sCriteria As String
        Dim sSql As String
        sCriteria = "WHERE 1=1 "


       
        
        'The source for this code can either be from a table or query
        If Me![Search] <> "" Then
            sCriteria = sCriteria & " AND MedicalRecordsUpdating.Serial  """ & Search & """"
        End If

        If Me![SearchEvent] <> "" Then
                sCriteria = sCriteria & " AND MedicalRecordsUpdating.Event """ & SearchEvent & """"
        End If
        sSql = "SELECT DISTINCT [Serial],[OccUranceDate],[Event],[Issue],[ResolutionComments],[ResolvedDate] from MedicalRecordsUpdating " & sCriteria
        
        Forms![RecordManagment]![FrmMedicalRecordsUpdating].Form.RecordSource = sSql
        Forms![RecordManagment]![FrmMedicalRecordsUpdating].Form.Requery
        
        
End Sub

 

[maikon]

What's the sCriteria function?

I think "Where 1=1" always returns an error...

Please, say what do you want with your code...

 

[Exodus]

I have no idea what the "Where 1=1" does.
I have barrowed the code from an example.

I'm trying to requry a sub form based either or both text boxes.

here is the original

Private Sub cmdSearch_Click()
On Error Resume Next

    Dim sSql As String
    Dim sCriteria As String
        sCriteria = "WHERE 1=1 "


        'This code is for a specific search where you will need to enter the exact string
        'The source for this code can either be from a table or query
        If Me![Index] <> "" Then
                sCriteria = sCriteria & " AND qrySearchCriteriaSub.Index = """ & Index & """"
        End If

        'This code is for a Like search where can enter part of a string
        'The source for this code can either be from a table or query
        If Me![Title] <> "" Then
            sCriteria = sCriteria & " AND qrySearchCriteriaSub.Title like """ & Title & "*"""
        End If

        If Me![AreaCode] <> "" Then
                sCriteria = sCriteria & " AND qrySearchCriteriaSub.AreaCode Like """ & AreaCode & "*"""
        End If

        If Me![NewsPaper] <> "" Then
                sCriteria = sCriteria & " AND qrySearchCriteriaSub.NewsPaper = """ & NewsPaper & """"
        End If

        If Me![StartDate] <> "" And EndDate <> "" Then
            sCriteria = sCriteria & " AND qrySearchCriteriaSub.DateOfPaper between #" & Format(StartDate, "dd-mmm-yyyy") & "# and #" & Format(EndDate, "dd-mmm-yyyy") & "#"
        End If

        If Me![Subject] <> "" Then
            sCriteria = sCriteria & " AND qrySearchCriteriaSub.Subject like """ & Subject & "*"""
        End If
        
        If Nz(DCount("*", "qrySearchCriteriaSub", Right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
        sSql = "SELECT DISTINCT [NewsPaperID],[Index],[Title],[AreaCode],[NewsPaper],[DateofPaper],[Subject] from qrySearchCriteriaSub " & sCriteria
        Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.RecordSource = sSql
        Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.Requery
        Else
        MsgBox "The search failed find any records" & vbCr & vbCr & _
        "that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
        End If
End Sub

 

[Exodus]

Got It need the = for the criteria.
Pbaldy helped me.