Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    Unhappy Unique number "event is in" the db table vs "is not in" event

    I am absolutely frustrated that I can't figure out the code to allow the user of my db to add data to another form when a unique number (MRN) is in the db table. Here goes.



    Form 1 (tblContact) is used for entering initial patient data when that patient enters the hospital the first time. The MRN is a unique number that is used to track that patient.

    Form 2 (tblReadmit) is used when a patient in the db is re-admitted.

    Except for the MRN each form has completely different data used to track different entities.

    Here's what I need to do:

    Patient 123456 (MRN) is arriving the first time. All data is entered by staff. That patient is discharged in three days. My system tracks the patient's discharge.

    Two months later patient 123456 is re-admitted. Staff starts with Form 1. First thing entered is the MRN. Since that number is already in the db I want the data entry person to be taken to Form 2 (SetFocus on MRN), to enter the re-admission data. Then Form 2 returns staff to Form 1 with a message that Form 2 has been updated.

    On vbOK Form 1 should return to a blank MRN field where the process can start all over again.

    We process about 30 patients per day of which 1 to 2% are re-admissions.

    If you have some code or other suggestions that may work please insert so I can try it.

    Thanx in advance for some help.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What you want to accomplish is not difficult. Maybe this link will give you some ideas but open the form with the acDialog argument to stop the code in the first form.

  3. #3
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    Thanx for the response

    Thanx. I'll give it a try. If it works I'll post the entire code in case someone else would need it.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Please feel free to post back here if it gives you too much trouble.

  5. #5
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    Where to apply

    I have tried to use the suggestion to no avail. I would appreciate some more detailed help as to where to apply the code. Is it applied to the "Form" parameters or to a "Field" parameter such as my MRN field. A sample code would really help since I am only slightly above the novice level in Access.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    HOw about posting whatever code you are currently trying to use to open FORM #2.

  7. #7
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77
    The code should first make sure that the number is in the table and then if it is, open form 2 (Time Data) from which the user can proceed. If it is not in the table then the user can simply complete the data on the original form. When a new patient is added the table should requery upon close as well as leave the "PatientMRN" field blank and ready to continue.

    To this end I have inserted the following code:

    Code:
    Private Sub PatientMRN_AfterUpdate()
       On Error GoTo Err_Command17_Click
       Dim Answer As Integer
       Answer = MsgBox("Do you want to readmit this MRN?", vbYesNo, "Patient MRN")
       'Make a decision based on button user clicked.
       If Answer = vbYes Then
          'Code to execute Open Readmit Form.
          DoCmd.OpenForm "Time Data", acNormal, , , acFormAdd, acWindowNormal
       Else
          'Code to execute if user clicks on No.
          If Null Then
             DoCmd.Close , "Time Data", acSaveNo
          End If
       End If
    Exit_Command17_Click:
       Exit Sub
    Err_Command17_Click:
       MsgBox Err.Description
       PatientMRN.SetFocus
       Resume Exit_Command17_Click
    End Sub
    It works for getting to the second form (Time Data) when the user selects "Yes". But, at the same time form 2 (Time Data) opens the user gets the standard message about the number already being in the system and must click "OK". As the user selects "Yes" a new record is added to the original table with no data at all.

    When "No" is selected the new number should left (in effect saying that the new number is not in the table) in the "PatientMRN" field and set focus to the next field allowing the user to complete adding data to the original form and then save the new patient data to the table.

    Thanx for the help.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Your code should be in the BEFOREUpdate event rather than the AfterUpdate event and it should look more like:
    Code:
    Private Sub PatientMRN_BeforeUpdate(Cancel As Integer)
       On Error GoTo Err_PatientMRN
       ' See if the MRN is already in the system.
       Me.RecordsetClone.FindFirst "[MRN] = '" & Me.PatientMRN & "'"
       If Not Me.RecordsetClone.NoMatch Then
          '-- It is an existing patient
          If MsgBox("Do you want to readmit this MRN?", vbYesNo, "Patient MRN") = vbYes Then
             '-- Open the Re-admit Form.
             '
             '-- BIG NOTE-!!!
             '
             '-- We're passing the MRN in the OpenArgs so the OnLoad event of the next
             '-- form will need to catch it and put it in the correct control in the Form.
             '
             DoCmd.OpenForm "[Time Data]", , , , acFormAdd, acDialog, Me.PatientMRN
             '-- Clear any changes to the current form
             Me.Undo
             '-- And hold the focus in the current control
             Cancel = True
          End If
    '   Else
    '      '-- Do nothing, this MRN is not currently in the system.
       End If
       
    Exit_PatientMRN:
       Exit Sub
       
    Err_PatientMRN:
       MsgBox "Error      : " & Err.Number & vbCrLf & _
              "Description: " & Err.Description
       Resume Exit_PatientMRN
       
    End Sub
    Are you *sure* you want to duplicate the MRN if the user answers NO to the Readmit question?

  9. #9
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    Yes No

    I haven't tried the code yet but, yes if the MRN is not in the system I need the user to be kept at the 1st form such that they can add the new MRN (Patient) data. If the MRN is in the table then all I need for the user is to add the data to table 2.

    In other words - When the MRN is input into the PatientMRN field and if the MRN is NOT in the table then the focus should go to the next data field on the 1st form. If it IS in the table then form 2 should open. When data input is finished closing form 2 results in the user being taken back to form 1 with PatientMRN as focus and blank.

    Easy aye!!!

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    From Post #7 that confused me:
    Quote Originally Posted by hawzmolly View Post
    When "No" is selected the new number should left (in effect saying that the new number is not in the table)
    What you are saying now is more like what I think it should be. If the MRN is in the system then don't even ask them a question, just go to the next form. I modified the code to just go to the next form. Read the BIG NOTE!
    Code:
    Private Sub PatientMRN_BeforeUpdate(Cancel As Integer)
       On Error GoTo Err_PatientMRN
       ' See if the MRN is already in the system.
       Me.RecordsetClone.FindFirst "[MRN] = '" & Me.PatientMRN & "'"
       If Not Me.RecordsetClone.NoMatch Then
          '-- It is an existing patient. Open the Re-admit Form.
             '
             '-- BIG NOTE-!!!
             '
             '-- We're passing the MRN in the OpenArgs so the OnLoad event of the next
             '-- form will need to catch it and put it in the correct control in the Form.
             '
             DoCmd.OpenForm "[Time Data]", , , , acFormAdd, acDialog, Me.PatientMRN
             '-- Clear any changes to the current form
             Me.Undo
             '-- And hold the focus in the current control
             Cancel = True
    '   Else
          '-- Do nothing, this MRN is not currently in the system.
          '-- Just let the focus proceed to the next control.
       End If
     
    Exit_PatientMRN:
       Exit Sub
     
    Err_PatientMRN:
       MsgBox "Error      : " & Err.Number & vbCrLf & _
              "Description: " & Err.Description
       Resume Exit_PatientMRN
     
    End Sub

  11. #11
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    Big Note

    I am assuming "Big Note is "

    '-- BIG NOTE-!!!
    '
    '-- We're passing the MRN in the OpenArgs so the OnLoad event of the next
    '-- form will need to catch it and put it in the correct control in the Form.

    Thanx for the help.

    Finally found and bought a book yesterday - "MS Office Access 2003 Bible" by Prague,Irwin, and Reardon - Seems to be very comprehensive and will help me learn more about Access. Up till 1:30am last night reading - seems as though my db is pretty much on target.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Your assumption on the Big Note is correct and the [Time Data] form will need to look at the OpenArgs for the MRN to use.

  13. #13
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77
    I have tried the code as shown below. Problem is the only thing it does is kick in the following Access message which means that the number is in the system. The PatientMRN is a text field with "No Duplicates" allowed which is the reason the reason the message kicks in when I tab or click on another field. There is a defugality somewhere.

    "The changes you requested to the table were not accepted because they would create duplicate values....."

    The code:

    Private Sub PatientMRN_BeforeUpdate(Cancel As Integer)
    On Error GoTo Err_PatientMRN
    ' See if the MRN is in the system.
    Me.RecordsetClone.FindFirst "[PatientMRN] = '" & Me.PatientMRN & "'"
    If Not Me.RecordsetClone.NoMatch Then
    '-- It is an existing patient. Open the Re-admit Form.
    '
    '-- BIG NOTE-!!!
    '
    '-- We're passing the MRN in the OpenArgs so the OnLoad event of the next
    '-- form will need to catch it and put it in the correct control in the form.
    '
    DoCmd.OpenForm "[frmTimeData]", , , , acFormAdd, acDialog, Me.PatientMRN
    '-- Clear any changes to the current form
    Me.Undo
    '-- And hold the focus in the current control
    Cancel = True
    Else
    '-- Do nothing, this MRN is not currently in the system.
    '-- Just let the focus proceed to the next control.
    End If

    Exit_PatientMRN:
    Exit Sub

    Err_PatientMRN:
    MsgBox "Error : " & Err.Number & vbCrLf & "Description: " & Err.Description
    Resume Exit_PatientMRN

    End Sub

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Does it try to open the frmTimeData form?

  15. #15
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    No

    It either open the message or takes focus on the next field while leaving the MRN filed blank. If it goes to the next field it does save the new number.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. aSTR = Dir("C:\*.*") >> "Type Mismatch"
    By JGrant in forum Programming
    Replies: 1
    Last Post: 03-28-2009, 05:17 AM
  2. Replies: 0
    Last Post: 09-25-2008, 12:19 PM
  3. Export to .rtf of "104-" converts to "-655&qu
    By Sherri726 in forum Import/Export Data
    Replies: 0
    Last Post: 12-19-2006, 03:16 PM
  4. Replies: 2
    Last Post: 08-31-2006, 12:19 PM
  5. "Count" and "Countif" in Reports
    By JMantei in forum Reports
    Replies: 1
    Last Post: 06-20-2006, 02:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums