Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Calling up a function procedure using a query

    Hi,
    I inherited a project from my previous number and he had written a module procudure to interprest results of an exam. The module is shown below:



    Option Compare Database

    ' User defined type for multiple returns from function MatchAnswers

    Public Type Answers
    Correct As Integer
    InCorrect As Integer
    Total As Integer
    TotalCorrect As Integer
    PercentCorrect As Single ' Percentage rounded to 2 decimal places
    End Type

    Function IsFormOpen(szName As String)
    IsFormOpen = (SysCmd(acSysCmdGetObjectState, acForm, szName) <> 0)
    End Function


    Public Function MatchAnswers(intQuestion As Integer, strCheck As String) As Answers

    ' Function to extract data from the Question sets.
    '
    ' Can return: Total Answers on sheet, Total Correct Answers, Correct Answers give, Incorrect answers given
    ' Percentage of correct answers given against total correct answers
    '
    ' Uses a user-defined type called answers - Defined above ^^^
    '
    '


    Dim intCountCorrect As Integer
    Dim strMatchAnswers As String
    Dim strMatch As String
    Dim strMatchCorrect As String
    Dim intCorrectAnswer As Integer
    Dim intIncorrectAnswer As Integer
    Dim intCorrect As Integer
    Dim strSQL As String


    On Error GoTo err_handle

    Dim rs As Recordset

    ' Select correct answers from crib table
    strSQL = "SELECT * FROM Level1CorrectAnswers WHERE QuestionNo=" & Trim(Str(intQuestion)) & ";"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

    ' Total possible answers
    MatchAnswers.Total = rs.Fields("Answers")
    intCorrect = rs.Fields("CorrectAnswers")

    ' Total correct answers
    MatchAnswers.TotalCorrect = intCorrect
    strMatchAnswers = rs.Fields("MatchAnswers")

    rs.Close

    Do
    strMatch = Left(strCheck, InStr(1, strCheck, ";"))
    strCheck = Right(strCheck, Len(strCheck) - InStr(1, strCheck, ";"))
    If InStr(1, strMatchAnswers, strMatch) > 0 Then
    intCorrectAnswer = intCorrectAnswer + 1
    Else
    intIncorrectAnswer = intIncorrectAnswer + 1
    End If
    Loop Until strCheck = ""

    ' Correct answers given
    MatchAnswers.Correct = intCorrectAnswer

    ' InCorrect answers given
    MatchAnswers.InCorrect = intIncorrectAnswer

    ' Percentage of Correct answers given - ignores incorrect
    MatchAnswers.PercentCorrect = Round((intCorrectAnswer / intCorrect) * 100, 2)

    Exit Function

    err_handle:

    ' If error give obviously wrong answers
    MatchAnswers.Correct = -32768
    MatchAnswers.InCorrect = -32768
    MatchAnswers.PercentCorrect = -32768
    MatchAnswers.Total = -32768
    MatchAnswers.TotalCorrect = -32768

    End Function

    The problem is when I use a reporting query exactly like he'd asked ie:
    Q2Correct:MatchAnswers.Correct(2,[Question2])

    I get the following error message "Undefined function 'MatchAnswers.Correct' in expression".

    I came across a similar problem in the forum and the person solved the problem by changing the module name to 'Main'. I tried that but with no success. Would appreciate any help.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The correct syntax should be:
    Code:
    MatchAnswers(2, [Question2]).Correct
    However when I try this Access (2003) reports a general error and closes, though this works in the debug window:
    Code:
    print MatchAnswers(2, [Question2]).Correct
    I'm afraid you cannot use a function returning a user-defined type in a query .

    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    Thanks Sinndho. I will use correct syntax.

    Any ideas on how I can call up the function if a query is out of the question?
    Last edited by luckyluke44; 03-11-09 at 11:34.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If for any reason using the MatchAnswers function is necessary, what I would do would be to encapsulate it into another function when I call it from a query, like this:

    Code:
    Public Enum AnswersElements
        Correct = 1
        InCorrect = 2
        Total = 3
        TotalCorrect = 4
        PercentCorrect = 5
    End Enum
    
    Public Function GetMatchAnswersElement(intQuestion As Integer, strCheck As String, intElement As AnswersElements) As Variant
    
    Select Case intElement
        Case Correct
            GetMatchAnswersElement = MatchAnswers(intQuestion, strCheck).Correct
        Case InCorrect
            GetMatchAnswersElement = MatchAnswers(intQuestion, strCheck).InCorrect
        Case Total
            GetMatchAnswersElement = MatchAnswers(intQuestion, strCheck).Total
        Case TotalCorrect
             GetMatchAnswersElement = MatchAnswers(intQuestion, strCheck).TotalCorrect
       Case PercentCorrect
            GetMatchAnswersElement = MatchAnswers(intQuestion, strCheck).PercentCorrect
    End Select
    
    End Function
    Now I can write the query like this:

    Code:
    Q2Correct:GetMatchAnswersElement(2, [Question2], Correct)
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •