Combo boxes work, then fail

[Guest]

I have a form with 28 enabled fields, 7 not enabled, for information
occasionally needed by the user, and a subform containing 13 fields.

The database is being prepared for a non-profit organization related to
autism.

I want to let the user select a particular person's record, using a combo
box that contains the following fields: AutoNumber index (hidden),
Last-Name-First, Address and City-State-Zip.

When I set up the field, using the Properties box, Data tab and RowSource
field to view the query and alphabetize the Last-Name-First, the combo box
works perfectly.

If I close the database and re-open it, the combo box doesn't work. It
doesn't respond to any of the characters I type in that are the first few
characters of one of the names in the form' underlying table. If I click on
the down arrow at the right end of the combo box, the list of entries is
there, in alphabetical order.

I have other databases on this computer that are working perfectly, allowing
me to find a record I am looking for.

When the primary combo box is working, several lookup fields on the parent
form and on the sub form also let me type the first few characters of an
option, see the rest of the choice in the field, and press Tab to select the
option viewed. When the combo box is not working, the lookup fields also do
not work.

If I change to the design view of the form, while the combo box is not
working, and create another combo box that does the same thing as the primary
combo box, and return to the form view, the new combo box works, and the
lookup fields also work, but closing and re-opening the database makes all of
them not work again.

One other symptom: There are three forms available to the user, one with
fewer fields, but the same target for the combo box, and the other with a zip
code lookup in the combo box. When the combo box in one form works, they all
work. If the combo box on any of the forms fails, the combo boxes on the
other forms also fail.

I have tried creating simpler forms, but the combo box operation in them has
the same problem.

Using the Compact and Repair routine makes the database smaller, but does
not restore the operation of non-functioning combo boxes.

I also tried uninstalling Microsoft Office Pro 2003 and reinstalling it, but
the problem was not corrected.

Can anyone offer me another diagnostic/corrective path to take?

Thank you!

OldFatherWilliam+

 

[Guest]

Hi

Just a though - is the combox's Auto Expand set to = Yes.

Go to the source of the combo and click the build option (...) and create a
new query to act as the source (the wizard will do this for you) and this
should sort out the problem.

 

[Guest]

Hi

Just a though - is the combox's Auto Expand set to = Yes.

Go to the source of the combo and click the build option (...) and create a
new query to act as the source (the wizard will do this for you) and this
should sort out the problem.

 

[Guest]

Thanks, Wayne, but I'm still wondering...

Auto Expand is set to = Yes.

In the toolbars I have an icon with the ... in it, along with the magic
wand, but when I click it I get the code window, waiting for me to enter some
code that I want run Before Update. That won't help my problem, I'm afraid.

Again, thanks.

 

[BruceM]

Is the combo box unbound? Since it is a search combo box, it should not be
bound. What is the After Update code for the search combo box?

 

[Guest]

Hi

You are looking in the wrong place.
Open the form in design view and select the combo box.
Right click and select Prpoertiy Box
Select the Data column
Select Row Source Type row and select Table/Query
Select the Row Source row (under the last row)
Click the Build option (...)
Buid the query and sort it as you want
Save - select Yes to Do you want to save etc etc etc

Open the form in normal view and check the box and it should work.

 

[Guest]

Thanks, Bruce. Yes, the combo box is unbound. The code is:

Private Sub Combo175_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[fldAllID] = " & Str(Nz(Me![Combo175], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I also have tried this, from another working database, with the names
changed to fit this database:

Sub Combo175_AfterUpdate()
' Find the record that matches the control.

Me.RecordsetClone.FindFirst "[fldAllID] = " & Me![Combo175]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

That code, generated by the wizard some years ago, works at first, then
fails after closing and re-starting the database.

 

[Guest]

Yes, this worked, but the same way as before. It worked when I went to form
view, but failed after I closed and re-opened the database. ...and yes, it
did save the changes.

 

[Guest]

Dear Wayne,

Problem solved! A tech person at EVERYTHINGACCESS found the solution. When
ANSI 92 is installed, the RowSource has to start with SELECT DISTINCT, not
just SELECT.

Thanks again,
Bill

 

[Guest]

Dear Bruce,

Problem solved! A tech person at EVERYTHINGACCESS found a B article that
says that, with ANSI 92 installed, the RowSource has to start with SELECT
DISTINCT, not just SELECT.

Thanks,
Bill

 

[BruceM]

You could try this (although it seems to be pretty much what you tried in
the second example):

Dim rs as Object

Set rs = Me.RecordsetClone
rs.FindFirst "fldAllID = " & Me.Combo175
Me.Bookmark = rs.Bookmark

This code assumes that fldAllID is the key field (for the person whose
record you want to find, I assume), and that the bound column of Combo 175
is the same field. I can't see a reason for using Nz for an autonumber
field, which cannot be zero or null, or for wrapping the string function
around the field, which has the effect of adding a leading space, as I
understand it.

Thanks, Bruce. Yes, the combo box is unbound. The code is:

Private Sub Combo175_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[fldAllID] = " & Str(Nz(Me![Combo175], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I also have tried this, from another working database, with the names
changed to fit this database:

Sub Combo175_AfterUpdate()
' Find the record that matches the control.

Me.RecordsetClone.FindFirst "[fldAllID] = " & Me![Combo175]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

That code, generated by the wizard some years ago, works at first, then
fails after closing and re-starting the database.

Is the combo box unbound? Since it is a search combo box, it should not
be
bound. What is the After Update code for the search combo box?

 

[Guest]

I had the same problem, but a tech person at EVERYTHINGACCESS found a KB
article that said that, with ANSI 92 installed, the RowSource has to say
SELECT DISTINCT, not just SELECT.

....OldFrWilliam