Results 1 to 6 of 6
  1. #1
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66

    Combo box challenges

    Four Tables:
    Vendor (Vendor details for contracts)
    Product (Product details, with VendorID to tie them to a vendor)
    Order (Contract and One-Time Order details, also with a VendorID but this time for the reseller of products)


    OrderProduct (join of Order and Product, to record multiple products against one contract)

    1) I am building a form for entering and editing contracts, with a subform for adding all the products tied to the contract (continuous form). The challenge is in the filtering of the Product combobox. Rowsource = Product where Product.VendorID = Form.VendorID. If adding a new record, I'd want the user to select the Vendor via an unbound combobox to filter the Product combo box. That's straightforward and I have that working.

    However, if editing an existing record in the same form, the unbound Vendor combobox is unpopulated, which means the Product combobox doesn't have a value to use as criteria so it is also blank.

    2) I thought of adding the VendorID to the OrderProduct table to make it a little easier, so I added a bound Vendor combobox next to the Product combobox. Works like a champ to bring up the existing record. But if someone chooses to add a different Vendor to the same contract, the Product RowSource changes and all the comboboxes above the record go blank (because the control is looking at the new record's Vendor combo box. So now I've created an issue with NEW records...

    If you need to see the database, I'd prefer to share it privately...it's for the company I work for and prefer not to have it freely available.
    Thanks in advance,
    Phil

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    For me, it would be easier if you were to post a copy of your db. If you have any sensitive data then remove it. Would be good if you could make a couple of fictitious records to illustrate your problem.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Phil,
    There are a couple ways of doing this, there is a "work-around" that uses a text box or you could change the row source of the product combo in the enter and exit events. Have a look at this thread that explains both with some pros and cons.
    https://www.access-programmers.co.uk.../#post-1689843

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66

    Uploaded db

    Attachment 42411I've attached my db, zipped and with a lot of the extraneous stuff deleted. I am building a primary, unbound form (like a dashboard) called ITFM, that you use to set default parameters then open subforms. Use the New Contract button to open the form in question. It pulls up the record and everything is populated. Then change the vendor in the new record row to something different...the product above goes blank.
    Attached Files Attached Files

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please have a look at the attached updated file. You will notice that the previous records for different vendors go blank while you actively select the product for the current record but they get restored soonest you exit the product combo.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Vlad,
    That is outstanding! I like the way you solved the problem. I'm not proficient in VBA, and don't do enough VBA work to make it worth the effort, but can definitely see the power of VBA in solving problems like this.
    It seems to me that success with Access databases, however, doesn't so often rely on technical knowledge but rather creativity in how to solve the problem - something the pros here are great at!
    Again, thanks so much.
    All the best,
    Phil

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

Similar Threads

  1. Replies: 4
    Last Post: 08-14-2019, 02:23 PM
  2. Replies: 2
    Last Post: 10-21-2014, 07:57 AM
  3. Replies: 7
    Last Post: 06-08-2012, 11:12 AM
  4. Replies: 3
    Last Post: 10-26-2010, 09:29 AM
  5. Weighted Average - Challenges
    By edmund_xue in forum Access
    Replies: 0
    Last Post: 04-02-2008, 12:54 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