Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Updating query at runtime

Discussion in 'Information Technology' started by Someone, Nov 10, 2005.

  1. Someone

    Someone
    Expand Collapse
    Guest

    Hello

    My main form has a datasheet subform that is based on a query to a table. I
    have unbound text boxes on the main form, so that users can search the
    subform by entering some data and filter out like records (the query uses
    'Like "*" & [Forms]![FormName]![FieldName] & "*" Or Is Null'. The user
    simply enters some text into the relevant box and the data in the subform
    requeries itself with matching records.

    I have been asked if I could add a search on a particular field between two
    dates (the dates do not appear in the subform as it's not required
    information in that view). The field in question has 213 records and is
    referenced elsewhere in the database using a listbox, so that users can
    easily pick the correct entry they require. I would therefore like to have
    a search form with an unbound list box with all 213 records and a date
    picker for the 'From' and 'To' date. I would then like the existing query
    in the subform (that I described above) to update with the information
    entered. This is easy to get to work with just the listbox, but not with
    the dates too.

    I know that within a query, I can enter 'Between #dd/mm/yy# And #dd/mm/yy#'
    to search between two dates, but how could I get the information I capture
    from the form to update in the query? I tried referencing the relevant
    field names on the form within the query (i.e. Between
    #[Forms]![FormName]![FieldName]# etc), but this doesn't work because, when
    opening the main form, I'm asked for the parameter values.

    I'm very much hoping that this is possible. If so, can someone help me out?

    Many thanks
    M
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    ?Have you also tried

    Between [Forms]![FormName]![FieldName] And ...

    --
    Regards

    Jeff Boyce
    <Office/Access MVP>

    "Someone" <someone@somewhere.com> wrote in message
    news:Ofc1ixe5FHA.3188@TK2MSFTNGP15.phx.gbl...
    > Hello
    >
    > My main form has a datasheet subform that is based on a query to a table.

    I
    > have unbound text boxes on the main form, so that users can search the
    > subform by entering some data and filter out like records (the query uses
    > 'Like "*" & [Forms]![FormName]![FieldName] & "*" Or Is Null'. The user
    > simply enters some text into the relevant box and the data in the subform
    > requeries itself with matching records.
    >
    > I have been asked if I could add a search on a particular field between

    two
    > dates (the dates do not appear in the subform as it's not required
    > information in that view). The field in question has 213 records and is
    > referenced elsewhere in the database using a listbox, so that users can
    > easily pick the correct entry they require. I would therefore like to

    have
    > a search form with an unbound list box with all 213 records and a date
    > picker for the 'From' and 'To' date. I would then like the existing query
    > in the subform (that I described above) to update with the information
    > entered. This is easy to get to work with just the listbox, but not with
    > the dates too.
    >
    > I know that within a query, I can enter 'Between #dd/mm/yy# And

    #dd/mm/yy#'
    > to search between two dates, but how could I get the information I capture
    > from the form to update in the query? I tried referencing the relevant
    > field names on the form within the query (i.e. Between
    > #[Forms]![FormName]![FieldName]# etc), but this doesn't work because, when
    > opening the main form, I'm asked for the parameter values.
    >
    > I'm very much hoping that this is possible. If so, can someone help me

    out?
    >
    > Many thanks
    > M
    >
    >
    >
     
  4. Kou Vang

    Kou Vang
    Expand Collapse
    Guest

    I did something similiar, and ran into a lot of problems. I was trying to
    get 2 dates to be used as criteria as well for a query. My work around was
    this:

    I ran a query, that stored the date values into a table, then ran the dates
    into the criteria of the query using the Dlookup function. Access did not
    allow me to use the criteria off the form straight into the query, or at
    least I tried for a week unsuccessfully. But now I have two queries instead
    of one, but at least it works. I just used a macro to run all the queries
    and table updates. I'm pretty sure you can use a macro to run the queries
    and requery if neccessary. Good Luck!
     
  5. Someone

    Someone
    Expand Collapse
    Guest

    What do you mean?

    If you're suggesting that I enter that in the main form's query, this won't
    work, as explained, because when opening the main form, it asks for the
    parameter values for DateFrom and DateTo. This is because the values are
    yet to be filled in unless the user opens the search form and enters the
    values.

    If I've misunderstood, could you clarify?

    Many Thanks
    M

    "Jeff Boyce" <JeffBoyce_IF@msn.com-DISCARD_HYPHEN_TO_END> wrote in message
    news:O7W6BSf5FHA.1416@TK2MSFTNGP09.phx.gbl...
    > ?Have you also tried
    >
    > Between [Forms]![FormName]![FieldName] And ...
    >
    > --
    > Regards
    >
    > Jeff Boyce
    > <Office/Access MVP>
    >
    > "Someone" <someone@somewhere.com> wrote in message
    > news:Ofc1ixe5FHA.3188@TK2MSFTNGP15.phx.gbl...
    >> Hello
    >>
    >> My main form has a datasheet subform that is based on a query to a table.

    > I
    >> have unbound text boxes on the main form, so that users can search the
    >> subform by entering some data and filter out like records (the query uses
    >> 'Like "*" & [Forms]![FormName]![FieldName] & "*" Or Is Null'. The user
    >> simply enters some text into the relevant box and the data in the subform
    >> requeries itself with matching records.
    >>
    >> I have been asked if I could add a search on a particular field between

    > two
    >> dates (the dates do not appear in the subform as it's not required
    >> information in that view). The field in question has 213 records and is
    >> referenced elsewhere in the database using a listbox, so that users can
    >> easily pick the correct entry they require. I would therefore like to

    > have
    >> a search form with an unbound list box with all 213 records and a date
    >> picker for the 'From' and 'To' date. I would then like the existing
    >> query
    >> in the subform (that I described above) to update with the information
    >> entered. This is easy to get to work with just the listbox, but not with
    >> the dates too.
    >>
    >> I know that within a query, I can enter 'Between #dd/mm/yy# And

    > #dd/mm/yy#'
    >> to search between two dates, but how could I get the information I
    >> capture
    >> from the form to update in the query? I tried referencing the relevant
    >> field names on the form within the query (i.e. Between
    >> #[Forms]![FormName]![FieldName]# etc), but this doesn't work because,
    >> when
    >> opening the main form, I'm asked for the parameter values.
    >>
    >> I'm very much hoping that this is possible. If so, can someone help me

    > out?
    >>
    >> Many thanks
    >> M
    >>
    >>
    >>

    >
     
  6. Someone

    Someone
    Expand Collapse
    Guest

    All

    Thank you for your assistance.

    I have come up with the answer.

    I decided to duplicate the query used on the subform and included an
    additional paramater for the date field, which referenced the date pickers
    on the search form in the manner I described in my original post.

    In the code for the search form, I included a RecordSource change for the
    subform whose query I wanted to change
    (Forms!MainForm!SubForm.Form.RecordSource = "QueryName"). I also edited the
    code on my 'reset search' button so that the original query is restored
    thereafter.

    Hope that helps anyone else who finds themselves in a similar scenario!

    Thanks
    M

    "Someone" <someone@somewhere.com> wrote in message
    news:Ofc1ixe5FHA.3188@TK2MSFTNGP15.phx.gbl...
    > Hello
    >
    > My main form has a datasheet subform that is based on a query to a table.
    > I
    > have unbound text boxes on the main form, so that users can search the
    > subform by entering some data and filter out like records (the query uses
    > 'Like "*" & [Forms]![FormName]![FieldName] & "*" Or Is Null'. The user
    > simply enters some text into the relevant box and the data in the subform
    > requeries itself with matching records.
    >
    > I have been asked if I could add a search on a particular field between
    > two
    > dates (the dates do not appear in the subform as it's not required
    > information in that view). The field in question has 213 records and is
    > referenced elsewhere in the database using a listbox, so that users can
    > easily pick the correct entry they require. I would therefore like to
    > have
    > a search form with an unbound list box with all 213 records and a date
    > picker for the 'From' and 'To' date. I would then like the existing query
    > in the subform (that I described above) to update with the information
    > entered. This is easy to get to work with just the listbox, but not with
    > the dates too.
    >
    > I know that within a query, I can enter 'Between #dd/mm/yy# And
    > #dd/mm/yy#'
    > to search between two dates, but how could I get the information I capture
    > from the form to update in the query? I tried referencing the relevant
    > field names on the form within the query (i.e. Between
    > #[Forms]![FormName]![FieldName]# etc), but this doesn't work because, when
    > opening the main form, I'm asked for the parameter values.
    >
    > I'm very much hoping that this is possible. If so, can someone help me
    > out?
    >
    > Many thanks
    > M
    >
    >
    >
     
  7. German Saer

    German Saer
    Expand Collapse
    Guest

    You can use in the form for the subform:

    me.subformname.form.filterOn=true
    me.subformname.form.filter="where anycondition"

    and all you need to do, it is query with all the records. The Filter
    property will do the rest

    "Someone" <someone@somewhere.com> wrote in message
    news:%23gLbDTj5FHA.3296@TK2MSFTNGP09.phx.gbl...
    > All
    >
    > Thank you for your assistance.
    >
    > I have come up with the answer.
    >
    > I decided to duplicate the query used on the subform and included an
    > additional paramater for the date field, which referenced the date pickers
    > on the search form in the manner I described in my original post.
    >
    > In the code for the search form, I included a RecordSource change for the
    > subform whose query I wanted to change
    > (Forms!MainForm!SubForm.Form.RecordSource = "QueryName"). I also edited

    the
    > code on my 'reset search' button so that the original query is restored
    > thereafter.
    >
    > Hope that helps anyone else who finds themselves in a similar scenario!
    >
    > Thanks
    > M
    >
    > "Someone" <someone@somewhere.com> wrote in message
    > news:Ofc1ixe5FHA.3188@TK2MSFTNGP15.phx.gbl...
    > > Hello
    > >
    > > My main form has a datasheet subform that is based on a query to a

    table.
    > > I
    > > have unbound text boxes on the main form, so that users can search the
    > > subform by entering some data and filter out like records (the query

    uses
    > > 'Like "*" & [Forms]![FormName]![FieldName] & "*" Or Is Null'. The user
    > > simply enters some text into the relevant box and the data in the

    subform
    > > requeries itself with matching records.
    > >
    > > I have been asked if I could add a search on a particular field between
    > > two
    > > dates (the dates do not appear in the subform as it's not required
    > > information in that view). The field in question has 213 records and is
    > > referenced elsewhere in the database using a listbox, so that users can
    > > easily pick the correct entry they require. I would therefore like to
    > > have
    > > a search form with an unbound list box with all 213 records and a date
    > > picker for the 'From' and 'To' date. I would then like the existing

    query
    > > in the subform (that I described above) to update with the information
    > > entered. This is easy to get to work with just the listbox, but not

    with
    > > the dates too.
    > >
    > > I know that within a query, I can enter 'Between #dd/mm/yy# And
    > > #dd/mm/yy#'
    > > to search between two dates, but how could I get the information I

    capture
    > > from the form to update in the query? I tried referencing the relevant
    > > field names on the form within the query (i.e. Between
    > > #[Forms]![FormName]![FieldName]# etc), but this doesn't work because,

    when
    > > opening the main form, I'm asked for the parameter values.
    > >
    > > I'm very much hoping that this is possible. If so, can someone help me
    > > out?
    > >
    > > Many thanks
    > > M
    > >
    > >
    > >

    >
    >
     
  8. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    Your post had
    Between #[Forms...
    mine had
    Between [Forms...

    Also, if the form isn't open when you run the query, Access will have to
    prompt for the parameters.

    --
    Regards

    Jeff Boyce
    <Office/Access MVP>

    "Someone" <someone@somewhere.com> wrote in message
    news:OmSxLli5FHA.2628@TK2MSFTNGP11.phx.gbl...
    > What do you mean?
    >
    > If you're suggesting that I enter that in the main form's query, this

    won't
    > work, as explained, because when opening the main form, it asks for the
    > parameter values for DateFrom and DateTo. This is because the values are
    > yet to be filled in unless the user opens the search form and enters the
    > values.
    >
    > If I've misunderstood, could you clarify?
    >
    > Many Thanks
    > M
    >
    > "Jeff Boyce" <JeffBoyce_IF@msn.com-DISCARD_HYPHEN_TO_END> wrote in message
    > news:O7W6BSf5FHA.1416@TK2MSFTNGP09.phx.gbl...
    > > ?Have you also tried
    > >
    > > Between [Forms]![FormName]![FieldName] And ...
    > >
    > > --
    > > Regards
    > >
    > > Jeff Boyce
    > > <Office/Access MVP>
    > >
    > > "Someone" <someone@somewhere.com> wrote in message
    > > news:Ofc1ixe5FHA.3188@TK2MSFTNGP15.phx.gbl...
    > >> Hello
    > >>
    > >> My main form has a datasheet subform that is based on a query to a

    table.
    > > I
    > >> have unbound text boxes on the main form, so that users can search the
    > >> subform by entering some data and filter out like records (the query

    uses
    > >> 'Like "*" & [Forms]![FormName]![FieldName] & "*" Or Is Null'. The user
    > >> simply enters some text into the relevant box and the data in the

    subform
    > >> requeries itself with matching records.
    > >>
    > >> I have been asked if I could add a search on a particular field between

    > > two
    > >> dates (the dates do not appear in the subform as it's not required
    > >> information in that view). The field in question has 213 records and

    is
    > >> referenced elsewhere in the database using a listbox, so that users can
    > >> easily pick the correct entry they require. I would therefore like to

    > > have
    > >> a search form with an unbound list box with all 213 records and a date
    > >> picker for the 'From' and 'To' date. I would then like the existing
    > >> query
    > >> in the subform (that I described above) to update with the information
    > >> entered. This is easy to get to work with just the listbox, but not

    with
    > >> the dates too.
    > >>
    > >> I know that within a query, I can enter 'Between #dd/mm/yy# And

    > > #dd/mm/yy#'
    > >> to search between two dates, but how could I get the information I
    > >> capture
    > >> from the form to update in the query? I tried referencing the relevant
    > >> field names on the form within the query (i.e. Between
    > >> #[Forms]![FormName]![FieldName]# etc), but this doesn't work because,
    > >> when
    > >> opening the main form, I'm asked for the parameter values.
    > >>
    > >> I'm very much hoping that this is possible. If so, can someone help me

    > > out?
    > >>
    > >> Many thanks
    > >> M
    > >>
    > >>
    > >>

    > >

    >
    >
     

Share This Page