Copying a single record to same table

[mick3911]

When a user enters the required data and clicks on the ’Save’ button, I would like to copy the record and paste it into the same table with one exception in that the Date Completed is placed into the PrevTrgCompDate field.
I have the following code which works great except that the Date Completed data is not placed into the PrevTrgCompDate field. (Last line of the code).
Can someone point out to me where I am going wrong and the best way to fix this issue.

Private Sub btnSave_Click()

If IsNull(Me.Date_Completed) Then
MsgBox "Date Completed is required"
Me.Date_Completed.SetFocus

Else

If IsNull(Me.Trained_By) Then
MsgBox "Trained By is required"
Me.[Trained_By].SetFocus

Else

Me.Required.Value = False

Dim currentID As Long

currentID = TrainingID

DoCmd.GoToRecord record:=acNewRec

EmployeeID = DLookup("EmployeeID", "tblTrainingHistory", "TrainingID=" & currentID)
TrainingItemID = DLookup("TrainingItemID", "tblTrainingHistory", "TrainingID=" & currentID)
TrainingCodeSOPNo = DLookup("TrainingCodeSOPNo", "tblTrainingHistory", "TrainingID=" & currentID)
TrainingDescription = DLookup("TrainingDescription", "tblTrainingHistory", "TrainingID=" & currentID)
IssueNo = DLookup("IssueNo", "tblTrainingHistory", "TrainingID=" & currentID)
PrevTrgCompDate = DLookup("Date_Completed", "tblTrainingHistory", "TrainingID=" & currentID)

[Minty]

I suspect that the date completed hasn't been saved so it doesn't find a record.
Try a

If Me.Dirty Then Me.Dirty = False

to force a save just before all the Dlookups.

Also I would be prefixing all the updated controls with Me.txtMyControlName to reference them on the form correctly.

[CJ_London]

just curious as to why you need to save a second record - the previous completed date is easy to determine

[mick3911]

Hi Minty,

Unfortunately that hasn't solved it. The same still happens.

[mick3911]

The idea is that there is an existing record (training item), when training is completed the user enters the relevant details.
The DB is set up so that dependant on the training item, refresher training will be required, the 'copied record' will then be completed when the refresher training training is completed and so on.
We need to keep all training records as historic data

[mick3911]

As a note, I have tried an Append query (here is the SQL code)

INSERT INTO tblTrainingHistory ( EmployeeID, TrainingItemID, TrainingCodeSOPNo, TrainingDescription, IssueNo, PrevTrgCompDate, [Refresher Due] )SELECT tblTrainingHistory.EmployeeID, tblTrainingHistory.TrainingItemID, tblTrainingHistory.TrainingCodeSOPNo, tblTrainingHistory.TrainingDescription, tblTrainingHistory.IssueNo, tblTrainingHistory.[Date Completed], qryEmployeeHistory.[Due Refresher]
FROM (tblTrainingItems INNER JOIN tblTrainingHistory ON tblTrainingItems.TrainingCodeSOPNo = tblTrainingHistory.TrainingCodeSOPNo) INNER JOIN qryEmployeeHistory ON tblTrainingItems.TrainingItemID = qryEmployeeHistory.TrainingItemID;

But this duplicates the record multiple times.

[Minty]

That would work but you need to concatenate in a where clause to restrict it just adding the one record.

WHERE TrainingID=" & Me.currentID

[CJ_London]

We need to keep all training records as historic data

I understand that, just not why you need the previous date field since it is easily determined. However you've answered my question and I don't want to distract you from your problem

[CJ_London]

looking at the problem

you said

which works great except that the Date Completed data is not placed into the PrevTrgCompDate field

so that implies there is something wrong with this line

PrevTrgCompDate = DLookup("Date_Completed", "tblTrainingHistory", "TrainingID=" & currentID)

since the other fields are being populated so currentID is presumably correct

so either you have a typo or date completed is blank

and looking at your sql you have [date completed] and in your code "Date_Completed", one with an underline and one with a space

Since the sql works in that it does not generate errors, I assume the space is the correct spelling, so you need

PrevTrgCompDate = DLookup("[Date Completed]", "tblTrainingHistory", "TrainingID=" & currentID)


Note it is generally a bad idea to have spaces in field and table names - it causes problems like this