Combo box hangs

[CSDunn]

Hello,
I have tried in vain to create a combo box that either looks up records from
a table or view, or finds records based on the selection from the combo box.

No matter what I do, the combo box shows the correct values, but when I
click the drop down box and attempt to select an item on the list, nothing
happens. That is, the full list from the combo box is still displayed, and
clicking a list item has no effect.

I can see that I have the correct number of records in record navigation
box, and if I use the arrows to navigate, the values in the combo box will
then change as I advance through the records.

It seems like a simple problem, but I can't figure it out. The record source
of the form is a SQL Server 2000 view called 'MMAdminSpecificStudent2_vw',
and the combo box gets its RowSource property through the Form_Open event by
the following code:

Private Sub Form_Open(Cancel As Integer)
Me.Combo45.RowSource = "SELECT PERMNUM, FirstName, LastName, STATUS FROM
MMAdminSpecificStudent2_vw WHERE TeacherID=" & Me.OpenArgs '& " Order By
Lastname"
End Sub

The unique table property of the form is set to MMAdminSpecificStudent2_vw.

What could be causing this problem?

Thanks!

CSDunn

 

[Guest]

Are you sure that the "Locked" property is set to "No". If you set it to "YES" you will experience something like you describe. Locked property is in on the data tab of the combobox properties. It is also possible to set this proprerty in VBA, make sure that you dont lock your combobox in your code

Regard

Tore

 

[CSDunn]

The combo box is not locked. I have a combo box set up on another form so
that its Record Source is populated on the Form_Open event. The Record
Source of the form is a SQL Server 2000 View, and the Unique Table Property
of the form shows the three tables that make up the View.

On the form where the combo box is causing a problem, I have a different
View for the Record Source of that Form, and that Form's Unique Table
property does not see the tables that make up the View.

I will test to see if this is the problem. Does this sound like it could be
a problem, and if it is, what options do I have?

Thanks again!

CSDunn

Are you sure that the "Locked" property is set to "No". If you set it to

"YES" you will experience something like you describe. Locked property is in
on the data tab of the combobox properties. It is also possible to set this
proprerty in VBA, make sure that you dont lock your combobox in your code.

 

[Guest]

Is your combobox bound or unbound? Bound means that the item selected in the combobox is stored in a field in a table. Unbound means that the item selected in the combobox is stored in memory (RAM) only. An unbound combobox may be populated on the forms ON OPEN or ON CURRENT event, and it shows its contents to the operator while the current form (record) is shown

I have a feeling your combobox is bound to a field in your view. Try to make your combobox unbound to see if you can select an item. Open form in designview and delete the text that is shown in the combobox, "Unbound" will appear
Or open combobox properties, go to data tab, delete whatever you have in "Control source" to make it "unbound". Does this make it possible to select an item

If it is unbound and you still have problems, I would look into the tables you are using as source for your combobox. Do they all have a primary key defined? Access quite often require this to make a selection.

If the combobox is bound and tables are OK, may be a change of value in the combobox tries to update a field in the underlying table with a value that is not allowed. Usually column(0) of the combobox is the bound column, in your case this seems to be the PERMNUM field. Does the field bound to the combobox accept updates with PERMNUM values

I dont understand the purpose of your combobox. If it is to update a field of your view or underlying table you could write a procedure on the combobox AFTER UPDATE event to handle this. If the purpose is record navigation, you could likewise write a procedure to move to the record selected in the combobox. A procedure can be written so that any table can be updated, no matter the setting of your UNIQUe TABLE property. At the end of the combobox AFTER UPDATE procedure you include Me.Requery to update your Access form with the changed values in underlying tables

Regard

Tor

----- CSDunn wrote: ----

The combo box is not locked. I have a combo box set up on another form s
that its Record Source is populated on the Form_Open event. The Recor
Source of the form is a SQL Server 2000 View, and the Unique Table Propert
of the form shows the three tables that make up the View

On the form where the combo box is causing a problem, I have a differen
View for the Record Source of that Form, and that Form's Unique Tabl
property does not see the tables that make up the View

I will test to see if this is the problem. Does this sound like it could b
a problem, and if it is, what options do I have

Thanks again

CSDun

Are you sure that the "Locked" property is set to "No". If you set it t

"YES" you will experience something like you describe. Locked property is i
on the data tab of the combobox properties. It is also possible to set thi
proprerty in VBA, make sure that you dont lock your combobox in your code

 

[CSDunn]

Tore,
Thanks again for your help. I have tried the Combo box as bound and unbound,
have used different record sources for the form where I was or was not able
to define a UNIQUE TABLE, and have experienced the same problem. The tables
in the View I want to use all have Unique Indexes on them.

Here is the plan for this Combo box: On Form1, an user selects the name of a
school teacher, and if they want to see student details for that teacher,
they click a button called "Students", and this opens Form2.

Form2 contains the combo box (Combo45) in question. There is an event that
takes place in the Open event of of Form2 that causes the Row Source of the
Combo45 to contain a list of students based on the TeacherID passed from
Form1.

In Form2, Combo45 is located in the Form Header. The Detail section of the
form has a number of buttons in it that when clicked, open different reports
about the student selected in Combo45. These reports require StudentID
and/or StudentGrade parameters to show the correct student information in
the reports.

All I need for Combo45 to do is to somehow supply me with the correct
StudentID and StudentGrade when a students name is selected in the combo
box, so that I will have the correct parameters to pass to the reports. So
Combo45 does not update anything, I just need it to supply values, and I
can't use a list box.

With that in mind, what would you recommend?

Thanks again!

CSDunn

Is your combobox bound or unbound? Bound means that the item selected in

the combobox is stored in a field in a table. Unbound means that the item
selected in the combobox is stored in memory (RAM) only. An unbound combobox
may be populated on the forms ON OPEN or ON CURRENT event, and it shows its
contents to the operator while the current form (record) is shown.

I have a feeling your combobox is bound to a field in your view. Try to

make your combobox unbound to see if you can select an item. Open form in
designview and delete the text that is shown in the combobox, "Unbound" will
appear.

Or open combobox properties, go to data tab, delete whatever you have in

"Control source" to make it "unbound". Does this make it possible to select
an item?

If it is unbound and you still have problems, I would look into the tables

you are using as source for your combobox. Do they all have a primary key
defined? Access quite often require this to make a selection.

If the combobox is bound and tables are OK, may be a change of value in

the combobox tries to update a field in the underlying table with a value
that is not allowed. Usually column(0) of the combobox is the bound column,
in your case this seems to be the PERMNUM field. Does the field bound to the
combobox accept updates with PERMNUM values?

I dont understand the purpose of your combobox. If it is to update a field

of your view or underlying table you could write a procedure on the combobox
AFTER UPDATE event to handle this. If the purpose is record navigation, you
could likewise write a procedure to move to the record selected in the
combobox. A procedure can be written so that any table can be updated, no
matter the setting of your UNIQUe TABLE property. At the end of the combobox
AFTER UPDATE procedure you include Me.Requery to update your Access form
with the changed values in underlying tables.