Hi there - can anyone help with my syntax - when a user presses the submit command button i want the date that they have entered into an unbound drop down date field called 'dls' to be inserted into a field called 'datelastsubmitted' into 'tblusers' and it should enter it against the appropriate user who is logged in (this logged in users are stored in a global variable called 'loginname').

Her is my syntax:

Private Sub submit_Click()
Dim sql As String
dls = Me!dls
Dim rsdbase As Database
Dim rstemp As Recordset
Set rsdbase = CurrentDb
Set rstemp = rsdbase.OpenRecordset("INSERT INTO tblusers[datelastsubmitted] VALUES dls WHERE tblusers[loginname] = '" & loginname & "'")
DoCmd.RunSQL sql
End Sub

VBA doesnt like the  line set rstemp to loginname & "'") - can anyone point out where i have gone wrong.

Thanks,

Rhys.

Hi Rhys

I guess that your user table has a primary key on the user ID, if so try the below.

Private Sub submit_Click()
Dim rstemp As Recordset
Dim dls As Date

dls = Me!dls
Set rstemp = CurrentDb.OpenRecordset("tblusers")
rstemp.Index = "PrimaryKey"
rstemp.Seek "=", loginname
If rstemp.NoMatch Then
rstemp.AddNew
rstemp!loginname = loginname
rstemp!datelastsubmitted = dls
rstemp.Update
Else
rstemp.Edit
rstemp!datelastsubmitted = dls
rstemp.Update
End If
rstemp.Close
Set rstemp = Nothing
End Sub

ADG, thanks for that. It works perfectly.

Cheers buddy,

Rhys.

Hi - this code works perfectly but ive realised that i need to include additional code that checks the existing value in 'datelastsubmitted' in 'tblusers' and then does not allow users to enter a date on the form that is prior to the date that they have entered the last time i.e. if they last entered 31/10/2006 then they should not be able to subsequently enter 30/09/2006. I guess its kind of an IF statement that does a dlookup on the value entered in 'tblusers' and then compares it to the value entered into the form and either submits if more recent, or brings up a message box stating that the user must enter a date that is more recent than the last entry.

Thanks,

Rhys.

Hi Rhys

You could edit the code as below:

Private Sub submit_Click()
Dim rstemp As Recordset
Dim dls As Date
Dim responce

dls = Me!dls
Set rstemp = CurrentDb.OpenRecordset("tblusers")
rstemp.Index = "PrimaryKey"
rstemp.Seek "=", loginname
If rstemp.NoMatch Then
rstemp.AddNew
rstemp!loginname = loginname
rstemp!datelastsubmitted = dls
rstemp.Update
Else
If rstemp!datelastsubmitted <= dls Then
rstemp.Edit
rstemp!datelastsubmitted = dls
rstemp.Update
Else
responce = MsgBox("Invalid date", vbOKOnly)
' .. any other you may need goes here

End If
End If
rstemp.Close
Set rstemp = Nothing
End Sub

Hi ADG - i cant get this to work - adding the code in has no effect because regardless of the date entered, it still allows it to be inserted into the database, however changing <= to >= as a matter of interest brings up the message box each time regardless of the date entered, so i must be missing something here

Im guessing the line

'If rstemp!datelastsubmitted <=dls Then' - isnt being evaluated properly even though it brings up no coding error.

What do you think?

Thanks,

Rhys.

got it to wokr now - mistake on my part.

cheers!