Combo box stopped working after 1 years

[2Grandpa]

This is an inspection record form. I use three combo boxes to get the model, vehicle number and part number for the inspection. The vehicle number is where the problem is occurring. When the form opens a model number is selected in Program combo box. The code listed below is in the On Change Event field.

cboPartNumber.Requery
cboBuno.SetFocus
cboBuno.Value = Null
cboBuno.Requery

This code sets focus on the model number field, cboBuno, deletes any data in the field and requires the data based on the model selected. The vehicle combo box’s Row Selection criteria are listed below.

SELECT G_BUNO.Buno, G_BUNO.Program FROM G_BUNO WHERE (((G_BUNO.Program)=[Forms]![frmProductAuditAAdd]![cboProgram]) AND ((G_BUNO.Shipped)=No)) ORDER BY G_BUNO.Buno;

No error message is received after the Program combo box is selected. I ran the Row Selection criteria in design mode with a Model number and a “*” and it works fine. I recreated the Vehicle Number combo box and got the same results. “cboProgram” is in the Name field in the Program combo box. The Part Number combo box also uses the cboProgram field in its selection criteria and it still works. The last changes made to this form were about a year ago.

Where else can I look?

 

[genomon]

cboBuno.Value = Null

For starters, nothing can "equal" null.

"Artificial Intelligence is no match for Natural Stupidity"

 

[ZOR]

Should be cboBuno = Null

 

[genomon]

No it shouldn't. Null is a condition, not a value. There isn't anything that can "equal" null. You can test For IsNull, Is Null, Not IsNull or Is Not Null, but you can't ever "equal" null. The = sign is incorrect syntax in this example.

"Artificial Intelligence is no match for Natural Stupidity"

 

[PHV]

genomon, the syntax is perfectly legal as the instruction is an assignment, not a conditional expression.

 

[genomon]

This is what I was trying to remember - I think it's from an old issue of the defunct Visual Basic Programming Journal:

Null, Empty, Nothing, and vbNullString

These strange values have slightly different meanings.
Null. Null is a Variant subtype like Integer or String. It represents
a variant that contains no valid data.
This is different from zero, Nothing, Empty, or vbNullString.
Most other values when combined with Null produce a Null result.
For example:
Null - Null is Null not 0
Null + 7 is Null not 7
Null = Null is Null not True
You can use IsNull to determine whether an expression is Null:
If IsNull(my_variable) Then ...

Empty. This is a variant subtype like Integer or String. It represents a
variable that has not yet been initialized. This is different from Null
which represents a value that specifically contains no valid data.
A variant variable that has not yet been initialized has the value Empty.
You can use IsEmpty to see if a variant has been initialized.
If IsEmpty(my_variant) Then ...

Nothing. This is an object reference that points to no object. Set an
object reference to Nothing to free that reference. If no other references
point to the object, Visual Basic will destroy the object.
Dim obj As Form1
:
Set obj = Nothing ' Free the object reference.
Use Is Nothing to determine whether a reference points to nothing:
If obj Is Nothing Then ...

vbNullString. This constant represents an empty string. This is not the
same as a blank string "". It is a nothing string. It is treated as an
empty string "" for most Visual Basic purposes. Its real use is for passing
null parameters to library functions.

Null is a strange value. It is not zero, it is not Nothing, it is not the same
as vbNullString. It is something undefined.

"Artificial Intelligence is no match for Natural Stupidity"

 

[2Grandpa]

I commented the "cboBuno.Value = Null" out. And the problem still exists. Any other ideas.

 

[genomon]

Sorry to get off-topic. Seems odd that it worked and then quit. If the code didn't change, maybe the database got wacky? For instance, does G_BUNO.Shipped = No return anything (maybe all the fields were changed to yes)?
Did you try a compact & repair, just for giggles? Kinda grabbing at straws here.....



"Artificial Intelligence is no match for Natural Stupidity"

 

[JoeAtWork]

In Access if something just stops working and you didn't make any change, it is usually that the MDB file got slightly (sometimes majorly) corrupted.

What often fixes it for me is if I do a decompile of the database. Run something like the following in a command window:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /decompile "C:\MyCorruptDB.mdb"

Of course, change the path to MSACCESS.EXE and the MDB file according to what's on your computer.

 

[missinglinq]

To be honest, I've gotten a headache trying to follow your original post, probably because I've been up 24 hours, but that's another story! A couple of random thoughts come to me; it happens so seldom, I like to tell people!

We tend to only look at the fact that nothing has been changed in the database in the past year, but has anything changed in your Access application? No mention has been made of the version of Access being used here. Are you, by chance, running v2003 with SP3 in stalled? Genomon mentioned the possibility G_BUNO.Shipped possibly being changed from No to Yes; one of the bugs introduced by the now infamous SP3, I believe, was the changing of boolean values.

Another thought relates to the placement of the code. As a rule, the combobox AfterUpdate event really should be used for this kind of thing, not the OnChange event. The OnChange event for a combobox fires if a selection is made from it, either by clicking on a selection or scrolling down and hitting <Enter>, and code will execute as expected. The problem is that the OnChange event also fires every time a user enters a character into the combobox! So if a user types data in, to utilize the AutoExpand feature, the code fires after each letter is entered, which can lead to problems. I was asked to troubleshoot an established db that, like yours, had been running fine for quite some time, only to start failing. I finally traced it to just this problem! The user originally entering data was used to scrolling then clicking to make a selection, then was replaced by a data entry person who was accustomed to entering the first couple of characters to use the Autoexpand, and the app was bombing.



The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[2Grandpa]

Thank you for your suggestions. I decompiled the database and moved the code to the after update action field.

Now what I find is the data is on the drop down list but you cannot see it. When you select an item it appears in the combo box. I checked the fore ground and backgroud colors and they are okay (See below). There are about 30 users of the database and all are having this same problem on this one ciombo box. There are 5 other combo boxes with the same formats that are not affected.

Is there a seperate format for the drop down list?
Visible: Yes
Back color: 52479
Fore color: 0
Font name: Tahoma
Font size: 8
Font weight: Normal

 

[PHV]

Did you apply SP3 on office2003 ?
If yes:

http://support.microsoft.com/kb/945674/

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

Already asked, PHV, but as you probably noticed, not answered! As I said above, I seem to remember SP3 being accused of arbitrarily changing boolean values.


The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[genomon]

I decompiled the database

Huh? You mean compacted?

"Artificial Intelligence is no match for Natural Stupidity"

 

[missinglinq]

I suspect since the code he posted was

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /decompile "C:\MyCorruptDB.mdb"

that he meant decompiled.Decompiling does, sometimes, fix these things.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[genomon]

Yikes missinglinq! I've been working with Access for 15 years, and never heard of "decompiling" an mdb. I compact, back up, & save often. Did a Google on it & voila! Of course, the directions I found were for saving & compacting...
Never too late to learn!

"Artificial Intelligence is no match for Natural Stupidity"

 

[missinglinq]

Actually, that's one of the things that I enjoy about working with Access; no matter how experienced you are, there's always something new to learn!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[2Grandpa]

I think we figured out what happened. We have not installed Service Pack 3 yet. Our IT folks have some problems with it. But they installed what I think is the fix to Service Pack 3 to fix the missing data problem it caused. We tested a new computer without this file installed and the combo box still works. We are trying to un-install the fix to Pack 3.

The file was named msohtml or msohtml1. We installed it the week of 14 Apr.

Thanks for all your help.

 

[genomon]

to fix the missing data problem

Aha.....

"Artificial Intelligence is no match for Natural Stupidity"