Warehouse Inventory Project

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

 

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

 

[ahmad_rmh]

Thanks Kamayo ...

after putting option explicit back again error going on the following.

Cancel = True

 

[Kamayo Ako]

Put Option Explicit back though.

I knew you are expert sir. But possible sir you can check the attachment . If i put back the "Option explicit" the sorting has an error when i remove its working . I need your help on how to avoid that error even with Option Explicit declared.

 

[jdraw]

RE testver2rev1.accdb

I would recommend
-use Option Explicit as second row in all modules
-adjust the date checking/validation

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

-dim Dates as Date

You are misusing Cancel = True. That is an option on some events. Perhaps I am missing your intent.

 

[Kamayo Ako]

RE testver2rev1.accdb

I would recommend
-use Option Explicit as second row in all modules
-adjust the date checking/validation

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

-dim Dates as Date

You are misusing Cancel = True. That is an option on some events. Perhaps I am missing your intent.

THANK YOU SO MUCH SIR

 

[ahmad_rmh]

Dear Experts,

I have made a QOH value in inventory adjustment form (dlookup function) to see how much QOH for inventory adjustment.

But the issue is that QOH is dynamically changing as the QOH gets changed.

How to make static as the record saved?

======get solved by myself===============

 

[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

====get solved by myself=============

 

[Gasman]

These forums are here to help people.
You have had lots of help here.
Please reciprocate and post the solutions to your issues. That might help someone else in the future, with similar issues.
It is all about give and take. Do not just take.