Change unbound combobox from number to text

[kstevens]

I have a form with 12 unbound comboboxes whose query is determined from the combobox before it. Example: No2 box rowsource is set to the query and then requeried on the afterupdate of box1.

Now if sombody makes a mistake on box2 and is on box5, then they can redrop down their box2 option. When they do this (since each box requeries the next box) i empty the values form the rest of the boxes (3, 4, 5) and then they get requeried depending on what they have chosen.

The problem occurs when the box5 query returns a number format. I need the number format so that i can keep the decimal format the way i have chosen. The next time box 5 is used, and the result is text, then i get the warning about "the value you have chosen is not valid". I tried setting the format on box5 to text on the number4 got focus event like this

no5.format = text

But that doesnt seem to work. Can anybody explain how to either set the format to text, or completely reset the box so that it is actually unbound again.

I read about rowsourcetype, but it didnt seem to have anything to help me.

[NeoPa]

K,

You ask a question regarding how to handle certain data, but you don't give us any information about the data we should be considering, other than it is from a query which changes depending on ComboBox #4.

With some clarification of the question I'm sure someone could help.

[missinglinq]

I agree with NeoPa that we need more information in order to help. I'm also confused as to how the query for combobox 5 can return text data one time and numeric data another.

Linq ;0)>

[kstevens]

A typical afterupdate event looks like this (this is part of the afterupdate of the no2 box)

If No1.Column(1) = "F" Then
    Cap3.Caption = "Material"
    No3.RowSource = "qryMatl"
    Note.Value = "Select API material designation."
End If

If No1.Column(1) = "C" Then
    Cap3.Caption = "Model"
    No3.RowSource = "qryChokeManu"
    Note.Value = "Select choke manufacturer and model."
End If

No3.Requery
No3.Format = Text
No3 = ""
No3.Visible = True
Me.No3.SetFocus
Me.No3.Dropdown

a typical gotfocus event looks like this (this is the gotfocus event for the no4 box)

No5.RowSource = "qryBlank"
No6.RowSource = "qryBlank"
No7.RowSource = "qryBlank"
No8.RowSource = "qryBlank"
No9.RowSource = "qryBlank"
No10.RowSource = "qryBlank"
No11.RowSource = "qryBlank"
No12.RowSource = "qryBlank"
Cap5.Caption = ""
Cap6.Caption = ""
Cap7.Caption = ""
Cap8.Caption = ""
Cap9.Caption = ""
Cap10.Caption = ""
Cap11.Caption = ""
Cap12.Caption = ""
No5.Visible = False
No6.Visible = False
No7.Visible = False
No8.Visible = False
No9.Visible = False
No10.Visible = False
No11.Visible = False
No12.Visible = False

i used the statement "if sombody makes a mistake", this was an incorrect stament, i will explain why further down. There are many options in these queries, and each option changes the part number that they are creating. If the no2 box contains a seal such as an o-ring (the industry standard for the size of an o-ring is a 3 digit number) then the no5 box will have its query set to pull data that is formatted as a number (i.e. 345). Once the part number is completed (from the unseen columns of the boxes... the keys) then the user has the ability to create another part number. When they go to box no1 again then all of the other boxes get there queries cleared

no2.rowsource = "qryBlank"

the combobox becomes hidden

no2.visible = false

and the whole process starts over again (see... no mistake here). But this time lets say they choose a choke manufacturer in no2 box. If they do this then the no5 query pulls data that is alphanumeric (i.e. Shaffer Model 34 Choke), if they try to choose one of these options then this error comes up ""the value you have chosen is not valid". The first column of information builds the part number, and the second column (the options that they are choosing) becomes the description for the part.

It seems that once a combobox has been used by a query that returns a numeric value, then it becomes stuck as numeric, and text cannot be entered into without closing the database and starting over. That is when i added the

No3.Format = Text

to the afterupdate event of the boxes, but it didnt seem to fix the problem. I have tried setting the format to text before the requery and after, with what seems to be no difference in the way it works... or should i say, doesnt work.

I hope this is better... if not i will try again, lol.

[NeoPa]

If you look at lines #13 & #14 of your first block of text, you will notice that the .Requery comes first, followed by a line that will never execute as it's bugged. Unless, that is, that the word Text in your code refers to a variable (I can't imagine you'd knowingly post such code without the Dim line included). Otherwise, the .Format property takes a string value.

However, assuming you are trying to explain that the returned values of some of the queries associated with any given ComboBox can return text values while others can return numeric ones, AND that text data fails when formatted as numeric, then you will probably have to ensure that the format is changed to text ("@" I think) before the .Requery is called, and to a numeric format string after it is called.

[missinglinq]

Maybe this could all be fixed by simply having all values returned defined as Text. The fact is that a field contains all digits, i.e. the characters 0-9, does not mean it should be defined as a Number Datatype. In point of fact, unless the field is going to be used in mathematical operations, it should not be defined as Numeric,

Telephone "numbers," account "numbers," ID "numbers" and yes, parts "numbers" should be defined as Text.

Wouldn't this solve your problem?

Linq ;0)>

[kstevens]

i tried

No5.Format = "@"
No5.Requery
No5 = ""
No5.Visible = True
Me.No5.SetFocus
Me.No5.Dropdown

and it still failed at the same point... actually it failed before, this time it highlighted

no5 = ""

and gave the message about the value not being valid, i guess because an empty string is no good for numbers.... same problem.

Yes NeoPa,
Any combobox can return a text value from a query or a number value, once a number has been reutrned though, it will not allow text again.

When i tried this

No5.Column(2).Format = "@"

it failed also... "Object Required".

Linq,
Yes, not everytime, but sometimes math is done with the number, not only that, when the queries return number values there is 1155 options, that i dont want to type by hand, they are in decimal increments of 64ths of an inch... .000, .015625, .03125, .046785, etc. and i have them formatted to be to 3 decimal places (for "cleaness") , so it is much easier to leave them in number format. I have multiple queries that are designed like this, increments of .005, .010, 64ths, 32nds, 16ths, 8ths, and 1/4" So i dont want to hand type each one and then display as a text value.

btw, i didnt neccesarily think that

no5.fomat = text

would work, i had already tried the "@" but when i typed the word text, it capitalized itself correctly and turned blue... so i was happy with it, lol.

[NeoPa]

Right. It seems we have a couple of confusions here. I will deal with what I can, but without an accurate view of exactly what's going on here I may take a false step or two. Try these first to ensure they work for you.

I would guess that setting :

No5 = ""

should probably be changed to :

No5 = Null

I expect the former would work only if a value of "" already existed in the list.

.Format of "General" may be a better thing to set it to than "@" explicitly.

For your original lines #13 to #18 I would consider something like :

With Me.No3
  .Format = "General"
  .Requery
        'For queries that need a special format only
        .Format = {Whatever}
  .Value = Null
  .Visible = True
  Call .SetFocus
  Call .Dropdown
End With

The indented line is simply for when you need a special format. Otherwise "General" is fine for text and general numeric formatting.

Lastly, "Text" (no quotes) didn't turn blue for me. Intellisense allows it, but when I try to compile it, it fails. The only time it wouldn't fail is if you don't have "Require Variable Declaration" set (See Require Variable Declaration). In that case it would be an unset variable containing the value Null. As explained in the linked thread, this is never a good idea.

[kstevens]

This seems strange to me, but whatever i set the format to, gets updated as the information.

If I have

.Format = "General"

and choose a numeric value, the box data says General.... like i chose it, is that wierd?

if I have

.Format = "Auto"

then the box has "Auto" in it.....

I did turn "Require Variable Declaration On". Thanks, i wish i could get this to work, it is getting very frustrating.

[kstevens]

Any ideas on why my dropdown changes to the actual format style... Lets see if this makes it clearer...

When I dropdown the combobox, my options are

.000
.005
.010
.015

If i select

.010

Then my box actually says

General

I cant seem to make it stop, lol

[NeoPa]

I may be able to.

What are the settings for the ComboBox dealing with columns and what are the columns provided by the source query?

[kstevens]

All queries are setup like this... and i mean all of them,

SELECT [64K].KDes, [64K].Key
FROM 64K
WHERE ((Not ([64K].KDes) Is Null))
ORDER BY [64K].ID;

Each table is made up of 3 fields. ID ("Autonumber "), Key ("Text" 2 Alphanumeric characters from AA to 99: AA, AB, AC....A1, A2, not using any 'O's, as in the word hOt, so they dont get confused with 0's, just thought i would say that, lol), and KDes ("Number, Double" or "Text" the key's description: 2-1/16" 10,000 WP, 2-1/16" 15,000 WP... ans so on) or for the KDes that is returning a numeric value (0.000, 0.063, 0.125, 0.188).

Key is used to create the Part Number by concatenating no1.column(1) & no2.column(1).. ., and KDes is used to create the description no1 & ", " & no2...

All comboboxes are setup like:
ColumnCount: 1
RowSourceType: Tabel/Query
BoundColumn: 1
Limit To List: Yes
Allow Value List Edits: No

When you asked the question about the columns I decided to change the field name of the ID, Key, and KDes in the numeric querys to NID, NKey, and NKDes, just to see if the combobox pulling a different field name would help reset the box to the format of the query...it didn't.

FYI, if i dont pull Numeric formats, it works all day long. Once i pull a number format, as long as i keep pulling numbers then it is fine, until i try to get another text, but i think you already know this.

[NeoPa]

I'm sorry K. I haven't given up on you.

I'll make a special effort to post a proper response this evening.

[kstevens]

Thanks NeoPa, I almost double posted last night just to bump back up and remind you, and decided that it would be MUCH worse if I did, lol, jk, i wouldnt do that. Thanks for devoting time your time to this.

[NeoPa]

I'm sorry K.

I've looked at the data and the explanation and can't understand how you're getting the results you describe.

Any chance of attaching a sanitised copy of your database (not all data required, and possibly not even all tables). This would need to be Compacted (from Tools Menu) and the resultant database zipped up too.

If you can attach this with asome simple instructions on how I would run it in such a way to see the problems you're describing it may be possible for me to determine what's not quite right.