FIFO - Demo attached works to consume stock correctly, but how to calculate profit

[writeprivate]

Hi Orange - hope all good.

I have made a lot of progress and I am trying to tackle a problem and have hit a brick wall. Please could you help me

THE PROBLEM
- Currently the stack of FIFO queries use the criteria reference "<=[forms]![frmReports]![TDate]" (use QryAvailableStock as example)
- I am using this same set of queries to generate output for other forms (not just the reports)
- This means I need to have the Report form open at the same time as the other forms using this query to prevent an Enter Parameter request

PROPOSED SOLUTION I AM WORKING ON
- I have thought the best approach would be to create a global variable called "TDate" value to be stored as a global variable and then I can update the query criteria to "<=[TDate]"
- The SetTDate function can set the date to today when starting the application and so I can use the FIFO queries on other forms (without the Report form needing to be open)
- But where the Reports need other FDate and TDate, I am using an after update event on those unbound fields of the Reports form to set the global variable.
- This all works.

RESULT SO FAR
- I have created a global variable and set up functions to set the TDate

Module 1: "DeclareGlobalDateVariables" with the code
Option Compare Database
Global TDate As Date
Global FDate As Date


Module 2: "DateFunctions" with the code

Option Compare Database
Public Function SetTDate()
TDate = Date
End Function

Public Function SetFDate()
FDate = Date
End Function


- To test, I have created a blank form with a button. The msgbox gives the correct value ie. todays date.


Private Sub Command3_Click()
SetTDateMsgBox Format(TDate, "Short Date")
DoCmd.OpenQuery "QryStockAvailable"


End Sub


ERROR
- when the above is button clicked, I get an Enter Parameter Value request for "TDate"


TROUBLESHOOTING
- I have tried to use various syntax for the criteria eg. [TDate], TDate, [DeclareGlobalDateVariables].[TDate] etc. Nothing seems to work.
- I have read this post which speaks about the fact that you cannot use a public or global variable directly in a query but I am not sure how to use this infomation. Perhaps this is useful for you to look at: https://www.experts-exchange.com/art...MS-Access.html
- Note that I previously declared the variable as a Public variable ie. Public TDate As Date but it had the same errors


Please can you help me get this to work - it is a real roadblock for me

[orange]

No demo attached?? Am I suppose to update y copy or are you sending a new version?

The second line of every module should be:
Option Explicit

This forces variable declaration -- every variable is checked to exist and spelled correctly. Solves a lot of issues.

Experts Exchange is not a free service. I am not a member. However, Dale Fye (author) is well known and respected.

[writeprivate]

Thanks - yes please use the existing database i sent you as the solution would apply to my current version

I am thinking perhaps I need to call a function in the query rather that a global variable - but i don't know

[writeprivate]

writeprivate,

Here are a couple of graphics mentioned in PM.
You can not attach files with a PM.

Sample BatchAllocation of a singleunit /1 of Product.


Attachment 49704

Revised frmReport with Check Available Product Button and Result

Attachment 49705

Hi Orange,

I was meaning to ask if it possible please could you send me this new Report form and underlying query to show Gross Current Availability - this would be a useful output - possibly send me back a db

thanks very much

[orange]

Replied via PM

[umargby]

How can i get complete tables and codes behind this fifo stock report umargby@gmail.com

[orange]

umargby,
Just stumbled upon your post today 2-May-23.
I think the last version of the database is the one posted in post 14.

[dharvz]

Hi writeprivate, i would love to see your database. if you can share it pls at wingskyfer@yahoo.com , thanks in advance.