Startup Form Failure (A97 SR2)

Topic

New Reply

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.

Help.

Pat

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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;
—————————————————————————————————————————————————————–

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  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(WinReturns0,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(PlaceReturns0,1,0)) as Wins,"
        sSql = sSql & " sum(iif(PlaceReturns= #" & 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

—————————————————————————————————————————————————————–

Pat

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

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.

Pat

Reply | Quote

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

Reply | Quote

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 , 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

Reply | Quote