Macro(?) condition

[Chumpalot]

Hi all,

I wonder if you anyone can help. I am using a modified version of the Northwind database and now just tweaking it slightly. I have my tabs along the top, one of which is 'Products'. When this tab is clicked a form (frm_productsmain) is called into the main window displaying my products. The form itself displays a datasheet (frm_productsdatasheet).

I'd like the datasheet to NOT display any products where the stock level (a calculated field) is 0.

Where would I do this? Is it done directly on the datasheet?

As part of the macro built into the Products tab I have a condition where any products that are discontinued are not displayed. This works fine as:

[Discontinued]=False

Thanks for any help you can provide. This really has me stumped.

Cheers
David

 

[CJ_London]

Where would I do this? Is it done directly on the datasheet?

you would do it in the datasheet recordsource.

At the moment it probably has a source something like [tblProducts]. Change it to something like

SELECT * FROM tblProducts WHERE Stocklevel>0

Alternatively change your macro from

[Discontinued]=False

to

[Discontinued]=False AND [Stocklevel]>0

 

[Chumpalot]

Thank you for your reply.

I can get neither to work. I think this is because 'Stock level' isn't a table field but is a calculated field.

The datasheet's record source is a query and I can't see 'Stock level' as a selectable field.

Thank you. I hope I'm explaining this sufficiently.

 

[CJ_London]

so where is and what is the stock level you refer to here

I'd like the datasheet to NOT display any products where the stock level (a calculated field) is 0.

 

[Chumpalot]

The stock level is a calc. field located on the products fofm and is worked out based on 3 other fields including 'InitialValue', 'quantityused' and 'quantitybought'.

 

[CJ_London]

did you try my second suggestion?

 

[Chumpalot]

did you try my second suggestion?

I did. I get a parameter box pop up asking the user to input a value.

I am :banghead:

 

[CJ_London]

well, I think you need to include your calculation in your form recordsource, then either solution should work

 

[Chumpalot]

Thank you for your help. I'll give this a go.

 

[Chumpalot]

This is the calculation I'm using to work out the [Stock Level] field:

=Nz(DSum("OnHand","qry_stock","ID=" & [ID]))

Would I build the '>0' into this somehow?

Thanks again
David

 

[CJ_London]

no - it would be in your macro

and your syntax to include in the recordsource looks wroing. I would have expected you to have in the query window

StockLevel:Nz(DSum("OnHand","qry_stock","ID=" & [ID]))

or in SQL

Nz(DSum("OnHand","qry_stock","ID=" & [ID])) AS StockLevel