469,150 Members | 1,952 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

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
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 28 '09 #1
29 7855
32,165 Expert Mod 16PB

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.
Aug 29 '09 #2
3,532 Expert 2GB
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)>
Aug 29 '09 #3
A typical afterupdate event looks like this (this is part of the afterupdate of the no2 box)
Expand|Select|Wrap|Line Numbers
  1. If No1.Column(1) = "F" Then
  2.     Cap3.Caption = "Material"
  3.     No3.RowSource = "qryMatl"
  4.     Note.Value = "Select API material designation."
  5. End If
  7. If No1.Column(1) = "C" Then
  8.     Cap3.Caption = "Model"
  9.     No3.RowSource = "qryChokeManu"
  10.     Note.Value = "Select choke manufacturer and model."
  11. End If
  13. No3.Requery
  14. No3.Format = Text
  15. No3 = ""
  16. No3.Visible = True
  17. Me.No3.SetFocus
  18. Me.No3.Dropdown
a typical gotfocus event looks like this (this is the gotfocus event for the no4 box)
Expand|Select|Wrap|Line Numbers
  1. No5.RowSource = "qryBlank"
  2. No6.RowSource = "qryBlank"
  3. No7.RowSource = "qryBlank"
  4. No8.RowSource = "qryBlank"
  5. No9.RowSource = "qryBlank"
  6. No10.RowSource = "qryBlank"
  7. No11.RowSource = "qryBlank"
  8. No12.RowSource = "qryBlank"
  9. Cap5.Caption = ""
  10. Cap6.Caption = ""
  11. Cap7.Caption = ""
  12. Cap8.Caption = ""
  13. Cap9.Caption = ""
  14. Cap10.Caption = ""
  15. Cap11.Caption = ""
  16. Cap12.Caption = ""
  17. No5.Visible = False
  18. No6.Visible = False
  19. No7.Visible = False
  20. No8.Visible = False
  21. No9.Visible = False
  22. No10.Visible = False
  23. No11.Visible = False
  24. 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
Expand|Select|Wrap|Line Numbers
  1. no2.rowsource = "qryBlank"
the combobox becomes hidden
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 29 '09 #4
32,165 Expert Mod 16PB
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.
Aug 29 '09 #5
3,532 Expert 2GB
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)>
Aug 30 '09 #6
i tried
Expand|Select|Wrap|Line Numbers
  1. No5.Format = "@"
  2. No5.Requery
  3. No5 = ""
  4. No5.Visible = True
  5. Me.No5.SetFocus
  6. Me.No5.Dropdown
and it still failed at the same point... actually it failed before, this time it highlighted
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. No5.Column(2).Format = "@"
it failed also... "Object Required".

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
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 30 '09 #7
32,165 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. No5 = ""
should probably be changed to :
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. With Me.No3
  2.   .Format = "General"
  3.   .Requery
  4.         'For queries that need a special format only
  5.         .Format = {Whatever}
  6.   .Value = Null
  7.   .Visible = True
  8.   Call .SetFocus
  9.   Call .Dropdown
  10. 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.
Aug 30 '09 #8
This seems strange to me, but whatever i set the format to, gets updated as the information.

If I have
Expand|Select|Wrap|Line Numbers
  1. .Format = "General"
and choose a numeric value, the box data says General.... like i chose it, is that wierd?

if I have
Expand|Select|Wrap|Line Numbers
  1. .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.
Aug 30 '09 #9
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
Expand|Select|Wrap|Line Numbers
  1. .000
  2. .005
  3. .010
  4. .015
If i select
Expand|Select|Wrap|Line Numbers
  1. .010
Then my box actually says
Expand|Select|Wrap|Line Numbers
  1. General
I cant seem to make it stop, lol
Aug 31 '09 #10
32,165 Expert Mod 16PB
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?
Aug 31 '09 #11
All queries are setup like this... and i mean all of them,
Expand|Select|Wrap|Line Numbers
  1. SELECT [64K].KDes, [64K].Key
  2. FROM 64K
  3. WHERE ((Not ([64K].KDes) Is Null))
  4. 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.
Aug 31 '09 #12
32,165 Expert Mod 16PB
I'm sorry K. I haven't given up on you.

I'll make a special effort to post a proper response this evening.
Sep 2 '09 #13
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.
Sep 2 '09 #14
32,165 Expert Mod 16PB
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.
Sep 2 '09 #15
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.
Expand|Select|Wrap|Line Numbers
  1. Raw Material
  2. I-Beam
  3. Pick Any (it wont matter)
  4. Pick Any
  5. Pick Any
  6. Pick Any
  7. Pick Any
  8. Pick the only option
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:
Expand|Select|Wrap|Line Numbers
  1. Raw Material
  2. Flange Forging
  3. Pick Any (it wont matter)
  4. Pick Any
  5. Pick Any ( this is where you will hit the error)
  6. Pick the only option
  7. Pick Any
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
File Type: zip PN Creation.zip (164.4 KB, 87 views)
Sep 2 '09 #16
32,165 Expert Mod 16PB
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.
Sep 2 '09 #17
No problem, thanks
(hmm, a message has to be at least 20 characters to post... looks like I got it beat now, lol)
Sep 2 '09 #18
32,165 Expert Mod 16PB
I guess.
Sep 2 '09 #19
32,165 Expert Mod 16PB

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.
Sep 3 '09 #20
Thanks, try this one.
Attached Files
File Type: zip PNCreation.Zip (120.4 KB, 115 views)
Sep 3 '09 #21
32,165 Expert Mod 16PB

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.
Sep 4 '09 #22
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 :)
Does England really still use fahrenheit, i thought only the US did?
Sep 4 '09 #23
32,165 Expert Mod 16PB
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 :)
Sep 4 '09 #24
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.
Sep 4 '09 #25
32,165 Expert Mod 16PB
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...
Sep 4 '09 #26
32,165 Expert Mod 16PB
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.
Sep 4 '09 #27
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.
Sep 4 '09 #28
32,165 Expert Mod 16PB
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.
Sep 4 '09 #29
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strValue as String
  2. strValue = Me.SearchBy
  3. Me.SearchBy = strValue
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.
Oct 16 '09 #30

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Werner Kaiser | last post: by
1 post views Thread by Marcus Vinícius M. Montezano | last post: by
3 posts views Thread by Clemens Chiba - Greentube I.E.S. AG | last post: by
reply views Thread by Tim Cowan | last post: by
9 posts views Thread by matt | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.