January 31, 2015 at 11:44 pm
I'm working on someone's database and he's using an autonumber as an Invoice Number. Then if he gets another request from his customer that's just like a previous invoice, he clones it. (InvoiceHeader and InvoiceDetail). In SQL Server, I can turn off IDENTITY_INSERT, but not so in Access. So do I just create an Autonumber to deal with the parent-child relationship and then another indexed column (or two) to uniquely identify an invoice so I can find/clone okay?
Maybe something like:
CREATE TABLE InvoiceHeader(
InvoiceNumber INT IDENTITY NOT NULL,
CloneOfInvoice INT,
CloneNumber TINYINT,
CustomerID INT...
FOREIGN KEY (CloneOfInvoice) REFERENCES InvoiceHeader(InvoiceNumber)
seems like I need:
1. a unique invoiceNumber (not displayed, just for relationships)
2. a displayed invoiceNumber with a format like [0-9][0-9][0-9][0-9][A-Z]
Am I on the right track? Just not sure how to do this. One key thing I think I left out... once an Invoice/job is completed, the recordes get archived to a different table.
Any thoughts?
Thanks!
Pieter
February 8, 2015 at 3:31 pm
How are you triggering the cloning of a record? If it is a button on the main form (presumably bound to the table InvoiceHeader), then you would probably find the easiest process to be a VBA procedure initiated by the OnClick event for the button that uses either ADO or DAO to add the new Invoice to the InvoiceHeader, capture the Identity value of that record, and then add the InvoiceDetail record, all in the VBA procedure. You could also do the deed using SQL statements assembled in VBA if you aren't comfortable with DAO or ADO, but debugging the statements is a more complex task than debugging with DAO or ADO. In addition, you can provide some dialog boxes to inform the user that things have worked - or they haven't. For example, you could prompt the user to make sure they really want to clone that specific Invoice.
Sorry about the delay in responding - been travelling for several days.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 8, 2015 at 5:53 pm
Wendell,
I'm okay at VBA. I am pretty sure Allen Browne has some code to clone records inside a transaction. The only hard part is retrieving the value of the last autonumber value inserted. (Bookmark?) The rest would be trivial.
I think I have to get the client to better explain what he wants. It's not always clear. For example, there are a few columns in the database that appear to be in the wrong table, so for the most part, the database is obvious, but not always.
Thanks,
Pieter
February 9, 2015 at 7:45 am
The usual method for getting the value of the identity column is to do a query that gives you the maximum value of the identity right after you add the record. (In the Access engine, the value of the identity is set as soon as you begin an insert operation, but if you link to SQL Server for the table, it isn't set until you actually save the record, so we use the maximum value in the table, as most of our back-ends are SQL Server.) Here's some sample DAO code using that technique:
'Triple check warning
If Me.tabMain = 1 Then
If MsgBox("Are you sure you want to add this person to the system even thought this could be a duplicate?", vbCritical + vbYesNo + vbDefaultButton2, "Possible Duplicate") = vbNo Then
Exit Function
End If
End If
'SQL Change
Set rsAdd = CurrentDb.OpenRecordset("SELECT * FROM tblPeople WHERE lngPersonID=-1", dbOpenDynaset, dbSeeChanges + dbAppendOnly)
rsAdd.AddNew
rsAdd!strLastName = Me.strMemberLastName
rsAdd!strFirstName = Me.strMemberFirstName
rsAdd!strMiddleName = Me.strMemberMiddleName
rsAdd!strSuffix = Me.strMemberSuffix
rsAdd.Update
lngNewPersonID = DMax("lngPersonID", "tblPeople", "strLastName = '" & Me.strMemberLastName & "' and strFirstName = '" & Me.strMemberFirstName & "'")
Set rsAdd = CurrentDb.OpenRecordset("SELECT * FROM tblMembers WHERE lngMemberNumber=-1", dbOpenDynaset, dbSeeChanges + dbAppendOnly)
rsAdd.AddNew
rsAdd!lngMemberNumber = lngNewPersonID
rsAdd.Update
Note the cautionary warning prompt at the beginning - you might want that sort of thing to prevent accidental button clicks. Also note the use of the -1 in the criteria of the SELECT statements - that ensures that it will be an empty recordset when you start the add.
Working on a database that someone else designed is always a challenge - hope this helps.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply