473,729 Members | 2,130 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Duplicate record on form including subform records and multivalued fields

1 New Member
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.


Here is the code I have been using:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command53_Click()
  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
  10.         'Save and edits first
  11.     If Me.Dirty Then
  12.         Me.Dirty = False
  13.     End If
  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
  26.      Set rst = Me.RecordsetClone
  27.        Set rstmv = rst!Staff.Value
  29.                 Do While Not rstmv.EOF
  31.    rsp.Edit
  32.     rstmv.Edit
  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
  42.                 !O2 = Me.O2
  43.                 !First_Aid = Me.First_Aid
  44.         !Spares = Me.Spares
  46. 'etc for other fields.
  47.             .Update
  49.             'Save the primary key value, to use as the foreign key for the related records.
  50.             .Bookmark = .LastModified
  51.             lngID = !Dive_Number
  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
  63.             'Display the new duplicate.
  64.             Me.Bookmark = .LastModified
  65.          MsgBox "Dive Sucessfully Duplicated. DONT FORGET TO CHANGE THE SITE NAME."
  67.         End With
  68.     End If
  70. Exit_Handler:
  71.     Exit Sub
  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 = """"
  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
  18. Private Sub Form_Unload(Cancel As Integer)
  19.     Dim rs As DAO.Recordset
  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 4902
32,569 Recognized Expert Moderator MVP
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
460 Recognized Expert Moderator Contributor
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

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 data in many of the fields so I paste in the same values of the previous record and then edit what needs edited in the new record saving much retyping of the same data. Doing this however creates the definite possibility of creating a duplicate...
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 form - even though NO Child field data are entered? There's no problem if at least one of the Child's subform fields is entered. But if instead a command button is clicked in the Child's subform to open yet another form and create records in...
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 created a MainTable (and related form), which has an associated SubForm (popup) along with its underlying, separate Table. The tables' relationship is one to many respectively. The primary key
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 records the subform's table has. I cannot put the subform as Data Entry because I cannot print the main form & subform together if the subform is "data entry". (comes up blank every time)
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", "orcl"); if ( ! $c ) { echo "Unable to connect: " . var_dump( OCIError() );
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" except with a new makeup id. I created a form Makeup Copy with Line Subform. In the main form there is ID (key field), Comments, Customer, Description plus an unbound field "NewMakeup". Each line of the subform has the fields "Makeup (primary...
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 now am stuck. Sorry if this is a basic question. I am using Access 2007. I have a form for data entry. Database is biological samples. One table has sample code, date, other fields. Another table has multiple records for each sample, with...
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 the primary key and the field that hold the criteria is the same as the primary key of the main form (I hope that makes sense). I have tried making the field null using the Form!MainForm!SubForm.Form!Control.Value = null for both fields, but it just...
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 create the form with the duplicate record button, I copied a different form I created which is for new records. When I click on the duplicate record button I created, Access brings up a blank form (there is no data copied over from the previous...
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 the date in the parent-child connection and both contain a date textbox, the only difference is that the main allows the user to enter the date and the date is invisible on the subform. I have tried a few pieces of code that I have found on similar...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.