Can't update table automatically.Function problem

[niclive]

Helo.I'm having trouble with this function.I created a table with four
fields dtinitial, dtexpire, regkey and ID as primary key.I want to
update the table if the user enters a correct regkey.However I don't
want to use sql update.I want it to be done automatically without user
interaction.When I try the function I get an error saying Object
Required.This comes after the welcome msgbox displays.
What could be the problem?Please help.

Public Function expire()
On Error GoTo Err_trap


Dim dtexpire As Date
Dim dtInitial As Date
Dim regkey1 As String

dtexpire = DLookup("[dtexpire]", "[Source]", "[dtexpire]")
dtInitial = DLookup("[dtInitial]", "[Source]", "[dtInitial]")
regkey = DLookup("[regkey]", "[Source]", "[regkey]")
regkey1 = InputBox("Please enter your Section Code:","Section Code
Required")

If dtexpire < dtInitial Then
If regkey1 = regkey Then
MsgBox " WELCOME !", vbInformation, "Access Granted"

Source.dtexpire = Source.dtexpire + 30
Source.regkey = Source.regkey + 1001

Else
MsgBox "You provided wrong information!", vbInformation, "Access
Denied"
DoCmd.Quit
End If
End If

Exit_function:
Exit Function

Err_trap:
MsgBox Err.Description
Resume Exit_function
End Function

 

[Andy Hull]

Hi

You can't update data in a table like that.
You need to open a recordset or issue an SQL statement from within your code.
In this instance it looks most suitable to issue an SQL statement.

I am assuming you have a table called Source and that it has 1 row of data.
If not, this code will need tweaking.
If so, use...

Dim SQLString as String 'Place this with other Dim statements

and reaplce

Source.dtexpire = Source.dtexpire + 30
Source.regkey = Source.regkey + 1001

with...

SQLString = "update source set dtexpire = dateadd("d", 30, dtexpire), regkey
= regkey + 1001"

Also, change the following...

dtexpire = DLookup("[dtexpire]", "[Source]", "[dtexpire]")
dtInitial = DLookup("[dtInitial]", "[Source]", "[dtInitial]")
regkey = DLookup("[regkey]", "[Source]", "[regkey]")

to...
dtexpire = DLookup("[dtexpire]", "[Source]")
dtInitial = DLookup("[dtInitial]", "[Source]")
regkey = DLookup("[regkey]", "[Source]")

The 3rd parameter is the equivalent of a WHERE clause in an SQL statement so
should be used to pick a particular row in the table. If you only have 1 row
then the 3rd parameter isn't needed at all.

Regards

Andy Hull

Helo.I'm having trouble with this function.I created a table with four
fields dtinitial, dtexpire, regkey and ID as primary key.I want to
update the table if the user enters a correct regkey.However I don't
want to use sql update.I want it to be done automatically without user
interaction.When I try the function I get an error saying Object
Required.This comes after the welcome msgbox displays.
What could be the problem?Please help.

Public Function expire()
On Error GoTo Err_trap


Dim dtexpire As Date
Dim dtInitial As Date
Dim regkey1 As String

dtexpire = DLookup("[dtexpire]", "[Source]", "[dtexpire]")
dtInitial = DLookup("[dtInitial]", "[Source]", "[dtInitial]")
regkey = DLookup("[regkey]", "[Source]", "[regkey]")
regkey1 = InputBox("Please enter your Section Code:","Section Code
Required")

If dtexpire < dtInitial Then
If regkey1 = regkey Then
MsgBox " WELCOME !", vbInformation, "Access Granted"

Source.dtexpire = Source.dtexpire + 30
Source.regkey = Source.regkey + 1001

Else
MsgBox "You provided wrong information!", vbInformation, "Access
Denied"
DoCmd.Quit
End If
End If

Exit_function:
Exit Function

Err_trap:
MsgBox Err.Description
Resume Exit_function
End Function