Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Subform = duplicate last subform record without button control

    Hi!
    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

    strSql = "INSERT INTO [REGSTAT32511] (STATLU, PTLU) " & "SELECT" & MaxID + 1 & ", STATLU, PTLU" & "FROM REGSTAT32511" & "WHERE ID = & MaxID"

    Set dbs = CurrentDb
    dbs.Execute strSql



    End Sub
    ** this doesn't trigger at all which I assume is because it's in From Open

    Any and all suggestions are greatly appreciated!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Not sure I understand the setup, but a couple of issues with the code. The DMax() would need a criteria to get the max for the appropriate account:

    General: DLookup Usage Samples

    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:

    Debugging
    Paul

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Foskbou View Post
    ... would like to duplicate my last Registration that relates to this Account
    How do you determine which is the "last Registration"?

    You wrote:
    Quote Originally Posted by Foskbou View Post
    **this is a problem because the MaxID is not necassarily the max for this Account[
    So is there a date/time field that contains the date of this "last Registration", an autonumber field?
    Have a nice day!

  4. #4
    Join Date
    Jun 2010
    Posts
    186
    Quote Originally Posted by Sinndho View Post
    How do you determine which is the "last Registration"?

    You wrote:

    So is there a date/time field that contains the date of this "last Registration", an autonumber field?

    There is both autonumber and date/time

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •