Duplicate record in Access Main Form and Sub Form

**zmbd** · Apr 28 '15, 04:45 PM

please do the debug/compile first as described here:
[*]> Before Posting (VBA or SQL) Code

You will need to fix any errors and then re-run the debug/compile until it returns no errors.

Me.[Recipe_Sub]. is a typical reference to a subform... do you have a subform on the mainform that is named "Recipe_Sub " or is there a typo here? Check the properties of the subform.

**Joe Y** · Apr 28 '15, 05:38 PM

Hi zmbd,

Recipe_Sub is an actual form name. I don't know how to fix the code so I can't figure out how to debug....

Thanks,
Joe

**jforbes** · Apr 28 '15, 06:17 PM

I'm a little concerned with line 45 (and 46) where they have a Percent Sign. SQL isn't going to like that much.

Code:

   strSql = "INSERT INTO [Q_Recipe] (recipeID, RecipeVersion, productID, ProductVersion, [icode]%[/icode], Recipe_UOM) " _

**Joe Y** · Apr 28 '15, 06:58 PM

jforbes,

I know naming a field with % is a bad habit. However, i have this for a while and it seemed not causing issue until now. I am hoping this is not the cause in this case. I will check if changing the field name will help solving the problem.

Thanks,
Joe

**jforbes** · Apr 28 '15, 07:08 PM

Hey Joe,

I think I've got some pointers for you that might make things easier for you in the long run. In the code that you posted, you are mixing and matching two different styles of database edits, Recordset Clone and Execute(). It's typically more efficient to do it one way or the other and depending on your user setup, more reliable.

I'm a big fan of using Execute(). There are times when it's preferable to us Recordsets like when writing a complex routine to compare records or when concatenating data across rows, but for simple inserts, Execute() is quick and easy.

So first thing I would do, with the Execute() in mind is to create a function in a Module to perform the Copying of a Recipe:

Code:

Public Function duplicateRecipe(ByVal lSourceRPID As Long, ByVal lSourceRPVer As Long, ByRef lNewRPVer As Long, ByRef lSubCount As Long) As Boolean
On Error GoTo ErrorOut

    Dim strSql As String     'SQL statement.
     
    duplicateRecipe = False
    lNewRPVer = DMax("[RecipeVersion]", "[TRecipeName]", "[RecipeID] = " & lSourceRPID & "") + 1
     
    If lNewRPVer > 1 Then
        strSql = ""
        strSql = strSql & "SELECT INTO TRecipeName ("
        strSql = strSql & "  RecipeID"
        strSql = strSql & ", RecipeVersion"
        strSql = strSql & ", RName"
        strSql = strSql & ", Procedure"
        strSql = strSql & ", CasePack"
        strSql = strSql & ", Sauce_Unit"
        strSql = strSql & ", UnitPerCase"
        strSql = strSql & ", UnitPkgMtlWt"
        strSql = strSql & ", PalletTie1"
        strSql = strSql & ", PalletTie2"
        strSql = strSql & ", PalletTier"
        strSql = strSql & ", PalletType"
        strSql = strSql & ") SELECT "
        strSql = strSql & "  RecipeID"
        strSql = strSql & ", " & lNewRPVer & " AS RecipeVersion"
        strSql = strSql & ", RName"
        strSql = strSql & ", Procedure"
        strSql = strSql & ", CasePack"
        strSql = strSql & ", Sauce_Unit"
        strSql = strSql & ", UnitPerCase"
        strSql = strSql & ", UnitPkgMtlWt"
        strSql = strSql & ", PalletTie1"
        strSql = strSql & ", PalletTie2"
        strSql = strSql & ", PalletTier"
        strSql = strSql & ", PalletType"
        strSql = strSql & " WHERE RecipeID=" & lSourceRPID & " AND RecipeVersion=" & lSourceRPVer & ""
        strSql = strSql & ";"
        strSql = strSql & "SELECT INTO Q_Recipe ("
        strSql = strSql & "  RecipeID"
        strSql = strSql & ", RecipeVersion"
        strSql = strSql & ", productID"
        strSql = strSql & ", ProductVersion"
        strSql = strSql & ", Recipe_UOM"
        strSql = strSql & ") SELECT "
        strSql = strSql & "  RecipeID"
        strSql = strSql & ", " & lNewRPVer & " AS RecipeVersion"
        strSql = strSql & ", productID"
        strSql = strSql & ", ProductVersion"
        strSql = strSql & ", Recipe_UOM"
        strSql = strSql & " WHERE RecipeID=" & lSourceRPID & " AND RecipeVersion=" & lSourceRPVer & ""
        executeSQL (strSql)
                
        lSubCount = DCount("RecipeID", "Q_Recipe", "RecipeID=" & lSourceRPID & " AND RecipeVersion=" & lSourceRPVer & "")
        duplicateRecipe = True
    End If

ExitOut:
    Exit Function

ErrorOut:
    MsgBox Err.Description
    Resume ExitOut
End Function

Public Sub executeSQL(ByRef sSQL As String)
On Error GoTo ErrorOut
    dbLocal.Execute sSQL, dbFailOnError + dbSeeChanges
ExitOut:
    Exit Sub
ErrorOut:
    Call MsgBox("Could not execute SQL: " & vbCrLf & vbCrLf & Err.Description)
    Resume ExitOut
End Sub

Public Function dbLocal(Optional bolCleanup As Boolean = False) As DAO.Database
  ' This function started life based on a suggestion from
  '   Michael Kaplan in comp.databases.ms-access back in the early 2000s
  ' 2003/02/08 DWF added comments to explain it to myself!
  ' 2005/03/18 DWF changed to use Static variable instead
  ' uses GoTos instead of If/Then because:
  '  error of dbCurrent not being Nothing but dbCurrent being closed (3420)
  '  would then be jumping back into the middle of an If/Then statement
  On Error GoTo ErrHandler
    Static dbCurrent As DAO.Database
    Dim strTest As String

  If bolCleanup Then GoTo closeDB

retryDB:
    If dbCurrent Is Nothing Then
       Set dbCurrent = CurrentDb()
    End If
    ' now that we know the db variable is not Nothing, test if it's Open
    strTest = dbCurrent.Name

exitRoutine:
    Set dbLocal = dbCurrent
    Exit Function

closeDB:
    If Not (dbCurrent Is Nothing) Then
       'dbCurrent.close ' this never has any effect
       Set dbCurrent = Nothing
    End If
    GoTo exitRoutine

ErrHandler:
    Select Case Err.Number
      Case 3420 ' Object invalid or no longer set.
        Set dbCurrent = Nothing
        If Not bolCleanup Then
           Resume retryDB
        Else
           Resume closeDB
        End If
      Case Else
        MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in dbLocal()"
        Resume exitRoutine
    End Select
End Function

This function would take the ID and Version and create a copy of it, then return the new Version and count of Sub Records as well as a Boolean of whether or not it was successful. You don't need to use the dbLocal() and executeSQL() functions. These are functions I use often and seems like they could benefit you.

Once the function is created, you can call it from the Form. I used the code you provided and mocked this up as an example:

Code:

Private Sub Command0_Click()
 
    Dim strSql As String    'SQL statement.
    Dim lRPID As Long       'Primary key, recipe ID, value of the new record.
    Dim lRPVer As Long      'Primary key, Version, value of the new record.
    Dim lNewRPVer As Long
    Dim lSubCount As Long
 
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        lRPID = Nz(Me!recipeID, 0)
        lRPVer = Nz(Me!RecipeVersion, 0)
        
        ' Duplicate
        If duplicateRecipe(lRPID, lRPVer, lNewRPVer, lSubCount) Then
            Me.Refresh
            Me.RecordsetClone.FindFirst "RecipeID=" & lRPID & " AND RecipeVersion=" & lNewRPVer & ""
            If lSubCount = 0 Then MsgBox "Main record duplicated, but there were no related records."
        Else
            MsgBox "Could not duplicate Record with ID of '" & lRPID & "' and Version of '" & lRPVer & "'"
        End If
    End If
End Sub

This might be overkill for your situation and it's just a suggestion, but a structure like this tends to make it easier to troubleshoot problems like what you are experiencing.

Lastly, A couple things to note when I mocked up the code

If you are going to use a % as a Field Name, you will want to enclose it in brackets
You'll want to perform an insert against a Table and not a Query.

Hope it helps.

**zmbd** · Apr 28 '15, 07:45 PM

jforbes: Nice work as usual - and sharp eyes, I missed that % sign.. :)

Joe, you should break the Percent sign habit - eventually it will come back to haunt you :)

Access reserved symbols
The following symbols must not be used as part of a field name or as part of an object name:
. / * ; : ! # & - ? " ' $ %

You'll want to take a look a the full references:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

IMHO: one shouldn't use spaces within the names either; however, they are allowed, just makes coding more difficult.

**Joe Y** · Apr 28 '15, 09:20 PM

Thank you guys. It will take me a while to digest and test the advised code.

Also many thanks to the advises. I totally forgot the insert against table not query concept.

Will report back the results.

Joe.

**Joe Y** · May 25 '15, 12:28 AM

Hi,

I tried jforbes method, but was not successful. Since I don’t quite understand jforbes’ method, I went back to Allen Browne’s method with a couple changes. I feel I am close, but still with following problems.

Using following code, I can get all fields in main form duplicated in to the new record. Subform is populated with a test data as described in the Insert Into statement. However, this is not what I wanted as my goal is to also duplicate the data in the subform into the new record.

Code:

Main form record source: table TRecipeName
Subform record source: table TRecipeID]

Code:

Private Sub CmdCopy_Click()
'Purpose:   Duplicate the main form record and related records in the subform.
 Dim dbs As Database
' Dim rstRecipe As DAO.Recordset
 Set dbs = CurrentDb
    Dim strSql As String     'SQL statement.
    Dim RPID As String       'Primary key value of the new record.
    Dim RPVer As String      'Primary key value of the new record.
    Dim strRecipeID As String
    Dim user As String
    user = UserName
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If

    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select a record to duplicate."
          Exit Sub
           
    Else
        'Duplicate the main record: add to form's clone.
        
       user = UserName
       strRecipeID = Me.[txt_recipeID]
       
       RPID = Me.[txt_recipeID]
       RPVer = DMax("[RecipeVersion]", "[TRecipeName]", "[RecipeID] = '" & strRecipeID & "'") + 1
        
        With Me.RecordsetClone
            
            .AddNew
                !RecipeID = Me.[txt_recipeID]
                !RecipeVersion = RPVer
                !RName = Me.[txt_Description]
                !Procedure = Me.[TxtProcedure]
                !CasePack = Me.[txt_UnitPack]
                !Sauce_Unit = Me.[txtSaucwPkt]
               ‘etc.
            .Update
           Me.Requery
           .Bookmark = .LastModified

dbs.Close
                strSql = "INSERT INTO [TRecipeID] (recipeID, RecipeVersion, productID, ProductVersion, [%], Amt, Recipe_UOM) VALUES " _
                   & "('" & RPID & "', '" & RPVer & "', '100000', '1', '100', '100' , 'LB'); "
                DBEngine(0)(0).Execute strSql

        End With
    End If
Me.Requery
DoCmd.GoToRecord , , acLast

End Sub

When I replace the Insert Into Statement with following one then I got

Code:

Run-Time error ‘3075’ Syntax error (missing operator) in query expression ‘555’

Which 555 is the recipe ID that I want to copy from.

Code:

strSql = "INSERT INTO [TRecipeID] (recipeID, RecipeVersion, productID, ProductVersion, [%], Recipe_UOM) " _
                   & "SELECT " & RPID & " As recipeID, " & RPVer & " As RecipeVersion, productID, ProductVersion, [%], Recipe_UOM " _
                   & "FROM [TRecipeID] WHERE recipeID = " & Me.[txt_recipeID] & " & AND & RecipeVersion = " & Me.[txtRecipeVer] & "; "

When I click debug Access pointed to this line

Code:

DBEngine(0)(0).Execute strSql

I think the problems may be due to the syntax AND in the SQL statement?? Hope someone can help me out with this.

Thanks,
Joe

**zmbd** · May 25 '15, 05:53 PM

Insert

Code:

Debug.print strSQL

at line 48 (just following your strSQL )
Run
<ctrl><g> to open the immediate window.
Cut and paste the resolved string in a code-block and we'll take a look at it.

**Joe Y** · May 25 '15, 07:09 PM

zmbd,

Thanks for showing this technique.

The resolved string is as below.

Code:

INSERT INTO [TRecipeID] 
   (recipeID, RecipeVersion, productID
      , ProductVersion, [%]
      , Recipe_UOM) 
SELECT 555
      As recipeID
   , 5 As RecipeVersion, productID
   , ProductVersion, [%], Recipe_UOM 
FROM [TRecipeID] 
WHERE recipeID = 555  
  & AND & RecipeVersion = 4;

**zmbd** · May 25 '15, 07:38 PM

Give me a minute to review... OP changed post while I was looking...

Joe, If you feel comfortable doing so, please PM me the SQL string as it is actually resolving. It is important to see how the string is actually formatted .

In this specific case; confidentiality here trumps our normal post in the public rule :)

**Joe Y** · May 26 '15, 02:16 AM

zmbd,

Thanks for pointing out the cause of my SQL Statement issue. After adding a couple of "'" and & to wrap around the variables the code is working now!

Thanks,
Joe

**zmbd** · May 26 '15, 01:29 PM

First, thank you for your trust.
I compared your neutered string with what you send me via PM and have neutered the string to match while preserving the formatting of the string.

What I've done here is stepped the string so that we can follow some logic in the SQL:

Code:

 INSERT INTO [TRecipeID] 
(recipeID, RecipeVersion
   , productID, ProductVersion
   , [%], Recipe_UOM) 
SELECT 
   AAA BB CCC DDD EEEE 
      As recipeID
   , 5 
      As RecipeVersion
   , productID, ProductVersion
   , [%], Recipe_UOM 
FROM [TRecipeID] 
WHERE recipeID = 
   AAA BB CCC DDD EEEE  &
      AND & RecipeVersion = 4;

+ First note is that you can not have a field named "AAA BB CCC DDD EEEE" without it being in brackets (on line 6) The SQL engine looks for commas and spaces for the logical blocking.
+ Second note is that you have the ampersand "&" in the string in the WHERE clause line 14 and line 15. These should resolve out
+ Third note is that in there is an extra space between the "AAA BB CCC DDD DDDD" and the ampersand line 14 (not really an issue persay... just the OCD in me I think) and we'll remove that anyway once the string is building correctly.
+ finally that recipeID = AAA BB CCC DDD EEEE we need some quotes around the string value... strange how this value is the same as your field name in the opening SELECT ???

So fixing all of these we should have a string that resolves to:

Code:

 INSERT INTO [TRecipeID] 
(recipeID, RecipeVersion
   , productID, ProductVersion
   , [%], Recipe_UOM) 
SELECT 
   [AAA BB CCC DDD EEEE]
      As recipeID
   , 5 
      As RecipeVersion
   , productID, ProductVersion
   , [%], Recipe_UOM 
FROM [TRecipeID] 
WHERE recipeID = 
   'AAA BB CCC DDD EEEE'
      AND RecipeVersion = 4;

So pulling from the last Code Block of your post (#9) and making the corrections (I'm guessing that [RecipeVersion] is a numeric field type and not string/text):

Code:

 strSql = _
   "INSERT INTO [TRecipeID]" & _
      " (recipeID, RecipeVersion" & _
      ", productID, ProductVersion" & _
      ", [%], Recipe_UOM)" & _
   " SELECT" & _ 
      " [" & RPID & "] As recipeID, " & _ 
      RPVer & " As RecipeVersion" & _
      ", productID, ProductVersion" & _
      ", [%], Recipe_UOM" & _
   " FROM [TRecipeID]" & _ 
   " WHERE recipeID = '" & Me.[txt_recipeID] & _ 
      "' AND RecipeVersion = " & Me.[txtRecipeVer] & ";"

--- now I don't have your form so the above my have a syntax or typo issue; however, it should work as is.

**Joe Y** · May 26 '15, 09:38 PM

zmbd

Many Thanks! The code works.

I guess using " ' " is the same as " [ " to wrap a field name?

The version is a text field. Although Access still operate the math of adding 1 on a text field, I know I will have to change it to a number field, eventually.

Thanks,
Joe

Duplicate record in Access Main Form and Sub Form

Duplicate record in Access Main Form and Sub Form

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment