Hi All,
I have below function that is used return a field name depending on which form is open (which type of stock is being booked in)
In the relevant query the criteria is set to WhichStockEntryFormIsOpen("Product")
The query returns 0 records despite the Debug.Print WhichStockEntryFormIsOpen returning the right answer ([Forms]![frmStockEntry].[Product]). If I fix this answer as a test in the function it returns the correct number of records.
I can't work out why
"[Forms]![" & WhichStockEntryFormIsOpen & "].[" & FieldName & "]" = [Forms]![frmStockEntry].[Product] --> 0 records
[Forms]![frmStockEntry].[Product] --> 5 records
For some reason this used to work but now found out it isn't, and I can't work out why.
Many Thanks
I have below function that is used return a field name depending on which form is open (which type of stock is being booked in)
Code:
Public Function WhichStockEntryFormIsOpen(FieldName As String) As Variant
If CurrentProject.AllForms("frmStockEntry").IsLoaded Then
WhichStockEntryFormIsOpen = "frmStockEntry"
Else
If CurrentProject.AllForms("frmStockEntryPreCompounded").IsLoaded Then
WhichStockEntryFormIsOpen = "frmStockEntryPreCompounded"
Else
WhichStockEntryFormIsOpen = "frmStockEntryLicensed"
End If
End If
WhichStockEntryFormIsOpen = "[Forms]![" & WhichStockEntryFormIsOpen & "].[" & FieldName & "]"
Debug.Print WhichStockEntryFormIsOpen
'Test only
'WhichStockEntryFormIsOpen = "Sodium Chloride 0.9%"
'WhichStockEntryFormIsOpen = [Forms]![frmStockEntry].[Product]
End Function
In the relevant query the criteria is set to WhichStockEntryFormIsOpen("Product")
The query returns 0 records despite the Debug.Print WhichStockEntryFormIsOpen returning the right answer ([Forms]![frmStockEntry].[Product]). If I fix this answer as a test in the function it returns the correct number of records.
I can't work out why
"[Forms]![" & WhichStockEntryFormIsOpen & "].[" & FieldName & "]" = [Forms]![frmStockEntry].[Product] --> 0 records
[Forms]![frmStockEntry].[Product] --> 5 records
For some reason this used to work but now found out it isn't, and I can't work out why.
Many Thanks