Results 1 to 9 of 9
  1. #1
    trident is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    141

    Search box not working

    I created a combo box to search by BIN Location, i followed the same from a youtube video.

    the form name is InvntrySrch_cbo. below is the code entered in the dropdown box.



    Private Sub cboBin_AfterUpdate()Dim myBinLocation As String
    myBinLocation = "Select * from Inventory4Location where ([BIN Location]=" & Me.cboBin & ")"
    Me.InvntrySrch_cbo.Form.RecordSource = myBinLocation
    Me.InvntrySrch_cbo.Form.Requery
    End Sub

    Attached is sample of the DB.

    thanking in advance to assistance given.

    Trident
    Attached Files Attached Files

  2. #2
    trident is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    141
    on the third line

    Me.InvntrySrch_cbo.Form.RecordSource = myBinLocation

    the error is the INVNTRY_CBO.FORM

    I replaced the name, created a new subform, started from scratch, and the same error is showing.

  3. #3
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    trident-Inventory-v02.zip
    Here you go. I used the Wizard to create the search.

  4. #4
    trident is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    141
    Quote Originally Posted by davegri View Post
    trident-Inventory-v02.zip
    Here you go. I used the Wizard to create the search.
    Dear Davegri,

    Thank you for taking time to look on the problem.

    However, the error still lingers, it is showing the 3rd line.

    Private Sub cboBin_AfterUpdate()Dim myBinLocation As String
    myBinLocation = "Select * from Inventory4Location where ([BIN Location]=" & Me.cboBin & ")"
    Me.InvntrySrch_cbo.Form.RecordSource = myBinLocation
    Me.InvntrySrch_cbo.Form.Requery
    End Sub

    which I have highlighted is the error.

    Hope you can expound on this and a little bit of explanation.

    Thank you.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The line of code you use to change the sub form record source is
    Code:
    myBinLocation = "Select * from Inventory4Location where ([BIN Location]=" & Me.cboBin & ")"
    The main problem is that the bound column of combo box "cboBin" is "ID" (a number) and the field "BIN Location" in the query for the sub form is text.

    BTW, having "ID" as the name of the PK field for every table leaves a lot to be desired.

    So you need to add "Inventory4Location.ID" to the query.



    Changing the sub form record source is (IMHO) a PITA.
    I would just set the filter property of the sub form.
    Attached Files Attached Files

  6. #6
    trident is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    141
    Quote Originally Posted by ssanfu View Post
    The line of code you use to change the sub form record source is
    Code:
    myBinLocation = "Select * from Inventory4Location where ([BIN Location]=" & Me.cboBin & ")"
    The main problem is that the bound column of combo box "cboBin" is "ID" (a number) and the field "BIN Location" in the query for the sub form is text.

    BTW, having "ID" as the name of the PK field for every table leaves a lot to be desired.

    So you need to add "Inventory4Location.ID" to the query.



    Changing the sub form record source is (IMHO) a PITA.
    I would just set the filter property of the sub form.
    Dear ssanfu,

    Thank you for the explanation and solution.

    You are correct that the dropdown cboBIN includes the [ID] which is a number and that the BIN LOCATION is a text.

    However, if I have created the drowdown cboBIN as only the description and not including the [ID], will the earlier code work?

    Trident

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Probably. You would have to add the text delimiters and the reference to the sub form from the main form must be correct. Try it. (on a COPY of your dB)

    Much easier to add the ID field and set the filter as I demonstrated.....


    Good luck with your project.

  8. #8
    trident is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    141
    Quote Originally Posted by ssanfu View Post
    Probably. You would have to add the text delimiters and the reference to the sub form from the main form must be correct. Try it. (on a COPY of your dB)

    Much easier to add the ID field and set the filter as I demonstrated.....


    Good luck with your project.
    Dear ssanfu,

    I tried the same with Item No, it works no errors but it does not list any data.

    cboBIN = ID (Number), BIN (Text) - this works

    when I created cboITEMNO, it is showing no data, without any error. it has the same information

    cboITEMNO = ID (Number), ITEMNO (Text)

    how come i dont get the data i entered.

    Trident

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Option Explicit requires that all variables be declared via a DIM statement before being used in code. If an undeclared variable is referenced, an error will occur at compile time.
    It protects against misspelled variable names.

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

Similar Threads

  1. VBA Search code not working
    By rmrha21 in forum Programming
    Replies: 3
    Last Post: 12-02-2014, 10:59 AM
  2. Search not working in my form
    By scorpion99 in forum Forms
    Replies: 3
    Last Post: 11-06-2014, 01:37 PM
  3. Search box not working
    By banker in forum Forms
    Replies: 2
    Last Post: 08-16-2012, 07:59 PM
  4. Combo Box search not working
    By Lowell in forum Forms
    Replies: 1
    Last Post: 06-02-2012, 12:53 AM
  5. Search field is not working correctly
    By jakeao in forum Programming
    Replies: 9
    Last Post: 05-18-2009, 07:47 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