This code should work.
Note that I've modified your steps so that you write to the fields directly
and not the controls. This is to avoid the possibility of the subform
"skipping" a record (if you're using an autonumber field) when you start
writing to the controls.I've assumed that the names User and Date also are
the names of the fields in the subform's recordsource query.
I also am assuming that you want to save the subform's record after those
two values are written into it. If not, leave out the .Recordset.Update
step.
With Forms![Invoice Approval Form New]![Reject Reason Subform].Form
.Recordset.AddNew
.Recordset!User.Value = strUsername
.Recordset![Date].Value = Now()
.Recordset.Update
End With
Also, note that you should not name a field "Date". It and many other words
are reserved words in ACCESS and should not be used for control names, field
names, etc. Allen Browne (MVP) has a very comprehensive list of reserved
words at his website:
Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html
See these Knowledge Base articles for more information about reserved words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266
Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763
See this site for code that allows you to validate your names as not being
VBA keywords:
basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18
--
Ken Snell
<MS ACCESS MVP>
Richard said:
I want to add new record in subform, but the code below will just overwrite
the existing data. I need additional coding to move to new record in the
subform.
Can you help, please? Thanks
With Forms![Invoice Approval Form New]![Reject Reason Subform].Form
.User.Value = strUsername
.Date.Value = Now()
End With
