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 :confused:

Any advice is appricated :)

Many Thanks
Jackie

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

Anybody out there!

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...

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

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?

I'm not sure :confused:, 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 :)

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.

many thanks again help would be great!!