Function in query criteria (1 Viewer)

[Thicko]

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)

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

 

[plog]

Your function never executes past the If statements. When you set a value to the name of the function, it returns that value and you are done. If the 2 forms you explicitly check for are not open, it hits the default case and this happens:

WhichStockEntryFormIsOpen = "frmStockEntryLicensed"

When it does it returns "frmStockEntryLicensed" and the function is over, it never goes to the code below. You need to use a variable and assign the form open value to it, instead of WhichStockEntryFormIsOpen.

You only use WhichStockEntryFormIsOpen to return a value, you can't assign it values and hope to use them later in the function.

 

[Thicko]

Thanks plog. The code after the IF statement is run because I get the correct output from the Debug.Print

Just incase I have changed the function to:

Dim CurrentForm As String

If CurrentProject.AllForms("frmStockEntry").IsLoaded Then
     CurrentForm = "frmStockEntry"
     Else
     If CurrentProject.AllForms("frmStockEntryPreCompounded").IsLoaded Then
         CurrentForm = "frmStockEntryPreCompounded"
         Else
         CurrentForm = "frmStockEntryLicensed"
    End If
End If

WhichStockEntryFormIsOpen = "[Forms]![" & CurrentForm & "].[" & FieldName & "]"
Debug.Print WhichStockEntryFormIsOpen

However the same issue is still there

 

[plog]

Then the issue is with the query. My guess is you are trying to use what is returned from the query as a variable and not as a string. Can you post your SQL to confirm?

 

[MajP]

What about
WhichStockEntryFormIsOpen = forms(WhichStockEntryFormIsOpen).controls(FieldName).value

 

[Thicko]

Brilliant MajP, that's got it working.

Many Thanks All