Multiple criteria search

[jackie77]

Hi all

I am new to access and have been working on creating a database to manage service jobs. I have adapted the search code which was very kindly placed on this forum by Grommit but am having problems getting it to work. To some of you out there this is probably really simple to fix but its most definitely beyond my current experience as after 3 weeks of working on it I am still no further forward. I have attached an example database that I am working on (using access 2007) I would greatly appreciate if someone could have a look and let me know where I am going wrong (bearing in mind this is a work in progress!!), what I am looking for it to do is on the Search_Job form I want to be able to search using a number of different criteria, with the results listed in the subform. If anyone has any suggestions, I am not expecting someone to do the work for me just to give me some advise on a a way of fixing it

Any advice is appricated

Many Thanks
Jackie

 

[jackie77]

Hi everyone

I thought I would save it in access 2003 also, is there anyone out there with any ideas I'm getting desperate!

Jackie

 

[jackie77]

Anybody out there!

 

[thisisntwally]

well i deleted the orphaned 'end sub' and it runs error free.

what exactly is the problem?

edit: ahh here is one. onclick search ==>

Me.Mainsubform.Form.RecordSource = "SELECT * FROM [Main_Report_Query]"

BuildFilter


but it seems theres more...

 

[jackie77]

Hi thanks for having a look, I am very surprised it is operating error free for you, I to have removed the orphaned 'end sub' but I’m still unable to get it to operate, when I search under any of the Booked date, Job Id, House Number, Address boxes and click search nothing happens the results do not show up on the subform or the main form (nothing happens, even when I know it exists), when I search Area the Run time error 3071 appears. What I want it to do is first off display all the query rows in the subform when I enter the search criteria only the results are then displayed in the subform and the first result is displayed on the main form! Not sure how it works for you and not for me and thoughts is it working for anyone else?

Jackie

 

[thisisntwally]

yeah, i spoke too soon, error free <> operating properly.

Im suspicious of how you had things set up, and am currently playing around with something like this: (in case you care to play along at home)

 Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    Dim strSQL As String
    
    varWhere = Null     
    If Me.searchjobid > "" Then
        varWhere = varWhere & "[JobID] LIKE """ & Me.searchjobid & "*"" AND "
    End If

    If Me.searchhouse > "" Then
        varWhere = varWhere & "[HouseNumber] LIKE """ & Me.searchhouse & "*"" AND "
    End If

    If Me.searchaddress > "" Then
        varWhere = varWhere & "[Address] LIKE """ & Me.searchaddress & "*"" AND "
    End If
 
    If Me.searchdate > "" Then
        varWhere = varWhere & "[PlannedServiceDate] LIKE """ & Me.searchdate & "*"" AND "
    End If
    
    
    If Me.searcharea > 0 Then
        varWhere = varWhere & "[AreaName] = " & Me.searcharea & " AND "
    End If
 
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
     End If
     End If
    strSQL = "SELECT * FROM [Main_Report_Query]"
    strSQL = strSQL & varWhere
        
    Me.Mainsubform.Form.RecordSource = strSQL
    
   Me.Mainsubform.Requery

im mainly playing with the area, are the values returned supposed to be one greater than the list value?
also, you have already set mainsubforms recordsource as a query, how did you plan on reconsiling the two?

 

[jackie77]

I'm not sure , the original code used (by Gromit) was only designed to display the results on a subform and did not have any text/display boxes on the main form, I have successfully used the code in one of my other forms to display on the subform only, its only when I tried to adapt this to display the results in the main form and the subform that I fell into problems, as you can tell I am completely new to this and each step I take takes me a few hours of reading about it then a few weeks of trying to get it to work!!!!! unsuccessfully most of the time (everyone keeps saying it takes a while to get the hang of it, but I'm not so sure!!!!)

The code I used in the other form was

Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub

Private Function BuildFilter() As Variant
    
 

    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
    
    
   
    
     ' Check for LIKE Last Name
    If Me.txtBooked_Date > "" Then
        varWhere = varWhere & "[BookedDate] LIKE """ & Me.txtBooked_Date & "*"" AND "
    End If
    
    ' Check for Engineer
    If Me.cmbEngineer > 0 Then
        varWhere = varWhere & "[EngineerID] = " & Me.cmbEngineer & " And "
    End If
    
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
        
         End If
    
    
    Debug.Print varWhere
BuildFilter = varWhere
   

End Function

Jackie

 

[thisisntwally]

i'm still waiting on the access programmer's high myself. I have to say, i've not worked with subforms much, im heading home for the day, but i'll take another look tomorrow and see what i can find. If it works in one subform, it should work on another.

 

[jackie77]

many thanks again help would be great!!