Error 3075, Please Help!

[Adam McReynolds]

I keep getting an error '3075' syntax error(missing operator) in query expression 'prikey =' . My code runs fine when I first enter in the number but when I take the number out the After Update code runs again and produces this error.

I have 3 unbound textboxes: txt_rid1, txt_sn1, and txt_incoming1

My prikey field is a auto number

The debugger keeps focusing on this: "prikey = " & Me.txt_rid1 & "" , but what I don't get is how does it run fine on entering data but not removing it?

Private Sub txt_rid1_AfterUpdate()
'Field Line 1
If IsNull(Me.txt_rid1) Or Me.txt_rid1 = "" Then
Me.txt_sn1 = ""
Me.txt_incoming1 = ""
End If

If DCount("prikey", "tbl_module_repairs", "prikey = " & Me.txt_rid1 & "") = 0 Then
Me.txt_rid1 = ""
Me.txt_sn1 = ""
Me.txt_incoming1 = ""
Me.txt_rid1.SetFocus
MsgBox "Please Enter a Valid Report ID for Field 1 "
Cancel = True
Exit Sub
End If

Me.txt_sn1 = DLookup("incoming_module_sn", "tbl_module_repairs", "prikey = " & Me.txt_rid1 & "")
Me.txt_incoming1 = DLookup("incoming_disposition", "tbl_module_repairs", "prikey = " & Me.txt_rid1 & "")
Me.Refresh

End Sub

Any help would be much appreciated.

 

[CJ_London]

The debugger keeps focusing on this: "prikey = " & Me.txt_rid1 & ""

What is happening is that you are supplying a null value to "prikey = " so the query fails

I would modify your afterupdate event to not run if there is not a value i.e.

Private sub txt_rid1_afterupdate()
...your code...
 
if not isnull(me.txt_rid1) then
If DCount("prikey", "tbl_module...execute your code
end if
end sub

also you don't need the final double quote

"prikey = " & Me.txt_rid1

is sufficient

 

[Adam McReynolds]

What is happening is that you are supplying a null value to "prikey = " so the query fails

I would modify your afterupdate event to not run if there is not a value i.e.

Private sub txt_rid1_afterupdate()
...your code...
 
if not isnull(me.txt_rid1) then
If DCount("prikey", "tbl_module...execute your code
end if
end sub

also you don't need the final double quote

is sufficient

Thank you. Works great now!

My Final Code:

Private Sub txt_rid1_AfterUpdate()
'Field Line 1
If IsNull(Me.txt_rid1) Or Me.txt_rid1 = "" Then
Me.txt_sn1 = ""
Me.txt_incoming1 = ""
Else
If DCount("prikey", "tbl_module_repairs", "[prikey] = " & Me![txt_rid1]) = 0 Then
Me.txt_rid1 = ""
Me.txt_sn1 = ""
Me.txt_incoming1 = ""
Me.txt_rid1.SetFocus
MsgBox "Please Enter a Valid Report ID for Field 1 "
Cancel = True
Exit Sub
End If
End If

If Not IsNull(Me.txt_rid1) Then
If DCount("prikey", "tbl_module_repairs", "prikey = " & Me.txt_rid1 & "") = 1 Then
Me.txt_sn1 = DLookup("incoming_module_sn", "tbl_module_repairs", "prikey = " & Me.txt_rid1 & "")
Me.txt_incoming1 = DLookup("incoming_disposition", "tbl_module_repairs", "prikey = " & Me.txt_rid1 & "")
Me.Refresh
End If
End If

End Sub