Solved: Copy and go back to previous record

[Imdabaum]

Before I leave to play with voice recognition, I have one last request that just came through from my manager.

We have a projects database. For the most part it is to keep us from duplicating work for the company, we can always track the requirements and goals and tell the user, "No that's already been done, please go here to access this utility." However, some of these projects require Help Files. In these instances, I've been requested to copy specific data from the record and create a new record based on it.

I'm struggling with the Bookmark concept. I want to bookmark the record I'm currently in. Then I want to go to a new record set the data and return to the record I was at, not just the one before it as my searches are turning up on google. I mean semantically previous does not always equal the 'one' before.

So far I have this.

'.....
lRecID = Me.PL_ID
DoCmd.Hourglass True
If (CreateHelpFileProject(Me.AssignmentTitle, Me.ReqName, #9/30/2010#, Me.ProjectRequirements)) Then
CreateNotification Me.PL_ID, Me.AsnEmail, Me.txtAsnDate, Me.ProjectRequirements, NeedsHF
Else
MsgBox "Notifcation cannot be sent because there are no requirements for this project."
End If
DoCmd.Hourglass False
DoCmd.FindRecord "[PL_ID]= " & lRecID
'......
End Sub

Private Function CreateHelpFileProject(sTitle As String, _
sRName As String, _
dAsnDate As Date, _
sRequirements As String) As Boolean

On Error GoTo Err_Handler
Dim sSQL As String
sTitle = "Help File for " & sTitle
'Create new Help file record
DoCmd.RunCommand acCmdRecordsGoToNew
Me.AssignmentTitle = sTitle
Me.cboReqName = sRName
Call cboReqName_Click
Me.AsnDate = Format(dAsnDate, "Short Date")
Me.cboAssignTo = "MARY"
Call cboAssignTo_AfterUpdate
Me.ProjectRequirements = sRequirements
Me.cboProjectType = "Helpfile"
DoCmd.RunCommand acCmdSaveRecord
CreateHelpFileProject = True
Exit_Proc:
Exit Function
Err_Handler:
CreateHelpFileProject = False
MsgBox Err.Description, vbInformation, "Error: " & Err.Number
Resume Exit_Proc
End Function

[Imdabaum]

Delete... sorry, I was rushing out the door. all it took was one more second. I forgot to set focus to the ID field.

[hansup]

You store the current record PL_ID in a variable, then call the function ... which goes to a new record in the form ... adds values from the previous record to controls bound to the new record ... saves the new record ... then back in the calling code, find the previous record again and make it current.

Consider building a SQL INSERT statement with VALUES from the current record. Execute the INSERT. Requery the form if you want the new record included in the form's recordset. No need to navigate between records in the form.

I don't know what cboReqName_Click and cboAssignTo_AfterUpdate do, so this may not work, but I would revise the function something like this:

Private Function CreateHelpFileProject(sTitle As String, _
sRName As String, _
dAsnDate As Date, _
sRequirements As String) As Boolean

On Error Goto Err_Handler
Dim strSql As String
Dim strBookMark As String

strBookMark = Me.Bookmark

strSql = "INSERT INTO Projects " & _
"(AssignmentTitle, ReqName, AsnDate)" & vbNewLine & _
"VALUES ('" & sTitle & "', '" & sRName & "', #" & _
Format(dAsnDate, "yyyy/mm/dd") & "#);"
CurrentDb.Execute strSql, dbFailOnError
Me.Requery
Me.Bookmark = strBookMark

CreateHelpFileProject = True
Exit_Proc:
Exit Function
Err_Handler:
CreateHelpFileProject = False
MsgBox Err.Description, vbInformation, "Error: " & Err.Number
Resume Exit_Proc
End Function

[Imdabaum]

Well the person I inherted the application from has several text fields that are bound to additional columns from the combo drop box. So field1=column(0), field2= column(1), etc... The after updates just make sure those values are set.