|
Subject:
|
recovering an autonumber primary key
|
|
Posted By:
|
Loralee
|
Post Date:
|
10/1/2005 10:56:30 PM
|
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
|
|
Reply By:
|
dartcoach
|
Reply Date:
|
10/1/2005 11:02:18 PM
|
Loralee,
Could you post the code from your procedure?
Kevin
dartcoach
|
|
Reply By:
|
Loralee
|
Reply Date:
|
10/1/2005 11:28:03 PM
|
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
|
|
Reply By:
|
Loralee
|
Reply Date:
|
10/1/2005 11:32:22 PM
|
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...
|
|
Reply By:
|
dartcoach
|
Reply Date:
|
10/1/2005 11:37:18 PM
|
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
|
|
Reply By:
|
dartcoach
|
Reply Date:
|
10/1/2005 11:39:42 PM
|
You might also try to retrive the PK immediately after the update. I.E.
.update
myPK = !Pk
Kevin
dartcoach
|
|
Reply By:
|
Loralee
|
Reply Date:
|
10/1/2005 11:40:46 PM
|
is it possible to requery then move the cursor to the last record of the recordset in tblAUth and then pull the PK back?
|
|
Reply By:
|
Loralee
|
Reply Date:
|
10/1/2005 11:43:02 PM
|
Thanks, Again!
I'll give it a try.
Loralee
|
|
Reply By:
|
dartcoach
|
Reply Date:
|
10/1/2005 11:44:54 PM
|
Loralee,
Let me know if it works.
Kevin
dartcoach
|
|
Reply By:
|
Loralee
|
Reply Date:
|
10/2/2005 12:52:32 AM
|
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
|
|
Reply By:
|
dartcoach
|
Reply Date:
|
10/2/2005 12:54:41 AM
|
Loralee,
Your more than welcome, glad I could help.
Kevin
dartcoach
|
