Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Subform not being sorted (Access 2003 winxp sp2)

    I have a form for quotes with a subform that works fine.

    I created a button for searching by customer name that opens a small form with a combo box on it that is linked to the customer name field in the quote table that has the following code:

    Private Sub cboSearch_AfterUpdate()
    DoCmd.OpenForm "frmQuotes", , , "((QCustomer Like ""*" & cboSearch & "*""))"
    DoCmd.Close acForm, "frmquotesearch"
    End Sub

    which works fine in selecting all the records with that name

    BUT

    the subform shows every record in the associated table, rather than just the ones connected by the quote number of the shown record, as it normally does.

    I think this is due to the fact that I have sorted on the name, whereas the subform records are connected by number but most likely I am wrong.

    Could someone explain what is happening and what I need to do in order to fix it please.
    "Heading for the deep end"

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

    Re: Subform not being sorted (Access 2003 winxp sp2)

    It shouldn't matter how you sort the records.
    Make sure that the Link Child Fields and Link Master Fields properties of the subform are set correctly.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Subform not being sorted (Access 2003 winxp sp

    Could you please have a look at the sample db below.

    You will see that the form that opens can be scrolled through and the items in the subform change as they should, but click on the Search by Customer button and select a name from the droplist and the filtered form shows all the items in the list.

    I couldn't get the db under 400kb, even with compacting so here is a link.

    www.shute-eng.com.au/Jobbing.mdb
    "Heading for the deep end"

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

    Re: Subform not being sorted (Access 2003 winxp sp

    I'd filter frmQuotes instead of opening it while it is already open:
    <code>
    Private Sub cboSearch_AfterUpdate()
    With Forms!frmQuotes
    .Filter = "QCustomer Like ""*" & Me.cboSearch & "*"""
    .FilterOn = True
    End With
    DoCmd.Close acForm, Me.Name
    End Sub
    </code>
    BTW, frmQuoteSearch should be an unbound form, there is no reason for it to have a record source.

    See attached version (39K zipped, I removed stuff that wasn't necessary for this form/subform problem)
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Subform not being sorted (Access 2003 winxp sp

    Once again Hans,

    Many thanks for your help. I have made the relevant changes and of course it works perfectly!
    "Heading for the deep end"

Posting Permissions

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