limit to list issues with combo box

[striker73]

I have a combo box that is pulling job IDs from my Job table. The rowsource of the combo box is "SELECT [Job].[Job_ID] FROM Job" I have Limit to List set to "No" so that a user will be able to type "1*" in the combo box without getting an error message.

This doesn't appear to work because I still get the error message: "The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits." I'm guessing that it is having issues because I am entering a string value and not a numeric value, since the Job_ID field is a number.

I have tried using the NotInList procedure to override the error message, but it seems like that code only runs when "Limit to List" is set to "Yes." I am able to suppress the error message, but the combo box still demands that I select a value in the list. Does anyone have any ideas on how I can get around this? Thanks!!

 

[AccessMoron]

It would appear that the message you are getting is because the combo box is bound to a numeric field on your report.

 

[striker73]

I believe that is correct. Is there any way to get beyond that? Is there any way that I can supress the error message?

 

[Bry12345]

Is there some reason that your Job_ID is not a text field? That is what is causing your error, as you suspected.

Generally speaking, even though a field will contain numbers, you need not make the data type one of the numerical types unless you plan to perform numerical calculations on that field.

A 'Job_ID' type field doesn't typically require numerical calculations, so you can probably reset the data type for that field and solve your problem. - - - -

Bry

 

[striker73]

I'm just worried that if I change Job_ID to a text field then it will mess up other areas of my database, ie. if I have select statement that search for Job_ID = 1, I will have to go back and put single quotes around the 1, correct?

 

[Bry12345]

Yes, that's true, because those have now become strings, not numerical values.

Guess it's a tradeoff. - - - -

Bry

 

[striker73]

Another drawback to changing the Job_ID to a text field would be that I wouldn't be able to order by Job_ID. I would get something like:

1
10
11
2
3
4
5
6
7
8
9

 

[striker73]

Another idea that I have been tinkering with is having a combo box and a text box that I will show and hide depending on what the user types with the keypress procedure. It sort of works, but it's definatley not seamless (sometimes it loses the value, after it switches to the textbox, it highlights the entire textbox so when you type something else, you lose all the values you just typed, etc).

I was just wondering though, if I created a temp table where I instered the job_ids as strings and then pulled the values from there, if that would take up too much processing time (what if there are thousands of jobs in the database), etc.

Another idea that I haven't tried out, is to pull the Job_ID from a query. I know if you pull a field from a table, it maintains the field type, but what if you typed [Job_ID] & "" for the query field. Would this change the field to a string? Just doing a little brainstorming. Thanks for your help

 

[Bry12345]

I don't believe that will work, but I'm not positive. I believe that the data type remains fixed.

I guess from your first post that in that instance with the '1*', you want to select all jobs beginning with '1'?

It seems that you want to be able to accomplish sorting and other tasks that require both text and numerical values for the same field. It might be possible to accomplish some tasks by building recordsets and making use of variant definitions, but I have given this a lot of thought.

One solution might be to add some other column (such as a record entry date/time) that when sorted would mimic a sort on job_id if it were numerical.

Another solution might be to store the job_id in the table in two columns, one as a number and the other as text. You could then use whichever column would be appropriate for the task. (This stikes me as having something wrong with it, design wise, but I'm not sure why.) If your records are entered via a form, you could add the value to the second column with a bit of code prior to writing the record. - - - -

Bry

 

[striker73]

I think those might work, but for now I think I've got it working pretty nicely. I made a query that just took the field and added "" to it. This converted it to a string and now it doesn't complain when I enter "1*". Thanks so much!

 

[Bry12345]

You're welcome . . . but thank you for the "" tip! - - - -

Bry