Welcome to SPN

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

Sign Up Now!

Best way to handle Find in Subform

Discussion in 'Information Technology' started by Robert, Jul 28, 2006.

  1. Robert

    Robert
    Expand Collapse
    Guest

    We recently changed a database to using subform.

    After the change, when end user attempts to search a record in the subform,
    as it only shows those records related to that in main form, he is not
    successfully to search as before. That is fully understandable.

    However, he would like to find the record even though by searching a certain
    field in the subform so that both record in main form and subform changes
    (according to the record searched in the subform).

    Is there any way to do so OR just tell him that it is not possible ?

    Your advice is sought.

    Thanks
     
  2. Loading...

    Similar Threads Forum Date
    and finally, the best quote of them all Blogs Oct 17, 2015
    Sikhism, the best kept secret in the world Blogs Oct 17, 2015
    He’s The World’s Best Water Photographer… And He’s Just Released These Haunting Images. Sikh Sikhi Sikhism Aug 30, 2015
    General Best English Sikh History Books History of Sikhism Sep 21, 2013
    The best day of my life, ever! Inspirational Stories Aug 30, 2013

  3. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    Hum, that is going to be a bit hard to do...and, likely a bit confusing
    also...

    I would consider building a nice search form. You could join the main table
    + child table, and display the results in a grid...

    (so, main table fields, or child tables fields could be searched in the
    form...you could even use ctrl-f in this case..).

    Or, you could build somthing that prompts the two fields...

    Here is a few screen shots of what I mean:

    http://www.members.shaw.ca/AlbertKallal/Search/index.html

    And, you could even have some type of drill down...here is some more screens
    that might give you some ideas....

    http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  4. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The data structure behind a form/subform usually consists of 2 tables:
    a) the main form's table, with a primary key field;
    b) the subform's table, with a foreign key field to the main form's table.

    Since the subform's table relates to the primary key of the main form's
    table, the first thing you need to do is discover what the foreign key value
    is for the record you want to match. You can then find that record in the
    main form. At that point Access loads all the related records in the
    subform, so you can then find the record you want in the subform.

    The example below works with the Customer Orders form in Northwind. This
    form is bound to the Customers table, with Orders for the customer in the
    subform. If you add an unbound text box to the main form to enter the order
    number you want to see, it has to lookup which customer this is for, locate
    that record in the main form, and then choose the right order in the
    subform. The code assumed you added a text box named txtFindOrder to the
    main form (and set the form's AllowEdits property to Yes so you can enter a
    number to find):

    Private Sub txtFindOrder_AfterUpdate()
    Dim rs As DAO.Recordset
    Dim strWhere As String
    Dim varResult As Variant

    If Not IsNull(Me.txtFindOrder) Then
    If Me.Dirty Then 'Save first
    Me.Dirty = False
    End If
    'Lookup the customer for this order.
    strWhere = "OrderID = " & Me.txtFindOrder
    varResult = DLookup("CustomerID", "Orders", strWhere)
    If IsNull(varResult) Then
    MsgBox "No such order."
    Else

    'Find the record in the main form.
    strWhere = "CustomerID = """ & varResult & """"
    Set rs = Me.RecordsetClone
    rs.FindFirst strWhere
    If rs.NoMatch Then
    MsgBox "Customer not found. Is form filtered?"
    Else
    Me.Bookmark = rs.Bookmark
    Set rs = Nothing

    'Now find in the subform.
    strWhere = "OrderID = " & Me.txtFindOrder
    With Me.[Customer Orders Subform1].Form
    Set rs = .RecordsetClone
    rs.FindFirst strWhere
    If rs.NoMatch Then
    MsgBox "Not found in subform"
    Else
    .Bookmark = rs.Bookmark
    End If
    End With
    End If
    End If
    End If
    Set rs = Nothing
    End Sub

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Robert" <Robert@discussions.microsoft.com> wrote in message
    news:emFOdUNeGHA.1208@TK2MSFTNGP02.phx.gbl...
    > We recently changed a database to using subform.
    >
    > After the change, when end user attempts to search a record in the
    > subform, as it only shows those records related to that in main form, he
    > is not successfully to search as before. That is fully understandable.
    >
    > However, he would like to find the record even though by searching a
    > certain field in the subform so that both record in main form and subform
    > changes (according to the record searched in the subform).
    >
    > Is there any way to do so OR just tell him that it is not possible ?
    >
    > Your advice is sought.
    >
    > Thanks
     
  5. Robert

    Robert
    Expand Collapse
    Guest

    Dear Allen,

    Thank you for your reply and I have tried your suggestion in the Northwind
    database.

    However, my case is more complicated than the sample. It is a database with
    Equipment and Lessee information (1:M). End user wants to use Control + F
    in the subform (Lessee) to find the Equipment in the main form. Of course,
    we can provide a text box in the main form just like the sample.

    However, I find that I am not able to amend your sample to one that search a
    text field (Like - They enter "Browne" and find that Equipment is leased by
    him.

    Your advice is sought

    Robert

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:%23%232zbmNeGHA.1272@TK2MSFTNGP03.phx.gbl...
    > The data structure behind a form/subform usually consists of 2 tables:
    > a) the main form's table, with a primary key field;
    > b) the subform's table, with a foreign key field to the main form's table.
    >
    > Since the subform's table relates to the primary key of the main form's
    > table, the first thing you need to do is discover what the foreign key
    > value is for the record you want to match. You can then find that record
    > in the main form. At that point Access loads all the related records in
    > the subform, so you can then find the record you want in the subform.
    >
    > The example below works with the Customer Orders form in Northwind. This
    > form is bound to the Customers table, with Orders for the customer in the
    > subform. If you add an unbound text box to the main form to enter the
    > order number you want to see, it has to lookup which customer this is for,
    > locate that record in the main form, and then choose the right order in
    > the subform. The code assumed you added a text box named txtFindOrder to
    > the main form (and set the form's AllowEdits property to Yes so you can
    > enter a number to find):
    >
    > Private Sub txtFindOrder_AfterUpdate()
    > Dim rs As DAO.Recordset
    > Dim strWhere As String
    > Dim varResult As Variant
    >
    > If Not IsNull(Me.txtFindOrder) Then
    > If Me.Dirty Then 'Save first
    > Me.Dirty = False
    > End If
    > 'Lookup the customer for this order.
    > strWhere = "OrderID = " & Me.txtFindOrder
    > varResult = DLookup("CustomerID", "Orders", strWhere)
    > If IsNull(varResult) Then
    > MsgBox "No such order."
    > Else
    >
    > 'Find the record in the main form.
    > strWhere = "CustomerID = """ & varResult & """"
    > Set rs = Me.RecordsetClone
    > rs.FindFirst strWhere
    > If rs.NoMatch Then
    > MsgBox "Customer not found. Is form filtered?"
    > Else
    > Me.Bookmark = rs.Bookmark
    > Set rs = Nothing
    >
    > 'Now find in the subform.
    > strWhere = "OrderID = " & Me.txtFindOrder
    > With Me.[Customer Orders Subform1].Form
    > Set rs = .RecordsetClone
    > rs.FindFirst strWhere
    > If rs.NoMatch Then
    > MsgBox "Not found in subform"
    > Else
    > .Bookmark = rs.Bookmark
    > End If
    > End With
    > End If
    > End If
    > End If
    > Set rs = Nothing
    > End Sub
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Robert" <Robert@discussions.microsoft.com> wrote in message
    > news:emFOdUNeGHA.1208@TK2MSFTNGP02.phx.gbl...
    >> We recently changed a database to using subform.
    >>
    >> After the change, when end user attempts to search a record in the
    >> subform, as it only shows those records related to that in main form, he
    >> is not successfully to search as before. That is fully understandable.
    >>
    >> However, he would like to find the record even though by searching a
    >> certain field in the subform so that both record in main form and subform
    >> changes (according to the record searched in the subform).
    >>
    >> Is there any way to do so OR just tell him that it is not possible ?
    >>
    >> Your advice is sought.
    >>
    >> Thanks

    >
    >
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    So is the Browne in the foreign key, or do you need to look it up?

    You can look up a text value if you add quotes the the Criteria string,
    e.g.:
    strWhere = "ClientID = """ & Me.txtName & """"

    Unless you have many thousands of clients, you could use an unbound combo
    for the user to select the name.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Robert" <Robert@discussions.microsoft.com> wrote in message
    news:eyJNmEyeGHA.1856@TK2MSFTNGP03.phx.gbl...
    > Dear Allen,
    >
    > Thank you for your reply and I have tried your suggestion in the Northwind
    > database.
    >
    > However, my case is more complicated than the sample. It is a database
    > with Equipment and Lessee information (1:M). End user wants to use
    > Control + F in the subform (Lessee) to find the Equipment in the main
    > form. Of course, we can provide a text box in the main form just like the
    > sample.
    >
    > However, I find that I am not able to amend your sample to one that search
    > a text field (Like - They enter "Browne" and find that Equipment is leased
    > by him.
    >
    > Your advice is sought
    >
    > Robert
    >
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:%23%232zbmNeGHA.1272@TK2MSFTNGP03.phx.gbl...
    >> The data structure behind a form/subform usually consists of 2 tables:
    >> a) the main form's table, with a primary key field;
    >> b) the subform's table, with a foreign key field to the main form's
    >> table.
    >>
    >> Since the subform's table relates to the primary key of the main form's
    >> table, the first thing you need to do is discover what the foreign key
    >> value is for the record you want to match. You can then find that record
    >> in the main form. At that point Access loads all the related records in
    >> the subform, so you can then find the record you want in the subform.
    >>
    >> The example below works with the Customer Orders form in Northwind. This
    >> form is bound to the Customers table, with Orders for the customer in the
    >> subform. If you add an unbound text box to the main form to enter the
    >> order number you want to see, it has to lookup which customer this is
    >> for, locate that record in the main form, and then choose the right order
    >> in the subform. The code assumed you added a text box named txtFindOrder
    >> to the main form (and set the form's AllowEdits property to Yes so you
    >> can enter a number to find):
    >>
    >> Private Sub txtFindOrder_AfterUpdate()
    >> Dim rs As DAO.Recordset
    >> Dim strWhere As String
    >> Dim varResult As Variant
    >>
    >> If Not IsNull(Me.txtFindOrder) Then
    >> If Me.Dirty Then 'Save first
    >> Me.Dirty = False
    >> End If
    >> 'Lookup the customer for this order.
    >> strWhere = "OrderID = " & Me.txtFindOrder
    >> varResult = DLookup("CustomerID", "Orders", strWhere)
    >> If IsNull(varResult) Then
    >> MsgBox "No such order."
    >> Else
    >>
    >> 'Find the record in the main form.
    >> strWhere = "CustomerID = """ & varResult & """"
    >> Set rs = Me.RecordsetClone
    >> rs.FindFirst strWhere
    >> If rs.NoMatch Then
    >> MsgBox "Customer not found. Is form filtered?"
    >> Else
    >> Me.Bookmark = rs.Bookmark
    >> Set rs = Nothing
    >>
    >> 'Now find in the subform.
    >> strWhere = "OrderID = " & Me.txtFindOrder
    >> With Me.[Customer Orders Subform1].Form
    >> Set rs = .RecordsetClone
    >> rs.FindFirst strWhere
    >> If rs.NoMatch Then
    >> MsgBox "Not found in subform"
    >> Else
    >> .Bookmark = rs.Bookmark
    >> End If
    >> End With
    >> End If
    >> End If
    >> End If
    >> Set rs = Nothing
    >> End Sub
    >>
    >> "Robert" <Robert@discussions.microsoft.com> wrote in message
    >> news:emFOdUNeGHA.1208@TK2MSFTNGP02.phx.gbl...
    >>> We recently changed a database to using subform.
    >>>
    >>> After the change, when end user attempts to search a record in the
    >>> subform, as it only shows those records related to that in main form, he
    >>> is not successfully to search as before. That is fully understandable.
    >>>
    >>> However, he would like to find the record even though by searching a
    >>> certain field in the subform so that both record in main form and
    >>> subform changes (according to the record searched in the subform).
    >>>
    >>> Is there any way to do so OR just tell him that it is not possible ?
     
  7. Robert

    Robert
    Expand Collapse
    Guest

    Dear Allen,

    Thank you for your advice.

    I have two more questions
    1) How to handle the case if there is more than 1 lessee- Like there are
    more than 1 Allen ?
    2) If I create an unbound combo box, how can I populate the newly added
    lessee to that combo box ?

    Thanks
    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:%23cEj7NzeGHA.4892@TK2MSFTNGP02.phx.gbl...
    > So is the Browne in the foreign key, or do you need to look it up?
    >
    > You can look up a text value if you add quotes the the Criteria string,
    > e.g.:
    > strWhere = "ClientID = """ & Me.txtName & """"
    >
    > Unless you have many thousands of clients, you could use an unbound combo
    > for the user to select the name.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Robert" <Robert@discussions.microsoft.com> wrote in message
    > news:eyJNmEyeGHA.1856@TK2MSFTNGP03.phx.gbl...
    >> Dear Allen,
    >>
    >> Thank you for your reply and I have tried your suggestion in the
    >> Northwind database.
    >>
    >> However, my case is more complicated than the sample. It is a database
    >> with Equipment and Lessee information (1:M). End user wants to use
    >> Control + F in the subform (Lessee) to find the Equipment in the main
    >> form. Of course, we can provide a text box in the main form just like
    >> the sample.
    >>
    >> However, I find that I am not able to amend your sample to one that
    >> search a text field (Like - They enter "Browne" and find that Equipment
    >> is leased by him.
    >>
    >> Your advice is sought
    >>
    >> Robert
    >>
    >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >> news:%23%232zbmNeGHA.1272@TK2MSFTNGP03.phx.gbl...
    >>> The data structure behind a form/subform usually consists of 2 tables:
    >>> a) the main form's table, with a primary key field;
    >>> b) the subform's table, with a foreign key field to the main form's
    >>> table.
    >>>
    >>> Since the subform's table relates to the primary key of the main form's
    >>> table, the first thing you need to do is discover what the foreign key
    >>> value is for the record you want to match. You can then find that record
    >>> in the main form. At that point Access loads all the related records in
    >>> the subform, so you can then find the record you want in the subform.
    >>>
    >>> The example below works with the Customer Orders form in Northwind. This
    >>> form is bound to the Customers table, with Orders for the customer in
    >>> the subform. If you add an unbound text box to the main form to enter
    >>> the order number you want to see, it has to lookup which customer this
    >>> is for, locate that record in the main form, and then choose the right
    >>> order in the subform. The code assumed you added a text box named
    >>> txtFindOrder to the main form (and set the form's AllowEdits property to
    >>> Yes so you can enter a number to find):
    >>>
    >>> Private Sub txtFindOrder_AfterUpdate()
    >>> Dim rs As DAO.Recordset
    >>> Dim strWhere As String
    >>> Dim varResult As Variant
    >>>
    >>> If Not IsNull(Me.txtFindOrder) Then
    >>> If Me.Dirty Then 'Save first
    >>> Me.Dirty = False
    >>> End If
    >>> 'Lookup the customer for this order.
    >>> strWhere = "OrderID = " & Me.txtFindOrder
    >>> varResult = DLookup("CustomerID", "Orders", strWhere)
    >>> If IsNull(varResult) Then
    >>> MsgBox "No such order."
    >>> Else
    >>>
    >>> 'Find the record in the main form.
    >>> strWhere = "CustomerID = """ & varResult & """"
    >>> Set rs = Me.RecordsetClone
    >>> rs.FindFirst strWhere
    >>> If rs.NoMatch Then
    >>> MsgBox "Customer not found. Is form filtered?"
    >>> Else
    >>> Me.Bookmark = rs.Bookmark
    >>> Set rs = Nothing
    >>>
    >>> 'Now find in the subform.
    >>> strWhere = "OrderID = " & Me.txtFindOrder
    >>> With Me.[Customer Orders Subform1].Form
    >>> Set rs = .RecordsetClone
    >>> rs.FindFirst strWhere
    >>> If rs.NoMatch Then
    >>> MsgBox "Not found in subform"
    >>> Else
    >>> .Bookmark = rs.Bookmark
    >>> End If
    >>> End With
    >>> End If
    >>> End If
    >>> End If
    >>> Set rs = Nothing
    >>> End Sub
    >>>
    >>> "Robert" <Robert@discussions.microsoft.com> wrote in message
    >>> news:emFOdUNeGHA.1208@TK2MSFTNGP02.phx.gbl...
    >>>> We recently changed a database to using subform.
    >>>>
    >>>> After the change, when end user attempts to search a record in the
    >>>> subform, as it only shows those records related to that in main form,
    >>>> he is not successfully to search as before. That is fully
    >>>> understandable.
    >>>>
    >>>> However, he would like to find the record even though by searching a
    >>>> certain field in the subform so that both record in main form and
    >>>> subform changes (according to the record searched in the subform).
    >>>>
    >>>> Is there any way to do so OR just tell him that it is not possible ?

    >
    >
     
  8. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    A1: Set the RowSource of your combo to query such as this:
    SELECT ClientID, Surname & ", " & FirstName AS FullName FROM tblClient
    ORDER BY Surname, FirstName;
    The query combines the name fields into one field for the user choose from.

    A2: Use the AfterUpdate event of the form where new entries are made to
    Requery the combo. Example:
    Private Sub Form_AfterUpdate()
    Forms!Form2.MyCombo.Requery
    End Sub

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Robert" <Robert@discussions.microsoft.com> wrote in message
    news:%23YZ9UnXfGHA.1208@TK2MSFTNGP02.phx.gbl...
    > Dear Allen,
    >
    > Thank you for your advice.
    >
    > I have two more questions
    > 1) How to handle the case if there is more than 1 lessee- Like there are
    > more than 1 Allen ?
    > 2) If I create an unbound combo box, how can I populate the newly added
    > lessee to that combo box ?
    >
    > Thanks
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:%23cEj7NzeGHA.4892@TK2MSFTNGP02.phx.gbl...
    >> So is the Browne in the foreign key, or do you need to look it up?
    >>
    >> You can look up a text value if you add quotes the the Criteria string,
    >> e.g.:
    >> strWhere = "ClientID = """ & Me.txtName & """"
    >>
    >> Unless you have many thousands of clients, you could use an unbound combo
    >> for the user to select the name.
    >>
    >> --
    >> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >> Tips for Access users - http://allenbrowne.com/tips.html
    >> Reply to group, rather than allenbrowne at mvps dot org.
    >>
    >> "Robert" <Robert@discussions.microsoft.com> wrote in message
    >> news:eyJNmEyeGHA.1856@TK2MSFTNGP03.phx.gbl...
    >>> Dear Allen,
    >>>
    >>> Thank you for your reply and I have tried your suggestion in the
    >>> Northwind database.
    >>>
    >>> However, my case is more complicated than the sample. It is a database
    >>> with Equipment and Lessee information (1:M). End user wants to use
    >>> Control + F in the subform (Lessee) to find the Equipment in the main
    >>> form. Of course, we can provide a text box in the main form just like
    >>> the sample.
    >>>
    >>> However, I find that I am not able to amend your sample to one that
    >>> search a text field (Like - They enter "Browne" and find that Equipment
    >>> is leased by him.
    >>>
    >>> Your advice is sought
    >>>
    >>> Robert
    >>>
    >>> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >>> news:%23%232zbmNeGHA.1272@TK2MSFTNGP03.phx.gbl...
    >>>> The data structure behind a form/subform usually consists of 2 tables:
    >>>> a) the main form's table, with a primary key field;
    >>>> b) the subform's table, with a foreign key field to the main form's
    >>>> table.
    >>>>
    >>>> Since the subform's table relates to the primary key of the main form's
    >>>> table, the first thing you need to do is discover what the foreign key
    >>>> value is for the record you want to match. You can then find that
    >>>> record in the main form. At that point Access loads all the related
    >>>> records in the subform, so you can then find the record you want in the
    >>>> subform.
    >>>>
    >>>> The example below works with the Customer Orders form in Northwind.
    >>>> This form is bound to the Customers table, with Orders for the customer
    >>>> in the subform. If you add an unbound text box to the main form to
    >>>> enter the order number you want to see, it has to lookup which customer
    >>>> this is for, locate that record in the main form, and then choose the
    >>>> right order in the subform. The code assumed you added a text box named
    >>>> txtFindOrder to the main form (and set the form's AllowEdits property
    >>>> to Yes so you can enter a number to find):
    >>>>
    >>>> Private Sub txtFindOrder_AfterUpdate()
    >>>> Dim rs As DAO.Recordset
    >>>> Dim strWhere As String
    >>>> Dim varResult As Variant
    >>>>
    >>>> If Not IsNull(Me.txtFindOrder) Then
    >>>> If Me.Dirty Then 'Save first
    >>>> Me.Dirty = False
    >>>> End If
    >>>> 'Lookup the customer for this order.
    >>>> strWhere = "OrderID = " & Me.txtFindOrder
    >>>> varResult = DLookup("CustomerID", "Orders", strWhere)
    >>>> If IsNull(varResult) Then
    >>>> MsgBox "No such order."
    >>>> Else
    >>>>
    >>>> 'Find the record in the main form.
    >>>> strWhere = "CustomerID = """ & varResult & """"
    >>>> Set rs = Me.RecordsetClone
    >>>> rs.FindFirst strWhere
    >>>> If rs.NoMatch Then
    >>>> MsgBox "Customer not found. Is form filtered?"
    >>>> Else
    >>>> Me.Bookmark = rs.Bookmark
    >>>> Set rs = Nothing
    >>>>
    >>>> 'Now find in the subform.
    >>>> strWhere = "OrderID = " & Me.txtFindOrder
    >>>> With Me.[Customer Orders Subform1].Form
    >>>> Set rs = .RecordsetClone
    >>>> rs.FindFirst strWhere
    >>>> If rs.NoMatch Then
    >>>> MsgBox "Not found in subform"
    >>>> Else
    >>>> .Bookmark = rs.Bookmark
    >>>> End If
    >>>> End With
    >>>> End If
    >>>> End If
    >>>> End If
    >>>> Set rs = Nothing
    >>>> End Sub
    >>>>
    >>>> "Robert" <Robert@discussions.microsoft.com> wrote in message
    >>>> news:emFOdUNeGHA.1208@TK2MSFTNGP02.phx.gbl...
    >>>>> We recently changed a database to using subform.
    >>>>>
    >>>>> After the change, when end user attempts to search a record in the
    >>>>> subform, as it only shows those records related to that in main form,
    >>>>> he is not successfully to search as before. That is fully
    >>>>> understandable.
    >>>>>
    >>>>> However, he would like to find the record even though by searching a
    >>>>> certain field in the subform so that both record in main form and
    >>>>> subform changes (according to the record searched in the subform).
    >>>>>
    >>>>> Is there any way to do so OR just tell him that it is not possible ?
     
  9. Robert

    Robert
    Expand Collapse
    Guest

    Dear Allen,

    I have setup the combo box successfully. Thank you for your help.

    Regards,
    Robert

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:uFOQF4XfGHA.4080@TK2MSFTNGP03.phx.gbl...
    > A1: Set the RowSource of your combo to query such as this:
    > SELECT ClientID, Surname & ", " & FirstName AS FullName FROM tblClient
    > ORDER BY Surname, FirstName;
    > The query combines the name fields into one field for the user choose
    > from.
    >
    > A2: Use the AfterUpdate event of the form where new entries are made to
    > Requery the combo. Example:
    > Private Sub Form_AfterUpdate()
    > Forms!Form2.MyCombo.Requery
    > End Sub
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Robert" <Robert@discussions.microsoft.com> wrote in message
    > news:%23YZ9UnXfGHA.1208@TK2MSFTNGP02.phx.gbl...
    >> Dear Allen,
    >>
    >> Thank you for your advice.
    >>
    >> I have two more questions
    >> 1) How to handle the case if there is more than 1 lessee- Like there are
    >> more than 1 Allen ?
    >> 2) If I create an unbound combo box, how can I populate the newly added
    >> lessee to that combo box ?
    >>
    >> Thanks
    >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >> news:%23cEj7NzeGHA.4892@TK2MSFTNGP02.phx.gbl...
    >>> So is the Browne in the foreign key, or do you need to look it up?
    >>>
    >>> You can look up a text value if you add quotes the the Criteria string,
    >>> e.g.:
    >>> strWhere = "ClientID = """ & Me.txtName & """"
    >>>
    >>> Unless you have many thousands of clients, you could use an unbound
    >>> combo for the user to select the name.
    >>>
    >>> --
    >>> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >>> Tips for Access users - http://allenbrowne.com/tips.html
    >>> Reply to group, rather than allenbrowne at mvps dot org.
    >>>
    >>> "Robert" <Robert@discussions.microsoft.com> wrote in message
    >>> news:eyJNmEyeGHA.1856@TK2MSFTNGP03.phx.gbl...
    >>>> Dear Allen,
    >>>>
    >>>> Thank you for your reply and I have tried your suggestion in the
    >>>> Northwind database.
    >>>>
    >>>> However, my case is more complicated than the sample. It is a database
    >>>> with Equipment and Lessee information (1:M). End user wants to use
    >>>> Control + F in the subform (Lessee) to find the Equipment in the main
    >>>> form. Of course, we can provide a text box in the main form just like
    >>>> the sample.
    >>>>
    >>>> However, I find that I am not able to amend your sample to one that
    >>>> search a text field (Like - They enter "Browne" and find that Equipment
    >>>> is leased by him.
    >>>>
    >>>> Your advice is sought
    >>>>
    >>>> Robert
    >>>>
    >>>> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >>>> news:%23%232zbmNeGHA.1272@TK2MSFTNGP03.phx.gbl...
    >>>>> The data structure behind a form/subform usually consists of 2 tables:
    >>>>> a) the main form's table, with a primary key field;
    >>>>> b) the subform's table, with a foreign key field to the main form's
    >>>>> table.
    >>>>>
    >>>>> Since the subform's table relates to the primary key of the main
    >>>>> form's table, the first thing you need to do is discover what the
    >>>>> foreign key value is for the record you want to match. You can then
    >>>>> find that record in the main form. At that point Access loads all the
    >>>>> related records in the subform, so you can then find the record you
    >>>>> want in the subform.
    >>>>>
    >>>>> The example below works with the Customer Orders form in Northwind.
    >>>>> This form is bound to the Customers table, with Orders for the
    >>>>> customer in the subform. If you add an unbound text box to the main
    >>>>> form to enter the order number you want to see, it has to lookup which
    >>>>> customer this is for, locate that record in the main form, and then
    >>>>> choose the right order in the subform. The code assumed you added a
    >>>>> text box named txtFindOrder to the main form (and set the form's
    >>>>> AllowEdits property to Yes so you can enter a number to find):
    >>>>>
    >>>>> Private Sub txtFindOrder_AfterUpdate()
    >>>>> Dim rs As DAO.Recordset
    >>>>> Dim strWhere As String
    >>>>> Dim varResult As Variant
    >>>>>
    >>>>> If Not IsNull(Me.txtFindOrder) Then
    >>>>> If Me.Dirty Then 'Save first
    >>>>> Me.Dirty = False
    >>>>> End If
    >>>>> 'Lookup the customer for this order.
    >>>>> strWhere = "OrderID = " & Me.txtFindOrder
    >>>>> varResult = DLookup("CustomerID", "Orders", strWhere)
    >>>>> If IsNull(varResult) Then
    >>>>> MsgBox "No such order."
    >>>>> Else
    >>>>>
    >>>>> 'Find the record in the main form.
    >>>>> strWhere = "CustomerID = """ & varResult & """"
    >>>>> Set rs = Me.RecordsetClone
    >>>>> rs.FindFirst strWhere
    >>>>> If rs.NoMatch Then
    >>>>> MsgBox "Customer not found. Is form filtered?"
    >>>>> Else
    >>>>> Me.Bookmark = rs.Bookmark
    >>>>> Set rs = Nothing
    >>>>>
    >>>>> 'Now find in the subform.
    >>>>> strWhere = "OrderID = " & Me.txtFindOrder
    >>>>> With Me.[Customer Orders Subform1].Form
    >>>>> Set rs = .RecordsetClone
    >>>>> rs.FindFirst strWhere
    >>>>> If rs.NoMatch Then
    >>>>> MsgBox "Not found in subform"
    >>>>> Else
    >>>>> .Bookmark = rs.Bookmark
    >>>>> End If
    >>>>> End With
    >>>>> End If
    >>>>> End If
    >>>>> End If
    >>>>> Set rs = Nothing
    >>>>> End Sub
    >>>>>
    >>>>> "Robert" <Robert@discussions.microsoft.com> wrote in message
    >>>>> news:emFOdUNeGHA.1208@TK2MSFTNGP02.phx.gbl...
    >>>>>> We recently changed a database to using subform.
    >>>>>>
    >>>>>> After the change, when end user attempts to search a record in the
    >>>>>> subform, as it only shows those records related to that in main form,
    >>>>>> he is not successfully to search as before. That is fully
    >>>>>> understandable.
    >>>>>>
    >>>>>> However, he would like to find the record even though by searching a
    >>>>>> certain field in the subform so that both record in main form and
    >>>>>> subform changes (according to the record searched in the subform).
    >>>>>>
    >>>>>> Is there any way to do so OR just tell him that it is not possible ?

    >
    >
     

Share This Page