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
JeffBrown
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
soconne
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.
bpsmith
got it to wokr now - mistake on my part.
cheers!
luigidor
ADG, thanks for that. It works perfectly.
Cheers buddy,
Rhys.
Al33327
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
crazyabtdotnet
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.