J
jo
Could anyone help with modifying the code example below?
My current database uses the autonumber field as the Primary key so I did
not have a problem adapting it for my use. However the dB I am now creating
needs the code to be modified as table 1 does not have its Primary key
assigned to its autonumber field.
For Table 1 the autonumber field is called ID_Main and the Primary key has
been assigned to a field called” acd”.
For Table 2 the Primary key has been assigned to the autonumber field i.e.
ID_SI
For Table 3 the Primary key has been assigned to the autonumber field i.e.
ID_A
The table Relationship has be configured as one to many
The field called “acd” from table1 links the other “acd” fields in table 2
and 3
The following is the core part of a routine that I have been using to
duplicate a current record.
'Duplicate the current record on the form
Dim rst As Recordset
Dim aVarContents() As Variant
Dim iCount As Integer
Dim iFldCount As Integer
Dim heldtxtIDAndProjectRef As Variant
Dim MSResponse As Variant
Dim HeldSigOb As Date
Dim HeldPrintForAuth As Date
iFldCount = Me.RecordsetClone.Fields.Count - 1
ReDim aVarContents(iFldCount) 'resize the array
'Duplicate the current record into an array
Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark
'Get contents of main record
For iCount = 0 To iFldCount
aVarContents(iCount) = rst.Fields(iCount)
Next iCount
'This code relies on the Primary key being an autonumber field
'If you have other fields that must be unique
'then you will need to handle that by identifying the primary
'key (unique field) in the loop (perhaps by field name)
With rst
.AddNew
For iCount = 0 To iFldCount
If .Fields(iCount).Attributes _
And dbAutoIncrField Then
'Autonumber field so Skip and let it fill
'automatically
Else 'This is not primary key so populate field
If IsNull(aVarContents(iCount)) = False Then
'check for nulls, else do it
.Fields(iCount) = aVarContents(iCount)
End If
End If
Next iCount
.Update
End With
End If
Set rst = Nothing
Thank you for any assistants
My current database uses the autonumber field as the Primary key so I did
not have a problem adapting it for my use. However the dB I am now creating
needs the code to be modified as table 1 does not have its Primary key
assigned to its autonumber field.
For Table 1 the autonumber field is called ID_Main and the Primary key has
been assigned to a field called” acd”.
For Table 2 the Primary key has been assigned to the autonumber field i.e.
ID_SI
For Table 3 the Primary key has been assigned to the autonumber field i.e.
ID_A
The table Relationship has be configured as one to many
The field called “acd” from table1 links the other “acd” fields in table 2
and 3
The following is the core part of a routine that I have been using to
duplicate a current record.
'Duplicate the current record on the form
Dim rst As Recordset
Dim aVarContents() As Variant
Dim iCount As Integer
Dim iFldCount As Integer
Dim heldtxtIDAndProjectRef As Variant
Dim MSResponse As Variant
Dim HeldSigOb As Date
Dim HeldPrintForAuth As Date
iFldCount = Me.RecordsetClone.Fields.Count - 1
ReDim aVarContents(iFldCount) 'resize the array
'Duplicate the current record into an array
Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark
'Get contents of main record
For iCount = 0 To iFldCount
aVarContents(iCount) = rst.Fields(iCount)
Next iCount
'This code relies on the Primary key being an autonumber field
'If you have other fields that must be unique
'then you will need to handle that by identifying the primary
'key (unique field) in the loop (perhaps by field name)
With rst
.AddNew
For iCount = 0 To iFldCount
If .Fields(iCount).Attributes _
And dbAutoIncrField Then
'Autonumber field so Skip and let it fill
'automatically
Else 'This is not primary key so populate field
If IsNull(aVarContents(iCount)) = False Then
'check for nulls, else do it
.Fields(iCount) = aVarContents(iCount)
End If
End If
Next iCount
.Update
End With
End If
Set rst = Nothing
Thank you for any assistants