Results 1 to 12 of 12
  1. #1
    hithere is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    63

    Find record

    I have a patient form that is controlled by a parameter query. The user must enter the ID of the patient to display that patient’s particular file.

    Once the patient form is opened to a particular entry there is a “Edit Patient Referral” button. Once the button is clicked I would like the patient referral information form to be opened to that patient’s referral information (which is controlled by the patient referral info query). If a referral does not exists for the patient I would like a message box to state that a referral does not exist.

    The two forms are linked by the customer ID and each patient referral has its own referral ID. I know access provides a button to open a form to a selected record by criteria but it does not account for records that do not exist.



    I have not done much programming or coding so I’m not sure how exactly to do this. I was thinking of starting with a lookup to see if a record exists by the referral ID in the patient referral info query but am uncertain how to execute this.

    Any insight would be great!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How is the ID input? Is it by query input parameter prompt or is input to a form and query refers to form?

    I never use input prompt because can't validate the entry and can't refer to the entry in code. I have user input criteria on a form. This allows validation control and the value is available for further processing in code. I also use only VBA, no macros.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    hithere is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    63
    The patient ID is input by a input parameter prompt. This is my way of narrowing down this list because it is very extensive.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That method is subject to the restrictions I stated.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    hithere is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    63
    Is there any way I can kind of get around this? I've uploaded a copy of the database so you can see what I am talking about. It's ok if the the the user doesn't go through the filter. I'm just not sure how to direct the user through the apropriate forms and records.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    'Get around' will mean having user input values to a form. Then the query parameters would be references to the form controls. Alternatively, use WHERE argument of DoCmd.OpenForm (or OpenReport) to grab the form inputs and pass filter criteria to form/report.


    Check out this tutorial http://www.datapigtechnologies.com/f...tomfilter.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    hithere is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    63
    I mades the changes sugguested in the tutorial and this filters nicely. Thanks.

    I'm not quite sure what you mean by using the WHERE in DoCmd argument because I haven't ventured much into coding. Essentially what I would like to have happen is to open the frmPReferral form to the selected patient if a record exists and close the frmFindP form or show a message that a record does not exist.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What code is opening the form - a macro? I don't use macros but I think macro can perform the equivalent of WHERE argument:

    DoCmd.OpenForm "formname", , , "fieldname=" & Me.formcontrol
    or
    DoCmd.OpenForm "formname", , , "fieldname=" & InputBox("Enter Customer ID")

    Is the customer ID a number or text value?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    hithere is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    63
    It is a text field. I would like to stick with code I suppose to allow for the capability of a messgae box if records are non existent.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you use the VBA code I show then the criteria in the query would be removed. If you use a combobox that limits user choices as parameter input, then you can offer them only choices that have records.

    Text field criteria values must be delimited with apostrophe (dates would be #).
    DoCmd.OpenForm "formname", , , "fieldname='" & Me.formcontrol & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    hithere is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    63
    How could I limt the user choices, assuming this will possibly utilize a filter?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, use a combobox with a RowSource that is a filtered query. This SQL might require joining tables. I don't know your data structure so can't be specific. Set the combobox LimitToList property to Yes.

    Check out the tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 about comboboxes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA: Find Record from in table from form
    By shimmy84 in forum Programming
    Replies: 6
    Last Post: 03-19-2012, 10:51 PM
  2. best way to find a record in a linked table
    By BRV in forum Programming
    Replies: 14
    Last Post: 11-10-2011, 09:31 AM
  3. Find Record using Query
    By tomself1 in forum Programming
    Replies: 3
    Last Post: 04-18-2011, 09:34 AM
  4. Find Record button error
    By Zedlexx in forum Forms
    Replies: 0
    Last Post: 03-11-2009, 09:26 AM
  5. How to Find a Record
    By lschlitt in forum Programming
    Replies: 2
    Last Post: 09-09-2006, 06:24 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