filter subform cbo depening on choice mainform

[Ingis]

I'm havent worked with access for a lot of years, now I'm in the need to create some new stuff. It's a bit hard to get back into it.



I have a form with a subform. Mainform is the main info of an order. Subform are the orderlines. One order can only come from one supplier. (selected on the mainform)
You can ofcourse add line to your order (or see what already is added on this order). The last thing is working and adding a new line is also not a problem. The thing is that I want the combobox of ArtId only exist out of articles you can order from the selected supplier in the mainform (filter? )
someone who can direct me in the correct direction?










thx
Wesley

[Welshgasman]

I would set the recordsource of the subform combo in the AfterUpdate event of the main form combo?

Criteria would be along the lines of

Dim strSQL as String
strSQL = "SELECT ItemID, ItemName from Items WHERE ItemSupplierID = " & Me.ComboID
DEbug.Print strSQL ' to check the syntax is correct
Me.subformcontrol.form.control.recordsource = strSQL

where control is the name of your subform combo

HTH

[Ingis]

I would set the recordsource of the subform combo in the AfterUpdate event of the main form combo?

Criteria would be along the lines of

Dim strSQL as String
strSQL = "SELECT ItemID, ItemName from Items WHERE ItemSupplierID = " & Me.ComboID
DEbug.Print strSQL ' to check the syntax is correct
Me.subformcontrol.form.control.recordsource = strSQL

where control is the name of your subform combo

HTH

Thank you very much for the answer. This already helps. I only have a many on many relations table between company and articles, because you can get some articles from different supplier

I'm guessing something like this?

"SELECT a.ItemID, a.ItemName from articles as a, betweentable as b WHERE b.ItemSupplierID = " & Me.ComboID ...

update:

I have the sql running in a test querry.

SELECT a.ID, a.Description
FROM Articles AS a, Articles_Company AS ac
WHERE ac.SupId)=1 AND ac.ArtId)=[a].[ID]

this works in selecting the items sold by a certain supplier.
But somehow I can't get this combined in also showing the already ordered items for that certain order.

[Welshgasman]

Well you are going to have to go through that Articles_Company table.

I would try it out in the Query Design window and get the correct sql that way, then amend with the relevant CompanyID ?

[Ingis]

I have a working sql

SELECT a.ID, a.Description
FROM Articles AS a, Articles_Company AS ac
WHERE ac.SupId)=1 AND ac.ArtId)=[a].[ID]

this works in selecting the items sold by a certain supplier (in a seperate test querry).
But somehow I can't get this combined in also showing the already ordered items for that certain order.

so the subform is showing the ordered lines and you can add (hopefully) new lines, where you only can select the available items.

[Welshgasman]

You have two errant ) in there, so I cannot see that working correctly?
Plus I would have thought you would only filter for the supplier?

Can you upload the DB? I only have 2007, so might not be able to open it anyway, but worth a try.?

[Ingis]

I added the db

[Welshgasman]

Yes, sorry, Unrecognised.

Without being able to play with it, I do not have the expertise.
However as the order would be from the same supplier, then I am puzzled as to why you cannot combine both.?
Existing order lines would be in the record already, but would still be for the same supplier, so new records should again just show articles for the same supplier?

[Ingis]

Yes, sorry, Unrecognised.

Without being able to play with it, I do not have the expertise.
However as the order would be from the same supplier, then I am puzzled as to why you cannot combine both.?
Existing order lines would be in the record already, but would still be for the same supplier, so new records should again just show articles for the same supplier?

thank you for trying and the info!

is there a way to change the source of a component of a subform? in your example its a requery of the entire subform. Can I get to the level of the components itself?

[Welshgasman]

No, I was just talking about the combo, but I see I said recordsource and not rowsource.

Plus I did use the control in that code, just the wrong property

[Ingis]

No, I was just talking about the combo, but I see I said recordsource and not rowsource.

Plus I did use the control in that code, just the wrong property

Hey, thank you very much. I have it working.... more or less.

I only have now one problem remaining.



The updates happen correctly. I have only the available articles in my combo. The only thing is that the artid itself is empty, until I click in it. When I click if fills with the correct value.
I have now a on current event on my mainform to update the combobox when I scroll through the records.

[Welshgasman]

A combo will always start empty unless you set a default value, however it should still reflect what was used for previous records, providing the combo is bound to a field.?

[Ingis]

A combo will always start empty unless you set a default value, however it should still reflect what was used for previous records, providing the combo is bound to a field.?

it is indeed bound to a field of a querry. So normal (before i did all the above stuff) you would click/scroll to an order and the subform would show the orderlines. The combo would also show the correct description. Now not anymore. But if you click on it, it fills with the correct info. So it looks it doens't get updated or so?

[Welshgasman]

Hmm, perhaps you need to check if the subform is on a new record first?
What happens if you load the form for an old order, do NOT change the supplier, what does the subform show then?

Without being able to play with it, it is hard for me to see what might be wrong.