OnTimer not firing

[Peter Kinross]

If I open a form from within another Sub:

Code: Select all

DoCmd.OpenForm "frmFunds", acNormal

the OnOpen event fires:

Code: Select all

Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 500  '1/2 sec
Me.OnTimer = "=CallGetTot()"
Debug.Print Me.TimerInterval; Me.OnTimer
End Sub

The Debug.Print, prints the correct values. However the OnTimer does not get entered, nor does the TimerInterval get entered, and hence the event never fires. The OnOpen event is in the Form's code.
Yet, if I open the form directly the events all get entered and fire as expected.
Yesterday all this worked. Don’t think I did anything to the calling procedure. I have changed frmFunds a bit though, but I cannot find anything that would cause this.

[HansV]

Does the problem persist after quitting and restarting Access? After restarting Windows?

[Peter Kinross]

Unfortunately restarting Windows (and obviously Access) made no diff.

[HansV]

Try decompiling the database. See Decompile Your Microsoft Access Database to Improve Performance, Fix Corruption, and Avoid Strange VBA Errors.

[Peter Kinross]

Ha ha! I had forgotten that one. I used to do it frequently when I had replicated Dbs. The procedure I used was slightly more simple than that ref:
Access - Decompile databases
In ‘Run’ type MSACCESS /decompile.
When Access starts, hold down the shift key (stops auto macros running which would cause normal opening) and select the Db to decompile.
Open Code window and compile Db
Do a compact & repair.
However, it didn't work this time. It did reduce the Db size from 11Mb (after a Compact & Repair) to 6.6 Mb though.

Here is the code that calls that form (frmFunds)

Code: Select all

DoCmd.OpenForm "frmFunds", acNormal
With Forms![frmFunds]
    .Filter = "[PolSalesNo]=" & SNo
    .FilterOn = True
    ![txtWhoFor].Value = "Funds for: " & Nz(Me.Parent.Parent![txtFirstName]) & " " & Nz(Me.Parent.Parent![LastName]) & Chr(34)
    ![txtWhoFor].ControlSource = "=" & Chr(34) & "Funds for: " & Nz(Me.Parent.Parent![txtFirstName]) & " " & Nz(Me.Parent.Parent![LastName]) & Chr(34)
    ![txtSalesNoChecker] = SNo
    If Nz(![txtProduct]) = vbNullString Then
        ![txtProduct] = Nz(Me![Product])
    End If
    ![txtPolNo] = Nz(Me![Pol no])
'    ![txtSalesNo] = SNo 'This creates a record. We want to wait until a FundName is entered
'     before a record is created. (txtFundName_AfterUpdate() enters the txtSalesNo.
End With   'Forms![frmFunds]
SetSize    'sets the size of frmFunds to suit the number of records
If blnNoFunds = True Then
 'Trigger default val for [Sort]
 Forms![frmFunds]![txtSorter].SetFocus
 Forms![frmFunds]![txtFocusRecv].SetFocus
End If

The Funtion SetSize:

Code: Select all

Function SetSize()
'Sets size of frmFunds
Dim rsCopyFunds As Recordset, rsFunds As Recordset
Dim SalesNo As Long, NumRecs As Long
Dim MaxDateIn As Date
Dim Ht, Wdth
On Error GoTo Err_SetSize
Forms![frmFunds].TimerInterval = 1000000
SalesNo = Forms![frmFunds]![txtSalesNo]
'There are various 'DateIn's, we want the records that have the latest one
MaxDateIn = DMax("[DateIn]", "tblFunds", "[PolSalesNo] = " & SalesNo)
Set rsFunds = CurrentDb.OpenRecordset("tblFunds", dbOpenDynaset)
rsFunds.Filter = "[PolSalesNo] = " & SalesNo & " AND [DateIn] = #" & Format(MaxDateIn, "mm/dd/yyyy") & "#"
Set rsCopyFunds = rsFunds.OpenRecordset
rsCopyFunds.MoveLast
NumRecs = rsCopyFunds.RecordCount
'DoCmd.MoveSize(Right, Down, Width, Height)
'567 TWIPS per cm
'Header=2.034cm; Detail=0.462cm; Footer=0.423cm; Width=10.307cm
'Header=1153Twips; Footer=240Twips; - Hdr + Ftr = 1393 (change to 2300)
'Detail=262Twips (change to 233); Width=5844Twips (change to 6660)
Ht = 2350 + NumRecs * 233  'Pretty darned close on Office1
If Ht > 5600 Then Ht = 5600
Wdth = 9250
DoCmd.MoveSize , , Wdth, Ht

Exit_SetSize:
Set rsCopyFunds = Nothing: Set rsFunds = Nothing
Exit Function

Err_SetSize:
    MsgBox Err.Description
    Resume Exit_SetSize
    Resume
End Function

The OnOpen code for frmFunds:

Code: Select all

Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 500  '1/2 sec
Me.OnTimer = "=CallGetTot()"
Debug.Print Me.TimerInterval; Me.OnTimer
End Sub

The Debug.Print successfully prints the correct values of: 500 & =CallGetTot()
If I set the Me.TimerInterval to 50000, giving me time to open the already opened frmFunds in design mode, those values are 0 and blank. This is so even if I save frmFunds before opening in Design mode.
Maybe someone can see what is causing the problem.

[HansV]

The next thing to try is to create a new database, and to import all database objects from the problem database into the new one.
If you set any non-standard references in the Visual Basic Editor in the problem database, you'll have to set them in the new database too.
See if the timer works in the new database.

[Peter Kinross]

Thanks Hans. Used to frequently doing that with my old replicated Db back end. But this will be MUCH easier as I don't have to recreate each table (78 of them in this case) from scratch omitting the replication fields, being careful to exactly reproduce each field's properties. Even easier as most of the tables are linked. Still, it will take some time. Wish me luck :)

[Peter Kinross]

Bummer! Didn't work.
Hans, doing this did remind me of one reason why I am so frustrated by MS. Doing the References for the new Db, a few had refs that weren't in the list, entailing looking them up. Fair enough, but dear old MS have the dialog too narrow to see the full reference. Can't even see the name of the file and no way to get to see it. MS have only had this prob since the year dot, yet haven't been bothered to fix it - Grrrr. Did see a Hans reply on Google to someone who had the same complaint - you supplied the file name and location. That got me out of one unfindable ref, had to Google further for others.

[HansV]

The code that opens frmFunds calls SetSize.
SetSize resets the TimerInterval of frmFunds to 100000, overruling the value of 500 set in the On Open event procedure. Could that be the cause of the problem?

[Peter Kinross]

Thanks Hans. I put that there to aid in finding another fault - what a dill!

ThanksHansStamp.gif