Results 1 to 8 of 8
  1. #1
    Casper2012 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    18

    Can anyone help please... I am stuck

    Hello...



    I have started a very basic database that is beginning to get larger and larger. I need to be able to search by my customers phone numbers and when the result from the search pulls up, I need to be able to "click" on the result and open that field so I can edit or put information in the field.

    See, when customers call in I need to be able to quickly search for their phone number, provided they have already called in, and pull the results up. The problem is, that some people have 2-4 numbers that pulls up also. I need to be able to click on their results and then open the form to type info in the record while i have them on the phone....

    I have looked at a few examples, but none have been able to work for me.

    I am really beating my head up against the wall......

    Could anyone PLEASE...... point me in the right dirrection.

    I have enclosed a smaller smaple of the databse that I am working. It is not the actual one, because it is way to big to post on here....


    Help Please!...

  2. #2
    Casper2012 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    18

    Applying a filter to a form

    I have a form that I would like to apply a simple filter to. I need to filter data by phone numbers. My question is, when I put a button on my form by selecting Form Operations, and then Apply Form Filter.... what would be the steps to filter the form using this command button , or can this even be done.

    Or would it be better to run a Macro or query on the form....??

    Just need some direction,

    thanks for any help!

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    This button does nothing more than the Toggle Filter button available on the ribbon. You have to first define the filter criteria. This can be preset in the Filter property for the form. However, using the filter tools from the ribbon or the shortcut (right click) menu will modify this property while the form is open. The changed filter might even save when the form closes.

    If you want to offer users search and filter capability other than the intrinsic Access tools, will require code. I 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.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You should not post projects that have confidential data such as real names, phone numbers, and emails. I suggest you remove/replace the file.

    Confused by your data. The contacts table has duplicate records. Why are there 4 identical records for Brandon Mills, 3 for Al Preaam?
    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
    Casper2012 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    18
    All these entries on the form are all made up. There are no real name or numbers being used.

    I created the duplicates to practice a code on. See, in my data (the real one) I have a customer that may have 3 different phone numbers. So when they call in, I need to be ale to search the db by phone number and show the resuts. However, than I need to be able to click that person and have them open up in my form. I have asked this question before and I have yet to find anyone that can give me a straight answer on how to get the results I am really looking for.

    I can do a parameter query and have them enter the phone number to search by, and it give me the exact results I need.. PERFECT! but.......... then i have no way of opening that information (from the query) into my form... UHGGGG!

    This has been so disappointing

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Okay, it's just that was a lot of data, didn't expect such realism in creating dummy records.

    I am not yet fully understanding what you need to do. A query cannot open into form. A form can have the parameterized query as its RecordSource. Open the form, parameter prompt gets value from user and the form opens to the set of records matching the input.

    I don't use parameter prompts. It is nearly impossible to control entry validation.

    The Contact Details form already has search facility built into it. It lists contacts by name. If you have contact on line, you have name and can see all their existing phone numbers.

    If the form you want to search through is Contact List, then build a phone number search box on that form that will either go to the desired record on that form or open the Contact Details form filtered to the desired record.
    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
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Keeping 3 or 4 records maintained because someone may have more than 1 phone number can be cumbersome. It would be better to setup the table properly with a 1 to many relational table where the relational table has the multiple phone numbers.

    After that is done, then it's a matter of creating a search type form to allow the user to enter any phone number and it then retrieves the 1 (and only 1) record from the main table which has the customer's main information.

    I would focus first on getting the tables/relationships setup correctly and then focus on designing a search type form for the phone number.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Good point pk, I should have noted that.

    pk is right, the Contacts table is not normalized and is duplicating data.

    Normalized tables would be:
    client info
    client phones
    call info (client ID would be foreign key in this table)
    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. Please assist on this task. I'm stuck.
    By Playerpawn in forum Access
    Replies: 3
    Last Post: 05-17-2011, 01:31 PM
  2. Help I am stuck
    By Darkglasses in forum Database Design
    Replies: 10
    Last Post: 03-04-2011, 09:10 PM
  3. Stuck on Query
    By wes028 in forum Access
    Replies: 9
    Last Post: 01-14-2010, 08:33 AM
  4. Newbie Here & Stuck
    By FOZILD in forum Access
    Replies: 5
    Last Post: 09-24-2009, 08:26 AM
  5. Newb stuck on Normalization
    By dave_wilford in forum Database Design
    Replies: 6
    Last Post: 05-19-2008, 02:39 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