ByRef argument type mismatch 2

[Duane8889]

Hello
I'm trying to call this function but am getting the error "ByRef argument type mismatch"
I'm using Access 2k and calling this from form code.

Private Sub cmdDeleteRecord_Click()
    Call DelRecord(strName) ' error here
End Sub

'   delete routine
Private Sub DelRecord(strName As String)
    On Error GoTo DelRecordErr:
    Dim strSQL As String
    strSQL = "Delete * FROM tblContacts WHERE " & _
        "Lastname = '" & strName & "'"
        DoCmd.RunSQL strSQL
        
Exit_DelRecord:
        Exit Sub
DelRecordErr:
        If Err.Number = 0 Then
            Exit Sub
        ElseIf Err.Number = 2501 Then
        MsgBox "Action canceled by the user.", vbInformation, "Canceled"
            Else
        MsgBox Err.Number & ": " & Err.Description, vbCritical, "Canceled"
            End If
            Resume Exit_DelRecord
End Sub

thanQ for any ideas!

 

[jsteph]

Unless you've declared strName elsewhere as a string, it's undeclared, therefore a variant, and NULL to boot.

Since the called function's strName argument is ByRef and not ByVal, Access will not automatically cast the variant to a string as it passes from cmdDeleteRecord to DelRecord.

So...declare strName as a string, assign it a value before you pass it to the function.
--Jim

 

[Duane8889]

thanks Jim
I went ahead and added this bit...

strName = InputBox("Delete who?")

I changed the procedure to ...

Private Sub cmdDeleteRecord_Click()
    On Error GoTo DelRecordErr:
    Dim strSQL As String
    strName = InputBox("Delete who?")
    strSQL = "Delete * FROM tblContacts WHERE " & _
        "FirstName = '" & strName & "'"
        DoCmd.RunSQL strSQL
        
Exit_DelRecord:
        Exit Sub
DelRecordErr:
        If Err.Number = 0 Then
            Exit Sub
        ElseIf Err.Number = 2501 Then
        MsgBox "Action canceled by the user.", vbInformation, "Canceled"
            Else
        MsgBox Err.Number & ": " & Err.Description, vbCritical, "Canceled"
            End If
            Resume Exit_DelRecord
End Sub

and it works but now I get #Deleted in every textbox of the deleted record. But it doesn't show in the table, strange. If I get out of the form and come back it goes away.

 

[Remou]

Hi
That's the way it works. You need Me.Requery after DoCmd.RunSQL strSQL.

 

[Duane8889]

thanks Remou that worked perfectly!

 

[Duane8889]

Hello
Would it be possible to modify this code to delete a name from a list box? For example choosing a name in a list box then clicking a delete button.

 

[Remou]

Hi
This should work:

Dim strSQL As String
MsgBox "Delete " & Me!List0
strSQL = "Delete * FROM tblContacts WHERE " & _
         "FirstName = '" & Me!List0 & "'"
DoCmd.RunSQL strSQL
Me.List0.Requery

Always with the warning that all records with such a first name will be deleted. If the name is to be removed from the listbox, but not the table, the code needs to be changed.