Combo Boxes and Case Sensitivity in an Access 2007 Form

[bcarter17]

Hello everyone, been a while.



I am working on a new Access form and it works great except for one quirk. I have 4 combo boxes on the form. Each combo box, the first word in the list is in uppercase. If the user happens to be in lower case and types the first letter of the desired selection to "jump" to that area (like type a "p" to jump to the P's in the list). In 3 of the combo boxes, if you type a lower case letter and it finds your selection (with the first word now in lower case), it changes back to upper case when you hit enter to select that item. But on one combo box, when you hit enter, it keeps the first word in lower case as you move to the next field. Since I am using VB code to add the record to my table, it would put it into the table with the first word in lower case, not upper case (Example - Yellow Freight Company becomes yellow Freight Company).

Why would just one combo box be doing this and how can I correct it? I have looked at the properties for all 4 combo boxes and as far as I can tell, all 4 are identical in how they are set up.

Thanks for any help I can get on this, you guys have been great in the past.

Bob

[ranman256]

convert it to proper case:

if StrConv(cbo1, vbProperCase) = StrConv(cbo2, vbProperCase)...

or
if ucase(cbo1) = ucase(cbo2)...

[bcarter17]

Thanks for the reply.

So, add this into an "OnChange" or "OnUpdate" event for that combo box? I've been away from Access for a few years, so I'm a little rusty.

[Micron]

If you are using combos to search by, you typically don't bind them to a field or you get exactly the sort of behaviour you describe. So is the problem one bound and the others are not? If you must bind a search combo to a field, you'd need vba to force the entry to either upper, lower or proper case if you want to exert control. I'd suggest the BeforeUpdate event for that.

[orange]

Why exactly do you have case sensitivity? By default Access is case insensitive.
I suggest you post a copy of your evolving database so readers can see the issue(s) in context.

[bcarter17]

The whole form uses unbound fields. Once the user enters the data and clicks an action button, the VBA code checks to make sure all required fields have data to save, then it runs code to open the recordset and save each fields data as a new record. So each combo box is unbound.

Dim rstUpdate As Recordset, dbs As Database
Dim strLast As String

Set dbs = CurrentDb
Set rstUpdate = dbs.OpenRecordset("Sales")
If IsNull(fieldDeliveredTo) = True Then
MsgBox "You must provide minimum delivery information, or enter NONE!", vbOKOnly,
fieldDeliveredTo.SetFocus
ElseIf IsNull(fieldProduct) = True Then
MsgBox "You must select the type of product sold on this ticket!", vbOKOnly,
fieldProduct.SetFocus
Else
rstUpdate.AddNew
rstUpdate!DeliveredTo = fieldDeliveredTo
rstUpdate!Total = fieldTotal
rstUpdate.Update
fieldDeliveredTo = Null
fieldProduct = Null
fieldSoldTo.SetFocus

MsgBox "Order entered successfully!", vbOKOnly,

End If

[bcarter17]

Hello Orange!!

The list come from tables we maintain for the business.

One is for the product we sell.
One is for the Trucking companies we use.
Another is the truck number list, which stores the TARE weight of the truck as well. This list in the dropdown is shorted when you select the trucking company to only show truck numbers that match the user selection for the trucking company.

So examples would be

Trucking -
Nolker Trucking
Dennis Cook Trucking

Truck Number

49
41ED
BN15

Product -

1" Clean
1" Base
RipRap

Then in the previous post, I show the code I use to save the data from these unbound fields into the table. That works with no issues at all. Truck number works fine. On Product, the drop down list shows RipRap, but the use could type ri in lower case to jump to that selection on the list, but when they find it and hit enter, the R goes back to upper case as the list is (RipRap).

But, on the Trucking company field, if the user clicks into the field and types lower case n to jump to the n selections and it finds Nolker but shows it as nolker. On this field only, once it finds the selection with the first letter in lower case, when the user hits enter, it saves the name in the combo box starting in lower case and then eventually when the code runs, saves to the table lower case (nolker Trucking).

Does that help?

[Minty]

Have you got any formatting on the control?

if you put a > in the format it would force it to display in lower case I think. Other than that it makes no sense, if the underlying data is Nolker Trucker then it should store that.
(Actually you should be storing the primary key from that table, not the name but that's a whole other conversation. )

[orange]

We really need to see a sample database. It does not have to contain all of you data --only enough to illustrate the problem.
Please post using zip format.