Another Form Filtering Question

[baadams]

I've searched and found close solutions but nothing exact.

I've got a form with search criteria in the header. The detail of the form has another form that is based on a query that used the fields in the form header as the criteria. So each time the criteria is changed the query and form are refreshed. This works fine but the format is somewhat rigid. This is a customer requirement that the header have the search criteria and the detail be in datasheet view. So it can be edited.

Let’s say I have 3 criteria for viewing vehicles. Model, Type, Color. Currently you can choose a Model, Type and Color and it works fine. But if you want to just search by Model, I have to create a different query. The current query requires all three fields to be populated. This is easily done with SQL. The SQL select and sort will always be the same. The only difference is the where clause.

My question is how do I use VBA to change the record source of the form within the detail of the current form? (I understand this may not be the best design but the customer wants the result in datasheet view while still having the search criteria available in the header. Otherwise I would just filter the form in datasheet view but the header isn't available) I know the SQL but any effort to change the record source ends in an error. I've got a list box on another form where I change the row source based on SQL and it works great. This seems somewhat similar but I can't get it to work. Thanks for the help.

 

[boblarson]

First off, create a form that has no record source. Then put the form you want as the datasheet onto that form and use the main form as the spot where you will be putting your controls for Model, Type, and Color.

Then you would use some code in the form (you can use a command button or even use the after update event of each of the controls to call a function like this):

Dim strWhere As String
 
If Len(Me.Model & vbNullString) > 0 Then  ' that means we have as selection
   strWhere = "[Model] = " & Chr(34) & Me.Model & Chr(34) & " And "
End If
 
If Len(Me.[Type] & vbNullString) > 0 Then   ' Type is a bad field or object name as it is an Access Reserved word so it needs square brackets
   strWhere = strWhere & "[Type]= " & Chr(34) & Me.[Type] & Chr(34) & " And "
End If 
 
If Len(Me.Color & vbNullString) > 0 Then
   strWhere = strWhere & "[Color]= " & Chr(34) & Me.Color & Chr(34) & " And "
End If
 
strWhere = Left(strWhere, Len(strWhere) - 5
 
With Me.SubformControlNameHere.Form
     .Filter = strWhere
     .FilterOn = True
End With

I used the Chr(34) in each case because I was assuming that the values are all text. Chr(34) is a double quote ("). So if any of those are numbers remove both of the Chr(34) from that particular line of code.

SubformControlNameHere is the name of the Control on the Parent form which HOUSES the subform and is not the name of the subform itself unless they both share the same exact name. And the .Form. part is required (as is) to tell Access that you want something on the form and not the subform control. You can leave that part off if the subform control and subform names are the exact same but if they aren't it must be there or it will generate an error since the subform control doesn't have a filter or filteron property.

 

[baadams]

Thanks Bob, your the man! That's what I was looking for. I've already got the form set up, just need to implement the logic. I ended up doing something similar with the query, but it's not as elegant as your solution. Thanks for the help.