Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    combo box to search multiple fields

    i have 9 fields on my form all numerical, and i want to have a combo box that if i input a number it will take me to the record tha contains this number. example lets say field 1 is 40 and field 2 is 60 and field 3 is 80. i want to input 80 and it take me to thi record. does that make sense?

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    What you want done makes sense, however, I think you may have an issue with your table. Why would the number be in multiple fields? Could you provide a little more info about what youre trying to accomplish as far as the form goes?

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    It is a product table that shows the price of the product over the past 9 months. I am wanting to be able to type in a price, let's say $1250 and it display all products that have been this price in the past 9 months, i.e. have the number 1250 in the fields 1 - 9. Or let's say $780, I want it to display all products that have been this price over the past 9 months. I want the combo box to search Price 1, Price 2, Price 3, Price 4, Price 5, Price 6, Price 7, Price 8, and Price 9 until it displays the price of $780
    (these prices usually do not duplicate, they are unique).

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Your table setup is incorrect (IMO, at least). This should be broken down to 3 tables.
    tblProduct (with all the regular product information, sans the months and prices)
    tblMonth (2 fields, MonthID and MonthName)
    tblProductMonth (joint key with ProductID and MonthID)

    tblProductMonth will support a many-many relationship between tblProduct and tblMonth. You will have one record for each product for each month. For example

    ProductID, MonthID, Price
    1 1 500
    1 2 200
    1 3 400
    etc

    Now you only have to query one field. You check WHERE MonthID = whatever AND ProductID = whatever.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree that the table structure needs work. But I have questions:

    1) What happend in the 10th month? Do the prices shift down 1 month and the first month price disappears?

    2) Do the prices need to be in month order? What happens when Jan rolls around?

    3) Do all of the prices change every month?

    4) Does every product's price change every month?

    5) Do you need to keep a long term price history?


    My idea of the table structure:
    Code:
    tblProduct
    ---------------
    ProductID   Autonumber  PK
    Other fields(other product information)
    
    tblProductPrice 
    ---------------
    ProductPriceID  Autonumber
    ProductFK       LONG  - FK to tblProduct
    PriceDate       Date of Price
    Price           Currenct  - Price of Product

  6. #6
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    TheShabz

    I have never used a many-to-many relationship so I will have to google to understand this completely. I like the idea of this, this could work! In your example, I am more concerned with the productPrice than the Month. So, I am assuming I could just create the many-to-many relationship on the product price, but then I would be creating a many-to-many relationship for the productName to 9 different productPrice fields, which may or may not work, I will have to google, and use trial and error.

  7. #7
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    ssanfu:

    1) What happend in the 10th month? Do the prices shift down 1 month and the first month price disappears?
    It is a feed & seed store, so they are only open for 9 months out of the year, so the 10th month would actually be starting over with the 1st month.

    2) Do the prices need to be in month order? What happens when Jan rolls around?
    Order does not necessarily matter. I am really just more concerned with being able to input the price and getting the products that were this price. I honestly hadn’t thought about what happens when it’s time to reset the counter, maybe I could create a query that would back up this data as 2010 prices, and then start over with a fresh table for 2011? Any suggestions?

    3) Do all of the prices change every month?
    No some prices will stay the same from month to month, which would leave the price field blank for that month. For example lets say that product1 was $28 for Price1 and then remained the same for Price2, then no data would be input for Price2

    4) Does every product's price change every month?
    No every product’s price does not change monthly, sometimes it will remain the same.

    5) Do you need to keep a long term price history?
    Keeping a long term price history is a phenomenal idea, any suggestions on how I could do this? Kinda like what I was referencing above about when Price 9 rolls around, just like save the table as 2010 prices, and then have a blank table. It may even be something as creating a new table with the exact fields, and naming the old table 2010 prices.

  8. #8
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    3) Do all of the prices change every month?
    No some prices will stay the same from month to month, which would leave the price field blank for that month. For example lets say that product1 was $28 for Price1 and then remained the same for Price2, then no data would be input for Price2
    ________

    All you would need is another field for Year.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would it be possible to see your dB? Do a compact and repair, then zip it. The zipped file must be less than 2 mb. Otherwise, delete some of the data, then.... see above.

    (Note: change any sensitive data first)

  10. #10
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Here is a sample database, that is set up how I am needing it to be. It is not the prettiest in the world, but is still in the beginning phases so I can always modify the form to what I need it to be later on.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I modified your sample database. What do you think??

  12. #12
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    so you created two different tables, one for the tblProducts and tblProductPrice altho, I am not quite sure how the tblProducts is set up with the plus sign and being able to expand them, I have never done this?

    Then you built a query with joining the ProductID from tblProducts to the
    ProductID in the tblProductPrice

    And again, I am not sure how you set it up on the form to display all records on the one screen when you first load it. I do like how you put the search in the header. I have never done this or thought to do it, but it is much cleaner looking to have the combo box for searching in the header.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by jo15765 View Post
    so you created two different tables, one for the tblProducts and tblProductPrice altho, I am not quite sure how the tblProducts is set up with the plus sign and being able to expand them, I have never done this?
    When you are looking at the table, the "+" is Access's attempt to show related tables. This is called a "Sub Datasheet". Ignore it... I should have turned it off.

    I created the tables, then went into Relationships window, linked the tables and set the RI status.


    Then you built a query with joining the ProductID from tblProducts to the
    ProductID in the tblProductPrice
    Yep.

    And again, I am not sure how you set it up on the form to display all records on the one screen when you first load it. I do like how you put the search in the header. I have never done this or thought to do it, but it is much cleaner looking to have the combo box for searching in the header.
    • Create a form.
    • Show "FORM HEADER/FOOTER".
    • Set the background of the FORM HEADER to a different color than the detail section.
    • In the form properties, set the record source to the query. Note that the query does not have a WHERE clause.
    • Add the fields to the detail section. Cut the labels and add them to the form header.
    • In the form properties under the FORMAT tab, set the DEFAULT VIEW to "Continuous Forms".
    • Set whatever other properties you want


    • Add the unbound controls in the form header. Then add the code to set/unset the form filter.


    It does make a nice search form, doesn't it??

  14. #14
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    What is the RI status?

    And yes I agree, it makes a great search form! I would have never thought of doing it this way. I am always adding things to one table, and having a giant table, but making smaller ones, is much easier and more manageable.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

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

Similar Threads

  1. Multiple fields in combo box
    By spqr in forum Forms
    Replies: 2
    Last Post: 10-07-2010, 06:52 AM
  2. Replies: 3
    Last Post: 09-29-2010, 09:31 AM
  3. Replies: 4
    Last Post: 09-22-2010, 01:47 AM
  4. Help with a search feature or combo box
    By jmanis in forum Forms
    Replies: 14
    Last Post: 07-06-2010, 06:38 AM
  5. Multiple Fields In One Combo Box.
    By caljohn527 in forum Forms
    Replies: 1
    Last Post: 02-20-2009, 03:07 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