Results 1 to 13 of 13
  1. #1
    williamposey is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    16

    My Search Form seems to hang up my search request

    My Search Form seems to hang up my search request.

    I am creating a simple database that organizes a small number(maybe up to 500) of standardized patients with their medical history.
    I want to create a search form that allows a user to select as many search variables as they wish and have that linked to a query. This task is normally pretty simple, but I am having difficulties this time.

    When I use the form, sometimes it just hangs up for no reason and provides no search results. I know my criteria is correct, because if I use the query without the form, I fill in the parameters and get the results I want. But when I use the form, it will sometimes send back no results. I say “sometimes” because it doesn’t seem to have a pattern of the when’s and whys it wont work.

    I will need to search for more then 40 variables when looking up a patient so the form will make it easier then answering questions on the parameter window that opens up when I do a query.

    Anyway, Database is attached. All names and contact info has been removed to ensure privacy is not breached.

    Any help would be appreciated.
    Sample for Forum.zip



    PS, I know I should use separate tables for duplicate data, but due to importing issues I need to keep this database flat with no relationships.

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    You will always have problems when you have a Flat File.

    I would recommend you bite the bullet and normalise the table.

  3. #3
    williamposey is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    16
    So you think creating separate tables for demographic information and yes/no data will solve the problem? I am clearly intermediate level user but I thought a single table would make searches far easier.
    Cant figure out why the form search fails...

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi William

    The problem with your database is that you have numerous Yes/No fields, with the majority noit applicable in most cases for an Individual.

    I have attached an example of using the Search Form to produce another Form the the results.

    This however is not very practical and would still advise normalising the table.
    Attached Files Attached Files

  5. #5
    williamposey is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    16
    Thank you for your efforts. However, generating an image of a form after submitting a search query would not be useful to me. I need to use a form that will establish search parameters and then create a query not a report. the =[Forms]![Form1]![Anxiety] criteria should work seamlessly and still allow a field to be searched when a selection is not made in the form...

    Not sure why it doesn't work in this application

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi William

    The Form that the Search produces is not an Image it is an actual form that can be edited?

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Since your embedded macro for clear search is not working, you might have unanticipated spaces or null strings in your search boxes when you clear them manually.
    Try this VBA to clear the search data:
    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub Clear_Search_Click()
        Me.Gender = Null
        Me.Asthma = Null
        Me.Blood_Disease = Null
        Me.Anxiety = Null
        Me.Arthritis = Null
        Me.Race = Null
    End Sub

  8. #8
    williamposey is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    16
    Thank you Davegri. I will try that.. .Any ideas why the search function wont work regularly... Is there something wrong with my criteria? Seems simple but has been a real pain to solve. Thanks again.

  9. #9
    williamposey is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    16
    Fair enough.. But i need a query table... Not a form. Thanks for your efforts.

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by williamposey View Post
    Thank you Davegri. I will try that.. .Any ideas why the search function wont work regularly... Is there something wrong with my criteria? Seems simple but has been a real pain to solve. Thanks again.
    I made the suggestion as a means to possibly solve the problem. If you have unexpected values in your search boxes, then you would get unexpected results.

  11. #11
    williamposey is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    16
    Thanks Davgri.. I got my search to work now... I spent a alot time messing around and then just created the fields unbound and seems to work well...

    However Now i am struggling with the clear/ undo Button. I only use built in macros and the undo doesn't work. I assume I just copy the code you sent. then create a command button. then Build event, code builder and then paste it right in?

    I tried that but The button doesnt seem to do anything... Im a newby to VBS and Trying i understand why a simple " clear field" command wont work

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Instead of VB you can use this embedded macro:
    Click image for larger version. 

Name:	macro.png 
Views:	19 
Size:	21.6 KB 
ID:	41002

  13. #13
    williamposey is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    16
    Thanks.. I will try that next time... I ended up using

    Me.Gender = ""

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

Similar Threads

  1. Replies: 3
    Last Post: 09-12-2016, 11:49 AM
  2. Replies: 8
    Last Post: 09-02-2015, 03:00 PM
  3. Replies: 16
    Last Post: 05-04-2014, 09:19 AM
  4. Replies: 3
    Last Post: 09-02-2013, 04:33 PM
  5. Replies: 7
    Last Post: 08-08-2012, 03:28 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