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.
Code for end of month activity submission in an activity system
Papadog
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.
BlackCatBone
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
J Ma
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.
Aistina
ADG, thanks for that. It works perfectly.
Cheers buddy,
Rhys.
Guy Burstein
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
Juan Ignacio Gelos
got it to wokr now - mistake on my part.
cheers!