Results 1 to 12 of 12
  1. #1
    JulieMarie is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    12

    Combo box with a row source that can search two different fields

    I have a combo box on my enter new contract data form. When the user goes to the form they search by a vendor name to popoluate the vendor name and vendor number into the data entry form. I would like to have the ability to search by vendor name and vendor number. However, it seems I can't do both? They are fields in the same table. Is this a possibility or not?



    SELECT Vendor.[Vendor Name], Vendor.[Vendor No] FROM Vendor WHERE (((Vendor.[Vendor Name])<>Nz(Form![Vendor Name],1)));

    Vendor name is column 1 and vendor number is column 2.

  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,043
    How about

    SELECT Vendor.[Vendor Name], Vendor.[Vendor No]
    FROM Vendor
    WHERE Vendor.[Vendor Name]<>Nz(Form![Vendor Name],1)
    UNION ALL
    SELECT Vendor.[Vendor No], Vendor.[Vendor No]
    FROM Vendor
    WHERE Vendor.[Vendor Name]<>Nz(Form![Vendor Name],1)

    Which would leave the number in the second (presumably bound) column.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JulieMarie is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    12
    Thanks Paul! It now searches by both but now my form has a slight hiccup.

    In my after event I have the following:

    Private Sub Go_To_AfterUpdate()
    Me![Vendor Name] = Go_To.Column(0)
    Me![Vendor No] = Go_To.Column(1)
    End Sub

    This works when I search by vendor name. When I search by vendor number it puts the vendor number in both the vendor name and number fields.
    Attached Files Attached Files

  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,043
    I was assuming that the number field was bound, but either way you can add a third column for the name. That way the number is always in the second column, the name in the third.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    JulieMarie is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    12
    I think I've really messed something up here. My contract table is getting updated the way I want it to with my combo box. But my vendor table is adding a duplicate vendor name in its table. I'm new to this and I can't figure out for the life of my how to make my vendor table stop getting duplicate data. Did I do this all wrong? I have a vendor table for vendor name, ID and address and the contract table has other information with the vendor name and ID being the same. Is it not possible to link two tables and only update one of them?
    Attached Files Attached Files

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,043
    Which form/combo?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    JulieMarie is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    12
    Form - New Contract Data
    Combo Box - You can choose between one or the other combo box to complete the form. Both combo boxes update the Vendor table. Only the Contract table should be updated.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,043
    The form is bound to a query that includes both tables. I would have it bound to the contract table only. I'd have a combo box to select vendors that got its selections from the vendor table (row source) but was bound to the vendor number table in the contract table (control source).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    JulieMarie is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    12
    Thank you! Thank you! Thank you!

    So now that I have unbound the vendor table from my form I had to remove my vendor name field from the form because it is not bound. So the vendor name now only appears in the drop down combo boxes when they search by vendor name or vendor number. Would it be worth trying to make a false box to show the vendor name while they enter the data or best to leave well enough alone. Thanks again!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,043
    I'd use the first method here:

    BaldyWeb - Autofill
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    JulieMarie is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    12
    Thank you so much pbaldy! I really appreciate it!

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,043
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Search as you type Row Source Alteration
    By michael.legge@rbc.com in forum Access
    Replies: 1
    Last Post: 07-03-2013, 08:45 AM
  2. Replies: 3
    Last Post: 12-11-2012, 09:12 AM
  3. Replies: 7
    Last Post: 08-08-2012, 03:28 PM
  4. Replies: 1
    Last Post: 05-03-2012, 04:59 AM
  5. combo box to search multiple fields
    By jo15765 in forum Forms
    Replies: 21
    Last Post: 12-23-2010, 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