Change unbound combobox from number to text

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
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.

re: Change unbound combobox from number to text

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.

re: Change unbound combobox from number to text

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)>

re: Change unbound combobox from number to text

A typical afterupdate event looks like this (this is part of the afterupdate of the no2 box)
a typical gotfocus event looks like this (this is the gotfocus event for the no4 box)
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
the combobox becomes hidden
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
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.

re: Change unbound combobox from number to text

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.

re: Change unbound combobox from number to text

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)>

re: Change unbound combobox from number to text

i tried
and it still failed at the same point... actually it failed before, this time it highlighted
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
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
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.

re: Change unbound combobox from number to text

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 :
should probably be changed to :
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 :
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.

re: Change unbound combobox from number to text

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

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

if I have
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.

re: Change unbound combobox from number to text

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
If i select
Then my box actually says
I cant seem to make it stop, lol

re: Change unbound combobox from number to text

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?

re: Change unbound combobox from number to text

All queries are setup like this... and i mean all of them,
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.

re: Change unbound combobox from number to text

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

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

re: Change unbound combobox from number to text

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.

re: Change unbound combobox from number to text

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.

re: Change unbound combobox from number to text

I have removed all tables and querys (or is it queries) that do not depend on the instructions that i will send. If you stray outside of the directions... well dont do that.

You will see one dropdown when it opens ( i know you would never run it first, gotta make sure im not gonna delete your files or anything, lol). As you select dropdowns the next one will become visible and dropdown for you. Select the follwoing in order.
What matters most here is that the 5th box that you choose will be a numeric value. When you are done (it will say finished at the top) then click the dropdown on box 1 again and select:
If you do the Flange Forging first, you will be able to do the I-Beam without error. Once you do the I-Beam and try to do the Flange Forging again, you will error everytime... I love consistency :)

Thanks again NeoPa, I know you aren't obligated to help anybody here, yet you take your time out to help, that says alot about you.

BTW, no jokes about my code, lol.. You will realize (especially more than half way down the code) that i could have been MUCH more efficient with the coding.... much, much more, lol.

Attached Files

PN Creation.zip (164.4 KB, 3 views)

re: Change unbound combobox from number to text

I may not get much of a chance to look at this before the weekend K, but I can download it at least and will start when I get a little time for it.

re: Change unbound combobox from number to text

No problem, thanks
(hmm, a message has to be at least 20 characters to post... looks like I got it beat now, lol)

re: Change unbound combobox from number to text

I guess.

re: Change unbound combobox from number to text

K,

I'm sorry. I tried to look at it just now only to find that it is in Access 2007 format. I don't have Access 2007 (really don't want it either).

If you are able to present it in one (any) of the earlier formats I will happily look at it for you.

re: Change unbound combobox from number to text

Thanks, try this one.

Attached Files

PNCreation.Zip (120.4 KB, 6 views)

re: Change unbound combobox from number to text

K,

I have the database now & it works.

Remember always to Compact a database before zipping and attaching. This saves wasting space on the server.

I'll get on and look into this now.

re: Change unbound combobox from number to text

Thanks, What time is it there, you keep late hours... i think (i will now google time zone in london, lol)

ok, not too late 9:45, and the weather looks great :)
http://www.timeanddate.com/worldclock/city.html?n=136
Does England really still use fahrenheit, i thought only the US did?

re: Change unbound combobox from number to text

I haven't looked at the code yet, but the interface is neat. I like the way the Combos are invisible until they are in context.

I think I may enjoy getting to the heart of this :)

re: Change unbound combobox from number to text

I will be using it the most for the first 2 weeks and then it will be turned over to others. When i am using something, i want as few clicks/mousing as possible :) (that also keeps others from trying to use the dropdowns in the wrong order). Thanks, a compliment from you means alot.

re: Change unbound combobox from number to text

So they are, and yet you access the second column in your code :S

I would have expected that to fail, yet it doesn't. This is behaving like you had Column=2 (which is what I was anticipating earlier). Access throwing a curved ball like this is seriously unhelpful.

Let me try changing the settings so they more accurately reflect their usage and see what comes up...

re: Change unbound combobox from number to text

That helped not a jot :(

I've looked further into this and it seems to me there is nothing wrong with your design at all. It seems to be a feature of Access itself. When you use a ComboBox it seems to assign a format to the .Value based solely on the type of data you select. If you subsequently reuse the ComboBox for data that doesn't match the format that Access, in its wisdom, has assigned for you, then it fails on you.

I'm afraid the only solution I can think of is to provide two versions of each ComboBox where numeric and text values are required. Only the relevant one would be unhidden when required.

I'd be interested if there is another solution out there, but it seems clear to me that this is not an issue with your code, but rather with the Access design.

re: Change unbound combobox from number to text

Thanks for looking. I think i will not allow the ability to back up and change previous options, and add an "Add New PN" button, and actually close the form and reopen it, when it is pressed. Thanks again.

re: Change unbound combobox from number to text

I'm sorry to hear that K. That's a really smooth interface. I expect a little tidying of the code would make handling multiple ComboBoxes for each option quite straightforward.

The Select Case structure would make reading and understanding the code much simpler, therefore developing the extra complexity relatively simple in itself.

Your call at the end of the day though of course.

re: Change unbound combobox from number to text

kstevens, I had the same issue as you and just figured out a solution. I have a combo box where my end users will choose a "search by" selection, meaning record ID, division code, submitter, etc. The selection made will populate the record source of the next combo box with a SQL statement so when they drop down the 2nd box, the specific values related to the first box are the choices.

Example: They choose ID in the first box, so the 2nd box lists all the record ID's from the table. If they choose DIV in the first box, then all the DIV values are listed in the 2nd box. Very much as what you're trying to do. And like you, when they choose to search by DIV first (text value) and then by ID (numeric), everything is good. If, however, they FIRST choose to search by ID and then by DIV, they receive the "invalid value" error message you mentioned in your first post.

I've found if I add in the OnChange event and type in:
It converts the ID value to text and Access then treats it that way. Now when I choose DIV or any other text selection, it works.

Sure hope that helps you as it did me.