Results 1 to 6 of 6
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    Iff Puzzle VBA and DLookup

    I have one form frmShows with a many to many of show prices for years.

    Year 2012 (combo box) has $4.70
    Year 2013 (combo box) has $4.40

    These values are stored in the many to many junction so that each show can select the year and the price for that year. That works fine.

    I have a bookings form which selects shows then does a d-lookup for the show prices - I did this before I added the junction. The reason I did a d-lookup is that I needed to store the values at that time - the show price wouldn't affect the booking if it changed. Just something that was needed.



    The next task is to have a combo box select the year on the booking (i.e. what year they are going to book, this year or next) and then filter the shows according to if the year matches if the shows have a price in that year.

    The goal is that when you select a year on the booking form then only the shows with a price for that year will show in a combo box for that chosen year.

    So 2012

    gets Show A

    with 2012 Price

    but

    2013

    gets Show A

    with 2013 Price

    I can't have 2012 shows showing in 2013 shows either in case there isn't a price placed in the show table and vice versa.

    I'm guessing something with if show year = to show year price then only show shows with show year price but not sure how to put in vba....

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That sounds like cascading (dependent) comboboxes. Review http://datapigtechnologies.com/flash...combobox2.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Also I have thought about making a many to many junction between bookings and shows - but this would mean allocating shows to the year when I would rather just have the shows that have a 2013 price show only if they are priced for that year.

    So if the user has entered a price for 2013 then it shows by default. No price in 2013 and it won't show.

  4. #4
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    That sounds like cascading (dependent) comboboxes. Review http://datapigtechnologies.com/flash...combobox2.html
    thanks - watching

  5. #5
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Works! Even across the junction

    Thank you!!!!!

  6. #6
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    That sounds like cascading (dependent) comboboxes. Review http://datapigtechnologies.com/flash...combobox2.html
    um. Stuck.

    If I open the subform it works fine, if I use it in the mainform it asks for a parameter....?

    Updated - just put the mainform into the parameter - works

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

Similar Threads

  1. DLookUp Help
    By ThaGreenMoose in forum Forms
    Replies: 10
    Last Post: 06-15-2011, 12:08 PM
  2. VB Strategy Games - Circle Puzzle and Others
    By pkstormy in forum Code Repository
    Replies: 4
    Last Post: 03-05-2011, 03:18 AM
  3. DLookup()
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 09-15-2010, 07:20 AM
  4. Some neat puzzle visual basic games
    By pkstormy in forum Sample Databases
    Replies: 1
    Last Post: 09-05-2010, 06:57 PM
  5. Unique Puzzle
    By Christina G in forum Database Design
    Replies: 0
    Last Post: 03-14-2009, 06:37 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