Error in Code, but I Can't Find It!

[croy]

When my general-purpose form opens to a new record, the
following dialog arises:

Syntax error (missing operator) in query expression
'[IvSurvId] ='.

But I don't see the problem in the code.

I think the offending code is:

*****
Private Sub Form_Current()

Dim strWhere As String
Dim strWhere2 As String

strWhere = "[IvSurvId] = " & Me.Parent![IvSurvId]
strWhere2 = "[IvSurvId] = " & Me.Parent![IvSurvId] _
& " And [IvPage] = " & Me![txtIvPage].DefaultValue
Me![txtIvPage].DefaultValue = _
Nz(DMax("IvPage", "qryIvDetails", strWhere), 1)
Me![txtIvLine].DefaultValue = Nz(DMax("IvLine", _
"qryIvDetails", strWhere2), 0) + 1

If Me.Parent![cboIvType] = 1 Then
Me![cboDone].DefaultValue = 0
ElseIf Me.Parent![cboIvType] = 2 Then
Me![cboDone].DefaultValue = -1
End If

End Sub
*****

Does anyone see the error in the code?

Access 2002.

 

[Douglas J. Steele]

Are you sure that Me.Parent![IvSurvId] has a value? If it's Null, your Where
clause will be invalid.

If it does have a value, what's the datatype of IvSurvId? If it's text, you
need quotes around the value:

strWhere = "[IvSurvId] = '" & Me.Parent![IvSurvId] & "'"

(unless Me.Parent![IvSurvId] happens to contain apostrophes)

 

[Marshall Barton]

When my general-purpose form opens to a new record, the
following dialog arises:

Syntax error (missing operator) in query expression
'[IvSurvId] ='.

But I don't see the problem in the code.

I think the offending code is:

*****
Private Sub Form_Current()

Dim strWhere As String
Dim strWhere2 As String

strWhere = "[IvSurvId] = " & Me.Parent![IvSurvId]
strWhere2 = "[IvSurvId] = " & Me.Parent![IvSurvId] _
& " And [IvPage] = " & Me![txtIvPage].DefaultValue
Me![txtIvPage].DefaultValue = _
Nz(DMax("IvPage", "qryIvDetails", strWhere), 1)
Me![txtIvLine].DefaultValue = Nz(DMax("IvLine", _
"qryIvDetails", strWhere2), 0) + 1

If Me.Parent![cboIvType] = 1 Then
Me![cboDone].DefaultValue = 0
ElseIf Me.Parent![cboIvType] = 2 Then
Me![cboDone].DefaultValue = -1
End If

End Sub
*****

Since the only query expression in your code is in the DMax
strWhere2, I think the first place to look is there.

strWhere2 = "[IvSurvId] = " & Me.Parent![IvSurvId] _

when the IvSurvId field is Null. If the parent form is on a
new record, the field is guatanteed to be Null in the
current event.

Either check for that situation explicitly or use the Nz
function:

strWhere2 = "IvSurvId = " & Nz(Me.Parent!IvSurvId, 0) _

 

[croy]

Are you sure that Me.Parent![IvSurvId] has a value? If it's Null, your Where
clause will be invalid.

Bingo! As soon as I read that, I realized the problem.

Thank you!

 

[croy]

When my general-purpose form opens to a new record, the
following dialog arises:

Syntax error (missing operator) in query expression
'[IvSurvId] ='.

But I don't see the problem in the code.

I think the offending code is:

*****
Private Sub Form_Current()

Dim strWhere As String
Dim strWhere2 As String

strWhere = "[IvSurvId] = " & Me.Parent![IvSurvId]
strWhere2 = "[IvSurvId] = " & Me.Parent![IvSurvId] _
& " And [IvPage] = " & Me![txtIvPage].DefaultValue
Me![txtIvPage].DefaultValue = _
Nz(DMax("IvPage", "qryIvDetails", strWhere), 1)
Me![txtIvLine].DefaultValue = Nz(DMax("IvLine", _
"qryIvDetails", strWhere2), 0) + 1

If Me.Parent![cboIvType] = 1 Then
Me![cboDone].DefaultValue = 0
ElseIf Me.Parent![cboIvType] = 2 Then
Me![cboDone].DefaultValue = -1
End If

End Sub
*****

Since the only query expression in your code is in the DMax
strWhere2, I think the first place to look is there.

strWhere2 = "[IvSurvId] = " & Me.Parent![IvSurvId] _

when the IvSurvId field is Null. If the parent form is on a
new record, the field is guatanteed to be Null in the
current event.

You (and Douglas) nailed it. I wasn't excluding the code
from situations where the parent id is null (new record).

Thank you!