469,301 Members | 2,282 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,301 developers. It's quick & easy.

Duplicate record on form including subform records and multivalued fields

Please can you help? I am trying to duplicate a form from a button using vba. This has worked for years using Allen Browne's "Duplicate the record in form and subform." http://allenbrowne.com/ser-57.html

Now I want to change one of the fields to multivalue. I understand the difficulties with multivalued fields, but this is a 10 year old database and all I need to do is make this field be able to store multiple values, so think this will be easier than creating a new join table and updating everything related.

I am currently getting *Invalid use of Property* at the *rstmv = rstmv.Value line* (Line #36 below).

I have tried numerous versions and get different errors.

I think I should be opening the values of the multivalue field as a separate recordset, updating it then looping through the values but I am getting confused as I am not really sure what I am doing.

Any help will be greatly received.

Thanks

Here is the code I have been using:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command53_Click()
  2.  
  3. 'On Error GoTo Err_Handler
  4.     'Purpose:   Duplicate the main form record and related records in the subform.
  5.     Dim strSql As String    'SQL statement.
  6.     Dim lngID As Long       'Primary key value of the new record.
  7.     Dim rst As Recordset
  8.     Dim rstmv  As Recordset2
  9.  
  10.         'Save and edits first
  11.     If Me.Dirty Then
  12.         Me.Dirty = False
  13.     End If
  14.  
  15.     'Make sure there is a record to duplicate.
  16.     If Me.NewRecord Then
  17.         MsgBox "Select the record to duplicate."
  18.     Else
  19.         'Duplicate the main record: add to form's clone.
  20.         With Me.RecordsetClone
  21.             .AddNew
  22.                 !Site_Name = Me.Site_Name
  23.                 !Date_of_Dive = Me.Date_of_Dive
  24.                 !Time_of_Dive = Me.Time
  25.  
  26.      Set rst = Me.RecordsetClone
  27.        Set rstmv = rst!Staff.Value
  28.  
  29.                 Do While Not rstmv.EOF
  30.  
  31.    rsp.Edit
  32.     rstmv.Edit
  33.  
  34.  
  35.     rstmv.AddNew ' Add a new record to the asp Recordset
  36.     rstmv = rstmv.Value
  37.     rstmv.Update ' Commit the changes to the asp Recordset
  38.     imt.MoveNext
  39. Loop
  40.     .Update
  41.  
  42.                 !O2 = Me.O2
  43.                 !First_Aid = Me.First_Aid
  44.         !Spares = Me.Spares
  45.  
  46. 'etc for other fields.
  47.             .Update
  48.  
  49.             'Save the primary key value, to use as the foreign key for the related records.
  50.             .Bookmark = .LastModified
  51.             lngID = !Dive_Number
  52.  
  53.             'Duplicate the related records: append query.
  54.             If Me.[DiveDetailssubform].Form.RecordsetClone.RecordCount > 0 Then
  55.                 strSql = "INSERT INTO [DiveDetails] (Dive_Number, CustDateID, Type, Price) " & _
  56.                     "SELECT " & lngID & " As NewID, CustDateID, Type, Price " & _
  57.                     "FROM [DiveDetails] WHERE Dive_Number = " & Me.Dive_Number & ";"
  58.                 DBEngine(0)(0).Execute strSql, dbFailOnError
  59.             Else
  60.                 MsgBox "Main record duplicated, but there were no related records."
  61.             End If
  62.  
  63.             'Display the new duplicate.
  64.             Me.Bookmark = .LastModified
  65.          MsgBox "Dive Sucessfully Duplicated. DONT FORGET TO CHANGE THE SITE NAME."
  66.  
  67.         End With
  68.     End If
  69.  
  70. Exit_Handler:
  71.     Exit Sub
  72.  
  73. Err_Handler:
  74.     MsgBox "Error " & Err.Number & " - " & Err.Description, , "Duplicate_Click"
  75.     Resume Exit_Handler
  76. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Dim varID As Variant
  3.     Dim strDelim As String
  4.     'Note: If CustomerID field is a Text field (not a Number field), remove single quote at start of next line.
  5.     'strDelim = """"
  6.  
  7.     varID = DLookup("Value", "tblSys", "[Variable] = 'DiveIDLast'")
  8.     If IsNumeric(varID) Then
  9.         With Me.RecordsetClone
  10.             .FindFirst "[dive_number] = " & strDelim & varID & strDelim
  11.             If Not .NoMatch Then
  12.                 Me.Bookmark = .Bookmark
  13.             End If
  14.         End With
  15.     End If
  16. End Sub
  17.  
  18. Private Sub Form_Unload(Cancel As Integer)
  19.     Dim rs As DAO.Recordset
  20.  
  21.     If Not IsNull(Me.Dive_Number) Then
  22.         Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
  23.         With rs
  24.             .FindFirst "[Variable] = 'DiveIDLast'"
  25.             If .NoMatch Then
  26.                 .AddNew        'Create the entry if not found.
  27.                     ![Variable] = "DiveIDLast"
  28.                     ![Value] = Me.Dive_Number
  29.                     ![Description] = "Last DiveID, for form Dive Planner" & Me.Name
  30.                 .Update
  31.             Else
  32.                 .Edit          'Save the current record's primary key.
  33.                     ![Value] = Me.Dive_Number
  34.                 .Update
  35.             End If
  36.         End With
  37.         rs.Close
  38.     End If
  39.     Set rs = Nothing
  40. End Sub
1 Week Ago #1
2 3578
NeoPa
32,173 Expert Mod 16PB
I can't say I understand your full problem, but surely if rstmv is a DAO.Recordset object then assigning a value of any sort to it makes no sense. It would certainly be an invalid use of property as the error message says. On the other hand rstmv.Value makes no sense either so that would also be an invalid use of the property. What is it you even think you're doing with this bit of invalid code?
1 Week Ago #2
isladogs
291 Expert 256MB
You state that you understand the difficulties with multivalued fields.
If you do fully understand these, then you wouldn't be asking the question...

"I think this will be easier than creating a new join table and updating everything related."
That is definitely not the case

MVF data is actually stored is a deep hidden system table to which developers have no direct access.
Although you can do SELECT and UPDATE queries of MVF fields, you cannot do DELETE or INSERT (append) queries.
Whilst it might be possible to use a recordset, the code will probably get messy and I really wouldn't advise it.

For further info on the various issues with MVFs, please read my article Multivalued fields ...and why you really shouldn't use them
1 Week Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by paquer | last post: by
6 posts views Thread by teser3 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.