BeforeUpdate Event to Create New Copy of Current Record

[breakingme10]

I use VBA in the beforeupdate event of a text box, it creates a copy of the current record as it was and appends it with a new autonumber PK. This is causing problems with my other tables that use a FK link to that record. Basically the old record becomes a new record, and so when I go to enter data for my new record, it's still showing the old records data as current because it is using the same PK as the old record and the old record got a new PK. I want to flip flop it and make the new record get the new PK. I'm not sure how I alter this to do that. Here is my current code. I would really appreciate some assistance, I've been working on this database for months and I feel like I've made a huge mistake in my entire workflow.


I currently am keeping all my data in Excel for this as well for a backup in case something goes wrong and I think something has definitely gone wrong.

Private Sub TktNumber_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Set db = CurrentDb()
If Not (IsNull(Me.TktNumber)) Then
strSQL = 
"INSERT INTO LocateData(ExcavatorID, CallerID, ContactID, TicketTypeID, AddIntID, ContractorID, LocateNameID, LocateName, MasterJobNumber, JobNumber, CoverPage_NumOfTkts, LocateList_Order, PrevTktNumber, TktNumber, StartDate, StartTime, UpdateBy, ExpDate, WorkLocation, WorkType, WorkTypeNote, DrivingDirections, LocateInstructions, Remarks, DurationofWork, DurationUOMID, ExplosivesYN, MarkingsYN, GridYN, DirBoringYN, MultiTktYN, DateLocateInitiated, CoordinatesUsed, Notes, ActiveLocateYN) " & _
"SELECT ExcavatorID, CallerID, ContactID, TicketTypeID, AddIntID, ContractorID, LocateNameID, LocateName, MasterJobNumber, JobNumber, CoverPage_NumOfTkts, LocateList_Order, PrevTktNumber, TktNumber, StartDate, StartTime, UpdateBy, ExpDate, WorkLocation, WorkType, WorkTypeNote, DrivingDirections, LocateInstructions, Remarks, DurationofWork, DurationUOMID, ExplosivesYN, MarkingsYN, GridYN, DirBoringYN, MultiTktYN, DateLocateInitiated, CoordinatesUsed, Notes, 0 " & _
"FROM LocateData " & _
"WHERE (LocateID = " & Me.LocateID & ")"
db.Execute strSQL, dbFailOnError
Else
Exit Sub
End If
Me.PrevTktNumber = Me.TktNumber.OldValue
End Sub

[ranman256]

you cant create a copy until the source record exists.
that would be an Afterupdate event.

[orange]

Can you tell us in simple English what you are trying to achieve?
Why are you making a copy of the record?

One basic fact in database is set up primary key(s) that are immutable (never change).
Primary keys are really there for the DBMS --to ensure uniqueness of each record in that table.

BeforeUpdate is the last event before a record is saved. It is the ideal event to validate/verify proposed changes before accepting the record. It affords the opportunity to cancel the update.

Good luck.

[breakingme10]

I have a ticket number, each week we update the ticket and it changes. I want to go through the old ticket numbers list, update the ticket number to the new one and have the old ticket number move to the PreviousTktNumber field, and then create a new record with the that information in place. I was copying out the oldvalue information to a new record and that seems backwards and is causing issues with FK in other tables that are linked to that TktNumber. I update the ticket numbers with a continuous form that shows all the Active tkt numbers, i then enter the new ticket number over the old one in that form, the old value of the text box moves to PreviousTktNumber and the record copies out the old information. I attached the db. Speed of data entry is important, because I don't have a lot of time to sit there and retype all the information for a simple update of the ticket number and dates.
I need to be able to enter the new ticket number in the update locates form in the current ticket number textbox and have the previous value of that textbox move itself to PreviousTktNumber textbox. That works just fine right now. But then I need it to create a new record for my new ticket number and dates instead of just updating the old one and copying the old record into a new record.
I know what a primary key is and the importance of it, it just never occurred to me a week ago, when I wrote the BeforeUpdate code, that it was indeed changing the primary key of that instance of the locate.

[breakingme10]

I think I may have figured out what I should do. At the end of the week, copy all records that will be updated on Monday with a button, on Monday my Update form will show those records and then update the ticket numbers in those.

[orange]

Can you tell readers in 2 or 3 lines what a ticket is and what this database is about?

[breakingme10]

Ticket is issued for a locate request. Locate requests have to be updated weekly to keep them active so utility companies will refresh their paint or flags each week, lessening the chance of us hitting any underground lines while working.