Results 1 to 7 of 7
  1. #1
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54

    Use multi-select combo box values to fill date values in unbound control

    Any help with this would be greatly appreciated, as I am no programmer...



    I have a form on which the first field, cboStartDate, always a Monday, is picked from a combo box. Works correctly.

    The form has a multi-select combo box, cboAirDays filled with days of the week, with two fields; the (hidden) bound column contains values 1-7, the second is what the user makes his choices from Mon-Sun.

    The AfterUpdate event needs to do two things. First is to count the number of days selected, put that in txtAiringCount field, which used in a calculation * txtRate. That works correctly.

    I'd like the user to be able to see the actual dates in another field txtAirDates, just for information to verify. So for instance, if the cboStartDate is 6/29/20 and the user selects Tue and Thu (2 and 4), the txtAirDates field should display "6/30, 7/2"; Mon, Wed, Fri selection would display "6/29, 7/1, 7/3" in the txtAirDates field, etc...

    Let me know if you need any other information to help me with this bit of VBA.

    Thanks in advance!

  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
    Are the dates selected in cboAirDays always in the same week as the Monday in cboStartDate.
    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
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    Yes! The cboAirDates are just numbered 1-7 display Mon-Sun. The dates returned would be in the week starting on the StartDate.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    What is the SQL statement used as the Row Source for cboStartDate
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    I haven't finished that part yet. The form is a actually a subform. On the main form, the user has entered quarterly start and end dates, i.e. 7/1/20 through 9/30/20. The weeks in each quarter begin on Mondays. The cboStartDate will populate with Monday dates for the quarterly period displayed on the main form. There is a calculated field "EndDate", which is the Sunday date for what the user has picked for starting date. cboAirDates would be between StartDate and EndDate, depending on cboAirDays choice(s).

    I'd like to load that cboStartDate with that data too, using VBA, but temporarily have a simple lookup table with year, quarter, week#, start date fields.

    Thanks for your interest in my problem, and your quick response!

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I don't understand.
    I haven't finished that part yet.
    Are you saying that there is nothing to select in the combo box yet. If not, then it must have something as a Row Source property
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    Queries the lookup table for the Monday dates of each week in the Q-YYYY field on the main form. Would like to populate this cbo with VBA but the lookup works okay for now.

    SELECT tblDateLookup.Start
    FROM tblDateLookup
    WHERE (((tblDateLookup.Period)=[Forms]![Insertion Orders]![txtPeriod]));

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

Similar Threads

  1. Replies: 6
    Last Post: 04-15-2019, 09:03 PM
  2. Replies: 8
    Last Post: 05-11-2018, 11:39 AM
  3. Get Values From Multi Select List Box
    By Juan4412 in forum Programming
    Replies: 4
    Last Post: 03-21-2017, 02:26 PM
  4. Replies: 11
    Last Post: 08-04-2016, 09:56 AM
  5. Replies: 19
    Last Post: 09-09-2014, 01:36 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