Filtering Form from a many-to-many relationship

[rafa]

Hi,

I am trying to write a "[Forms]![Nameform].Filter =" a little bit more complex...

I need to filter a Form (Orders) in order to let a User only to access to his/her own orders. Tables "Orders" and "Users" are linked by a many-to-many intermediate table, "OrderUser" (with only three fields: IDOrderUser, IDOrderFK, IDUserFK). From a Dialogbox a User is selected from a combo, and then the filter in the clik event must be applied to see only the Orders from this User (thanks to the relation in the intermediate table). So I must write something like..

Take the IDUser from the combo, check the intermediate table and filter the form with all the Orders that this User have.

Puting a (forbidden) WHERE, the filter for the form would look like:

FIlter = IDOrder = IDOrderFK (From OrderUser table) WHERE: take IDOrderFK that fits IDUserFK = combo.value

IO course there are more than one order for each user. And I need the many-to-many because one order can have several Users. Until know, in all my efforts I only filtered the form to only the first order for a user.

After two weeks I think it is time to ask for some kelp...I don't even know it this can be done!

Many thanks in advance.

 

[James Deckert]

Sounds to me like you want a combobox to do a record lookup.
See if this video helps.
https://www.youtube.com/watch?v=EdH1aIWaD-0
If you still have questions on your specific problem, let me know.

 

[rafa]

Hi James, thank you very much for answering.

The combobox for the record lookup goes to the record that fits with the condition, only one, but also all the rest of records are still available (from the record selector at the bottom of the screen, for example). In my case I need to restrict the form navigation to the records, not only one, that fit the condition.

We can get it with something like this, for a simple condition:

[Forms]![LAB].Filter = "IDUser= " & Me.cboUserName.Value
[Forms]![LAB].FilterOn = True

This gives not only one or the first record: the whole Form is limited to the records that fit the conditions (after that the filter must be deactivated before closing the form). But my problem is that I am trying to filter a Form...from a field that is not inside the Form itself! Yes, nice idea...

What I would need is something like:

FIlter = IDOrder = IDOrderFK (From OrderUser table) WHERE: take IDOrderFK that fits IDUserFK = combo.value

so, make a pre-selection of data before the Filter. I tried making a Query, but then I have to change the source of the Form, from a Table to a Query. And change it again after.

Again, thank you so much for your time.

 

[James Deckert]

If you create the form and subform at the same time when using the Form Wizard, the subform will only show records related to the master form. So the combobox lookup should filter the subform automatically. This has to do the the "link master fields" and "link child fields" property of the subform.

 

[rafa]

Yes, that is true, but it is not what I am looking for...or what I am looking "form"..

The combo filter the form and goes to that record, but the rest of records are available. What I need is to restrict the form, only a few records will be accessible. That can be done, as far as I know, with the Filter property. I found a link, but I cannot paste it (less than 10 messages). If you search

"Filter a Form on a Field in a Subform"

in Google you will find a site from allenbrowne. I an looking for something like that.

Many thanks for your answer.

Rafa

 

[James Deckert]

Maybe if you included a screen shot I'd understand what you're talking about, because I don't understand what is different from you explanation and my previous post. The way a combobox record lookup can work is to select the item you want to display on the main form, and a subform will automatically restrict its view to items related to the item on the main form.
So If the main form showed a Customer, the subform would show all orders by that one customer (and no other customers orders).

 

[rafa]

Yes, maybe I did not explain it well. I do not want to filter a subform from the mainform: I want to filter the main form, restrict it only for some registers, using a user-password dialog-form.

I found the solution in the allenbrown site I wrote in the last post.


Dim sSQL As String

sSQL = "SELECT DISTINCTROW tblProduct.* FROM tblProduct " & _
"INNER JOIN tblProductSupplier ON " & _
"tblProduct.ProductID = tblProductSupplier.ProductID " & _
"WHERE tblProductSupplier.SupplierID = """ & Me.cboShowSup & """;"
Me.RecordSource = sSQL

Tlh tblProductSupplier is the many-to-many table between products and suppliers. This changed the recordsource of the main form (not a subform). Only the records that fit cboshowUp (= IDSupplier) will be accessible. It works.

Thank you very much for your answers. To write everything helped me to see all more clear and find a solution.