Msgbox error: Object Required

[Al]

I have a form that I want to replace the contents of one
field with the contents of another field if the user
clicks a command button. It shifts the fields up one...
However, when I get past the msgbox to the first If
statement, I get an error, "Object Required." Also, I'm
getting a response value for 'vbYes' of 6... is this right?
What am I doing wrong?

Private Sub DOff1_Click()
Dim Lid As Integer
Dim Off1 As Variant
Dim Off2 As Variant
Dim Response As String

On Error GoTo Err_DOff1_Click
Lid = Me.LOGID
Off1 = Me.TOff1
Off2 = Me.TOff2

Response = MsgBox("Are you sure you want to remove " &
Off1 & "?", vbYesNo)
If Response = vbYes And Off2 Is Null Then
DoCmd.RunSQL "UPDATE Log SET OFFNAME1 = " & Off2
& "WHERE LOGID = " & Lid
Else

 

[Al]

I figured out the "Object Required" problem by replacing
the "is null" with "= null" but I can't get the code to
run the first if statement when both conditions are true.
I hold my cursor over the variables in debug and see the
values are exactly what they should be, but the code just
steps right over it without ever executing the line.
TIA,
Al

 

[Dan Artuso]

Hi,
Is Null is for SQL, in VBA use IsNull(Off2)

 

[Emilia Maxim]

I figured out the "Object Required" problem by replacing
the "is null" with "= null" but I can't get the code to
run the first if statement when both conditions are true.
I hold my cursor over the variables in debug and see the
values are exactly what they should be, but the code just
steps right over it without ever executing the line.

Al,

try this:

Dim Lid As Integer
Dim Off1 As Variant
Dim Off2 As Variant
Dim Response As String

On Error GoTo Err_DOff1_Click
Lid = Me.LOGID
Off1 = Me.TOff1

Response = MsgBox("Are you sure you want to remove " &
Off1 & "?", vbYesNo)

'See Help item IsNull function for details
If Response = vbYes And IsNull(Me!TOff2) Then

'Another change: if TOff2 is empty, you can use a
'simpler syntax in the UPDATE statement
'(If it were not empty, you would need to enclose its
'value in quotes when used in SQL because it seems
'to be a text)
DoCmd.RunSQL "UPDATE Log SET OFFNAME1 = Null"
& " WHERE LOGID = " & Me!LOGID
Else

HTH

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de