Sweet jesus, will someone PLEASE help filter a form???

[Margarita]

Hello,
I am VERY frustrated and annoyed over having spent a ridiculous amount of time on something that should be very simple. I have a form of vendors, with a subform of invoices, which also has a subform with invoice details. All the fields are locked- I only want the user to use this form for review of data, not data entry. What I want to be able to do is jump to a specific invoice on the Invoices subform by typing it in, so you don't have to scroll through all the invoices to find the one you want.

I have posted a thread here before about getting help with some kind of search-as-you-type functionality, but that was no help- there are various versions of code for that all over different forums, but I can't get any of them to work. So-- I decided to go with a simpler approach:
-created a textbox called InvoicetoSearch where the user can type in the invoice number string they want to jump to
-created a button and in its on click event I want to filter the Invoices subform by the invoice number specified in InvoicetoSearch (i.e. filter the InvoiceNum field on Invoices_subform)

I first tried the filteron approach:

Private Sub FilterInvoicesButton_Click()
Me.Filter = "InvoiceNum = " & Me.InvoicetoSearch.Value
Me.FilterOn = True
End Sub

But I kept getting the error 2448 "You can't assign a value to this object". I've played with that code for a long time, did reasearch on the syntax and the way .filter should be used. NOTHING. I can't see what I'm doing wrong. I'm copying the code directly from the help files, only modifying the control names to fit my form. It's still not working.

So, on the verge of tears and mental insanity, I put that aside and decided to try the docmd.applyfilter approach:

DoCmd.ApplyFilter , "Invoicenum = '" & Me.InvoicetoSearch & "'"

Using this, when I click the button, I get asked for Invoicenum as a parameter. When I type it in, I get the form filtered, but if I leave it blank, the form is blank, obviously. This is NOT what I want. Why should the user have to type in the invoice number in the parameter box when she has already put it in InvoicetoSearch? Why is the value of InvoicetoSearch not taking through the ApplyFilter in the button's on click event? What am I missing?

I am begging for someone to shed some light on this. I realize that this is something very rudimentary and anyone with a little vba experience is going to laugh at my stupidity. But even if you laugh to yourself- I am BEGGING- please help! I have gone through four boxes of tissues and about to try the bang-head-on-wall approach.

THANK YOU!!

 

[vbaInet]

I hope you're not on the verge of trashing your computer Margarita

So the Filter property will filter your form and only display the matching record but from what you described you want to jump to the record. What you need is something like this:

dim rs as dao.recordset

set rs = me.subformcontrol.recordsetclone

with rs
    .filter "invoicenum = " & me.invoicetosearch
    if not .nomatch then 
        me.subformcontrol.form.bookmark = .bookmark
    end if
end with

 

[Beetle]

Also, if you want to you can upload a copy of your db with some dummy data and we can help you resolve your issues that way if you like.

 

[Margarita]

I hope you're not on the verge of trashing your computer Margarita

So the Filter property will filter your form and only display the matching record but from what you described you want to jump to the record. What you need is something like this:

dim rs as dao.recordset
 
set rs = me.subformcontrol.recordsetclone
 
with rs
    .filter "invoicenum = " & me.invoicetosearch
    if not .nomatch then 
        me.subformcontrol.form.bookmark = .bookmark
    end if
end with

I think your code made me take a few steps back away from the abyss of despair- thank you! Though the tears have not dried off quite yet...

I did the following:
-pasted your code into the on click event of the button
-changed 'subformcontrol' to the name of the subform on which the InvoicetoSearch, InvoiceNum controls and the button are located (TEMPSEXPENSES_Invoices_subform).
-tried to run it.

I get a compile error "Method or data member not found" and
"Set rs = Me.subformcontrol.RecordsetClone" is highlighted.

I am very close to going back to the tissues!

 

[Margarita]

Also, if you want to you can upload a copy of your db with some dummy data and we can help you resolve your issues that way if you like.

Hi Beetle, I am going to save that as my last resort simply because I have an upload/download limit on my work computer. But I am definitely a big fan of looking at the thing directly.
Thanks!
By the way- your signature quote is hilarious!

 

[Margarita]

Hello, I tried getting rid of the subform name and did the following:

Set rs = Me.RecordsetClone
With rs
    .Filter "invoicenum = " & Me.InvoicetoSearch.Value
    
    If Not .NoMatch Then
        Me.Bookmark = .Bookmark
    End If
End With
End Sub

I have gotten rid of the first compile error, but now getting a compile error "invalid use of property" with .filter highlighted.

I'll post back here if I am able to solve my own problem.

Thanks for getting me started in the right direction.

 

[boblarson]

If you are wanting to go to that record then it is:

[COLOR=#000000][COLOR=#0000bb][COLOR=black]Set rs = Me.RecordsetClone
With rs
    .[B][COLOR=red]FindFirst[/COLOR][/B] "invoicenum = " & Me.InvoicetoSearch
    
    If Not .NoMatch Then
        Me.Bookmark = .Bookmark
    End If
End With
End Sub  [/COLOR][/COLOR][/COLOR]

And if InvoiceNum is text then the quotes are needed:

[COLOR=#000000][COLOR=#0000bb][COLOR=black]Set rs = Me.RecordsetClone
With rs
    .[B][COLOR=red]FindFirst[/COLOR][/B] "invoicenum = " & Chr(34) & Me.InvoicetoSearch & Chr(34)
    
    If Not .NoMatch Then
        Me.Bookmark = .Bookmark
    End If
End With
End Sub  [/COLOR][/COLOR][/COLOR]

 

[Margarita]

Hello Bob, thank you so much! Just tried it and the findfirst method did the trick. You have no idea what a relief it is to finally have a working solution to this problem.

Right before finding your response, I was going to post back here with a tweak of the code posted by vbaInet, using the open a filtered recordset off of the original recordset approach:

Set rs = Me.RecordsetClone
rs.Filter = "invoicenum = " & Chr(34) & Me.InvoicetoSearch & Chr(34)
Set rs2 = rs.OpenRecordset

This did not produce an error but did nothing- the invoice record didn't jump. I was just curious if anyone out there could explain to me what I was missing in that piece of code.

In any case, thank you- to Bob, and to everyone who offered insight on my problem.

 

[vbaInet]

Cheers for correcting that aircode Bob. I haven't done any programming in VBA in a while that I forgot it was FindFirst not Filter. So yes Margarita, I wanted to type FindFirst but somehow I typed Filter.

 

[Margarita]

Cheers for correcting that aircode Bob. I haven't done any programming in VBA in a while that I forgot it was FindFirst not Filter. So yes Margarita, I wanted to type FindFirst but somehow I typed Filter.

No problem. Between the two of you, you've gotten me much farther along on this problem than I've been in days.
Thanks again!