Unanswered: Subform = duplicate last subform record without button control
I have a mainform - Accounts - with a subform - Registration - and the Record Source of the subform is a table.
The link is AccountName and the relationship is one(Account)-to-many(Registrations)
Accounts are unique records - there are no duplicate AccountNames allowed
The main(single) form opens from a previous dialog box with a combobox and button. The Account was chosen to make a change to the Registration(subform), and the focus is set to the subform. We do not modify the mainform here at all.
Since there is only 1 field (total of 9 fields on the table) in Registration that needs to be changed, I need to minimize data entry to avoid any errors and would like to duplicate my last Registration that relates to this Account.
I have seen versions of code using Click, RecordsetClone, and append query to do this but I use click to get into my mainform and I can't have the click duplicate the mainform. I don't want to put another button since I'm already focused in the subform.
Does anyone have suggestions to write this duplicated subform record using another event like Form Open?
Here's what I've tried:
Private Sub Form_Open(Cancel As Integer)
Dim strSql As String
Dim dbs As Database
Dim MaxID As Long
MaxID = DMax("ID", "REGSTAT32511") **this is a problem because the MaxID is not necassarily the max for this Account
You should probably wrap that in the Nz() function for those times when it's a new account and doesn't have an existing record. You're going to have some space issues with your SQL (like "...PTLUFROM..."). This may help you debug it: