Welcome to SPN

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

Sign Up Now!

Help performing a search

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

  1. Someone

    Someone
    Expand Collapse
    Guest

    Hello

    I need to incorporate a search facility into my database. Let me give a
    simple overview on how it's set up.

    There is a main form which has a subform in datasheet view. The subform is
    based on a query to a table (call it table a). The reason for this is that
    the main form has unbound text boxes so users can search the data in the
    subform (the view is requeried to show like records on the data entered -
    Like "*" & [FormControlName] etc).

    Amongst the buttons on the main form is one to open another form with three
    tabs. The form opens to the same record that is selected in the main form's
    subform. The default tab shows ALL fields of the record selected from the
    subform. On the second tab is another subform in form view for a different
    table (call it table b - it's actually a table of contact details). The
    third tab is irrelevant for this post.

    The relationship between table a and b is a one-to-many, where 1 record in
    table a can refer to many records in table b (i.e. a record in table a could
    have more than one contact name and/or address).

    I've been asked to incorporate a search facility on the contacts (table).
    It would need to locate the related record in table a (i.e. the master
    record). The problem is, I can't figure out the best way to do it.

    Ideally, I'd like to have a search form with textboxes that the user can
    fill in with their required search criteria. This then finds the related
    record in table a, based on the search just made on table b. If possible,
    I'd like this to requery the data on the main form's subform so the user can
    click open the record of their choosing. I've tried, but it's gone beyond
    me. Do anyone of you have any ideas on how you would go about this and what
    I'd need to do to accomplish it? You might think my idea is
    over-complicated and that there is a more straight-forward idea to fulfil
    this.

    I do hope I haven't sounded too vague in my request. If you need further
    detail, I'm happy to supply it.

    Many thanks for your time
    M
     
  2. Loading...


  3. Smartin

    Smartin
    Expand Collapse
    Guest

    Someone wrote:
    > Hello
    >
    > I need to incorporate a search facility into my database. Let me give a
    > simple overview on how it's set up.
    >
    > There is a main form which has a subform in datasheet view. The subform is
    > based on a query to a table (call it table a). The reason for this is that
    > the main form has unbound text boxes so users can search the data in the
    > subform (the view is requeried to show like records on the data entered -
    > Like "*" & [FormControlName] etc).
    >
    > Amongst the buttons on the main form is one to open another form with three
    > tabs. The form opens to the same record that is selected in the main form's
    > subform. The default tab shows ALL fields of the record selected from the
    > subform. On the second tab is another subform in form view for a different
    > table (call it table b - it's actually a table of contact details). The
    > third tab is irrelevant for this post.
    >
    > The relationship between table a and b is a one-to-many, where 1 record in
    > table a can refer to many records in table b (i.e. a record in table a could
    > have more than one contact name and/or address).
    >
    > I've been asked to incorporate a search facility on the contacts (table).
    > It would need to locate the related record in table a (i.e. the master
    > record). The problem is, I can't figure out the best way to do it.
    >
    > Ideally, I'd like to have a search form with textboxes that the user can
    > fill in with their required search criteria. This then finds the related
    > record in table a, based on the search just made on table b. If possible,
    > I'd like this to requery the data on the main form's subform so the user can
    > click open the record of their choosing. I've tried, but it's gone beyond
    > me. Do anyone of you have any ideas on how you would go about this and what
    > I'd need to do to accomplish it? You might think my idea is
    > over-complicated and that there is a more straight-forward idea to fulfil
    > this.
    >
    > I do hope I haven't sounded too vague in my request. If you need further
    > detail, I'm happy to supply it.
    >
    > Many thanks for your time
    > M
    >
    >


    I did something like this to do that... Set up a button on the form that
    calls a query. The query will request the search term.

    In the click event of the button:
    ===== (VBA)
    Private Sub cmdPersonSearch_Click()
    DoFilterButton ("qPersonSearch")
    End Sub
    =====

    ....and also in a class module (because the 'DoFilterButton' sub handles
    several different searches)...

    ===== (VBA)
    Private Sub DoFilterButton(QueryName As String)
    On Error GoTo FilterErr
    Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL

    Exit_DoFilterButton:
    Exit Sub

    FilterErr:
    MsgBox Err.Description
    Resume Exit_DoFilterButton
    End Sub
    =====

    In a query named PersonSearch:

    ===== (SQL)
    SELECT *
    FROM A
    WHERE A.ID IN
    (SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
    WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
    =====

    Any good?
    --
    Smartin
     
  4. Someone

    Someone
    Expand Collapse
    Guest

    Hi

    Thanks for taking the time to respond to me.

    What you said sounds perfect. I tried incorporating it, but I'm getting the
    following error when attempting to perform a search:

    Sub, Function, or Property not defined (Error 35)

    Can you help me resolve this?

    Thank you
    M
    "Smartin" <smartin108@yahoo.com> wrote in message
    news:pKSdnVcIdeFuTubenZ2dnUVZ_vydnZ2d@giganews.com...
    > Someone wrote:
    >> Hello
    >>
    >> I need to incorporate a search facility into my database. Let me give a
    >> simple overview on how it's set up.
    >>
    >> There is a main form which has a subform in datasheet view. The subform
    >> is based on a query to a table (call it table a). The reason for this is
    >> that the main form has unbound text boxes so users can search the data in
    >> the subform (the view is requeried to show like records on the data
    >> entered - Like "*" & [FormControlName] etc).
    >>
    >> Amongst the buttons on the main form is one to open another form with
    >> three tabs. The form opens to the same record that is selected in the
    >> main form's subform. The default tab shows ALL fields of the record
    >> selected from the subform. On the second tab is another subform in form
    >> view for a different table (call it table b - it's actually a table of
    >> contact details). The third tab is irrelevant for this post.
    >>
    >> The relationship between table a and b is a one-to-many, where 1 record
    >> in table a can refer to many records in table b (i.e. a record in table a
    >> could have more than one contact name and/or address).
    >>
    >> I've been asked to incorporate a search facility on the contacts (table).
    >> It would need to locate the related record in table a (i.e. the master
    >> record). The problem is, I can't figure out the best way to do it.
    >>
    >> Ideally, I'd like to have a search form with textboxes that the user can
    >> fill in with their required search criteria. This then finds the related
    >> record in table a, based on the search just made on table b. If
    >> possible, I'd like this to requery the data on the main form's subform so
    >> the user can click open the record of their choosing. I've tried, but
    >> it's gone beyond me. Do anyone of you have any ideas on how you would go
    >> about this and what I'd need to do to accomplish it? You might think my
    >> idea is over-complicated and that there is a more straight-forward idea
    >> to fulfil this.
    >>
    >> I do hope I haven't sounded too vague in my request. If you need further
    >> detail, I'm happy to supply it.
    >>
    >> Many thanks for your time
    >> M

    >
    > I did something like this to do that... Set up a button on the form that
    > calls a query. The query will request the search term.
    >
    > In the click event of the button:
    > ===== (VBA)
    > Private Sub cmdPersonSearch_Click()
    > DoFilterButton ("qPersonSearch")
    > End Sub
    > =====
    >
    > ...and also in a class module (because the 'DoFilterButton' sub handles
    > several different searches)...
    >
    > ===== (VBA)
    > Private Sub DoFilterButton(QueryName As String)
    > On Error GoTo FilterErr
    > Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL
    >
    > Exit_DoFilterButton:
    > Exit Sub
    >
    > FilterErr:
    > MsgBox Err.Description
    > Resume Exit_DoFilterButton
    > End Sub
    > =====
    >
    > In a query named PersonSearch:
    >
    > ===== (SQL)
    > SELECT *
    > FROM A
    > WHERE A.ID IN
    > (SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
    > WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
    > =====
    >
    > Any good?
    > --
    > Smartin
     
  5. Smartin

    Smartin
    Expand Collapse
    Guest

    Someone wrote:
    > Hi
    >
    > Thanks for taking the time to respond to me.
    >
    > What you said sounds perfect. I tried incorporating it, but I'm getting the
    > following error when attempting to perform a search:
    >
    > Sub, Function, or Property not defined (Error 35)
    >
    > Can you help me resolve this?
    >
    > Thank you
    > M
    > "Smartin" <smartin108@yahoo.com> wrote in message
    > news:pKSdnVcIdeFuTubenZ2dnUVZ_vydnZ2d@giganews.com...
    >
    >>Someone wrote:
    >>
    >>>Hello
    >>>
    >>>I need to incorporate a search facility into my database. Let me give a
    >>>simple overview on how it's set up.
    >>>
    >>>There is a main form which has a subform in datasheet view. The subform
    >>>is based on a query to a table (call it table a). The reason for this is
    >>>that the main form has unbound text boxes so users can search the data in
    >>>the subform (the view is requeried to show like records on the data
    >>>entered - Like "*" & [FormControlName] etc).
    >>>
    >>>Amongst the buttons on the main form is one to open another form with
    >>>three tabs. The form opens to the same record that is selected in the
    >>>main form's subform. The default tab shows ALL fields of the record
    >>>selected from the subform. On the second tab is another subform in form
    >>>view for a different table (call it table b - it's actually a table of
    >>>contact details). The third tab is irrelevant for this post.
    >>>
    >>>The relationship between table a and b is a one-to-many, where 1 record
    >>>in table a can refer to many records in table b (i.e. a record in table a
    >>>could have more than one contact name and/or address).
    >>>
    >>>I've been asked to incorporate a search facility on the contacts (table).
    >>>It would need to locate the related record in table a (i.e. the master
    >>>record). The problem is, I can't figure out the best way to do it.
    >>>
    >>>Ideally, I'd like to have a search form with textboxes that the user can
    >>>fill in with their required search criteria. This then finds the related
    >>>record in table a, based on the search just made on table b. If
    >>>possible, I'd like this to requery the data on the main form's subform so
    >>>the user can click open the record of their choosing. I've tried, but
    >>>it's gone beyond me. Do anyone of you have any ideas on how you would go
    >>>about this and what I'd need to do to accomplish it? You might think my
    >>>idea is over-complicated and that there is a more straight-forward idea
    >>>to fulfil this.
    >>>
    >>>I do hope I haven't sounded too vague in my request. If you need further
    >>>detail, I'm happy to supply it.
    >>>
    >>>Many thanks for your time
    >>>M

    >>
    >>I did something like this to do that... Set up a button on the form that
    >>calls a query. The query will request the search term.
    >>
    >>In the click event of the button:
    >>===== (VBA)
    >>Private Sub cmdPersonSearch_Click()
    >> DoFilterButton ("qPersonSearch")
    >>End Sub
    >>=====
    >>
    >>...and also in a class module (because the 'DoFilterButton' sub handles
    >>several different searches)...
    >>
    >>===== (VBA)
    >>Private Sub DoFilterButton(QueryName As String)
    >>On Error GoTo FilterErr
    >> Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL
    >>
    >>Exit_DoFilterButton:
    >> Exit Sub
    >>
    >>FilterErr:
    >> MsgBox Err.Description
    >> Resume Exit_DoFilterButton
    >>End Sub
    >>=====
    >>
    >>In a query named PersonSearch:
    >>
    >>===== (SQL)
    >>SELECT *
    >>FROM A
    >>WHERE A.ID IN
    >>(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
    >>WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
    >>=====
    >>
    >>Any good?
    >>--
    >>Smartin

    >
    >
    >


    There is a typo in my code: the query should be named *qPersonSearch*.
    I'm not sure if that's the problem or not and don't have handy access to
    the help files to suss out Error 35. Can you break execution and tell me
    what is throwing the error?

    Also note that as coded here the button goes on the main form, not the
    subform.

    --
    Smartin
     
  6. Someone

    Someone
    Expand Collapse
    Guest

    Hi

    The code breaks on 'Private Sub btnContactSearch_Click()' (highlighted
    yellow) and 'DoFilterButton' is highlighted in blue. I'm quoting the text -
    the quotes don't appear in the code! :)

    Thanks again - I do appreciate it.

    M

    "Smartin" <smartin108@yahoo.com> wrote in message
    news:rPadnayx2KFzkuDeRVn-sA@giganews.com...
    > Someone wrote:
    >> Hi
    >>
    >> Thanks for taking the time to respond to me.
    >>
    >> What you said sounds perfect. I tried incorporating it, but I'm getting
    >> the following error when attempting to perform a search:
    >>
    >> Sub, Function, or Property not defined (Error 35)
    >>
    >> Can you help me resolve this?
    >>
    >> Thank you
    >> M
    >> "Smartin" <smartin108@yahoo.com> wrote in message
    >> news:pKSdnVcIdeFuTubenZ2dnUVZ_vydnZ2d@giganews.com...
    >>
    >>>Someone wrote:
    >>>
    >>>>Hello
    >>>>
    >>>>I need to incorporate a search facility into my database. Let me give a
    >>>>simple overview on how it's set up.
    >>>>
    >>>>There is a main form which has a subform in datasheet view. The subform
    >>>>is based on a query to a table (call it table a). The reason for this is
    >>>>that the main form has unbound text boxes so users can search the data
    >>>>in the subform (the view is requeried to show like records on the data
    >>>>entered - Like "*" & [FormControlName] etc).
    >>>>
    >>>>Amongst the buttons on the main form is one to open another form with
    >>>>three tabs. The form opens to the same record that is selected in the
    >>>>main form's subform. The default tab shows ALL fields of the record
    >>>>selected from the subform. On the second tab is another subform in form
    >>>>view for a different table (call it table b - it's actually a table of
    >>>>contact details). The third tab is irrelevant for this post.
    >>>>
    >>>>The relationship between table a and b is a one-to-many, where 1 record
    >>>>in table a can refer to many records in table b (i.e. a record in table
    >>>>a could have more than one contact name and/or address).
    >>>>
    >>>>I've been asked to incorporate a search facility on the contacts
    >>>>(table). It would need to locate the related record in table a (i.e. the
    >>>>master record). The problem is, I can't figure out the best way to do
    >>>>it.
    >>>>
    >>>>Ideally, I'd like to have a search form with textboxes that the user can
    >>>>fill in with their required search criteria. This then finds the related
    >>>>record in table a, based on the search just made on table b. If
    >>>>possible, I'd like this to requery the data on the main form's subform
    >>>>so the user can click open the record of their choosing. I've tried,
    >>>>but it's gone beyond me. Do anyone of you have any ideas on how you
    >>>>would go about this and what I'd need to do to accomplish it? You might
    >>>>think my idea is over-complicated and that there is a more
    >>>>straight-forward idea to fulfil this.
    >>>>
    >>>>I do hope I haven't sounded too vague in my request. If you need
    >>>>further detail, I'm happy to supply it.
    >>>>
    >>>>Many thanks for your time
    >>>>M
    >>>
    >>>I did something like this to do that... Set up a button on the form that
    >>>calls a query. The query will request the search term.
    >>>
    >>>In the click event of the button:
    >>>===== (VBA)
    >>>Private Sub cmdPersonSearch_Click()
    >>> DoFilterButton ("qPersonSearch")
    >>>End Sub
    >>>=====
    >>>
    >>>...and also in a class module (because the 'DoFilterButton' sub handles
    >>>several different searches)...
    >>>
    >>>===== (VBA)
    >>>Private Sub DoFilterButton(QueryName As String)
    >>>On Error GoTo FilterErr
    >>> Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL
    >>>
    >>>Exit_DoFilterButton:
    >>> Exit Sub
    >>>
    >>>FilterErr:
    >>> MsgBox Err.Description
    >>> Resume Exit_DoFilterButton
    >>>End Sub
    >>>=====
    >>>
    >>>In a query named PersonSearch:
    >>>
    >>>===== (SQL)
    >>>SELECT *
    >>>FROM A
    >>>WHERE A.ID IN
    >>>(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
    >>>WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
    >>>=====
    >>>
    >>>Any good?
    >>>--
    >>>Smartin

    >>
    >>
    >>

    >
    > There is a typo in my code: the query should be named *qPersonSearch*. I'm
    > not sure if that's the problem or not and don't have handy access to the
    > help files to suss out Error 35. Can you break execution and tell me what
    > is throwing the error?
    >
    > Also note that as coded here the button goes on the main form, not the
    > subform.
    >
    > --
    > Smartin
     
  7. Smartin

    Smartin
    Expand Collapse
    Guest

    Someone wrote:
    > Hi
    >
    > The code breaks on 'Private Sub btnContactSearch_Click()' (highlighted
    > yellow) and 'DoFilterButton' is highlighted in blue. I'm quoting the text -
    > the quotes don't appear in the code! :)
    >
    > Thanks again - I do appreciate it.
    >
    > M
    >
    > "Smartin" <smartin108@yahoo.com> wrote in message
    > news:rPadnayx2KFzkuDeRVn-sA@giganews.com...
    >
    >>Someone wrote:
    >>
    >>>Hi
    >>>
    >>>Thanks for taking the time to respond to me.
    >>>
    >>>What you said sounds perfect. I tried incorporating it, but I'm getting
    >>>the following error when attempting to perform a search:
    >>>
    >>>Sub, Function, or Property not defined (Error 35)
    >>>
    >>>Can you help me resolve this?
    >>>
    >>>Thank you
    >>>M
    >>>"Smartin" <smartin108@yahoo.com> wrote in message
    >>>news:pKSdnVcIdeFuTubenZ2dnUVZ_vydnZ2d@giganews.com...
    >>>
    >>>
    >>>>Someone wrote:
    >>>>
    >>>>
    >>>>>Hello
    >>>>>
    >>>>>I need to incorporate a search facility into my database. Let me give a
    >>>>>simple overview on how it's set up.
    >>>>>
    >>>>>There is a main form which has a subform in datasheet view. The subform
    >>>>>is based on a query to a table (call it table a). The reason for this is
    >>>>>that the main form has unbound text boxes so users can search the data
    >>>>>in the subform (the view is requeried to show like records on the data
    >>>>>entered - Like "*" & [FormControlName] etc).
    >>>>>
    >>>>>Amongst the buttons on the main form is one to open another form with
    >>>>>three tabs. The form opens to the same record that is selected in the
    >>>>>main form's subform. The default tab shows ALL fields of the record
    >>>>>selected from the subform. On the second tab is another subform in form
    >>>>>view for a different table (call it table b - it's actually a table of
    >>>>>contact details). The third tab is irrelevant for this post.
    >>>>>
    >>>>>The relationship between table a and b is a one-to-many, where 1 record
    >>>>>in table a can refer to many records in table b (i.e. a record in table
    >>>>>a could have more than one contact name and/or address).
    >>>>>
    >>>>>I've been asked to incorporate a search facility on the contacts
    >>>>>(table). It would need to locate the related record in table a (i.e. the
    >>>>>master record). The problem is, I can't figure out the best way to do
    >>>>>it.
    >>>>>
    >>>>>Ideally, I'd like to have a search form with textboxes that the user can
    >>>>>fill in with their required search criteria. This then finds the related
    >>>>>record in table a, based on the search just made on table b. If
    >>>>>possible, I'd like this to requery the data on the main form's subform
    >>>>>so the user can click open the record of their choosing. I've tried,
    >>>>>but it's gone beyond me. Do anyone of you have any ideas on how you
    >>>>>would go about this and what I'd need to do to accomplish it? You might
    >>>>>think my idea is over-complicated and that there is a more
    >>>>>straight-forward idea to fulfil this.
    >>>>>
    >>>>>I do hope I haven't sounded too vague in my request. If you need
    >>>>>further detail, I'm happy to supply it.
    >>>>>
    >>>>>Many thanks for your time
    >>>>>M
    >>>>
    >>>>I did something like this to do that... Set up a button on the form that
    >>>>calls a query. The query will request the search term.
    >>>>
    >>>>In the click event of the button:
    >>>>===== (VBA)
    >>>>Private Sub cmdPersonSearch_Click()
    >>>> DoFilterButton ("qPersonSearch")
    >>>>End Sub
    >>>>=====
    >>>>
    >>>>...and also in a class module (because the 'DoFilterButton' sub handles
    >>>>several different searches)...
    >>>>
    >>>>===== (VBA)
    >>>>Private Sub DoFilterButton(QueryName As String)
    >>>>On Error GoTo FilterErr
    >>>> Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL
    >>>>
    >>>>Exit_DoFilterButton:
    >>>> Exit Sub
    >>>>
    >>>>FilterErr:
    >>>> MsgBox Err.Description
    >>>> Resume Exit_DoFilterButton
    >>>>End Sub
    >>>>=====
    >>>>
    >>>>In a query named PersonSearch:
    >>>>
    >>>>===== (SQL)
    >>>>SELECT *
    >>>
    >>>>FROM A
    >>>
    >>>>WHERE A.ID IN
    >>>>(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
    >>>>WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
    >>>>=====
    >>>>
    >>>>Any good?
    >>>>--
    >>>>Smartin
    >>>
    >>>
    >>>

    >>There is a typo in my code: the query should be named *qPersonSearch*. I'm
    >>not sure if that's the problem or not and don't have handy access to the
    >>help files to suss out Error 35. Can you break execution and tell me what
    >>is throwing the error?
    >>
    >>Also note that as coded here the button goes on the main form, not the
    >>subform.
    >>
    >>--
    >>Smartin

    >
    >
    >


    Sorry, there's a very misleading statement in my OP. I must have been
    tired (^:

    As coded above, DoFilterButton must be in the *same* module (i.e., your
    form code) as the button click event. Alternatively, you should be able
    to place the generic sub in a separate module and refer to it explicitly
    in the click event thus:

    ===== (VBA)
    Private Sub cmdPersonSearch_Click()
    ModuleName.DoFilterButton ("qPersonSearch")
    End Sub

    Sorry about that! How are we doing now?
    --
    Smartin
     
  8. Someone

    Someone
    Expand Collapse
    Guest

    Hi!

    I don't know how much to thank you for your help. It worked and I'm so very
    grateful to you.

    Thank you for taking the time to help me resolve my problem.

    M

    "Smartin" <smartin108@yahoo.com> wrote in message
    news:Vc6dnTwUHPRaguDenZ2dnUVZ_v6dnZ2d@giganews.com...
    > Someone wrote:
    >> Hi
    >>
    >> The code breaks on 'Private Sub btnContactSearch_Click()' (highlighted
    >> yellow) and 'DoFilterButton' is highlighted in blue. I'm quoting the
    >> text - the quotes don't appear in the code! :)
    >>
    >> Thanks again - I do appreciate it.
    >>
    >> M
    >>
    >> "Smartin" <smartin108@yahoo.com> wrote in message
    >> news:rPadnayx2KFzkuDeRVn-sA@giganews.com...
    >>
    >>>Someone wrote:
    >>>
    >>>>Hi
    >>>>
    >>>>Thanks for taking the time to respond to me.
    >>>>
    >>>>What you said sounds perfect. I tried incorporating it, but I'm getting
    >>>>the following error when attempting to perform a search:
    >>>>
    >>>>Sub, Function, or Property not defined (Error 35)
    >>>>
    >>>>Can you help me resolve this?
    >>>>
    >>>>Thank you
    >>>>M
    >>>>"Smartin" <smartin108@yahoo.com> wrote in message
    >>>>news:pKSdnVcIdeFuTubenZ2dnUVZ_vydnZ2d@giganews.com...
    >>>>
    >>>>
    >>>>>Someone wrote:
    >>>>>
    >>>>>
    >>>>>>Hello
    >>>>>>
    >>>>>>I need to incorporate a search facility into my database. Let me give
    >>>>>>a simple overview on how it's set up.
    >>>>>>
    >>>>>>There is a main form which has a subform in datasheet view. The
    >>>>>>subform is based on a query to a table (call it table a). The reason
    >>>>>>for this is that the main form has unbound text boxes so users can
    >>>>>>search the data in the subform (the view is requeried to show like
    >>>>>>records on the data entered - Like "*" & [FormControlName] etc).
    >>>>>>
    >>>>>>Amongst the buttons on the main form is one to open another form with
    >>>>>>three tabs. The form opens to the same record that is selected in the
    >>>>>>main form's subform. The default tab shows ALL fields of the record
    >>>>>>selected from the subform. On the second tab is another subform in
    >>>>>>form view for a different table (call it table b - it's actually a
    >>>>>>table of contact details). The third tab is irrelevant for this post.
    >>>>>>
    >>>>>>The relationship between table a and b is a one-to-many, where 1
    >>>>>>record in table a can refer to many records in table b (i.e. a record
    >>>>>>in table a could have more than one contact name and/or address).
    >>>>>>
    >>>>>>I've been asked to incorporate a search facility on the contacts
    >>>>>>(table). It would need to locate the related record in table a (i.e.
    >>>>>>the master record). The problem is, I can't figure out the best way
    >>>>>>to do it.
    >>>>>>
    >>>>>>Ideally, I'd like to have a search form with textboxes that the user
    >>>>>>can fill in with their required search criteria. This then finds the
    >>>>>>related record in table a, based on the search just made on table b.
    >>>>>>If possible, I'd like this to requery the data on the main form's
    >>>>>>subform so the user can click open the record of their choosing. I've
    >>>>>>tried, but it's gone beyond me. Do anyone of you have any ideas on
    >>>>>>how you would go about this and what I'd need to do to accomplish it?
    >>>>>>You might think my idea is over-complicated and that there is a more
    >>>>>>straight-forward idea to fulfil this.
    >>>>>>
    >>>>>>I do hope I haven't sounded too vague in my request. If you need
    >>>>>>further detail, I'm happy to supply it.
    >>>>>>
    >>>>>>Many thanks for your time
    >>>>>>M
    >>>>>
    >>>>>I did something like this to do that... Set up a button on the form
    >>>>>that calls a query. The query will request the search term.
    >>>>>
    >>>>>In the click event of the button:
    >>>>>===== (VBA)
    >>>>>Private Sub cmdPersonSearch_Click()
    >>>>> DoFilterButton ("qPersonSearch")
    >>>>>End Sub
    >>>>>=====
    >>>>>
    >>>>>...and also in a class module (because the 'DoFilterButton' sub handles
    >>>>>several different searches)...
    >>>>>
    >>>>>===== (VBA)
    >>>>>Private Sub DoFilterButton(QueryName As String)
    >>>>>On Error GoTo FilterErr
    >>>>> Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL
    >>>>>
    >>>>>Exit_DoFilterButton:
    >>>>> Exit Sub
    >>>>>
    >>>>>FilterErr:
    >>>>> MsgBox Err.Description
    >>>>> Resume Exit_DoFilterButton
    >>>>>End Sub
    >>>>>=====
    >>>>>
    >>>>>In a query named PersonSearch:
    >>>>>
    >>>>>===== (SQL)
    >>>>>SELECT *
    >>>>
    >>>>>FROM A
    >>>>
    >>>>>WHERE A.ID IN
    >>>>>(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
    >>>>>WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
    >>>>>=====
    >>>>>
    >>>>>Any good?
    >>>>>--
    >>>>>Smartin
    >>>>
    >>>>
    >>>>
    >>>There is a typo in my code: the query should be named *qPersonSearch*.
    >>>I'm not sure if that's the problem or not and don't have handy access to
    >>>the help files to suss out Error 35. Can you break execution and tell me
    >>>what is throwing the error?
    >>>
    >>>Also note that as coded here the button goes on the main form, not the
    >>>subform.
    >>>
    >>>--
    >>>Smartin

    >>
    >>
    >>

    >
    > Sorry, there's a very misleading statement in my OP. I must have been
    > tired (^:
    >
    > As coded above, DoFilterButton must be in the *same* module (i.e., your
    > form code) as the button click event. Alternatively, you should be able to
    > place the generic sub in a separate module and refer to it explicitly in
    > the click event thus:
    >
    > ===== (VBA)
    > Private Sub cmdPersonSearch_Click()
    > ModuleName.DoFilterButton ("qPersonSearch")
    > End Sub
    >
    > Sorry about that! How are we doing now?
    > --
    > Smartin
     
  9. Smartin

    Smartin
    Expand Collapse
    Guest

    Someone wrote:
    > Hi!
    >
    > I don't know how much to thank you for your help. It worked and I'm so very
    > grateful to you.
    >
    > Thank you for taking the time to help me resolve my problem.
    >
    > M
    >


    You're welcome, and glad to know you got it working!

    --
    Smartin
     
  10. Someone

    Someone
    Expand Collapse
    Guest

    "Smartin" <smartin108@yahoo.com> wrote in message
    news:lLOdneI_gNFPzuPenZ2dnUVZ_tKdnZ2d@giganews.com...
    > Someone wrote:
    >> Hi!
    >>
    >> I don't know how much to thank you for your help. It worked and I'm so
    >> very grateful to you.
    >>
    >> Thank you for taking the time to help me resolve my problem.
    >>
    >> M
    >>

    >
    > You're welcome, and glad to know you got it working!
    >
    > --
    > Smartin


    I hope you're still here!

    The contacts form I have has 12 fields that I want to be able to search on.
    How can I incorporate this into one query? I seem to either do it wrong or
    Access complains that I've exceeded 1024 characters!

    Do you think this will be possible?

    Thanks :)
    M
     
  11. Smartin

    Smartin
    Expand Collapse
    Guest

    Someone wrote:
    > "Smartin" <smartin108@yahoo.com> wrote in message
    > news:lLOdneI_gNFPzuPenZ2dnUVZ_tKdnZ2d@giganews.com...
    >
    >>Someone wrote:
    >>
    >>>Hi!
    >>>
    >>>I don't know how much to thank you for your help. It worked and I'm so
    >>>very grateful to you.
    >>>
    >>>Thank you for taking the time to help me resolve my problem.
    >>>
    >>>M
    >>>

    >>
    >>You're welcome, and glad to know you got it working!
    >>
    >>--
    >>Smartin

    >
    >
    > I hope you're still here!
    >
    > The contacts form I have has 12 fields that I want to be able to search on.
    > How can I incorporate this into one query? I seem to either do it wrong or
    > Access complains that I've exceeded 1024 characters!
    >
    > Do you think this will be possible?
    >
    > Thanks :)
    > M
    >
    >


    I'm not quite sure what you mean by this... you want to search 12 fields
    for the same term? This sounds like an ugly design, and I hope it's not
    what you mean, but such a search could be accomplished by modifying the
    query:

    ===== (SQL)
    PARAMETERS [Enter a name to search for] Text;
    SELECT *
    FROM A
    WHERE A.ID IN
    (SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
    WHERE
    B.PersonName LIKE '*' & [Enter a name to search for] & '*') OR
    B.Something1 LIKE '*' & [Enter a name to search for] & '*') OR
    B.Something2 LIKE '*' & [Enter a name to search for] & '*') OR
    ....repeat ad nauseum...
    ;
    =====

    Clarify?
    --
    Smartin
     
  12. Someone

    Someone
    Expand Collapse
    Guest

    "Smartin" <smartin108@yahoo.com> wrote in message
    news:WfSdnatXv5aiIePenZ2dnUVZ_t2dnZ2d@giganews.com...
    > Someone wrote:
    >> "Smartin" <smartin108@yahoo.com> wrote in message
    >> news:lLOdneI_gNFPzuPenZ2dnUVZ_tKdnZ2d@giganews.com...
    >>
    >>>Someone wrote:
    >>>
    >>>>Hi!
    >>>>
    >>>>I don't know how much to thank you for your help. It worked and I'm so
    >>>>very grateful to you.
    >>>>
    >>>>Thank you for taking the time to help me resolve my problem.
    >>>>
    >>>>M
    >>>>
    >>>
    >>>You're welcome, and glad to know you got it working!
    >>>
    >>>--
    >>>Smartin

    >>
    >>
    >> I hope you're still here!
    >>
    >> The contacts form I have has 12 fields that I want to be able to search
    >> on. How can I incorporate this into one query? I seem to either do it
    >> wrong or Access complains that I've exceeded 1024 characters!
    >>
    >> Do you think this will be possible?
    >>
    >> Thanks :)
    >> M

    >
    > I'm not quite sure what you mean by this... you want to search 12 fields
    > for the same term? This sounds like an ugly design, and I hope it's not
    > what you mean, but such a search could be accomplished by modifying the
    > query:
    >
    > ===== (SQL)
    > PARAMETERS [Enter a name to search for] Text;
    > SELECT *
    > FROM A
    > WHERE A.ID IN
    > (SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
    > WHERE
    > B.PersonName LIKE '*' & [Enter a name to search for] & '*') OR
    > B.Something1 LIKE '*' & [Enter a name to search for] & '*') OR
    > B.Something2 LIKE '*' & [Enter a name to search for] & '*') OR
    > ...repeat ad nauseum...
    > ;
    > =====
    >
    > Clarify?
    > --
    > Smartin


    Hello again

    What I meant to say was that the form has 12 different fields (contact,
    jobtitle, address1, address2, town, county, postcode, country, telephone,
    fax, email, website).

    I'd like for the user to be able to enter criteria into any one or more of
    these fields to find matching/like records.

    I tried the idea you mentioned above, but the records returned for any
    search in any field are the same, which isn't correct.

    One point: in your SQL statement, you've put the following:

    (SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID

    Did you actually mean B.AID or B.ID? If you did mean B.AID, how do I write
    that in SQL?

    Thanks!
    M
     

Share This Page