Warehouse Inventory Project

[ahmad_rmh]

kindly correct the formula for the reference of date in sub form.

=IIf(IsNull([AdjustmentSub].[Form]![ItemsFK]),Null,DSum("[Stock_In_Out]","TransactionsExtended","[ItemPK]=" & [AdjustmentSub].[Form]![ItemsFK] & "And [TranDate]<= #" & [Forms]![Adjustment].[Form]![TranDate] & "#"))

HeadFormName: Adjustment
SubformName: AdjustmentSub
DataReferenceQuery: TransactionsExtended

 

[jdraw]

How about
a) post a copy of the database with instructions to show the issue, or
b) try a few variations in syntax and report back eg,, a specific ItemPK until you get what you need/want?

 

[Gasman]

Do you actually need # when referring to form controls and they are actually a date format?

 

[ahmad_rmh]

Its actually a shortdate in the table. May be there is a conrol reference issue with the main form or something else.

 

[Pat Hartman]

Why are you using Forms!formname!controlname in this context rather than Me.controlname????????

 

[ahmad_rmh]

I am giving reference in the conrol source and it is an unbound field in subform.

 

[ahmad_rmh]

In the Main form i have got the result it's working but in sub form it's generating error.

 

[Gasman]

If a subform, the you need to refer to mainform!subformcontrol!Form.ControlName

Forms: Refer to Form and Subform properties and controls

 

[jdraw]

In the Main form i have got the result it's working but in sub form it's generate error.

Post the database or post the exact error message....were at post #68.....

 

[ahmad_rmh]

The control is on the main form trandate.

 

[Pat Hartman]

When you reference a subform record from outside the subform itself, how do you know what record you are referencing? The mainform is showing a single record so you always know what you are referencing but the subform is probably showing multiple records. WHICH do you want to reference?

 

[ahmad_rmh]

My question in simple terms is,

DSum quantity on hand from transactions extended query where item id is equal to item id in sub form,

Up to this part it's working,

now adding 2nd condition,

and trandate in transactions extended query is less than or equal to trandate on main form.

When i implement 2nd condition, it's showing error.

Whereas implementation of the conditions on main form, it's working as i have shown in the screenshot.

 

[Gasman]

Put your code within code tags

=IIf(IsNull([AdjustmentSub].[Form]![ItemsFK]),Null,DSum("[Stock_In_Out]","TransactionsExtended","[ItemPK]=" & [AdjustmentSub].[Form]![ItemsFK] & "And [TranDate]<= #" & [Forms]![Adjustment].[Form]![TranDate] & "#"))

You could also put all the criteria into a string variable and debug.print that until you get it correct? Then use that in the function? All basic debugging techniques.

You do not appear to have a space before the And?, so how it worked in the mainform is beyond me.

 

[ahmad_rmh]

kindly suggest why the filter is not showing results properly.

 

[Kamayo Ako]

kindly suggest why the filter is not showing results properly.

use this code in your search button.
Option Compare Database
'Option Explicit << remove this



Private Sub Search_Button_Click()

Dim strFilter As String
Dim strDateStart As String
Dim strDateEnd As String

If IsNull(Forms![test].FromDate) Or Forms![test].ToDate = "" Then
MsgBox "Start Date and End Date Required", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Forms![test].FromDate.SetFocus
Cancel = True

Else

strDateStart = "#" & Format(Forms![test]![FromDate], "mm-dd-yyyy") & "#"
strDateEnd = "#" & Format(Forms![test]![ToDate], "mm-dd-yyyy") & "#"
strFilter = "TranDate Between " & strDateStart & " And " & strDateEnd

Me.Filter = strFilter
Me.FilterOn = True
End If
End Sub

 

[Kamayo Ako]

use this code in your search button.
Option Compare Database
'Option Explicit << remove this



Private Sub Search_Button_Click()

Dim strFilter As String
Dim strDateStart As String
Dim strDateEnd As String

If IsNull(Forms![test].FromDate) Or Forms![test].ToDate = "" Then
MsgBox "Start Date and End Date Required", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Forms![test].FromDate.SetFocus
Cancel = True

Else

strDateStart = "#" & Format(Forms![test]![FromDate], "mm-dd-yyyy") & "#"
strDateEnd = "#" & Format(Forms![test]![ToDate], "mm-dd-yyyy") & "#"
strFilter = "TranDate Between " & strDateStart & " And " & strDateEnd

Me.Filter = strFilter
Me.FilterOn = True
End If
End Sub

 

[Minty]

'Option Explicit << remove this

You should always have this at the top of every code module.
It will help highlight at least 25% of the errors new users experience.

Removing it is very poor advice.

 

[Kamayo Ako]

You should always have this at the top of every code module.
It will help highlight at least 25% of the errors new users experience.

Removing it is very poor advice.

thank you for that concern. I'm just intermediate in VBA...

 

[ahmad_rmh]

Thanks Kamayo ...

 

[Gasman]

Thanks Kamayo ...

Put Option Explicit back though.