What is wrong with the VBA expression?

[raghuprabhu]

What am I doing wrong with this VBA expression? I have a form in datasheet view. The form is based on a query which show the records when the value is false. When I click to make the value is true, the form requires and does not show the record. That is working fine but it is not is not updating the fields DebBy and DelDate. It is coming up with the following error.

Case -2147217904 no value given for one or more required parameters.

Thanks in Advance

Raghu Prabhu

 

[John Big Booty]

The code in your After Update event, should look like;

Me.Dirty = False
sSql = "UPDATE tblDeleteRecord SET tblDeleteRecord.DelBy = Environ(" & Chr(39) & "UserName" & Chr(39) & "), tblDeleteRecord.DelDate = Now()" & _
        "WHERE (((tblDeleteRecord.DelBy) Is Null) AND ((tblDeleteRecord.DeleteThis)=-1));"

The UserName argument needs to be enclosed in single quotes, hence the Chr(39).

You will also need to add the following to a public [not a form] module;

Public Function Environ(Expression)
On Error GoTo Err_Environ
    
    Environ = VBA.Environ(Expression)

Exit_Environ:
    Exit Function

Err_Environ:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Environ

End Function

Without this you will get an error.

 

[raghuprabhu]

Thanks a lot Big John Booty


Cheers
Raghu

 

[bob fitz]

Hi

You may like to try this:

Private Sub DeleteThis_AfterUpdate()
On Error GoTo Err_DeleteThis_AfterUpdate_Error
    Dim sSql As String
 
    sSql = "UPDATE tblDeleteRecord SET DeleteThis = True, DelBy = '" & Environ("UserName") & "', DelDate = #" & Now() & "# WHERE " _
        & "(((tblDeleteRecordID)=" & Me.tblDeleteRecordID & "));"
    If Me.Dirty Then
        Me.Dirty = False
    End If
 
    CurrentDb.Execute sSql, dbFailOnError
    Me.Requery
 
Exit_ErrorHandler:
    Exit Sub
Err_DeleteThis_AfterUpdate_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    Resume Exit_ErrorHandler
End Sub

I have tested it. It seems to work for me.

 

[raghuprabhu]

Both solutions are very good. Each one could be used in different times due to some restriction or other.

Thanks once again.

Raghu