combo box to find record saying the item doesn't exist--but it does. (1 Viewer)

[theDBguy]

No, that's fine...I'm just trying to figure out if anything you did was different from what I have. And everything looks identical. So I'm not sure why I get the error with my form. So weird.

So, how about this? Take the file I gave you, create a new form and add your own combobox using the Wizard and let us know if you get the error or not.

 

[theDBguy]

So I tried changing the 3-digit IDs to 6-digit, and same thing. When I try to type anything longer than 3-digits in the cbo it tells me it's not an item on the list.

Please elaborate... How did you change the 3-digit IDs, so I can try to reproduce it on my end?

PS. I just realized, looking back at your db to try and change the 3-digit IDs to a 6-digit one, that the other records you originally gave us only had 5-digit InventoryIDs in them.

 

[M Costumes]

Please elaborate... How did you change the 3-digit IDs, so I can try to reproduce it on my end?

PS. I just realized, looking back at your db to try and change the 3-digit IDs to a 6-digit one, that the other records you originally gave us only had 5-digit InventoryIDs in them.

oops, yeah, every time I said "6-digit" I guess I meant 5-digit. I pulled up my data entry form (same form I'm trying to get this CBO box to work in) and just added 90 to the beginning of the ID number and saved.

 

[M Costumes]

So, how about this? Take the file I gave you, create a new form and add your own combobox using the Wizard and let us know if you get the error or not.

Worked like a dream. I did notice that when I changed it to DataEntry > yes it would not display anything (didn't get the error though!). when I changed it to "no" it would work just fine, no errors, and display everything. My form is a data entry form, so could the issue be that I have data entry set to "yes"?

 

[M Costumes]

OK, not fully caffeinated yet. While I do use the form to enter data, I do have DataEntry set to No. So....it's not that. lol.

 

[theDBguy]

Worked like a dream. I did notice that when I changed it to DataEntry > yes it would not display anything (didn't get the error though!). when I changed it to "no" it would work just fine, no errors, and display everything. My form is a data entry form, so could the issue be that I have data entry set to "yes"?

So, there's still a difference. Setting my sample form to DataEntry=Yes produced no error, whereas yours does.

Edit: Ahh, so that wasn't it...

 

[M Costumes]

OK, so this is interesting:
Made a new cbo box on my form using the wizard. Set the format to "general number" since the column in the table it is referencing is set to "number"
All of my original 5-digit IDs all start with 10. If I start typing 10 and the next number is 0, 1, or 2, it continues with the predictive text to a point. It will accept 10115, but not 10123. It does the same thing with number starting 102. But eventually it will stop predicting if I get high enough, like it will accept 1022 but not 1023---. Numbers starting 103 and higher give me the error no matter what.

Could something be lurking in the table itself?

 

[theDBguy]

Could something be lurking in the table itself?

That's definitely possible, which is why I mentioned the thing about "what's displayed versus what's stored" stuff earlier.

Also, how many records are in your table? Essentially, how many items is listed in the Combobox? That could also mean something.

 

[M Costumes]

That's definitely possible, which is why I mentioned the thing about "what's displayed versus what's stored" stuff earlier.

Also, how many records are in your table? Essentially, how many items is listed in the Combobox? That could also mean something.

Right now there are 666 records. That's not a pleasant number--ha! And I'm not even 1/4 of the way done with entering things.

How would I check if what's stored is different from what's displayed?

 

[theDBguy]

How would I check if what's stored is different from what's displayed?

One way is to run a query like this:

SELECT InventoryID, Len(InventoryID) AS NoOfChars FROM InevntoryTable

And then check if any of the results don't match. For example, you might see something like this:

InventoryID    NoOfChars
111            3
10608          5
10376          6 <-- This is wrong

 

[M Costumes]

One way is to run a query like this:

SELECT InventoryID, Len(InventoryID) AS NoOfChars FROM InevntoryTable

And then check if any of the results don't match. For example, you might see something like this:

InventoryID    NoOfChars
111            3
10608          5
10376          6 <-- This is wrong

Ran it. Everything matched.

 

[M Costumes]

part of me wonders if I should scrap the cbo and make it a text box that runs a macro to find the record after it updates.

 

[theDBguy]

Ran it. Everything matched.

You know, part of the reason why I added the ItemName earlier is because InventoryID alone doesn't make sense to me. So, how about posting a copy of your table with all the data you have, except only give us the InventoryID and remove all the other fields? All we're trying to do anyway is try to duplicate your issue, right? Just a thought...

 

[M Costumes]

You know, part of the reason why I added the ItemName earlier is because InventoryID alone doesn't make sense to me. So, how about posting a copy of your table with all the data you have, except only give us the InventoryID and remove all the other fields? All we're trying to do anyway is try to duplicate your issue, right? Just a thought...

Here you go! thank you so much for muddling through this with me. I also left the RecordID since that is the PK.

 

[theDBguy]

Here you go! thank you so much for muddling through this with me. I also left the RecordID since that is the PK.

Great! Thanks. Okay, take a look at the attached and open Form1. The first combo (labeled InventoryID) was created using the Wizard.

The second combo was manually created.

I tried entering 10008 into both of them.

 

[M Costumes]

Great! Thanks. Okay, take a look at the attached and open Form1. The first combo (labeled InventoryID) was created using the Wizard.

The second combo was manually created.

I tried entering 10008 into both of them.

First box tried 10007 = got error. Tried a few others, all gave errors.

Second box 10007 = worked
10125 = worked
10135 = worked
10156 = worked
10201 = worked
10235 = error.
10210 = error.
90100 = error

 

[The_Doc_Man]

I'm having a little bit of trouble following the steps here and came in late, but I have a dumb question.

For the case where you wanted to enter a 3-digit number but it won't find those... have you tried supplying leading zeros on the input? Or leading blanks? Just a hare-brained idea.

 

[M Costumes]

I'm having a little bit of trouble following the steps here and came in late, but I have a dumb question.

For the case where you wanted to enter a 3-digit number but it won't find those... have you tried supplying leading zeros on the input? Or leading blanks? Just a hare-brained idea.

yes, I tried changing them to start with 90 and it did not make any difference.

 

[theDBguy]

First box tried 10007 = got error. Tried a few others, all gave errors.

Second box 10007 = worked
10125 = worked
10135 = worked
10156 = worked
10201 = worked
10235 = error.
10210 = error.
90100 = error

Thanks! That helps really narrow down the issue. I think I fixed it. Try the attached again and enter the same problematic ID in both Comboboxes.

 

[M Costumes]

Thanks! That helps really narrow down the issue. I think I fixed it. Try the attached again and enter the same problematic ID in both Comboboxes.

Everything worked in both! What voodoo did you do?