Newbie Questions

Re: InfoPath 2003 Cannot Open User Defined Function

Hilary Stoupa — Fri, 14 Oct 2011 15:24:05 GMT

I know less than nothing about Access - is the array formula used in AssignmentsLookUp_Q? I'm assuming AssignmentsLookUp_Q returns a record set in Access, and the recordset you expect....

The heart of the issue is this:

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'CheckFields' in expression.

Searching the web for [Microsoft][ODBC Microsoft Access Driver] Undefined function in expression brings back some results that may be helpful for you - it seems like there may be a chance that the issue is with some syntax in the function that is not supported. I'm sorry I can't be more help here - again, my Access knowledge is extremely limited.

Re: InfoPath 2003 Cannot Open User Defined Function

gdgonzal — Fri, 14 Oct 2011 02:33:47 GMT

Thanks Hilary, but I don't believe the schema is rendering correctly.

Here's the full error I am getting:

The SQL statement cannot be represented as a tree view. To remove the SQL statement, click Edit SQL, and then delete the statement.

This is what is displayed within the SQL window:

select * from [AssignmentsLookUp_Q]

This is the error I get with I hit the next button:

[0x80040E14][Microsoft OLE DB Provider for ODBC Drivers] [Microsoft][ODBC Microsoft Access Driver] Undefined function 'CheckFields' in expression.

This is the UDF I am using within Access 2003; which basically check several field within the query I am using as a main data source to determine if they've been populated by the user:

Public Function CheckFields(ParamArray sArr()) As String
Dim j, fCnt As Integer

For j = 0 To UBound(sArr)
    If sArr(j) & "" <> "" Then
       fCnt = fCnt + 1
    End If

Select Case fCnt
      Case 0
      CheckFields = "Not Upated"
     Case Is = UBound(sArr) + 1
      CheckFields = "Update Completed"
     Case Else
      CheckFields = "Not Updated"

End Select

End Function

Finally, this is the array formula where I apply the above UDF to evaluate for populated fields:

Record Updated By Buyer: CheckFields([Questionaire_T]![Synopsis],[Questionaire_T]![SourcingEngagementDt],[Questionaire_T]![TimeframeStartDt],[Questionaire_T]![TimeframeFinishDt],[Questionaire_T]![3rdPartyDuration],[Questionaire_T]![SupplierName],[Questionaire_T]![SupplierBudget],[Questionaire_T]![CommCode],[Questionaire_T]![CommCodeDescription],[Questionaire_T]![CommodityGroup],[Questionaire_T]![CommodityGroupDescription],[Questionaire_T]![Sourcing Involved],[Questionaire_T]![BuyerName],[Questionaire_T]![SupplierRisk],[Questionaire_T]![SupplierPaymentTerms],[Questionaire_T]![SupplierQuoteValidity],[Questionaire_T]![HoneywellQuoteValidity])

Re: InfoPath 2003 Cannot Open User Defined Function

Hilary Stoupa — Thu, 13 Oct 2011 22:50:43 GMT

Hi - is the message that the SQL can't be displayed as a tree? If so, you can safely ignore that - when we add a SQL data connection, there is a dialog that likes to display the tree view for our returned dataset. As long as the form itself generates an appropriate schema for the data source, whether it displays correctly in a dialog in the DB connection wizard is ignorable.

InfoPath 2003 Cannot Open User Defined Function

gdgonzal — Thu, 13 Oct 2011 22:41:01 GMT

Hello,

I have a user defined function that works perfectly within Access 2003; however, when I attempt to open it as both main and secondary data source I receive error stating that the SQL tree statement is not supported (something similar to that anyway).

Is there a workaround for this issue?

Thank you