Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform problem (97/2K)

    I originally tagged this onto the end of a previous post but received no replies.
    I have a main form which contains, among others, a combo box from which one selects a Customer ID. The subform then is supposed to display the relevant Cutomer details. However, when first opening the main form and selecting a Customer ID, nothing happens in the subform. If I now close the form and reopen it, the previously selected Customer ID is displayed in the combo box together with the relevant Customer details in the subform. If I now select a different ID, nothing happens until I close and reopen the main form.
    The Combo box is bound to the form's underlying table and both the Linked Child and Linked Master fields of the subform display Customer ID, which is correct.
    I have created the form/subform from scratch on numerous occasions but the same thing keeps happening.

    Can anyone help?

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Subform problem (97/2K)

    Can you post a cut-down version of this db to have a look at. Just the bare minimum of tables and forms.
    (If you do this, can also look at your other problem with showing history records, which may be similar to this.)


    The combo box doesn't want to be bound . If it, then when you choose a customer you actually change the customer for the current record.
    Are the main form and the subform bound to the same table? One solution is to make the main form unbound. In this case you don't user link fields. Instead bind the subform to a query that takes the combo as a parameter. Then you need to requery the subofrm whenever you change the combo box.
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform problem (97/2K)

    Thanks yet again for your time, John.
    I'll attach a cut-down version as suggested sometime tomorrow.
    It is so frustrating when one thinks one has it cracked to be presented with problems which shouldn't happen.

    Regards

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform problem (97/2K)

    John
    I am unable to post an attachment because, despite compacting, I cannot get it less than 216K.
    It consists of 4 tables(empty), 1 query and 1 form/subform.
    This doesn't seem right.

    Any ideas?

    Should I build the whole db from scratch?

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Subform problem (97/2K)

    First try importing all your objects into a new (empty) database. If that doesn't do the trick, then ZIP the file and it should compact sufficiently.
    Wendell

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform problem (97/2K)

    Hi Wendell
    I am actually trying to select a record from the Customer table based on the Custmer ID.
    I still don't understand the subform's behaviour.

    Thanks for your time

    Rob
    Attached Files Attached Files
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  7. #7
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform problem (97/2K)

    Your problem is that you are using a main form and a sub-form the wrong way round. You have a one-to -many relationship Customer to Orders. The main form would be the customer (one end) and the sub-form would show all of the orders (many end) which that customer has made.

    As each order only has one customer you do not need a sub-form. Your main form query which forms the recordsource should include the customer details you want. Then when you set the customer you will automatically see the relevant details.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  8. #8
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform problem (97/2K)

    Thanks Steve for your input. However, I do not wish to display all the orders made by an individual customer. I want to select a Customer ID whose source is The Customer table then have the subform display the Customer details. The Customer ID for that order is then stored together with the rest of the order in tblOrders.
    I think I've sorted out the subform behaviour (a matter of requerying). My problem now is that a query which once worked properly is now misbehaving.
    Please take a look at the amended db and advise me on the problem and solution.

    If you think I need a complete redesign, I'll do so.

    Rob
    Attached Files Attached Files
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  9. #9
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform problem (97/2K)

    <hr>I want to select a Customer ID whose source is The Customer table then have the subform display the Customer details. <hr>

    If you use a query for the recordsource for your frmOrders in which you get the customer's details, the form will automatically display the customer's details when you select the customer you want using a combobox.

    See frmOrdersNew in the attachment (its a *very rough* form!)
    Attached Files Attached Files
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  10. #10
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform problem (97/2K)

    Thanks Steve,
    Point taken. Would you look at the query -qryDriverID-which was working correctly but not any longer? Running the query in the QBE grid only works for record number 1 and returns nothing for the other 2.
    I am at a loss to explain the problem.

    Many thanks again,

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  11. #11
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform problem (97/2K)

    Only Driver #1 has any data in My Area which is your criteria field!
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  12. #12
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform problem (97/2K)

    My driver table contains three records! If you have the inclination, perhaps you could enter a dummy record in to the table and test the query. In the meantime, I am going to create a new query to do the same thing and check it out.
    Very mysterious.

    Thanks again for your help. It's really appreciated.

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  13. #13
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform problem (97/2K)

    There is no data in records 2 and 3 in the My Area field hence it cannot find a match.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  14. #14
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform problem (97/2K)

    I solved the problem -User Stupidity- before you posted.
    You've been a great help.
    Apologies for any wasted time.

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

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