Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box (Access2K SR-1 ver 9.0.3821)

    system information: Windows 98, MS Access 2000 ver. 9.0.3821 SR-1
    database information: Database of library documents each with one of 12 possible category codes.
    problem: I am trying to open a form to display the database entries filtered by category. I have a
    form with a Combo Box to select category codes with an "After Update" event as follows:

    DoCmd.OpenForm "frm_PROF Form one", acNormal, "", &_
    "[tbl_prof entries]![CategoryControl]=[Forms]![frmNCPSELit1]![Combo15]"

    The "After Update" event only works after I go into design mode, open the event properties in VB and then close VB and run the application.

    If I open the db and attempt to select an item in the combo box list, it opens the drop down list but does not select the item in the list.

    Any ideas why this is happening?

  2. #2
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Access2K SR-1 ver 9.0.3821)

    Have you tried
    "[tbl_prof entries]![CategoryControl]=" & [Forms]![frmNCPSELit1]![Combo15]
    That might do it.
    Paul

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Access2K SR-1 ver 9.0.3821)

    Paul,
    Many thanks but it still behaves the same way with your suggested code. I am beginning to think that there is a missing reference to my project in VB or something like that.
    aap2.

  4. #4
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Access2K SR-1 ver 9.0.3821)

    You could hard code a value in place of the Forms! reference and see if it runs through that way. Something like
    "[tbl_prof entries]![CategoryControl] = 'Category3'"(or whatever value Combo15 holds). That might tell you which part of the string isn't connecting.
    Paul

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Access2K SR-1 ver 9.0.3821)

    Still the same behavior after hard coding a category code. I am investigating the references as I seem to remember reading a post here that stated the ADO is the default data access model and if DAO and ADO references are checked then it leads to problems.

    If all else fails, I will write the form in VB using ActiveX data objects to connect to the MS Access tables.

    Thanks again.
    aap2

  6. #6
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Access2K SR-1 ver 9.0.3821)

    ADO and DAO can exist side by side in your references. Access will give priority to whichever library is higher in the list. If it's ADO(which loads automatically when you create a new Db.) then you need to adjust the way you declare variables in your code so that Access knows you are using DAO. Also, you usually have to select the Microsoft DAO 3.6 Object Library from the list yourself. Access won't do it for you. An example of the declaration statement for DAO is:
    Dim db as DAO.Database 'which declares 'db' as a DAO variable and not ADO. It generally pertains to the Collections Objects in the Database but unless I'm not seeing all your code, this shouldn't effect what you are doing. Good Luck
    Paul

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Combo Box (Access2K SR-1 ver 9.0.3821)

    Hi,
    Does this form with the combobox on it then open another form which is bound to the data? If so, it might be easier to have the combobox on your data viewing form, and simply have it apply a filter to that data according to the category you select.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box (Access2K SR-1 ver 9.0.3821)

    This isn't a references issue. What exactly are you trying to do here?

    DoCmd.OpenForm "frm_PROF Form one", acNormal, "", &_
    "[tbl_prof entries]![CategoryControl]=[Forms]![frmNCPSELit1]![Combo15]"

    The syntax for OpenForm is:

    DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

    Don't put anything (not even a null string) in any of the arguments you're skipping and change the whole thing to this:

    DoCmd.OpenForm "frm_PROF Form one", acNormal, , "[CategoryControl]=" & [Forms]![frmNCPSELit1]![Combo15]

    If the combobox is returning a string value, you'll need to wrap that value in single quotes so it's interpreted as a string. Also, if CategoryControl is *not* the name of a field in the recordset, this won't work because the WhereCondition applies to the underlying recordset and not to the value of a control.
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Access2K SR-1 ver 9.0.3821)

    Thank you for the suggestion. I did have one form opening another. The users seem to think your idea will be an easier interface.

  10. #10
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Access2K SR-1 ver 9.0.3821)

    Charlotte,

    I want to open up a form that displays a subset of records, one record at a time, based on filter criteria selected in a combo box on another form (this may change, see other post). I modified the syntax of my OpenForm statement as you indicated. Closed the code window and it works fine. I close the database and open it again and it does not work. The list drops down but I am unable to select an item from the list. Very wierd.

    [CategoryControl] is a field in the underlying table where the records reside. It contains one numeric value (long integer) of 1 to 12.

    So I say to myself, "Self", I say, "am I overlooking the obvious?"
    Any suggestions are most welcome.

    Many thanks,
    aap2

  11. #11
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Access2K SR-1 ver 9.0.3821)

    Ok, I have solved the problem. I did overlook the obvious. There was a macro called autoexec that opened the form with Data Mode set to "Read Only". When Data Mode was changed to "Edit" it works fine.

    Thanks all.
    aap2

Posting Permissions

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