Limiting Products shown to specific customer

[marystewart78]

Hi,

I'm experimenting with the database attached (Access 2003) and on the Order Deatails subform I would like to be able to view only the products for the related CustomerID!

Any pointers on how to do this - very much appreciated !

Thanks,

Mary

(PS Some non-essential db components have been deleted in order to decrease size for upload)

 

[gromit]

Hi -

The usual approach is to use some visual basic code to modify the subform's row source property.

If you search the web for "cascading combo boxes" you will see similar approaches to how this works for two combo boxes.

HTH,

-g

 

[marystewart78]

I know it's probably very simple but I'm new to SQL and have tried looking at the other posts and I'm still lost.

I've tried puting [forms]![Order Detail Subform]![CustomerID] into the SQL query but can't get it to work - where am I going wrong??

 

[gromit]

Not clear what your complete SQL statement is, but it would be something like -

SELECT (some field) FROM (some table) WHERE [CustomerID] = (some value).

The (some value) should match whatever filter you are trying to set up. You can use a reference to a field on a form, or you can generate an explicit value with Visual Basic.

If you are new to SQL, I would actually start by working in a Query and see if you can generate the appropriate records there. Use the SQL view in the Query design to generate the SQL statement and see the results.

- g

 

[jmt2005]

Hi there - im quite new to databases myself and have had the same problem. From what i can remember, you have to make a query that is something like (for example)

SELECT tblProducts.ProductID, tblProducts.ProductName, tblProducts.CustomerID FROM tblProducts, WHERE forms![main form name].CustomerID = [mainform name]!forms![subform name].forms!CustomerID;

This is not exactly correct as its from memory, not sure about where the exclamation marks and full stops go, and as i said i'm very new to it all myself. I have a practice database at home where it worked though so what i'll do is have a look at the SQL on the form tonight and post another reply tomorrow for you.

The hardest bit i found was trying to reference the subform control - i found it quite complicated and can't even explain how i managed it. I think it was pure luck so you might want to try a search on this.

I dont want to drag on but if you did try it yourself with a query you add the criteria to the CustomerID and you have to click in the expression builder.
Hope this helps.