Filtering a filtered subform!

[zerokono]

please be patient!!!, I am totally new to access....

here is my problem. I have a main form <frmmaindata> with a combobox <Combo3>. When I select an item from the combobox there is a subform <tblreceipts> which correctly filters based on the item selected from the combobox <this happens via vba in the `OnChange' event procedure>. What I would like to do now is the problem..... Once the subform has filtered, I would also like to filter again <there is a textbox <transactiontype> which has various text in such as RECEIPTS, USAGE, TRANSFER etc. What I need is to filter the subform again to display RECEIPTS or can I do this in 1 action via the combobox? This may be a `so simple' fix but as I said I am `very new' to access and cant figure it out. Thanks in advance everyone.

[June7]

Exactly how are you using combobox to filter subform - VBA code or dynamic parameterized query? Review http://allenbrowne.com/ser-62.html

[zerokono]

Private Sub Combo3_Change()
Dim StrFilter As String
StrFilter = "materialdescription=" & Me.Combo3
Forms!frmmaindata!tblreceipts.Form.Filter = StrFilter
Forms!frmmaindata!tblreceipts.Form.FilterOn = True
End Sub

Jonarve Limited1.zip

[Welshgasman]

So in the AfterUpdate of your control, test if anything is there, then add to filter?
Something along the lines of

Dim strFilter As string
If Len(Me.txtControl ) > 0 Then
    strFilter = Me.Filter & " AND TransactionType = '" & Me.TransactionType & "'"
    Me.Filter = strFilter
End If

That assumes code is in the form.
Not sure if you have to switch off the filteron and back on again?

HTH

[zerokono]

Hi, thanks for the reply...

I put the code in the AfterUpdate of the control but get method or data member not found

are you suggesting that once the OnChange event has occurred I should code the AfterUpdate

[Welshgasman]

Hi, thanks for the reply...

I put the code in the AfterUpdate of the control but get method or data member not found

are you suggesting that once the OnChange event has occurred I should code the AfterUpdate

I am saying once, you enter something in your transactiontype control, then that code should run when it has been updated? You could also use the LostFocus event as well?
YOU have to get the names correct?

[zerokono]

if im understanding you correctly, that's not what i was after. When i select an item from the combobox in the main form, I want it to display the items selected and then I want to re-filter the subform to display all those items which have a transactiontype called RECEIPT.

Cheers

[Micron]

Basically, you cannot filter a filter. You would need to set the filter conditions for situation A and apply. If you take some other action and wish to filter further (situation B), you must reconstruct the filter for A+B and then reapply the filter. If what you're saying is that you want to ignore A and use only B filter, then you alter the filter code to suit only B and then reapply.

IMO you should use a combobox instead of a textbox to enter the second filter parameter. Any typos are likely to produce no records based on erroneous input. However, if you want to filter the subform as you type, then you'd need to use the OnChange event of your textbox. I wont' have time to download and play with your db right now so hopefully that helps a bit.

[Welshgasman]

if im understanding you correctly, that's not what i was after. When i select an item from the combobox in the main form, I want it to display the items selected and then I want to re-filter the subform to display all those items which have a transactiontype called RECEIPT.

Cheers

My understanding was you wanted to filter on the combo, then you want to filter further with a transaction type?
If it is one or the other, then just set the filter as required. Much easier. one will overwrite the other.

[Gicu]

Could you please try this:

Private Sub Combo3_AfterUpdate()
Dim StrFilter As String
StrFilter = "materialdescription=" & Me.Combo3 & " AND [transactiontype]='RECEIPT'"
Forms!frmmaindata!tblreceipts.Form.Filter = StrFilter
Forms!frmmaindata!tblreceipts.Form.FilterOn = True
End Sub

You want the code to be in the AfterUpdate instead of the Change event because the later fires for every keystroke.

Cheers,

[mike60smart]

I think it would be best if you explain what your business process is.

It looks to me like you want your tblInventory as a Main Form with a Subform to control Receipts.

If this is the case you need to get rid of ALL of the Lookup fields in your tables. These are a NO NO in access.

[orange]

I agree with Mike. It seems all responses are guessing what the OP is doing and/or wants.

@zerokono

if im understanding you correctly, that's not what i was after. When i select an item from the combobox in the main form, I want it to display the items selected and then I want to re-filter the subform to display all those items which have a transactiontype called RECEIPT.

It would be helpful to you and readers if you could tell us in non-database terms WHAT you are trying to automate in plain English. Once readers understand WHAT your requirement is, then options and alternatives as to HOW it may be done using Access will follow.

[ssanfu]

Naming consistency???

I have a main form <frmmaindata>
with a combobox <Combo3>.
When I select an item from the combobox there is a subform <tblreceipts>

You have a form with the name tblreceipts? Shouldn't the name be frmreceipts? Or sfrmreceipts??

Confusing

Also, you should be using the combo box "AfterUpdate" event, NOT the "OnChange" event. See Help for the "OnChange" event.....

[Welshgasman]

Naming consistency???


You have a form with the name tblreceipts? Shouldn't the name be frmreceipts? Or sfrmreceipts??

Confusing

Also, you should be using the combo box "AfterUpdate" event, NOT the "OnChange" event. See Help for the "OnChange" event.....

Sadly, the wizard names the form by default after the source object.
If a person knows no better, they will likely just accept it, when they save the form.

[zerokono]

thats it!!!!!

thank you so much, its exactly what I was looking for - perfect