473,288 Members | 1,705 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

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
Oct 10 '21 #1
2 4881
NeoPa
32,554 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?
Oct 10 '21 #2
isladogs
454 Expert Mod 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
Oct 10 '21 #3

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

Similar topics

8
by: Mark | last post by:
When my form goes to a new record, I have a procedure that copies the last record added to the form's underlying table into the form. The intent is that a series of new records may have the same...
4
by: | last post by:
This is blowing my mind because I thought this setup was working.... How is a new record generated in the table of a Child subform whenever a new record is generated in the table of the Master...
1
by: phaddock4 | last post by:
Being fairly inexperienced at Access 2000, i've been reading many posts here for the last several days, and testing myself to find the best approach to do the following in A2K: SET UP: I have...
3
by: paquer | last post by:
On my Main form I have a Command Button that opens a Subform in order to create a new Subform record. At this point I want the subform to show only the new record being created. Not all the...
6
by: teser3 | last post by:
I have my PHP inserting into Oracle 9i. But how do I prevent duplicate record entries? I only have 3 fields in the insert in the action page: CODE <?php $c=OCILogon("scott", "tiger",...
1
by: VinArt | last post by:
MS Acc 2003, XP Thank you in advance for any help. I have tables called "Makeup" and "Lines". Each makeup can have multiple lines. Goal is to create a new "makeup" with identical "lines"...
1
by: AlisonP | last post by:
Hi, I am a relatively new Access programmer, but have programmed computers in Fortran and done complex modeling for years. Am learning quickly, and have managed to figure everything out so far, but...
6
Seth Schrock
by: Seth Schrock | last post by:
I need to know how to use a For Each/Next loop to delete all records on a subform that have the same values in a field. The subform is a form based on a linking table with both fields combined as...
2
by: newaccessuser13 | last post by:
I am relatively new to Access (2010) and I am having difficulties in making a duplicate record button that was created with the wizard work correctly. I have a very simple database and form. To...
6
by: A1M6K | last post by:
Very new to VBA and struggling to link the main form and the subform to the same record in the same table. Currently they are each entering separate records in the same table. They are linked by...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.