Results 1 to 10 of 10
  1. #1
    Nosaj08 is offline Advanced Beginner
    Windows XP Access 2002 (version 10.0)
    Join Date
    Apr 2009
    Posts
    40

    Event Calendar Help

    Hey everyone,


    This question may be hard or it may be easy, I am not quite sure. I will try to explain it as simple as possible but it may be kinda long. Well here it goes:

    I have a form acting as a calendar that is able to automatically fill in the current month along in a textbox at the top and all of its dates, each as its own textbox below. (See Attached Screenshot) Within each date box, I have a list box that is able to look up data in a query and come back with the events that are on that specific day. (See Date 6/6/2009 in Screenshot)

    The problem I am having is, each list box has its own query to look up the specific date in its box, which leaves me with 41 different queries to run for the month and this slows down the form quite a bit and is pretty heavy on the CPU. Is there any way to have one query that pulls back information depending on what listbox is calling the information?

    For instance, right now my query criteria is [Forms]![frmEventCalendar]![D1], where D1 is the name of the specific list box. Is there a code that can replace D1 with the name of whatever calls the query?

    Thanks ahead of time!
    Jason

  2. #2
    Nosaj08 is offline Advanced Beginner
    Windows XP Access 2002 (version 10.0)
    Join Date
    Apr 2009
    Posts
    40
    Actually, maybe a better question to ask is whether or not there is a way to insert the criteria into the query using VB code in the form? I may be able to figure out the problem if I can do this.

    Jason

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You could use the syntax:
    Forms.frmEventCalendar(YourGlobalPublicVariable) if you want.

  4. #4
    Nosaj08 is offline Advanced Beginner
    Windows XP Access 2002 (version 10.0)
    Join Date
    Apr 2009
    Posts
    40
    Hey RuralGuy,
    I dont think I defined my value as a global variable, but as a Dim in the form. Below is the code that I used within my form to create the days in the calendar:

    Private Function filldates()
    Dim curday As Variant, curbox As Integer
    curday = DateSerial(Year(Me![FirstDate]), Month(Me![FirstDate]), 1) 'first day of month
    curday = DateAdd("d", 1 - Weekday(curday), curday) 'back to sunday
    For curbox = 0 To 41 'need to loop thru 42 textboxes
    Me("D" & curbox) = DateSerial(Year(Me![FirstDate]), Month(Me![FirstDate]), Day(curday))
    Me("D" & curbox).Visible = False
    If Month(curday) = Month(Me!FirstDate) Then Me("D" & curbox).Visible = True
    curday = DateAdd("d", 1, curday)

    Next curbox
    Then, I have the list box for each day refer to the date that is filled into the day's text box via the above code and look up the values in a query. So its 41 list boxes refering to 41 text boxes to call 41 queries, which I am trying to get to only 1 query. Hope this helps. And thanks for the quick response btw!

    Jason

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Somewhere I have someone elses version of what you are doing. I don't know if you want to look at it for ideas or not. Is this for your own use or a school project or just to learn Access? What does the SQL for the ListBoxes look like?

  6. #6
    Nosaj08 is offline Advanced Beginner
    Windows XP Access 2002 (version 10.0)
    Join Date
    Apr 2009
    Posts
    40
    Yeah, I got the base code from a template I found online and then modified the code slightly to make the calendar do what I want it to do. I am actually doing this for work as a side project. I took an access class a while ago and want to further my knowledge in it by picking up projects to work on here and there. As for the SQL for the list boxes, Ill have to post that when I get into work on Fridays. I'm an intern at my company so I am only in every other day. Anyways, anything that would help me with this would be great. I don't mind figuring it out, just would like to know what direction to move in. Thanks!

    Jason

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's an example that has some good ideas in it for your reading pleasure.

  8. #8
    Nosaj08 is offline Advanced Beginner
    Windows XP Access 2002 (version 10.0)
    Join Date
    Apr 2009
    Posts
    40
    Great, thanks! Ill pour through this to see if it can point me in the right direct. This is the same basic concept so I am sure I can get my version working from here. Ill keep you updated.

    Jason

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That's great Jason. We'll be around when you need us.

  10. #10
    Join Date
    Jun 2010
    Posts
    2
    This is perfect for what I am trying to do as well, is there any way that I could have this form and create a report that looks just like the large calendar?

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

Similar Threads

  1. Replies: 21
    Last Post: 06-03-2009, 05:54 PM
  2. Access Calendar Control
    By JGG in forum Access
    Replies: 7
    Last Post: 04-03-2009, 04:34 AM
  3. On-Click event transfer
    By nkenney in forum Forms
    Replies: 16
    Last Post: 03-26-2009, 09:02 PM
  4. Create a calendar report?
    By valkyry in forum Reports
    Replies: 0
    Last Post: 09-14-2006, 11:34 PM
  5. Calendar Mystery
    By BankWalker in forum Forms
    Replies: 0
    Last Post: 02-16-2006, 01:29 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