Results 1 to 12 of 12
  1. #1
    officialalexlee is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2018
    Posts
    5

    Search form using Combo Box

    Hello everyone,

    I am trying to create a form exactly like this video.

    It will be a dropdown combo box that searches a table and shows the results.

    Here is the link to the video. You can see what hes doing and get all his code at around 14:00
    https://www.youtube.com/watch?v=8N1hRBdsI1s

    Here is the code that I have attached to the combo box,

    Option Compare Database


    Private Sub cboProd1_Name_AfterUpdate()
    Dim myCustomer As String
    myCustomer = "Select * from rawdata where ([Prod1_Name] = '" & Me.cboProd1_Name & "')"
    Me.rawdata_subform.Form.RecordSource = myCustomer
    Me.rawdata_subform.Form.Requery
    End Sub


    Any help would be greatly appreciated!!

    Thank you.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does the code throw an error? If do what is it? A general comment:

    http://www.baldyweb.com/OptionExplicit.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    officialalexlee is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2018
    Posts
    5
    Error 3021. "Reserved Error"

    Also, whenever I try to hit a name from the list. A pop up appears that says "Enter Parameter Value" with Prod1_Name and a text box to enter a value into.

    I can attach more screenshots if needed.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The parameter prompt is Access telling you it can't find something. Double check the spelling of both the field and the control.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    You have a table named "rawdata" and also a form named the same?
    Suggest you change table name to tblRawData and the form name to frmRawData. Ambiguity stinks.
    Your select statement should be selecting from the tblRawData and Me.cboProd1_Name has to be the combobox control name on the form.
    Prod1_Name must be a text field in tblRawData.

    Code:
    Option Compare Database
    Option Explicit
    Private Sub cboProd1_Name_AfterUpdate()
    Dim myCustomer As String
    myCustomer = "Select * from tblRawData where ([Prod1_Name] = '" & Me.cboProd1_Name & "')"
    Me!rawdata_subform.Form.RecordSource = myCustomer
    Me!rawdata_subform.Form.Requery
    End Sub
    Also, this looks like a mainform/subform situation. If so, you don't need to set the subform recordsource on the fly.
    The lookup combobox finds the Prod1_name record on the main form and the subform automatically shows any child records.
    This assumes that tblRawData is defined in a one-to-many relationship with the many-side table defined as the recordsource of the subform.
    If you only have one table, not a one-to-many setup, then you don't need the subform at all. Just show the found record fields on the main form.
    Last edited by davegri; 05-25-2018 at 10:58 AM. Reason: more, syntax

  6. #6
    officialalexlee is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2018
    Posts
    5
    I changed my code to the changes you have listed but it still didn't fully fix the problem. It will run with no problems now but after selecting a name from the combo box, it returns with no values.

    Also, there are two different methods I tried that I thought might in some way fix the problem and I would like your recommendation if possible on which way to do it.

    1) linking combo box to get values from the Prod1_Name column in tblRawData. (with this method there are many duplicates and even if I hit one of the values in the combo box it does not return anything)

    2) linking combo box to get values from separate table tblProd1_Name that has all the unique Prod1_Name values. This is how he does it in the Youtube video originally but also, this method returns no values either.

    I am also not sure what the subform/main form situation is. I am extremely new to access so I apologize for not fully understanding what you are saying and sounding like an idiot probably. Though, It seems like the subform is an important part in outputting the data, if there wasn't a subform wouldn't it just sort the data in the tblRawData itself? I want to eventually make it so it turns the data into a report and it seems like the subform is an important part of that maybe.

    I have put hours into figuring this out and this really frustrates me, any help would be greatly appreciated.

    Again sorry for probably not knowing what im talking about.

    Thanks for your time.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Can you post a copy of your database here? We need to see your table and form structure to get on the same track for a quick recommendation.

  8. #8
    officialalexlee is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2018
    Posts
    5
    Here you are.

    Thanks
    Attached Files Attached Files

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, the subform shouldn't be in the detail section. The form should probably not have a record source. The master/child links should be blank. Then this works:

    Dim myCustomer As String
    myCustomer = "Select * from tblRawData where ([Prod1_Name] = '" & Me.cboProd1_Name & "')"
    Me.tblRawData_subform.Form.RecordSource = myCustomer
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    officialalexlee is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2018
    Posts
    5
    Where should the subform be?

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I tested in the form header. It would only belong in the detail section if the contents were related to each record in the main form's detail. In this case, they stand alone from that. The main form is only used to select a filter value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Take a look at this:
    Database-davegri-v01.zip
    I did away with the subform and the main form is in split view with a filter for the prod_name.

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

Similar Threads

  1. Replies: 5
    Last Post: 09-22-2016, 08:42 AM
  2. Search Combo Box on Form
    By Rhubie in forum Forms
    Replies: 15
    Last Post: 03-15-2013, 02:40 PM
  3. Replies: 7
    Last Post: 08-08-2012, 03:28 PM
  4. Combo Box Search Form
    By timbo in forum Forms
    Replies: 2
    Last Post: 04-25-2012, 02:56 PM
  5. Replies: 1
    Last Post: 04-20-2012, 03:16 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