Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo box filters (2000)

    Hello Loungers

    I've recently changed jobs and news of my database skills (mostly learnt in the lounge (mostly from Hans (thank you Hans))) has followed me!

    I've been asked to put together a database which will store document types, the site to which the document relates to and the document details.

    The basic task is not too tricky but I'm having problems filtering what appears on the forms mainly because I get an error message telling me the wizard hasn't been installed when I try to use one. And partly because I haven't played with a database for a while!

    The main form has a combo box which lists the document types and a list box which lists the documents. So far the list box lists all the documents in the database.

    I would be grateful for hints and tips re the syntax required to limit what appears in the list box as a function of the document type selection made in the combo box.

    Regards

    Graeme

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

    Re: Combo box filters (2000)

    Good luck in your new job!

    See ACC2000: How to Create Synchronized Combo Boxes and ACC2000: How to Synchronize Two Combo Boxes on a Form. Although meant for two combo boxes, the same techniques work for a combo box and a list box.

    If you have been provided with a limited installation of Access, you should ask the IT department to give you a complete one. Although you can do everything the wizards do yourself, it is often easier to use a wizard.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box filters (2000)

    Thanks for that Hans, I started again from scratch and it works a treat.

    I need to add another column to the list box to display another field from the table TabDocDetails. I would be grateful if you could let me know where to put the field name in the code below

    Me.LisDocSite.RowSource = "SELECT DocTitle FROM" & _
    " TabDocDetails WHERE DocTypeID = " & Me.ComDocType & _
    " ORDER BY DocTitle"
    Me.LisDocSite = Me.LisDocSite.ItemData(0)

    After that the next step to complete the exercise will be to add another table listing the sites to which the documents refer and a subform on the main form to display further details of the document selected in the list box. That bit shouldn't be too tricky! (Heard that before somewhere!)

    I was going to zip up the database and attach it to this post but this machine doesn't have winzip on it. I really need to talk to IT!

    Regards

    Graeme

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

    Re: Combo box filters (2000)

    Modify the part <code>... "SELECT DocTitle FROM" ...</code> to include the fields you want to display, separated by commas:

    <code>... "SELECT DocTitle, OtherField FROM" ...</code>

    Note: if the field name contains spaces or other "unusual" characters, enclose the field name in square brackets:

    <code>... "SELECT DocTitle, [Other Field] FROM" ...</code>

    Don't forget to set the Column Count of the list box to 2, and to specify the Column Widths if necessary.

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

    Re: Combo box filters (2000)

    >> this machine doesn't have winzip on it.

    Which version of Windows are you using? Windows XP (Home or Pro) has a zip option built in - right click a file in Windows Explorer and select Send To | Compressed (zipped) folder.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box filters (2000)

    At home I can do these things but this is Network Rail! We're using Windows NT 4. Without a copy of Winzip installed.

    The above advice has been implemented and the combo box controls the list box a treat. Thanks.

    I've tried looking at the Microsoft Knowledge Base for a similar article in order to learn how to control the fields on a subform with a selection in a list box but to no avail. Is there one?

    Regards

    Graeme

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

    Re: Combo box filters (2000)

    Number 3 in ACC2000: Four Ways to Move to a Record from a Combo Box Selection contains an example. You can also take a look at <post#=265308>post 265308</post#> and <post#=132267>post 132267</post#>.

  8. #8
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box filters (2000)

    All done (What do you mean about time too!) And functioning well.

    All it needs now is a search form, a few reports and splitting to an mde to stop the users messing up the forms! Then I can get on with my proper job!

    Thanks for your help.

    Regards

    Graeme

Posting Permissions

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