Error - The expression After Update you entered...

[Rob C]

An Access xp program recently began to report the following error...

The expression After Update you entered as the event property setting
produced the following error:.
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure],
* There may have been an error evaluating the function, event, or macro.

I have a table on a form where the names of the fields are 1,2,3,4...65.
(i.e., The names of the columns are numbers ranging from 1 to 65.
Basically relates to 5 sets of 13 weeks in which week forecasts are placed.)

The after update event for each of these fields is
Call ModifyForecastUp

Which is listed below...

Do you see any issues with regard to the code below which should cause such
an error ? There are several people using it, and only one person is
reporting this error... they emailed me the database and I am able to
replicate the error.


Private Sub ModifyForecastUp()
' Stores value entered in a vertical table
Dim db As Database
Dim rs As Recordset
Dim rsa As Recordset
Dim qd As QueryDef
Dim lngAccCol As Long
Dim lngControlNumber As Long
Dim lngForeYear As Long
Dim lngForeWeek As Long
Dim strSQL As String
Dim strCustomer As String
Dim strStockCode As String
Dim strSalesperson As String
Dim lngForecastQty As Long
Dim cntRow As Integer

If IsNull([Forms]![frmF].Form.ActiveControl) Then
lngForecastQty = 0
[Forms]![frmF].Form.ActiveControl = 0
Else
lngForecastQty = [Forms]![frmF].Form.ActiveControl
End If


strSalesperson = [Forms]![frmF]![txtSalesperson]
strCustomer = [Forms]![frmF]![Customer]
strStockCode = [Forms]![frmF]![StockCode]
lngAccCol = [Forms]![frmF].ActiveControl.Name
lngControlNumber = [Forms]![frmF]![lngControlNum]
strSQL = "SELECT tblForecastDateControl.RunNumberREP,
tblForecastDateControl.YearNumberREP, " & _
" tblForecastDateControl.WeekNumberREP FROM tblForecastHeaderControl INNER
JOIN " & _
" tblForecastDateControl ON tblForecastHeaderControl.RunNumberREP = " & _
" tblForecastDateControl.RunNumberREP " & _
" WHERE tblForecastDateControl.RunNumberREP = " & lngControlNumber & " And "
& _
" tblForecastDateControl.AccessBucket = " & lngAccCol & ""

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
lngForeYear = rs!YearNumberREP
lngForeWeek = rs!WeekNumberREP

strSQL = "SELECT count(*) as Rcnt from tblForecastDataUp WHERE
ForecastYearREP = " & lngForeYear & " " & _
" and ForecastWeekREP = " & lngForeWeek & " " & _
" and FStockCodeREP = " & Chr(34) & strStockCode & Chr(34) & " " & _
" and FCustomerREP = " & Chr(34) & strCustomer & Chr(34) & ""
Set rsa = db.OpenRecordset(strSQL)
rsa.MoveFirst
cntRow = rsa!RCnt

' If row already exist then modify the quantity
If cntRow = 1 Then
strSQL = "Update tblForecastDataUp set ForecastQtyREP = " & lngForecastQty
& " " & _
" where ForecastYearREP = " & lngForeYear & " " & _
" and ForecastWeekREP = " & lngForeWeek & " " & _
" and FStockCodeREP = " & Chr(34) & strStockCode & Chr(34) & " " & _
" and FCustomerREP = " & Chr(34) & strCustomer & Chr(34) & ""
Set qd = db.CreateQueryDef("", strSQL)
qd.Execute
End If

' Else Add the row
If cntRow = 0 Then
strSQL = "INSERT INTO tblForecastDataUp (ForecastYearREP, ForecastWeekREP,
" & _
" FStockCodeREP, FCustomerREP, ForecastTypeREP, FSalespersonREP,
ForecastQtyREP, " & _
" ForecastNoteREP, DateLastUpdatedREP, SynchNumberREP) " & _
" VALUES( " & lngForeYear & ", " & lngForeWeek & ", " & _
" " & Chr(34) & strStockCode & Chr(34) & ", " & _
" " & Chr(34) & strCustomer & Chr(34) & " ,1," & _
" " & Chr(34) & strSalesperson & Chr(34) & ", " & _
" " & lngForecastQty & " , Null, Null, Null)"
Set qd = db.CreateQueryDef("", strSQL)
qd.Execute
End If

rsa.Close
rs.Close
qd.Close
db.Close

Set rs = Nothing
Set rsa = Nothing
Set qd = Nothing
Set db = Nothing
[Forms]![frmF].Refresh
End Sub


Another odd thing when you click on a command box... the following error
also occurs...

The expression On Click you entered as the event property setting produced
the following error: Invalid use of Null.
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure],
* There may have been an error evaluating the function, event, or macro.

No-one else is getting this error either.

Thanks !