Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find Record in main form (A2K)

    Good Morning Everyone!

    I have a main form (fSNF single form view) which holds all member (INPATIENT) data and a subform (sfMemberdays
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Record in main form (A2K)

    There appear to be misplaced quotes in your code, but apart from that, it's not clear what you're doing with it. You'll have to provide more information.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Record in main form (A2K)

    Edited by HansV to remove white space from picture - 30 seconds in Paint is all it takes

    Hans,

    I will try again...but be briefer.

    The Main Form (tSNF) has a member's SSN, AuthorizationNo, AdmitDate, etc. and is in Single Form View.
    The subform (sfMemberDays)has SSN, AuthorizationNo, AdmitDate, DischargeDate, ApprovedDays and DeniedDays...nothing else and is in Data Sheet View. The link is on SSN.

    For this example, please see attached:

    On the subform (sfMemberDays) you will see this member has 2 Authorization #'s. 2289557 and 3357038. However, Auth# 2289557 has been inserted 3 times (this indicates this member has 3 records for that AuthorizationNo, 1 the PRECERT and 2 Concurrent Reviews). The first record will always be the Precert and any others listed will be the concurrent reviews. Each concurrent review has a different Admit Date (the previous records Discharge Date +1...which means the member never left the hospital/facility).

    Now, if the Case Manager needs to make changes to the record for, let's say, the concurrent review with Admit Date of 2/10/06), I need the ability for the user to:
    <UL><LI>Double Click on the AuthorizationNo in the SUBFORM which has the admit date they need, in this case, 2/10/06[/list]The OnDouble Click event should take the user the respective record on the MAIN FORM (tSNF). ....this will allow editing of the record......
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Record in main form (A2K)

    Try this:

    Private Sub AuthorizationNo_DblClick(Cancel As Integer)
    Dim rst As Object
    If Not IsNull(Me.AuthorizationNo) Then
    Set rst = Me.Parent.Recordset.Clone
    rst.FindFirst "AuthorizationNo = " & Me.AuthorizationNo & " AND AdmitDate = #" & _
    Format(Me.AdmitDate, "mm/dd/yyyy") & "#"
    If Not rst.EOF Then
    Me.Parent.Bookmark = rst.Bookmark
    End If
    End If
    End Sub

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Record in main form (A2K)

    I get an error msg: "method or data member not found" with
    <pre> rst.FindFirst "AuthorizationNo = " & Me.AuthorizationNo & " AND AdmitDate = #" & _ </pre>

    highlighted

    ....both MainForm and SubForm hold the field "AuthorizationNo"
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Record in main form (A2K)

    Select Tools | References... in the Visual Basic Editor. Do you have a reference with a ticked check box to

    a) Microsoft ActiveX Data Objects 2.n Library

    and/or

    [img]/forums/images/smilies/cool.gif[/img] Microsoft DAO 3.6 Object Library?

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Record in main form (A2K)

    I had DAO 3.1 and changed it to 3.6 but still got the same error
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Find Record in main form (A2K)

    I'm confused on one point.

    You started off by saying:
    >>I have a main form (fSNF single form view) which holds all member (INPATIENT) data and a subform (sfMemberdays
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Record in main form (A2K)

    You do need 3.6 for Access 2000 and later, so keep that. Change the code to

    Private Sub AuthorizationNo_DblClick(Cancel As Integer)
    Dim rst As DAO.Recordset
    If Not IsNull(Me.AuthorizationNo) Then
    Set rst = Me.Parent.RecordsetClone
    rst.FindFirst "AuthorizationNo = " & Me.AuthorizationNo & " AND AdmitDate = #" & _
    Format(Me.AdmitDate, "mm/dd/yyyy") & "#"
    If Not rst.NoMatch Then
    Me.Parent.Bookmark = rst.Bookmark
    End If
    End If
    End Sub

    Note that this version has RecordsetClone, not Recordset.Clone.

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Record in main form (A2K)

    Hans,

    Thanks for your help. Still got the message. I continued to debug it and came up with something else that works.

    I wanted to pass it on in case anyone was interested

    <pre>Private Sub AuthorizationNo_DblClick(Cancel As Integer)
    Dim rst As DAO.Recordset
    Dim AN, AD As String

    If IsNull(Me.AuthorizationNo) = False Then
    AN = Me.AuthorizationNo
    AD = Me.[Admit Date]

    Set rst = Me.Parent.RecordsetClone
    rst.FindFirst "AuthorizationNo = " & AN & " AND AdmitDate = #" & _
    Format(AD, "mm/dd/yyyy") & "#"
    If Not rst.NoMatch Then
    Me.Parent.Bookmark = rst.Bookmark
    End If
    End If
    End Sub
    </pre>



    I'm not sure why member couldn't be found....once I made it and Admit Date a variable, all was well.
    As always, thanks for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Record in main form (A2K)

    The problem appears to have been that the control is named Admit Date (with a space) instead of AdmitDate (without a space). There was no way for us to have guessed that. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Record in main form (A2K)

    The <font color=red>Main Form</font color=red> and <font color=blue>SubForm</font color=blue>, as I pointed out, both hold INPATIENT DATA (PRECERT Information). The <font color=blue>subform is in DATA SHEET VIEW </font color=blue> only holds small peices of data for each PRECERT (a summary, so to speak) i.e. Auth#, AdmitDate, DischargeDate, ApprovedDays and DeniedDays. Whereas, the <font color=red>Main Form, works as the INPUT form for EACH precert and would of course have the same information but much, much more and is in SINGLE FORM VIEW</font color=red>.

    When the user enters the <font color=red>Main Form, they only have SINGLE FORM VIEW</font color=red>. It's the subform that shows the related records for each precert for that member. So, if the Case Manager needs to make modifications to a case for that member, the Case Manager should be able to double click the CASE (CASE is determined by Auth# and AdmitDate) and the main form will reflect that CASE, therefore giving the Case Manager the ability the edit the other data that's on the MAIN Form, such as Facility , DX Codes, Provider, etc, that isn't on the subform.

    To better explain, I attached a screenshot in an earlier post.

    Either way, the problem was resolved at it's doing exactly as we need it to do.

    Thanks for the help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •