Saving Calculated Fields

[oxicottin]

Hello, I know your not supposed to do this and if I could figure out not to that would be way better... I attached my database I have been working on and im stuck and have been for some time on this. What I have to be able to do is email some data using outlook and it could be as an report attachment so if I could get the data into a query that would be great but I cant figure it out what so ever!


First I need the data from tbl_Hold in my report then im going to have to put a sub report tblsub_ProductHoldData.

BUT, here is my main problem if you open my main form frm_Hold in form view and you look at the calculated field in black that shows in the frmsub_ProductHoldData. I need to show that sum on my report along with a sum cartons held sum which thats not bad because its not a calculated field.

How can I do this?

Uploaded to my OneDrive

Link

Thanks,
SoggyCashew.....

 

[jrbarnett]

Hello

What you ideally need to do is to put some of these hard coded constants into another table (or tables) and join them into the main query appropriately. Your SQL can then do the lookups and calculations for you rather than having all these Iif statements based on data in a form. It won't be simple to get this set up or tested, but long term you will end up with more maintainable code.

Additionally, you should also be able to simplify your frmSubFormProductHoldData code by replacing all occurrences of [Forms]![frm_Hold] with [Parent]. One example is replacing:

IIf([Forms]![frm_Hold].[cboProductID].[Column](1)="SLOC NOS"

with

IIf([Parent].[cboProductID].[Column](1)="SLOC NOS"

This technique should also work in reports, so the same query can be used for both your form and report data sources, thus saving the need to duplicate code.

John

 

[oxicottin]

John, these are calculated fields the only way I know how to hard code these values into the tables using VBA is I would use something in the txtCartonsHeld after update like Me.txtText = Me.txtCartonsHeld and save txtText to the table then I could sum the number like can sum CartonsHeld. Is this what your meaning?

Thanks,
SoggyCashew.....

 

[jrbarnett]

Hello,
What do I mean by putting constants into another table? For ease of reference your query that is the basis of txtAutoHeld (which is the field in black) in frmSubProductHoldData is as follows:

=IIf([Forms]![frm_Hold].[cboProductID].[Column](1)="SLOC NOS",
	Round([Forms]![frm_Hold].[cboProductLength].[Column](3)*25/12),
	IIf([Forms]![frm_Hold].[cboProductLength].[Column](3)=1200,1000,
        IIf([Forms]![frm_Hold].[cboProductLength].[Column](3)=300,600,
	     Round([Forms]![frm_Hold].[cboProductLength].[Column](3)*50/12))
	)
 )*[txtCartonsHeld]

This contains 2 different conditional statements based on cboProductLength.column(3) and another 2 that format that same field. This field is the LotNumber field.

Could it be easily retrieved on the main form without storing a calculated value? Yes, use DLookup, something like:
=Dlookup ("LotNumber", "tblsub_ProductHoldData", "Hold_ID="&Me.HoldID);

In terms of storing data in tables to simplify your queries, I will need to spend a bit more time investigating to see that this works properlty, but you already seem to have duplicated data across your tbl_Products and tbl_ProductData tables.

John