Combo Box (Access2K SR-1 ver 9.0.3821)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote