Warehouse Inventory Project

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
 

Attachments

  • Capture.GIF
    Capture.GIF
    34.7 KB · Views: 108
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?
 
Do you actually need # when referring to form controls and they are actually a date format?
 
Its actually a shortdate in the table. May be there is a conrol reference issue with the main form or something else.
 
Why are you using Forms!formname!controlname in this context rather than Me.controlname????????
 
I am giving reference in the conrol source and it is an unbound field in subform.
 
In the Main form i have got the result it's working but in sub form it's generating error.
 
Last edited:
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.....
 
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?
 
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.
 
Put your code within code tags :(
Code:
=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. :(
 
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
 
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
 

Attachments

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...
 

Users who are viewing this thread

Back
Top Bottom