Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Matching records on sub form (2000)

    What l am trying to is to and get the records in the sub form matched up with the records in the main form.
    So that when the record button is clicked on in the sub form the corresponding record appears in the main form.

    This does work currently but not when there is more than one record in the sub form.
    l cannot work out a way to get corresponding records to be shown on the main form.
    see attachment for an example.

    Justin.
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching records on sub form (2000)

    The usual setup is that the subform is linked to the main form through the Link Master Fields and Link Child Fields properties of the subform as a control on the main form. This ensures that as the user moves from record to record in the main form, the subform will only display records associated with the record in the main form.

    You seem to want to do the reverse. I don't think the main form / subform setup is well suited for that. Why exactly do you want it this way?

  3. #3
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching records on sub form (2000)

    I thought this was the case and that the main form / sub form is not well suited for what l am trying to do.

    I have tried to illustrate as to what l am trying to do.
    I hope this helps.
    Aplogise if the example is a bit on the large size.

    Justin.
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching records on sub form (2000)

    As far as I can see, you should make the main form and subform change places. What is now the subform should become the main form, and vice versa. If you link them correctly, you should get the behaviour you want automatically.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching records on sub form (2000)

    I have tried this but am still faced as with a similiar problem as before.
    If you see the attachment you will see what l mean.

    Justin.
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching records on sub form (2000)

    The main record will never change when you move to another record in the subform. But now that you have exchanged the position of main and subform, that should not be relevant any more. What you originally wanted to do, was to be able to move to another record in the subform, and have the main form follow it. In the new situation, you move to a new record in the main form, and the subform should follow it automatically.

  7. #7
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching records on sub form (2000)

    I think l may have not expained myself clearly enough.
    What if there is more than one record in the sub form and it does not relant to the record in the main form.
    i.e. If l click on the record button in the sub form to go to the next record it should correspond with SI Ref "456" in the main form as this is the next record in the main form.
    This works fine when there is only one record in the sub form but not if there is more than one record.

    Apologies if l have not explained myself clearly enough previously.

    If this is not possible is there any alternative work around so to speak?

    Justin.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching records on sub form (2000)

    Let's take a step back, and not talk about main forms and subforms for the moment.

    You have two sets of data, let's say DataA and DataB (they can be tables or queries). There is some kind of relationship between DataA and DataB. Take a look at the following descriptions:
    1. <LI>Each record in DataA corresponds to a unique record in DataB, and each record in DataB corresponds to a unique record in DataA.
      <LI>Each record in DataA corresponds to a unique record in DataB, but there may be several records in DataA that correspond to the same record in DataB. For example, think of DataA as orders, and of DataB as customers. Each order belongs to a specific customer, but there may be several orders for the same customer.
      <LI>Each record in DataB corresponds to a unique record in DataA, but there may be several records in DataB that correspond to the same record in DataA. This is essentially the same as the previous case, but with DataA and DataB reversed.
      <LI>There may be several records in DataB that correspond to a specific record in DataA, and there may be several records in DataA that correspond to a specific record in DataB. For example, think of DataA as members of a club or organisation, and of DataB as a list of hobbies. Each person may have several hobbies, and several persons may have the same hobby.
      <LI>None of the above, there is no clear relationship between the records of DataA and DataB.
    Can you indicate which description resembles your situation?

  9. #9
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching records on sub form (2000)

    The scenerio which l can best describe it as is:

    - DataA (customers) DataB (orders)
    One customer can have many orders and one order can have many customers.
    Each order corrspondances to a unique record with the customer field.
    I would describe it as a "many to many" relationship.

    I am not to sure if you described this scenerio or not.
    Justin.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching records on sub form (2000)

    In a many-to-many relationship, you need an intermediate table. In the orders - customers example: if, as you write, "One customer can have many orders and one order can have many customers", you need a table that contains OrderID - CustomerID pairs.

    When displaying such a relationship, you must decide whether you want to use the customer or the order as basic unit.

    If you use customers, you create a main form based on the customers table, and a subform based on a query that combines the intermediate table and the orders table. For each customer, the subform only displays the orders for that customer. You cannot navigate to orders for another customer in the subform, you must go to another customer in the main form first.

    If you use orders as basic unit, it is similar, but with the role of orders and customers reversed. You would have a second main form and subform.

    It does not make sense to try to do it all in one form.

  11. #11
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching records on sub form (2000)

    Would it be possible to include an example database of what you mean.
    As l think l understand what you mean,altought an example database would ensure l fully understand.
    It would be much appreciated.

    Justin.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching records on sub form (2000)

    See the database attached to <post#=364203>post 364203</post#>. It uses students and courses as example, but the basic idea is the same. There is some discussion about many-to-many relationships in that thread.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •