Gegsy said:
I have a document database that often deals with multiple copies of a document. Each copy must have a record of its own. With the add new record form I would like the user to be able to add the extra copies automatically by duplicating the first entry "n" times but also incrementing the copy number field by one for each copy. Got the duplicate copy done OK but am stuck on how to increment the copy number. This database forms part of my yearly assessment and is due in a couple of weeks so would welcome some help.
many thanks
The incrementing part could be done by: Nz(DMax("[aField"],"tblSomething"),1)+1
in code (behind a 'make copy' button).
I'm not sure if you mean 'copy' the recordset or copy the document.
Believing you mean 'copy the recordset', that is: copy an ordernumber into a new ordernumber and bringing data from subtable, I have given the following example earlier:
Private Sub cmdCopy_Click()
On Error GoTo Err_SENDCOPY
Dim MyRs As DAO.Recordset
Dim sqlNew As String
Dim lngNewOID As Long
Dim intONr As Integer
Dim My2Msg As String, My2Ttl As String
Dim My2Btn As Integer, My2RetVal As Integer
My2Msg = "DO YOU REALLY WANT TO MAKE A COPY?"
My2Msg = Chr(13)
My2Btn = 51
My2RetVal = MsgBox(My2Msg, My2Btn, "COPY ORDER")
Me.Refresh
If My2RetVal = 2 Then
DoCmd.CancelEvent
Me!CustomerID.SetFocus
End
ElseIf My2RetVal = 7 Then
DoCmd.CancelEvent
Me!CustomerID.SetFocus
End
ElseIf My2RetVal = 6 Then
Set MyRs = Me.RecordsetClone
intONr = Nz(DMax("[Ordernr]", "tblOrders"),1) + 1
With MyRs
.AddNew
!Ordernr = intONr
!MyDate = Now
!MyTime = Time
!CustomerID = Me!CustomerID
!EmployeeID = Me!EmployeeID
.Update
End With
MyRs.Bookmark = MyRs.LastModified
lngNewOID = MyRs!OrderID
sqlNy = "INSERT INTO tblOrderdetails(ItemID,Price,Amount,Discount,Taxes ,OrderID) " _
& "SELECT ItemID,Price,Amount,Discount,Taxes," & lngNewOID _
'Now pay attention to the space in the following line:
& " FROM tblOrderdetails " _
& "WHERE OrderID = " & Me!OrderID
CurrentDb.Execute sqlNew, dbFailOnError
Me!CustomerID.SetFocus
End If
DoCmd.GoToRecord Record:=acLast
Exit_SENDCOPY:
Exit Sub
Err_SENDCOPY:
MsgBox Err.Source & Chr(13) _
& "Error # " & " " & Err.Number & " ORDERS/SENDCOPY" & Chr(13) _
& Err.Description & Chr(13), vbCritical + vbOKOnly
Resume Exit_SENDCOPY
End Sub
To make it easy, you could have a listbox on the main form and load data from the actual table when forms open.
Then when picking an item in the listbox you could fill the textboxes with data from the underlying table through:
DoCmd.GoToControl "[MyDocumentID]"
DoCmd.FindRecord lstVareInfo, acEntire, False, acDown, False, acCurrent, True
Just remember that the first field in the select query to fill the listbox should be the ID (Autonumber field) from the table and should be hidden
Hope this helps
