User Filters

[bschulze]

Not quite sure how to get started on this and I have been unsuccessful locating a solution on the board. I am new at Access/VB so bear with me.

This particular DB will resemble a Contacts DB. I am creating a Form that is query driven that the user will use to imput information. I would like to give the User functionality of filtering on fields like First Name, Last Name, Email, etc.... I dont beleive Form Filter will work for me because I would like the filter to update "on the fly". For instance if the user was looking for a person with the last name "Smith" and there were 50 people. I would like the user to then be able to filter on the first name field and ONLY see the first names of the people with the last name of "Smith". I hope this makes sense.

I dont have anything to share with the board (as I have not started as of yet), I am however looking for some suggestions to get started....Or if what I am thinking about is not possible maybe a workaround. Thanks in advance

 

[bschulze]

Main Form: frmLookup
Sub Form: frmLookupSubForm
Field Name: State

Below is what I tried and it bombed....did I misinterpret anything?

Forms!frmLookup!State.Form.Requery

The error I got was...
{Run-time error '438':}
{Object doesn't support this property or method}

 

[VicRauch]

Forms!frmLookup!NameOfTheSubFormControl.Form.Requery.
The name of the subform control is not always the same name as the subform itself. You will have to look at your main form (frmLookup) to see what the name of the subform control is. That name needs to be put into the area where NameOfTheSubFormControl shows above. Then it should work.
HTH,

 

[bschulze]

I appreciate all your help, however I am the reason this wont work. I cant figure it out. I am going to try your "Update" code you listed earlier in this post. I think "Search" and "Clear" buttons are probally the way to go on this anyways. I will let you know if I encounter any problems, it look easy enough to follow. Thanks again

 

[VicRauch]

I think you are saying you can not find the name of the subform control. If that is the case, then open your main form in design view, click ONLY once on the sub form. That will select the subform CONTROL. Then open the properties window and see what the name of the subform control is. Then, the last piece of code I gave you in my last post should work.
Hey, if I have completly missed it in this post, please let me know.

 

[bschulze]

your not missing the post at all. I was able to find the subform control name but that did not work either. It took care of the error but the filter did not work again. I must have really messed something up. I have tried to adapt your UpdateFilter() code but have hit a little stumbling block.

Function UpdateFilter()
With U
  .frmLookup = ""
  If Len(Nz(txtZipCode)) <> 0 Then
    .frmLookup = .frmLookup & "(Zip Like """ & txtZipCode & "*"")"
  End If
  If Len(Nz(txtStateAbbreviation)) <> 0 Then
    If Len(.frmLookup) > 0 Then
      .frmLookup = .frmLookup & " AND "
    End If
    .frmLookup = .frmLookup & "(StateAbbr Like """ & txtStateAbbreviation & "*"")"
  End If
  If Len(Nz(txtStateName)) <> 0 Then
    If Len(.frmLookup) > 0 Then
      .frmLookup = .frmLookup & " AND "
    End If
    .frmLookup = .frmLookup & "(StateName Like '" & txtStateName & "*')"
  End If
  If Len(Nz(txtCountyName)) <> 0 Then
    If Len(.frmLookup) > 0 Then
      .frmLookup = .frmLookup & " AND "
    End If
    .frmLookup = .frmLookup & "(CountyName Like '" & txtCountyName & "*')"
  End If
  If Len(Nz(txtCityName)) <> 0 Then
    If Len(.frmLookup) > 0 Then
      .frmLookup = .frmLookup & " AND "
    End If
    .frmLookup = .frmLookup & "(CityName Like '" & txtCityName & "*')"
  End If
  Me.Section(acDetail).Visible = True
  Me.Section(acFooter).Visible = True
  DoCmd.RunCommand acCmdSizeToFitForm
  Me![frmLookupSubForm].Form.Filter = .frmLookup
  Me![frmLookupSubForm].Form.FilterOn = True
End With
End Function

I am getting an error on the " .frmLookup = "" " part of the code. Since I dont really understand this part of the code I am at a loss. Is this a variable I have to declare?

 

[VicRauch]

Actually, this is not a "little" stumbling block, but a big one. Go back and re-read that post from Sept last year and I have explained what the "U" stands for. It is a Class that I have written because of how I am doing things in that one database. I really never should have posted that code unless I knew I was dealing with an expert level VBA person. Sorry!

Let me know what the error is when you do the Forms!frmName!SubFormName.Form.Requery. If nothing appears to happen, then check the filter for the subform and check to see that the FilterOn is True.

 

[bschulze]

lol...I should have let you know that I am at a level below a beginner...Sorry. I did not get an error on this:

Forms!frmLookup!qryJurisByZipsubform.Form.Requery

It just did not filter the subform. As far as the "FilterOn", I am not sure where that is. Is it on the Event tab because all I see is "On Filter"?


Maybe I should post exactly what this form should be doing. I have 5 text boxes on the frmLookup form. Basically these will be user defined search criteria that will filter the subform. I am not sure that updating the filter "On-The-Fly" is best as this will slow the filtering process for the user. What approach would you use for this type of application? I was thinking about using "Search" and "Clear" buttons to run the requery, that way the user will not have to wait while 100,000 records are being filtered everytime they change a text box. Please let me know if you would like other details. I really appreciate your patience.

 

[bschulze]

Any suggestions Vic? Maybe the way to go about this is simple and I am overthinking?

 

[VicRauch]

bschulze,
I would suggest going back and reading what SydneyGeek (Denis) posted last Sept. At your level of VBA coding and what you want done, I really think that will be the best approach for you. And, rather than having anything done on an AfterUpdate event, do the search only when a Search button is clicked. The code that Denis shows for the AfterUpdate event would be put in the OnClick event of the Search button. That saves the extra filtering on all those records. The Clear button is something that makes the criteria typing a little easier for the user. They can click the Clear button rather than deleting the content of the 5 search fields (text boxes) themself.
Hope this helps!

 

[bschulze]

I appreciate all your help...I will give it a try.