Report text box calling a function not working

[EdAROC]

I'm using a report to create a printout showing each manufacturing operation of a job along with information relevant to that operation. When there is a foil stamping operation a list of the foils used is to be printed. To create this list I've written a function - actually I copied a function that builds a list of the inks used in the printing operation and modified the code.

[I'm trying to anticipate questions. Please let me know if I've included too much info!]

When I run the report:
#Error
prints where I expect to see:
Foil: {foil1} ~ {foil2} ~ {foil3}
(The 3 foils in the job/order I'm using to test the report).

Here's information about the database, queries and report:

In a table ORDERS is a field ORDER_NO, it's an Integer.

qry1: Field: ORDERNO: ORDER_NO
This query collects information for each order of the job. (A job is a collection of one or more orders that are produced at the same time.)
I have to "convert" ORDER_NO to ORDERNO because the ERP database named the field for the JOB_NUMBER as ORDER_NO in one of the primary tables being used in the queries.

qry2: Field: ORDERNO Table: qry1
The values retrieved are correct, (right justified in the column) when I run the query

rpt1 has qry2 as the data source.

The text box that prints the foil list has:
Control Source: =IIf([MACH_NO] In (240,241,245,280,281,285,290,291,295),"Foil: " & FoilList([ORDERNO]),"")
When I remove ' & FoilList([ORDERNO])' the "Foil:" does print.
When I add back the FoilList stuff the output shows "#Error".

Module: Public Function FoilList(OrdNo As Integer) As String

I set a breakpoint at: Set db = CurrentDb, it's never reached - report is printed.

Here's the coding for the function:
Public Function FoilList(OrdNo As Integer) As String
Dim db As Database, rs As Recordset, strSQL As String, strList As String
Dim strDesc As String, strLastDesc As String

'Creates ~ delimited list of Foil Descriptions

'Return reference to current database.
Set db = CurrentDb

strSQL = "SELECT film_cst.film_desc from leaf"
strSQL = strSQL & " inner join orders on orders.spec_no = leaf.spec_no"
strSQL = strSQL & " inner join film_cst on film_cst.film_code = leaf.film_code"
strSQL = strSQL & " where orders.order_no = " & OrdNo & " and film_cst.film_type = 'L'"
strSQL = strSQL & " order by film_cst.film_desc"

Set rs = db.OpenRecordset(strSQL)

If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
strDesc = rs("film_desc")
'No Duplicates
If strDesc <> strLastDesc Then
If Len(strList) = 0 Then
strList = Trim(strDesc)
Else
strList = strList & " ~ " & Trim(strDesc)
End If
'Store the last description value
strLastDesc = strDesc
End If
rs.MoveNext
Loop

FoilList = strList

Else
FoilList = ""
End If

rs.Close

Set rs = Nothing
Set db = Nothing

End Function
--------------------

The SQL statement works (tested outside of Access). Unless, at the end of the WHERE clause the 'L' (single quotes) are not acceptable.

 

[jebry]

Hi!

Send both Mach_No and OrderNo to the function and do your If statement in the code. Then set the control source to:

=FoilList([OrderNo], [Mach_No])

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[EdAROC]

I'm working on your suggestion.
What would be VBA equivalent for the If statement condition used in Access?
VBA doesn't like the "In" functionality.
If MachNO Not In(240,241,245,280,281,285,290,291,295) Then

 

[Golom]

You're gonna hate it

If Not (    MachNO = 240 _
         OR MachNO = 241 _
         OR MachNO = 245 _
         OR MachNO = 280 _
         OR MachNO = 281 _
         OR MachNO = 285 _
         OR MachNO = 290 _
         OR MachNO = 291 _
         OR MachNO = 295) Then

or

If InStr(1,",240,241,245,280,281,285,290,291,295,", & _
           "," & Format(MachNO,"000") & ",") = 0 Then

 

[EdAROC]

Yuck. BUT! I don't hate it - The idea of new functions is to make the programmer's life easier by simplifying the code. Hence, the In(...) function in Access.

Geez. There are pros and cons for each. I had thought about the InStr function. It looks "cleaner" to me, but, am wary - it would fail if we decided to add two digit machine#s, like 40, wouldn't it?

Well, off to the "workbench" to do some rebuilding.

THANKS!

 

[Golom]

... it would fail if we decided to add two digit machine#s

No. Just change it to

If InStr(1,",240,241,245,280,281,285,290,291,295,", & _
           "," & Format(MachNO,[red]"0"[/red]) & ",") = 0 Then

which will work as long as you don't have machines with leading zeros.

If you want the function route then

Public Function IsIn(TheValue As Variant, _
                     ParamArray Vals() As Variant) As Boolean
    Dim x As Variant
    IsIn = False
    For Each x In Vals
        If Not IsNull(x) Then
            If TheValue = x Then
                IsIn = True
                Exit For
            End If
        End If
    Next
End Function

And use in code as

If IsIn(280,240,241,245,280,281,285,290,291,295) Then

 

[EdAROC]

I'm wondering why this technique (my original email) is failing. The concept is used elsewhere. The only difference I can see is the source of the argument. In the others the query entry was a table field, in this one I had to "massage" things a bit and build an expression. (See either original message or 2nd paragraph down).

Question - could it be failing because ORDERNO is not an Integer? (And in the coding I'm declaring it as an Integer).

What data type is created in a query with an expression: ORDERNO: [ORDER_NO] {ORDER_NO is an Integer field in a table}? For my own future reference - can I control the data type that is created?