Results 1 to 4 of 4
  1. #1
    yeskitty is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2019
    Posts
    2

    Subform referencing parent form value for query results

    Hi there

    I've been going around in circles trying to get my logic correct with this problem with no success . Please help me collective mind!

    This is for an inherited 'database' (aka flat table) which I am trying to quickly make usable before starting fresh.

    It is an events database where I want the user to start entering the details of a new event on the main form and after entering the Start Date for the subform to Requery with any other events on at the same time.

    I am trying to structure the main form as a method of entering/viewing the full event details and the subform to show other events which are registered on the same day.

    An example of the data structure is
    Start Date End Date
    1/10/19
    2/10/19 5/10/19
    4/10/19
    4/10/19 6/10/19

    When the user enters a new event on 04/10/19, I want the subform to update and show 3 dates already registered on the same day. I've tried a range of criteria on the subform data source but the closest result which I have been able to achieve is for all other events which start on the date - this means the long ongoing events are being omitted from the results.

    Any and all suggestions would be appreciated

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why doesn't every record have an EndDate value?

    You want all records where the entered dated falls on StartDate or is BETWEEN StartDate and EndDate?

    Possibly apply filter criteria like: [StartDate] = Forms!formname![textboxname] OR Forms!formname![textboxname] BETWEEN [StartDate] AND EndDate
    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
    Join Date
    Apr 2017
    Posts
    1,673
    Maybe you can add an example database here!

    For me is not clear, what you really have, and what you want to do.

    Code:
    It is an events database where I want the user to start entering the details of a new event on the main form...
    Your main form is events form? And all those events have some dates?

    And you have some subform in events form. But on which data is this subform based, and what it does?

    Code:
    When the user enters a new event on 04/10/19, I want the subform to update and show 3 dates already registered on the same day.
    Deeper into forest there is more trees! Does this subform also display same events, or what?

  4. #4
    yeskitty is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2019
    Posts
    2

    Talking

    Quote Originally Posted by June7 View Post
    Why doesn't every record have an EndDate value?

    You want all records where the entered dated falls on StartDate or is BETWEEN StartDate and EndDate?

    Possibly apply filter criteria like: [StartDate] = Forms!formname![textboxname] OR Forms!formname![textboxname] BETWEEN [StartDate] AND EndDate

    That worked perfectly! Thank you! I was using an AND instead of an OR which has made all of the difference.

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

Similar Threads

  1. Referencing query results in code?
    By Homegrownandy in forum Access
    Replies: 7
    Last Post: 04-27-2017, 01:54 AM
  2. referencing results in the same query,
    By Homegrownandy in forum Queries
    Replies: 5
    Last Post: 10-28-2015, 08:56 AM
  3. Replies: 3
    Last Post: 11-26-2014, 06:05 PM
  4. Replies: 6
    Last Post: 11-20-2014, 03:37 PM
  5. Replies: 3
    Last Post: 03-30-2012, 10:50 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