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
- Private Sub Command53_Click()
- 'On Error GoTo Err_Handler
- 'Purpose: Duplicate the main form record and related records in the subform.
- Dim strSql As String 'SQL statement.
- Dim lngID As Long 'Primary key value of the new record.
- Dim rst As Recordset
- Dim rstmv As Recordset2
- 'Save and 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
- 'Duplicate the main record: add to form's clone.
- With Me.RecordsetClone
- .AddNew
- !Site_Name = Me.Site_Name
- !Date_of_Dive = Me.Date_of_Dive
- !Time_of_Dive = Me.Time
- Set rst = Me.RecordsetClone
- Set rstmv = rst!Staff.Value
- Do While Not rstmv.EOF
- rsp.Edit
- rstmv.Edit
- rstmv.AddNew ' Add a new record to the asp Recordset
- rstmv = rstmv.Value
- rstmv.Update ' Commit the changes to the asp Recordset
- imt.MoveNext
- Loop
- .Update
- !O2 = Me.O2
- !First_Aid = Me.First_Aid
- !Spares = Me.Spares
- 'etc for other fields.
- .Update
- 'Save the primary key value, to use as the foreign key for the related records.
- .Bookmark = .LastModified
- lngID = !Dive_Number
- 'Duplicate the related records: append query.
- If Me.[DiveDetailssubform].Form.RecordsetClone.RecordCount > 0 Then
- strSql = "INSERT INTO [DiveDetails] (Dive_Number, CustDateID, Type, Price) " & _
- "SELECT " & lngID & " As NewID, CustDateID, Type, Price " & _
- "FROM [DiveDetails] WHERE Dive_Number = " & Me.Dive_Number & ";"
- DBEngine(0)(0).Execute strSql, dbFailOnError
- Else
- MsgBox "Main record duplicated, but there were no related records."
- End If
- 'Display the new duplicate.
- Me.Bookmark = .LastModified
- MsgBox "Dive Sucessfully Duplicated. DONT FORGET TO CHANGE THE SITE NAME."
- End With
- End If
- Exit_Handler:
- Exit Sub
- Err_Handler:
- MsgBox "Error " & Err.Number & " - " & Err.Description, , "Duplicate_Click"
- Resume Exit_Handler
- End Sub
Expand|Select|Wrap|Line Numbers
- Private Sub Form_Load()
- Dim varID As Variant
- Dim strDelim As String
- 'Note: If CustomerID field is a Text field (not a Number field), remove single quote at start of next line.
- 'strDelim = """"
- varID = DLookup("Value", "tblSys", "[Variable] = 'DiveIDLast'")
- If IsNumeric(varID) Then
- With Me.RecordsetClone
- .FindFirst "[dive_number] = " & strDelim & varID & strDelim
- If Not .NoMatch Then
- Me.Bookmark = .Bookmark
- End If
- End With
- End If
- End Sub
- Private Sub Form_Unload(Cancel As Integer)
- Dim rs As DAO.Recordset
- If Not IsNull(Me.Dive_Number) Then
- Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
- With rs
- .FindFirst "[Variable] = 'DiveIDLast'"
- If .NoMatch Then
- .AddNew 'Create the entry if not found.
- ![Variable] = "DiveIDLast"
- ![Value] = Me.Dive_Number
- ![Description] = "Last DiveID, for form Dive Planner" & Me.Name
- .Update
- Else
- .Edit 'Save the current record's primary key.
- ![Value] = Me.Dive_Number
- .Update
- End If
- End With
- rs.Close
- End If
- Set rs = Nothing
- End Sub