Enter parameter value error in vba access

Question

I have a simple VBA code that makes me update a database table with update statement . the field I want to update is date/time so the code I use is :

Public myDate, renewalDate As Date
Public Sub Renewal()
renewalDate = DLookup("renewalDate", "tblDate", "[id]=1")
Dim newDate As Date
Serial = "123456789"
If Forms![frmRenewal]![txtSerial] = Serial Then
    newDate = DateAdd("m", 1, renewalDate)
    MsgBox newDate
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL "Update tblDate SET tblDate.renewalDate = newDate WHERE (((tblDate.id)=1))"
    MsgBox "You successfully renewed your subscription and renewal date is now " & renewalDate
Else
    MsgBox "Wrong Serial"
End If
End sub

and here is the database table:

and I get this error when I run the code

Lee Mac · Accepted Answer · 2020-11-08 21:03:17Z

1

The issue is that your variable newDate is defined within the scope of the VBA subroutine Renewal and has no meaning within the SQL statement executed by the RunSQL method, therefore causing the SQL engine to interpret it as a parameter requiring a value.

Whilst you could concatenate the value of this variable as part of the SQL statement supplied to the RunSQL method, a cleaner approach might be to perform the calculation of the new date directly within the SQL statement, e.g.:

DoCmd.RunSQL "update tblDate set tblDate.renewalDate = DateAdd(""m"", 1, tblDate.renewalDate) where tblDate.id = 1"

answered Nov 8, 2020 at 21:03

Lee Mac

16.1k6 gold badges38 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ali El-Boghdady Over a year ago

thank you for your explanation it works now after i used your solution .... many thanks

Gustav · Accepted Answer · 2020-11-08 21:06:44Z

1

You can reduce it to:

Public myDate As Date, renewalDate As Date

Public Sub Renewal()

    Serial = "123456789"
    If Forms![frmRenewal]![txtSerial] = Serial Then
        renewalDate = DateAdd("m", 1, renewalDate)
        CurrentDb.Execute "Update tblDate SET renewalDate = DateAdd('m', 1, [renewalDate]) WHERE id = 1"
        MsgBox "You successfully renewed your subscription, and renewal date is now " & renewalDate
    Else
        MsgBox "Wrong Serial"
    End If

End sub

answered Nov 8, 2020 at 21:06

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

Enter parameter value error in vba access

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related