Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 1st, 2005, 10:56 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default recovering an autonumber primary key

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 With Quote
  #2 (permalink)  
Old October 1st, 2005, 11:02 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Loralee,

Could you post the code from your procedure?

Kevin


dartcoach
Reply With Quote
  #3 (permalink)  
Old October 1st, 2005, 11:28 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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 With Quote
  #4 (permalink)  
Old October 1st, 2005, 11:32 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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 With Quote
  #5 (permalink)  
Old October 1st, 2005, 11:37 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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 With Quote
  #6 (permalink)  
Old October 1st, 2005, 11:39 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Kevin

dartcoach
Reply With Quote
  #7 (permalink)  
Old October 1st, 2005, 11:40 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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 With Quote
  #8 (permalink)  
Old October 1st, 2005, 11:43 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thanks, Again!

I'll give it a try.

Loralee

Reply With Quote
  #9 (permalink)  
Old October 1st, 2005, 11:44 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Loralee,

Let me know if it works.

Kevin

dartcoach
Reply With Quote
  #10 (permalink)  
Old October 2nd, 2005, 12:52 AM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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 With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Foreign key not updating with Primary key xavier1945 BOOK: Access 2003 VBA Programmer's Reference 2 July 4th, 2007 09:48 PM
Primary key cf2006 BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 1 July 31st, 2006 07:21 PM
FOREIGN KEY and PRIMARY KEY Constraints junemo Oracle 10 June 15th, 2004 01:00 AM
Autonumber Primary Key JonnyRPI Access 1 June 27th, 2003 10:59 PM



All times are GMT -4. The time now is 06:46 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.