Page 1 of 7 1234567 LastLast
Results 1 to 15 of 93
  1. #1
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108

    Populate Day and allowable times based on date picker

    Hello Again



    I am making a form that collects tour reservation data. In my question i have three objects, 2 text boxes, and one drop down menu.

    Tour Date: [ ]
    Tour Day: [ ]
    Tour Time: [ ^]

    The university schedules tours Monday - Friday, at 10:30 and 1:30, and saturdays at 1:30 only.

    The tour date utilizes the date picker feature built in to access. Is there a way to take the day from the selected date, and automatically put it into the Tour Day field? And if that day happens to be a Saturday, allow only the 1:30 time to be selected from the drop down?

    That is my primary concern at this point...

    However, a couple other things that I want to look into are the following:

    -if VB has a stream reader, configure it to read text files to upload blackout dates (days which tours are not scheduled) and programatically block the inputter from selecting those dates on the date picker.

    -cap the number of tours scheduled per timeslot per day to 10.

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Is there a way to take the day from the selected date, and automatically put it into the Tour Day field?
    You can nest the weekday() function within the weekdayname() function to show the day of the week.

    =WeekdayName(Weekday([yourdatecontrol]))

    The Weekday() function returns the number for the particular day. The default is Sunday =1, so for a date that corresponds to a Saturday, the weekday() function returns a 7.


    And if that day happens to be a Saturday, allow only the 1:30 time to be selected from the drop down?
    Filtering your combo box or list box can be accomplished as well but it depends on how you have set up your tables.

    if VB has a stream reader, configure it to read text files to upload blackout dates (days which tours are not scheduled) and programatically block the inputter from selecting those dates on the date picker.
    I don't know what a stream reader is, but you can set up a table that holds the dates for which no tours are conducted and compare the date selected to the dates in that table and force a reselection if needed.

    Capping the number of tours per day can be accomplished by doing a count of people who have already signed up. You can use either a query or the DCount() domain aggregate function.

    I've attached a sample database with a form that illustrates how to filter the combo box when a date is entered.

  3. #3
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    Crap....

    I want to move on to doing that, but I am having a relatively stupid problem that is holding me up....

    Yesterday this worked somewhat, now I cannot get it working correctly no matter what. The problem is trivial... I have a checkbox and a textbox (betchya you already know where this one is going) when a user opens the form to complete a new record, there is a check box with a caption "Group Tour?" and a textbox labled "Group Size"

    If the check box is checked, the texbox should be enabled. If it is not checked, the text box should be disabled. DUH!

    I put an If Else statement in, as follows: (checkbox name: checkGroupTour, textbox name: Group Tour)

    =======

    Private Sub checkGroupTour_Enter()
    If checkGroupTour = False Then
    [Group Tour].Enabled = False
    Else
    [Group Tour].Enabled = True
    End Sub

    =======

    A stream reader is something used in C# that collects data from a text file and puts it into a table. Didnt know if that existed for VB as well. I will look at your example database for that list box.

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I would probably put the code in the After Update event of the check box rather than the Enter event.

    If you are using the same form for existing records, then you will have to put similar code in the On current event of the form.

    I would generally use the visible property rather than the enable property i.e. show the textbox only when need otherwise hide it. I'll leave that call to you.

  5. #5
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    Ok, with your advice, I have this (copy & pasted exactly as I have it on my screen)

    Option Compare Database
    Private Sub checkGroupTour_AfterUpdate()
    If checkGroupTour.Value = False Then
    [Group Tour].Enabled = False
    Else
    [Group Tour].Enabled = True
    End Sub

    So far, it still does not work properly... maybe it is my syntax? Are there any errors, added crap, missing stuff, pieces out of order in the code listed above? Or maybe it is something totally diffierent....

  6. #6
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    Also, when using the form, when i click the checkbox, a message box pops up with the following message:

    Compile error:

    Method or data member not found

    [OK][Cancel]


    Another problem I might suspect is this: prior to now, I had some code put in, and later found it to be riddled with mistakes, etc, so i highlighted it all but the top part that reads: "Option Compare Database" and deleted the rest of the code... So would that have any effect on this???

  7. #7
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    In order to alleviate any form corruption, i went ahead and rereated the the form and associated it with the proper table. I now have this as my code:

    Private Sub checkGroupTour_AfterUpdate()
    If checkGroupTour.Value = False Then
    [Group Tour].Enabled = False
    Else
    [Group Tour].Enabled = True
    End If
    End Sub

    Now the problem it is having is the ".Enabled" part. For some reason, the program suggests to use ".Value" and when I use that, it throws no error, however, the form does not behave as it is supposed to. Instead, while the checkbox is unselected by default, you can still enter information into the textbox which should be disabled. then after checking the box, and unchecking the box, the enabled attirbute of the textbox never goes to false. Any ideas?

  8. #8
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    Well, I guess it is worth my time making a post stating that after all this time tinkering, asking, and researching, I have made the discovery that the problem was I had the control names mixed up.

    On to bigger and better things...

  9. #9
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    jzwp11,

    I was able to take the day from the date control and put it into a locked text box. Perfect!

    However, I am a bit lost on how to set up the time selection....

    I see in your example that there are three tables... and on a visual basis I am trying to determine how they have been set up and how they relate to each other and the form fields themselves...

    I dont know how much work goes into this, but if it is relatively simple to break down, could you explain this to me in a step by step format? I am going to poke around that example you gave me and see if i can figure it out, but chances are i will start out with the wrong idea and do more damage than progress of any sort...

    You are very kind to have provided that for an example, thank you.

  10. #10
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    jzwp11,

    Ok, after tinkering around, I discovered that you used relationships and followed your example. I have plugged around with trying to use the query produced as an end result to control what times are available in the drop down menu. It is possible that I could copy and paste your query string, and just replace your names with mine. However, that would wind up having me come back with this same question over and over again untill I figued out how that was produced....

    In simple terms, how the heck did you come up with this:

    SELECT qryAvailableTourTimes.longDayNumber, qryAvailableTourTimes.dteTime FROM qryAvailableTourTimes WHERE (((qryAvailableTourTimes.longDayNumber)=Weekday([mydate]))) ORDER BY qryAvailableTourTimes.longDayNumber, qryAvailableTourTimes.dteTime;

    I am assuming this was created using a wizard? If so, which one did you use and what was the configuration you used? If you didnt use a wizard, well, i guess your "just good like that"

    I appreciate your help alot!

  11. #11
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I created the tblTourDays to hold the days of the week (I just numbered them, with the 1 assumed to be Sunday like the default setup of the weekday() function. I also set up a simple table to hold the times (you can add more times as needed if additional tour times are added in the future).

    Since a day could have multiple times associated with it (one-to-many relationship) and a time could be associated with more than one day (another one-to-many relationship), the only way to represent 2 one-to-many relationships between the same two tables is through the use of a junction table (tblTourDayTimes). After creating the tables, I then used the relationship window in Access to make the joins between the 3 tables. I added the records directly into the tables (starting with the tour days and time table first) to represent the 2 tour times for each day of the week except Saturday and Sunday. I added a record for the 1 tour time for Saturday.

    From there, I created the query qryAvailableTourTimes by adding the 3 tables to the query design grid (Access copies the relationships from the relationship window if you have them set up there beforehand). Then I just selected the appropriate fields I wanted.

    Then I created an unbound form and added 2 textbox controls. I added the nested function to the second textbox control as previously discussed. The value in the second textbox is based on the first, so anytime the date value in the first box is changed, the second will update accordingly. Then I added the combo box (using the combo box wizard) and based the combo box on the query. I went into the row source of the combo box (which is the query) and modified it by adding the criteria to check the weekday() of the date entered in the first textbox control to do the filtering. I did have to add some code in the After Update event of the first textbox to requery the combo box with each change in date.

  12. #12
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    Private Sub Combo9_AfterUpdate()
    Me.Combo9.Requery
    End Sub

    This is what I have on my database...

    I pick a date, click into the day text box then the day is populated correctly, click down to the combo box, and it lists 10:30 and 1:30 over and over rather than just one or two per day. It is showing all the times availbe during the week.

    Private Sub Combo9_AfterUpdate()
    Self explanitory...

    Me.Combo9.Requery
    Okay... I renamed the combo box to Combo9 because thats what it is on my form. I am new to VB, have used C# a bit, so the Me. has me stumped... Is this referencing to something? Or is it a command in VB? .Requery appears to command the box to requery.

    End Sub
    Duh...

    Does it matter where i place the chunk of code in the form code? I just plugged it into the bottom of the form. Maybe it needs to be placed elsewhere or does it not matter?

  13. #13
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The "me." portion is just a shorthand notation for the current form. It represents forms!yourformname. If you are doing anything in code with the current form or a control on the form you can use the "me." shorthand. If you are executing code from one form that impacts a second form then you have to use the full form reference notation for the second form.

    The reason you are getting the times repeating is that somehow you are not providing the day number to the query used for the combo box; specifically the WHERE clause shown in red below.

    Code:
    SELECT qryAvailableTourTimes.longDayNumber, qryAvailableTourTimes.dteTime FROM qryAvailableTourTimes WHERE (((qryAvailableTourTimes.longDayNumber)=Weekday([mydate]))) ORDER BY qryAvailableTourTimes.longDayNumber, qryAvailableTourTimes.dteTime;
    Also, the code: Me.Combo9.Requery should go in the After Update event of the text box where you enter the date not in the After Update event of the combo box

  14. #14
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    Progress!

    SELECT qryAvailableTourTimes.longDayNumber, qryAvailableTourTimes.dteTime FROM qryAvailableTourTimes WHERE (((qryAvailableTourTimes.longDayNumber)=Weekday(To ur_Date))) ORDER BY qryAvailableTourTimes.longDayNumber, qryAvailableTourTimes.dteTime;

    I updated this to reflect the information on my side.

    I am still having problems though...

    When I use the drop down box, regardless of what day I select, it displays 4 and 4 as the two choices...

    I also fixed my source code too, reflecting your advice. That is working properly now.

  15. #15
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    Also on the docket is this:

    I have 3 combo boxes for faculty appointment requests. The first one has a faculty member, time and location text box corresponding to it. ex.:

    Request Faculty Appointment: [ ^]
    Request Faculty Appointment 2: [ ^]
    Request Faculty Appointment 3: [ ^]

    ================================

    Faculty Member [ ]
    Time [ ] Location [ ]
    2 Time [ ] 2 Location [ ]
    3 Time [ ] 3 Location [ ]

    If there is no faculty appointment request, only the first combo box should remain active. The remainder of combo boxes plus text boxes should not be enabled. If there is a faculty appointment request, once the inputter selects either Yes Before Tour, Yes After Tour, or Yes Flexible Time, 2nd Appointment request combo should become active, and the faculty member text box, time text box, and location text box should become enabled. 2nd & 3rd appointment request combo box should follow this same pattern.

    I tried making the default value for all the combo boxes No, but they appear as zeros, and I encountered problems while building the code to facilitate my needs. Have you had experience with this type of thing? It is probably dead simple, but the dynamics of a combo box are throwing me off...

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

Similar Threads

  1. Date Picker?
    By gazzieh in forum Forms
    Replies: 9
    Last Post: 02-09-2013, 09:35 AM
  2. Date Picker doesn't appear
    By revnice in forum Access
    Replies: 9
    Last Post: 01-09-2012, 08:36 AM
  3. date picker activex control
    By mr2000 in forum Forms
    Replies: 1
    Last Post: 10-13-2010, 09:51 AM
  4. Date picker
    By BI4K12 in forum Access
    Replies: 1
    Last Post: 06-09-2010, 11:11 AM
  5. Microsoft Date and Time Picker 6.0
    By That Crazy Hockey Dood in forum Forms
    Replies: 0
    Last Post: 07-25-2007, 03:22 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