Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Startup Form Failure (A97 SR2)

    Here's one for you.
    I have a form that is entered as defined by the Start up procedures (ie. automatically when the DB is first fired up).

    This form is unbound but there is a Command Button that fires up a procedure that uses a query as part of a recordset, etc. This query also uses Functions that are defined under the Modules tab as Public functions. One of these functions is called 'NumberofWins'.
    I get the following problem if I place a line of code to run the Command Button in the OnActivate event of the form (I have tried the OnOpen, OnLoad, OnCurrent events as well, but they all result in the following error):

    Run-Time Error 3085
    Undefined Function 'NumberofWins' in expression.

    If I run the form from the Database window it runs ok. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Help.

    Pat

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Startup Form Failure (A97 SR2)

    Hi Pat,

    You might try to
    - Decompile the database (I presume you've encountered this in the Access forum, if not do a search),
    - Compile and save all modules,
    - Compact the database.

    You can also try importing all database objects into a new database; don't forget to set the Startup options in the new database.

    If this doesn't help, can you provide some more information about the NumberOfWins function and how it is used in the query?

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Startup Form Failure (A97 SR2)

    Thanks Hans, but nogo. I tried everything but creating a new database and importing all the objects.

    It seems that it won't work if the form is not visible (clutching at straws here) or something is not quite initiated with the database initially.

    If I just hit ok on the dialog box when the error happens it brings the form up without running the command button code (obviously because I stopped the code from running). If I then hit the command button it works. However I would like it to automatically when the database is initially opened.

    Any more ideas?

    Pat

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Startup Form Failure (A97 SR2)

    Perhaps you can post the code in the OnActivate event of the form (and any code called by it), the code of the function and the SQL of the query?

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Startup Form Failure (A97 SR2)

    Are you sure you want all this. As I said, if I run the form from the database window it works fine. It's almost as if there is a timing issue here,
    in that the database has not initialised itself properly.
    Excuse the long lines here.

    The function:
    Public Function NumberofWins(FormofHorse As Variant) As Integer
    Dim intNoofStarts, intPos As Integer, strPos As String
    NumberofWins = 0

    If IsNull(FormofHorse) Then Exit Function

    intNoofStarts = 0
    Dim intNoofPlaces As Integer
    intNoofPlaces = 0
    intPos = Len(FormofHorse) + 1
    ' Count the number of wins in last 5 starts
    Do While intPos > 1
    intPos = intPos - 1
    strPos = Mid(FormofHorse, intPos, 1)
    If strPos = "1" Then
    intNoofPlaces = intNoofPlaces + 1
    End If
    Loop
    NumberofWins = intNoofPlaces
    End Function
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------

    The OnLoad event (I took it out of the OnActivate event because it gets executed more than once):
    Private Sub Form_Load()
    Dim ii As Integer
    For ii = 1 To 1000
    DoEvents
    Next ii
    CommandRecalc_Click
    End Sub

    nb. The line CommandRecalc_Click opens up a recordset with the above query as it's source.
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------

    The SQL of the query:
    SELECT Det.DateofVenue, Det.TimeofRace AS TimeSched, Det.Venue, Det.RaceNo, Det.TypeofRace, Det.Class, Hdr.StateofTrack, Det.Distance, Det.HorseName, Det.FormofHorse, NumberofWins([formofhorse]) AS Wins, NumberofPlaces([formofhorse]) AS Places, Det.Barrier, Det.Placing, Det.ExcludeReason, Det.Special, ExcludeReason.Exclude, Det.WinDiv, Det.NewWinDiv, Det.WinPool, Det.WinReturns, Det.WinOutlay, Det.Comments, Hdr.ExcludeMtg, [Venues Midweek].ExcludeMeeting, Det.MultiReturns, Det.MultiOutlay, Det.MultiWinDiv, anyzero([formofhorse]) AS AnyZero, last3placed([formofhorse]) AS L3P, Right([formofhorse],2) AS r2h, Last5w2gt6th([FormofHorse]) AS L5gt6, WininLast2([FormofHorse]) AS WinL2, PlaceinLast2([FormofHorse]) AS Plinlast2, Det.PlaceReturns, Det.PlaceOutlay, Det.NewPlaceDiv, Month(Det.[DateofVenue]) AS Month, Weekday([det].[dateofvenue]) AS Days, Det.PlacePool, Det.PlaceDiv, Month([det].[DateofVenue]) AS DteDate, Day([det].[DateofVenue]) AS DayofMonth, Len([formofhorse]) AS LenForm, Left(Right([formofhorse],2),1)>Right(Right([formofhorse],2),1) AS GettingBetter, (Left(Right([formofhorse],2),1)="1" Or Right(Right([formofhorse],2),1)="1") And Right([formofhorse],2)<>"10" And Right([formofhorse],2)<>"01" AS aWnrInLast2, charter([formofhorse],1,5,3,True) AS CharterPub
    FROM ([MidWk Hdr] AS Hdr INNER JOIN [Venues Midweek] ON Hdr.Venue = [Venues Midweek].Venue) INNER JOIN ([MidWk Hdr Det] AS Det LEFT JOIN ExcludeReason ON Det.ExcludeReason = ExcludeReason.ExcludeReason) ON (Hdr.Venue = Det.Venue) AND (Hdr.DateofVenue = Det.DateofVenue)
    WHERE (((Det.TypeofRace)<>"M") AND ((Hdr.StateofTrack)<>"Heavy") AND ((Det.HorseName)<>"HorseName") AND ((Det.FormofHorse) Like "*1") AND ((Hdr.ExcludeMtg)=False) AND (([Venues Midweek].ExcludeMeeting)=False) AND ((Weekday([det].[dateofvenue])) In (1,7,2,3,4,5,6)) AND ((Month([det].[DateofVenue]))=12) AND ((Day([det].[DateofVenue]))<16)) OR (((Det.TypeofRace)<>"M") AND ((Hdr.StateofTrack)<>"Heavy") AND ((Det.HorseName)<>"HorseName") AND ((Det.FormofHorse) Like "*1") AND ((Hdr.ExcludeMtg)=False) AND (([Venues Midweek].ExcludeMeeting)=False) AND ((Weekday([det].[dateofvenue])) In (1,7,2,3,4,5,6)) AND ((Month([det].[DateofVenue])) Not In (6,7,8,12)))
    ORDER BY Det.DateofVenue, Det.TimeofRace, Det.Venue, Det.RaceNo;
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------
    <pre>The code called by the line CommandRecalc_Click:
    Private Sub CommandRecalc_Click()
    Select Case FrameOutlay
    Case 1, 3, 4, 5
    If IsNull(TextOutlay) Then
    MsgBox "Please enter an Outlay !!"
    Exit Sub
    End If
    End Select

    DoCmd.Close acQuery, "qry MidWk Hdr Det Table", acSaveNo
    DoCmd.Close acQuery, "qry MidWk Hdr Det Table by Month", acSaveNo
    DoCmd.Close acQuery, "qry MidWk Hdr Det Sum by Month", acSaveNo
    DoCmd.Close acQuery, "qry MidWk Hdr Det Multi Sum by Month", acSaveNo
    Dim dbs As Database, rs As Recordset
    Set dbs = CurrentDb
    Dim sSql As String
    sSql = "UPDATE [MidWk Hdr Det] SET WinReturns=0, NewWinDiv=0, PlaceReturns=0, NewPlaceDiv=0 "
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSql
    DoCmd.SetWarnings True
    Dim minWinPool As Long
    ' sSql = "SELECT Sum([qry MidWk Hdr Det Table].WinPool)/Count(*) AS AvgWinPool"
    ' sSql = sSql & " FROM [qry MidWk Hdr Det Table];"
    minWinPool = 0
    If FrameOutlay = 2 Or FrameOutlay = 3 Then
    minWinPool = 50000
    '???? minWinPool = rs!avgwinpool
    End If

    sSql = "SELECT * FROM [qry MidWk Hdr Det Table]"
    If Not IsNull(TextMonth) Then
    sSql = sSql & " WHERE Month(DateofVenue)=" & TextMonth
    End If
    sSql = sSql & " ORDER BY DateofVenue, TimeSched, Venue, RaceNo"
    Set rs = dbs.OpenRecordset(sSql)
    Dim sglWinners As Single, curNewDiv, curPool, curWinOutlay As Currency, curPlaceOutlay As Currency
    rs.MoveFirst
    Dim intPool As Long, chkWin As Boolean, intOutlay As Long, curDiv As Currency
    Dim intMultiples As Integer
    Do While Not rs.EOF
    rs.Edit
    ' If rs!BetOnIt Then
    ' rs!NewWinDiv = rs!WinDiv
    ' rs!ActualWinReturns = rs!ActualWinOutlay * rs!WinDiv
    ' End If
    '' If Check4ExclMidWk(rs!FormofHorse, rs!Class, rs!TypeofRace) Then
    curWinOutlay = 0
    curNewDiv = 0
    '' Else
    Dim lngPool As Long
    If CheckWin Then
    lngPool = rs!WinPool
    Else
    lngPool = rs!PlacePool
    End If
    If IsNull(lngPool) Or lngPool = 0 Then
    curWinOutlay = 0
    curNewDiv = 0
    Else
    intPool = lngPool
    If CheckApplyMinOutlay And intPool < minWinPool Then
    intPool = minWinPool
    End If
    If FrameOutlay = 1 Then
    ' Fixed outlay
    intMultiples = 1
    curWinOutlay = TextOutlay
    ElseIf FrameOutlay = 2 Then
    ' Outlay is 10% of Net pool
    intMultiples = 10 * 50
    ' curWinOutlay = (Fix((CalcNetPool(intPool, 1) * 10 / 100) / intMultiples) + 1) * intMultiples
    curWinOutlay = (Fix((intPool * 10 / 100) / intMultiples) + 1) * intMultiples
    ElseIf FrameOutlay = 3 Then
    ' Outlay is textPerc% of Net pool
    intMultiples = TextOutlay * 50
    curWinOutlay = (Fix((CalcNetPool(intPool, 1) * TextOutlay / 100) / intMultiples) + 1) * intMultiples
    ' curWinOutlay = (Fix(curWinOutlay / intMultiples) + 1) * intMultiples
    curWinOutlay = (Fix((intPool * TextOutlay / 100) / intMultiples) + 1) * intMultiples
    ElseIf FrameOutlay = 4 Then
    ' Initial Amount doubled up to 4 times
    intMultiples = 1
    curWinOutlay = TextOutlay
    ElseIf FrameOutlay = 5 Then
    ' Initial Amount and add Additional amount up to 4 times
    intMultiples = 1
    curWinOutlay = TextOutlay
    End If
    ' Ensure Outlay is a multiple of $10
    curWinOutlay = Fix(curWinOutlay / 10) * 10
    If CheckWin Then
    If rs!Placing = 1 Then
    If CheckInclExcl Then
    intOutlay = curWinOutlay
    chkWin = True
    curDiv = rs!WinDiv
    curNewDiv = CurNewDivi(intPool, chkWin, intOutlay, curDiv)
    rs!NewWinDiv = curNewDiv
    curNewDiv = curNewDiv * curWinOutlay
    ElseIf Not rs!Exclude Or IsNull(rs!Exclude) Then
    intOutlay = curWinOutlay
    chkWin = True
    curDiv = rs!WinDiv
    curNewDiv = CurNewDivi(intPool, chkWin, intOutlay, curDiv)
    rs!NewWinDiv = curNewDiv
    curNewDiv = curNewDiv * curWinOutlay
    Else
    curNewDiv = 0
    End If
    Else
    curNewDiv = 0
    End If
    Else
    If rs!Placing <> 0 And rs!PlaceDiv <> 0 Then
    If CheckInclExcl Then
    intOutlay = curWinOutlay
    chkWin = False
    curDiv = rs!PlaceDiv
    curNewDiv = CurNewDivi(intPool, chkWin, intOutlay, curDiv)
    rs!NewplaceDiv = curNewDiv
    curNewDiv = curNewDiv * curWinOutlay
    ElseIf Not rs!Exclude Or IsNull(rs!Exclude) Then
    intOutlay = curWinOutlay
    chkWin = False
    curDiv = rs!PlaceDiv
    curNewDiv = CurNewDivi(intPool, chkWin, intOutlay, curDiv)
    If curNewDiv < 1.01 Then
    If rs!PlaceDiv < 1 Then
    curNewDiv = rs!PlaceDiv
    Else
    curNewDiv = 1
    End If
    End If
    rs!NewplaceDiv = curNewDiv
    curNewDiv = curNewDiv * curWinOutlay
    Else
    curNewDiv = 0
    End If
    Else
    curNewDiv = 0
    End If
    End If
    End If
    '' End If
    If CheckInclExcl Then
    If CheckWin Then
    rs!WinReturns = curNewDiv - curWinOutlay
    Else
    rs!PlaceReturns = curNewDiv - curWinOutlay
    End If
    ElseIf rs!Exclude Then
    If CheckWin Then
    rs!WinReturns = curNewDiv
    Else
    rs!PlaceReturns = curNewDiv
    End If
    Else
    If CheckWin Then
    rs!WinReturns = curNewDiv - curWinOutlay
    Else
    If rs!PlaceDiv = 0 And rs!Placing > 0 Then
    rs!PlaceReturns = curNewDiv - curWinOutlay
    Else
    rs!PlaceReturns = curNewDiv - curWinOutlay
    End If
    End If
    End If
    If CheckWin Then
    rs!WinOutlay = curWinOutlay
    Else
    rs!PlaceOutlay = curWinOutlay
    End If
    rs.Update
    rs.MoveNext
    Loop
    If CheckWin Then
    sSql = "SELECT Sum(WinReturns) AS Winnings,"
    sSql = sSql & " Sum(iif(WinReturns<0,WinReturns,0)) as Losses,"
    sSql = sSql & " sum(iif(winReturns>0,1,0)) as Wins,"
    sSql = sSql & " sum(iif(winReturns<0,1,0)) as Loses,"
    sSql = sSql & " sum(WinOutlay)/Count(WinOutlay) as AvgOutlay"
    Else
    sSql = "SELECT Sum(PlaceReturns) AS Winnings,"
    sSql = sSql & " Sum(iif(PlaceReturns<0,PlaceReturns,0)) as Losses,"
    sSql = sSql & " sum(iif(PlaceReturns>0,1,0)) as Wins,"
    sSql = sSql & " sum(iif(PlaceReturns<0,1,0)) as Loses,"
    sSql = sSql & " sum(PlaceOutlay)/Count(PlaceOutlay) as AvgOutlay"
    End If
    sSql = sSql & " FROM [qry MidWk Hdr Det Table]"
    If Not IsNull(TextMonth) Then
    ' Particular month
    sSql = sSql & " WHERE Month(DateofVenue)=" & TextMonth
    If CheckInclExcl Then
    ' All results including excludes
    Else
    ' Dont include excludes
    sSql = sSql & " AND ( Not Exclude Or IsNull(Exclude)) "
    End If
    Else
    ' Null month - all months
    If CheckInclExcl Then
    ' All results including excludes
    Else
    ' Dont include excludes
    sSql = sSql & " WHERE (Not Exclude Or IsNull(Exclude)) "
    End If
    End If
    Set rs = dbs.OpenRecordset(sSql)
    rs.MoveFirst

    TextReturn = rs!Winnings
    TextLosses = rs!Losses
    TextWins = rs!Wins
    TextLoses = rs!Loses
    TextAvgOutlay = rs!AvgOutlay
    TextPerc = (TextWins * 100) / (TextWins + TextLoses)
    Dim strSql As String
    strSql = sSql & IIf(InStr(1, sSql, "WHERE", vbTextCompare) = 0, " WHERE ", " AND ") & " DateofVenue >= #" & Format(FromDate, "mm/dd/yy") & "#"
    Set rs = dbs.OpenRecordset(strSql)
    TextLast12Mths = rs!Winnings

    ' GoTo XXXexit

    Dim sSqlA As String

    sSqlA = "StateofTrack='Heavy'"
    Set rs = dbs.OpenRecordset(sSql & IIf(InStr(1, sSql, "WHERE", vbTextCompare) = 0, " WHERE ", " AND ") & sSqlA)
    rs.MoveFirst
    TextReturnHeavy = rs!Winnings
    TextLossesHeavy = rs!Losses
    TextWinsHeavy = rs!Wins
    TextLosesHeavy = rs!Loses
    TextHeavyPerc = (TextWinsHeavy * 100) / (TextWinsHeavy + TextLosesHeavy)

    sSqlA = "StateofTrack='Slow'"
    Set rs = dbs.OpenRecordset(sSql & IIf(InStr(1, sSql, "WHERE", vbTextCompare) = 0, " WHERE ", " AND ") & sSqlA)
    rs.MoveFirst
    TextReturnSlow = rs!Winnings
    TextLossesSlow = rs!Losses
    TextWinsSlow = rs!Wins
    TextLosesSlow = rs!Loses
    TextSlowPerc = (TextWinsSlow * 100) / (TextWinsSlow + TextLosesSlow)

    sSqlA = "StateofTrack='Dead'"
    Set rs = dbs.OpenRecordset(sSql & IIf(InStr(1, sSql, "WHERE", vbTextCompare) = 0, " WHERE ", " AND ") & sSqlA)
    rs.MoveFirst
    TextReturnDead = rs!Winnings
    TextLossesDead = rs!Losses
    TextWinsDead = rs!Wins
    TextLosesDead = rs!Loses
    TextDeadPerc = (TextWinsDead * 100) / (TextWinsDead + TextLosesDead)

    sSqlA = "StateofTrack='Good'"
    Set rs = dbs.OpenRecordset(sSql & IIf(InStr(1, sSql, "WHERE", vbTextCompare) = 0, " WHERE ", " AND ") & sSqlA)
    rs.MoveFirst
    TextReturnGood = rs!Winnings
    TextLossesGood = rs!Losses
    TextWinsGood = rs!Wins
    TextLosesGood = rs!Loses
    TextGoodPerc = (TextWinsGood * 100) / (TextWinsGood + TextLosesGood)

    sSqlA = "StateofTrack='Fast'"
    Set rs = dbs.OpenRecordset(sSql & IIf(InStr(1, sSql, "WHERE", vbTextCompare) = 0, " WHERE ", " AND ") & sSqlA)
    rs.MoveFirst
    TextReturnFast = rs!Winnings
    TextLossesFast = rs!Losses
    TextWinsFast = rs!Wins
    TextLosesFast = rs!Loses
    [TextFast%] = (TextWinsFast * 100) / (TextWinsFast + TextLosesFast)
    XXXexit:
    Set rs = Nothing
    Set dbs = Nothing
    If IsNull(TextMonth) Then
    DoCmd.OpenQuery "qry MidWk Hdr Det Table"
    DoCmd.OpenQuery "qry MidWk Hdr Det Sum by Month"
    Else
    DoCmd.OpenQuery "qry MidWk Hdr Det Table"
    ' DoCmd.OpenQuery "qry MidWk Hdr Det Table by Month"
    End If
    ' DoCmd.Close

    End Sub
    </pre>


    -----------------------------------------------------------------------------------------------------------------------------------------------------------------

    Pat

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Startup Form Failure (A97 SR2)

    Something interesting, when the form fails, I then try and run the query (that you have there in my last post) and I get #Error in the field NumberofWins as well as the other functions that are mentioned in the query.
    It almost seems that the functions (all are defined in the Modules section) are not available at that stage of opening the database.

    As soon as I exit from the error on the form the #Errors disappear from the query and it executes the functions correctly.

    Pat

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Startup Form Failure (A97 SR2)

    Pat,

    It's very strange indeed. I constructed a small database (of course I didn't try to reconstruct everything from your post). I had no problem running a query that uses your function from the OnLoad routine of the startup form...

    My last attempt for now: insert a small dummy procedure into the module containing NumOfWins, e.g.

    Public Sub MyBeep()
    Beep
    End Sub

    and call this at the start of the CommandRecalc_Click procedure. Perhaps this will force the module to be loaded...

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Startup Form Failure (A97 SR2)

    Hans

    Thanks for your help, it still doesn't work. Don't worry about it now, I will just have to click the button on the form manually after entering the database.
    Anyhow, thanks for your time.

    Pat

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Startup Form Failure (A97 SR2)

    Just thought I would let you know that I deleted the form opening in the Startup box and put an Openform in an Autoexec macro and it worked.

    Hmmm, It seems that Access is not quite finished initializing at Startup form time but seems to be ok at Autoexec macro time.

    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Pat

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Startup Form Failure (A97 SR2)

    Good you found a workaround. Thanks for reporting this, it might be useful to others having the same problem too.

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Startup Form Failure (A97 SR2)

    Dear valuable members of the board,
    from my humble experience, yes, I think this is a tip for general use.
    It certainly seems a bit arrogant inserting this post now, in the end <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>, but reading through this thread, scrolling down, in my head, I was already wondering why you didn't use AutoExec to trigger a function (or another procedure through that function) at the startup of a database. As far as I used it (but this, to be said, not at a professional application level) it has always worked fine. For what it's worth...
    Hasse

Posting Permissions

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