Page 1 of 4 1234 LastLast
Results 1 to 15 of 52
  1. #1
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82

    Search Function on form doesn't return any data from table even thought the info in on the table

    I have created a search function on a form in my database. I am trying to have a user enter a identification number for a table and be able to pull up the record.



    The search function only returns a result on certain records that are in the table.

    For example both wireNumber AES1234 and CTL1984 are both on my wireInfo Table and all fields on the table are populated for each record.


    Click image for larger version. 

Name:	AES1234 record on wireInfo Table.png 
Views:	26 
Size:	50.7 KB 
ID:	48157


    When I try to search for CTL1984 on my wireCategoryQuery_v3 form, I have no issues getting the record for CTL1984 to pop up.

    Click image for larger version. 

Name:	CTL1984 Search.png 
Views:	26 
Size:	30.3 KB 
ID:	48158

    However when a search for AES1234 doesn't return anything.
    Click image for larger version. 

Name:	AES1234 Search .png 
Views:	26 
Size:	29.2 KB 
ID:	48159


    The code that I am using to preform the search is

    [wireNumber] Like "*" & [Forms]![wireCategoryQuery_v3]![formSearch]


    Now at one point I was able to search for AES1234 and it would pull up a record. I think that at that point I had a query that was named wireCategoryQuery_V3 and then I renamed the query wireCatergoyQuery and I was no longer able to search for AES1234 but I could search for any wireNumber that was added after I renamed the query.

    Do I need to rebuild my query to be able to search the full table or could I simply change the name back?
    Attached Thumbnails Attached Thumbnails AES1234 record on wireInfo Table.png  

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Post a copy of the database. zip format

    If you make a simple query in the query designer, can you find AES1234? (no form involved)

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,857
    No. You could try changing the name back, just as a test, but you changed something else in doing so.
    Show your code (within code tags) and perhaps a cooy of yiur db, with instructions on how to replicate.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Yes, I can create a simple query and find AES1234 without using a form. I have also attached a zip copy of my database
    Attached Files Attached Files

  5. #5
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    The search button on the form is an apply filter macro with
    Code:
    [wireNumber] Like "*" & [Forms]![wireCategoryQuery_v3]![formSearch] 
    as the where argument of the macro

  6. #6
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Here is a zip copy of my database



    Tables and Reationships Test for Wire Database.zip

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How to repeat the issue? Instructions please.
    Most do not use macros.

    Click image for larger version. 

Name:	WireError.png 
Views:	26 
Size:	32.9 KB 
ID:	48162

  8. #8
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Just enter AES1234 into the search box. If you select an option from a drop down menu, you get error messages about the other fields being empty.

    Unfortunately, I am using macros because I can't figure out the VBA code. So far I haven't been able to get the VBA code that I have seen in tutorials online to work and the only way I have been able to do this is by a macro.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,857
    Your query is not producing data for AES1234, but is for AES12345.?

    You need to test your logic against the data being queried, not the table?

    You would need to add another * at the end, or better still, only have one at the end.?

    Anyway, get used to checking your data in steps. Do not put it all together and just hope it works.
    Build it in stages, as you would a house.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    But I can't get the search function to return anything for about half the records in the table. I was just using AES1234 and CTL1984 as an example.

    So it is not an issue of having a * at the end or not. I tried multiple searches.

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,857
    Put the form control as criteria in your query and see what it produces.
    If you want to search for data, then it needs to be in the data source.
    If you try aes12345 you will get a record, as THAT data is in your query.
    No data to match, nothing will be found.

    Run your query with no criteria, it only returns about half the records.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you tell us what you are trying to search for and under what conditions? Perhaps we can help you learn some vba.

    Remember we know nothing about your database, what it is/will be used for.... so all the detail you can provide will help with communications.

    @paul,

    Why can't he find AES1234???? That value is in the table, right?

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,857
    @orange,
    Yes, but form is based on a query and that record is not in it, but the ctl one is, so access is only doing what it is supposed to do. The query only returns about half the records in the table.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  15. #15
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    I am a broadcast engineer and I am working on move our cable documentation from a excel file to an access database.

    We have 17 categories of wires and the categories are based on what type of wire we are installing. We each wire is assigned a alpha numeric tag based on the wire category followed by a 4 to 5. For example if we are running a cable for standard definition video, we would label the cable SDV0001. If we are running a cable for high definition, the cable would be labeled HDV0001; a cat6 cable would be labeled NET0001. The wire number is our primary way of identifying a cable and I have to keep the current format for the wire numbers. My chief is very persnickety about the wire number, the fact that I convinced him to even try to use access for tracking our cables is a small miracle.

    In addition to the wire numbers, we also document the source rack and piece of equipment where the wire starts and the destination rack and equipment where the wire ends.

    I set up tables for the wire categories, source rack and destination racks in their own tables because we don't add equipment racks or new categories of cable that often. Since there are times where we might be waiting on a piece of equipment to be delivered or if we remove a piece of equipment we might have a wire sitting in a rack that is no connected to anything my chief wanted my to add a field to track if a cable is waiting to be connected, not connected or if it needs to be removed. My chief also wants to start tracking what day a cable was ran, who ran the cable and if we need to update an AutoCad drawing or labels on a patch panel. We also write a short description of the destination and source equipment.

    So ideally the way the database would work is that after I was finished running a cable I'd open the database and add all of the information about the cable that was just ran and add it to the database.

    Then when we needed to update a record because we swapped out equipment or the location of the equipment is changing, we could search the database for a particular wire number and update that record with the appropriate information.
    And when equipment is decommissioned and the wires are removed we would search the database and delete the records of the cables that we removed.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-05-2015, 11:11 PM
  2. Form doesn't add data to table
    By wardw in forum Forms
    Replies: 4
    Last Post: 05-22-2013, 03:29 PM
  3. Replies: 6
    Last Post: 10-25-2012, 04:13 AM
  4. Replies: 9
    Last Post: 02-15-2011, 03:05 PM
  5. Replies: 3
    Last Post: 01-31-2011, 11:47 AM

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