Results 1 to 15 of 15
  1. #1
    rlbroerman
    Guest

    find record (2003)

    Help,
    I want to find a record by soc sec no. I saw something similar and tried to duplicate part of it but it doesn't work. Can you help, please.See attached.
    Rick Broerman
    Attached Files Attached Files

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

    Re: find record (2003)

    I don't see any trace of what you attempted.

    The easiest way is to use the combo box wizard:
    - Make sure that the Control Wizards button on the Toolbox (the one with the magic wand and sparks) is "on" or "down".
    - Place a combo box on the form.
    - Select the third (and last) option "Find a record on my form ...", then click Next.
    - Follow the instructions.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: find record (2003)

    I assume you are referring to the query Patients Query.

    I think the problem lies with the Input Mask you have on the soc sec no.

    The Literal characters (-) are not being stored in the Soc Sec No, so when you search it finds nothing.

    Change the mask to 999-99-9999;0;_ , then re-enter the existing soc sec numbers. the 0 after the ; tells Access to store the literal.
    Regards
    John



  4. #4
    rlbroerman
    Guest

    Re: find record (2003)

    I tried what you suggested but it doesn't seem to work. Ultimately I want to get the most recent visit for a particular Soc Sec No. The sample I was looking at put a "1" in the "Top Value" property in the Query Properties. I can't find Query Properties with " Top Value" in it. Do I need a newer version of access to get this "Top Property".
    Rick Broerman
    Attached Files Attached Files

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

    Re: find record (2003)

    Open the query in design view.
    Click in an empty part of the upper half of the query design window.
    Activate the Properties window (if you don't see it, select View | Properties).
    Top Values is the fourth property.
    You can select only specific values from the dropdown list, but you can type any value you want into the box.

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

    Re: find record (2003)

    BTW, the SSN values in the Patients and Visits tables don't match, as John already noted. When you have corrected this, turn on Referential Integrity for the relationship between the two tables. You won't be able to create orphan visit records from then on.

  7. #7
    rlbroerman
    Guest

    Re: find record (2003)

    Thanks, It works great but I need to know how to put an input mask into a query criteria so that I can only enter a Soc Sec No with the correct number of digits and dashes. See attached.
    Thanks again for the help.
    Rick Broerman
    Attached Files Attached Files

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

    Re: find record (2003)

    You cannot specify an input mask for a parameter in a query.
    You'll have to create a search form and place a text box on the form with an input mask. You can then use the text box as parameter in the query, i.e. replace [Enter SSN] with [Forms]![Name_of_Form]![Name_of_Textbox]. Or, even better, place a combo box on the form that lists all social security numbers in the table, so that the user can select from the list.

  9. #9
    rlbroerman
    Guest

    Re: find record (2003)

    I tried this but can't get it to work, can you help, please.

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: find record (2003)

    Have a look at the attachment.

    I had to remove the format property from Soc Security Number in Patients. Not sure what it was doing.

    Next step would be to display the results of Visits Query in a Form rather than directly.
    Attached Files Attached Files
    Regards
    John



  11. #11
    rlbroerman
    Guest

    Re: find record (2003)

    My mistake . your first suggestion is what I need. The second could contain thousands of SSN,s and would be difficult to use.
    Thanks again.

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: find record (2003)

    You can change the ComboBox to a textbox by going into Design View, right click on the combobox and choose "change to"..."Text box" from the popup menu.
    Then you can add the mask to the text box.

    But even with thousands of people I think I would still use the combo. Do you actually know what SSN to type in? Would it be easier to search by name, with the SSN displayed to confirm that it is the right one? There are quite a few variations possible around this.
    Regards
    John



  13. #13
    rlbroerman
    Guest

    Re: find record (2003)

    How do I display the results in my "Visits" form, as you suggest?
    Thanks again.

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

    Re: find record (2003)

    You can set the record source of the form to the search query, and requery the form in the After Update event of the text box (or combo box) in which the SSN is entered:

    Private Sub NameOfTextbox_AfterUpdate()
    Me.Requery
    End Sub

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: find record (2003)

    Here is a demo that does what Hans described.

    Unless there is something unusual going on, the Visits table should not contain the names of the Patient. The names are in the Patient table and are retrieved from there via the SSN number.
    Attached Files Attached Files
    Regards
    John



Posting Permissions

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