How to Avoid or Trap a Syntax Query Error

[Dalan]

I can't seem to find a workaround of Query Syntax Error. Actually, the
query performs just fine, except when the last record on a related
subform is deleted, then it generates a Runtime Error 3075 - Syntax
error (missing operator) in query expression 'IDRef = '-- of course,
because there are no more records left with the same IDRef number. And
Set rst = dbs.OpenRecordset(strSQL) is highlighted in the Function
module.

The Function module below pulls the IDRef numbers and amounts from a
Totals Query. There is sfrmAddCost subform on a frmMain and when
adding or editing the subform records all is fine. It is when the only
or last record is deleted that the above error appears.

I have tried several approaches in resolving this issue, but to no
avail. Would appreciate anyones' assistance and advice. Thanks, Dalan
Function GetTotalAdditional(IDRef) As Currency

Dim rst As Recordset, strSQL As String
Dim dbs As Database
Set dbs = CurrentDb
If Not IsNull(IDRef) Then

strSQL = "Select TotalAdditional FROM qryAddCost WHERE IDRef = " &
IDRef

Set rst = dbs.OpenRecordset(strSQL)

If rst.RecordCount = 0 Then
GetTotalAdditional = 0
Else
GetTotalAdditional = rst![TotalAdditional]

End If
End If

Set rst = Nothing
Set dbs = Nothing

Exit Function

End Function

[Steve Jorgensen]

First, in Access 2000 and above, it's best to bind the field to an invisbile
control, and reference that - more reliable. Second, sometimes, on a new
record, values will be Empty rather than Null, so check for IsNull(...) or
IsEmpty(...). I think that will solve your problem.

On 22 Feb 2004 22:00:29 -0800, ot***@safe-mail.net (Dalan) wrote:

I can't seem to find a workaround of Query Syntax Error. Actually, the
query performs just fine, except when the last record on a related
subform is deleted, then it generates a Runtime Error 3075 - Syntax
error (missing operator) in query expression 'IDRef = '-- of course,
because there are no more records left with the same IDRef number. And
Set rst = dbs.OpenRecordset(strSQL) is highlighted in the Function
module.

The Function module below pulls the IDRef numbers and amounts from a
Totals Query. There is sfrmAddCost subform on a frmMain and when
adding or editing the subform records all is fine. It is when the only
or last record is deleted that the above error appears.

I have tried several approaches in resolving this issue, but to no
avail. Would appreciate anyones' assistance and advice. Thanks, Dalan
Function GetTotalAdditional(IDRef) As Currency

Dim rst As Recordset, strSQL As String
Dim dbs As Database
Set dbs = CurrentDb
If Not IsNull(IDRef) Then

strSQL = "Select TotalAdditional FROM qryAddCost WHERE IDRef = " &
IDRef

Set rst = dbs.OpenRecordset(strSQL)

If rst.RecordCount = 0 Then
GetTotalAdditional = 0
Else
GetTotalAdditional = rst![TotalAdditional]

End If
End If

Set rst = Nothing
Set dbs = Nothing

Exit Function

End Function