I have an unbound form that accepts user input, verifies data, then saves the data to respective tables, before it launches a report (authorization for care) which will print. I am planning to populate the report (authorization) with information on the input form (which will become invisible).

I just realized I also need the primary key from the authorization for the report (which is an autonumber and will be created once the procedure saves the record..... and I'm not sure how to recover it.

The input form is unbound, so the PK will not appear on its own. If there is more than one authorization for a given patient DLookup will need the PK. ... I don't anticipate more than 2 of us using this at a time, so it is likely the PK for the last record will be the one sought..... Would it be better to break up the procedure into 2 button events, where one saves the record and the other constructs the SQL for the report?

Is there another function I could call?

Thank you,

Loralee

Loralee,

Could you post the code from your procedure?

Kevin


dartcoach

This is the code from frmInputAuth. That's my input form and what I was planning on using to launch the report. RptAuthorization is the report which is launched (essentially a single authorization for medical care for one individual). tblAuth is the table which is being populated, and it's PK (authID) is autonumber. [Maybe autonum datatype wasn't too smart...)

Thanks for taking a look and any suggestions.

Private Sub cmdCreateAuth_Click()
Dim lngPatientID As Long
Dim strPtFirstName As String
Dim strPtLastName As String
Dim strPTAddress As String
Dim strPtCityFK As String
Dim strPTZip As String
Dim strPtDOB As String
Dim lngReferFK As Long
Dim lngVendorFK As Long

' variables for Authorization entered by User
Dim strServiceType As String
Dim strIssueDate As String
Dim dtmDateStart As Date
Dim dtmDateEnd As Date
Dim strProcDate As String
Dim strService As String
Dim strPractitionerWholeName As String
Dim varParentFirstName As Variant
Dim varParentLastName As Variant


Dim db As DAO.Database
Dim wrk As DAO.Workspace
'Dim rstReferral As DAO.Recordset ' hold tblReferral data
Dim rstAuth As DAO.Recordset ' hold tblAuth Data
Dim rstPatient As DAO.Recordset ' hold tblPatient data

Set db = CurrentDb
'Set rstReferral = db.OpenRecordset("tblReferral", dbOpenDynaset)
Set rstAuth = db.OpenRecordset("tblAuth", dbOpenDynaset)
Set rstPatient = db.OpenRecordset("tblpatient", dbOpenDynaset)
Set wrk = DBEngine(0)

' record the keys!
lngPatientID = Forms!frmpatientdata!PatientID
lngReferFK = Forms!frmpatientdata!frmReferralSF!ReferID

With rstPatient ' using NZ here as creating "" will serve as place holder
    .FindFirst "PatientID = " & lngPatientID
    strPtFirstName = Nz(!PtFirstName)
    strPtLastName = Nz(!PtLastName)
    strPTAddress = Nz(!PtAddress)
    strPtCityFK = Nz(!PtCityFK)
    strPTZip = Nz(!PtZip)
    strPtDOB = Nz(!PtBD)
    varParentFirstName = !ParentFirstName
    varParentLastName = !ParentLastName
End With

    txtPtFirstName = strPtFirstName
    txtPtLastName = strPtLastName
    txtPtAddress = strPTAddress
    txtPtCityFK = strPtCityFK
    txtPtZip = strPTZip
    txtPtBirthdate = strPtDOB
    If Not IsNull(varParentLastName) Then
        txtParentLastName = varParentLastName
    End If

    If Not IsNull(varParentFirstName) Then
        txtParentFirstName = varParentFirstName
    End If


' Now intake user's input and save to tables, notify user only if REQUIRED fields blank
If cboServiceType > 0 Then
    strServiceType = cboServiceType
Else
    MsgBox "Select either Diagnostic or Treatment."
    cboServiceType.SetFocus
End If

If txtIssueDate = "" Then
    MsgBox "Enter the date authorization was issued"
    txtIssueDate.SetFocus
Else
    strIssueDate = txtIssueDate
End If

If txtDateStart = "" Then
    MsgBox "You must enter the date the Authorization becomes effective."
    txtDateStart.SetFocus
Else
    dtmDateStart = txtDateStart
End If

If txtDateEnd = "" Then
    MsgBox "You must enter the date the Authorization ends."
    txtDateEnd.SetFocus
Else
    dtmDateEnd = txtDateEnd
End If

If dtmDateStart > dtmDateEnd Then
    MsgBox "Please enter an End Date that is AFTER the start date."
    txtDateEnd.SetFocus
End If

If txtService = "" Then ' also check this one for size
    MsgBox "You must enter a description of the service authorized."
    txtService.SetFocus
Else
    If Len(txtService) > 175 Then
        MsgBox "Please edit description of Service authorized to less than 175 spaces."
        txtService.SetFocus
    Else
        strService = txtService
    End If
End If

If txtPractitionerWholeName <> "" Then
    strPractitionerWholeName = txtPractitionerWholeName
End If

If txtProcDate <> "" Then
    strProcDate = txtProcDate
End If

' wrk.BeginTrans ' hold off until figure out!
' now create record in tblAuth
With rstAuth
    .AddNew
    !referfk = lngReferFK
    !CTUVendorFK = lngVendorFK
    !servicetype = strServiceType
    !issuedate = strIssueDate
    !datestart = dtmDateStart
    !dateend = dtmDateEnd
    !service = strService

    If strProcDate <> "" Then
        !procdate = strProcDate
    End If

    If strPractitionerWholeName <> "" Then
        !ctupractitioner = strPractitionerWholeName
    End If

    .Update
End With


'wrk.CommitTrans 'dbflushoscachewrites


rstAuth.Close
rstPatient.Close

Set rstPatient = Nothing
Set rstAuth = Nothing

' Me.Visible = False
DoCmd.OpenReport "rptauthorization"


End Sub

One more thing. tblPatient is the main table on the patient side of the schema. tblPatient relates to tblReferral 1:N, tblAuth relates to tblReferral 1:M, RI is enforced. In case you're wondering...

Loralee,

After the update, since the autonumber field is your PK, it will be the last record in the recordset.
close rstAuth, then re-open it, go to the last record and save the PK to a variable. Use that to create the SQL for the report.

Kevin

dartcoach

You might also try to retrive the PK immediately after the update. I.E.
.update
myPK = !Pk

Kevin

dartcoach

is it possible to requery then move the cursor to the last record of the recordset in tblAUth and then pull the PK back?

Thanks, Again!

I'll give it a try.

Loralee

Loralee,

Let me know if it works.

Kevin

dartcoach

KEvin,
Yes, it sure did work. Thank you so much. I'be got several test records saved and a few test printouts of the report.

I have to laugh at another problem I hadn't picked up on..... In testing, I was wondering WHY my printer has been so busy- Oops! I forgot to add the ACPreview argument to the open report statement. :|

Thanks Again,
Loralee