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

[M Costumes]

I'm working in an inventory database. I have a form where you can A) enter a new item/record or B) update an existing item/record. I have a combo box that I set up using the wizard to display the selected record on the form by referencing the ItemID number. Some ItemID numbers are 3-digits and some are 6-digits. The RecordID is a separate thing, and is autonumbered.

Selecting from the drop-down part works beautifully. But I also want to be able to type in the ItemID--which does not always work. It will work for the 3-digit IDs, but when I try for a 6-digit it gives me the "the text you entered isn't an item on the list" error. It does predictive type until I fill 3 digits, then it stops. All the 6-digit IDs are in the drop down and I can access them that way. But eventually there will be hundreds, and that's just a PITA to do that much scrolling.

In the table, the ItemID number is set to "number". In the cbo box I also set the format to "general number". Are there any other settings I could check/change? I'm also wondering if changing the 3-digit IDs to 6-digits is the obvious fix here?

I'm very new to this, and don't have a computer background. I appreciate your help! Thank you.

 

[theDBguy]

Hi. It might help if you could post the row source for your Combobox and also some its properties like bound column, column widths, and column count.

 

[M Costumes]

Hi. It might help if you could post the row source for your Combobox and also some its properties like bound column, column widths, and column count.

Row source: SELECT [Inventory].[RecordID], [Inventory].[InventoryID] FROM Inventory;
column count: 2
column width: 0";1"
bound column: 1

When I set the cbo up with the wizard, I selected to hide the key field [RecordID] so only the numbers associated with [InventoryID] appear in the cbo.

 

[arnelgp]

more or less how many items in the combo?

 

[theDBguy]

Row source: SELECT [Inventory].[RecordID], [Inventory].[InventoryID] FROM Inventory;
column count: 2
column width: 0";1"
bound column: 1

When I set the cbo up with the wizard, I selected to hide the key field [RecordID] so only the numbers associated with [InventoryID] appear in the cbo.

Hi. Thanks. The only reason I could think of then is whatever is displayed may not seem to be what is actually stored in the table.

Can you post some sample data?

 

[M Costumes]

more or less how many items in the combo?

right now about 700. And there will be more. Lots, lots more.

 

[M Costumes]

Hi. Thanks. The only reason I could think of then is whatever is displayed may not seem to be what is actually stored in the table.

Can you post some sample data?

I think it's stored and displaying correctly. I use the same ID numbers to run reports and that has always worked just fine. Which is what makes me think the 3-digit ones are the wrench in the works, because they're not sequential with the 6-digit ones? Luckily there are only 43 items with 3-digit IDs so if I have to change them, it's not a disaster

I can post some sample data from the actual DB when I'm back at work tomorrow.

 

[The_Doc_Man]

What is the data type of the field that you are calling InventoryID ?

 

[M Costumes]

What is the data type of the field that you are calling InventoryID ?

Number

 

[Cronk]

Maybe you have the bound field in the combo being the autonumber RecordID which just so happens to coincide with the 3 digit ItemID but does not coincide with the 6 digit ItemID.

 

[arnelgp]

Maybe you have the bound field in the combo being the autonumber RecordID which just so happens to coincide with the 3 digit ItemID but does not coincide with the 6 digit ItemID

RecordID is hidden, so the combo will search on the Visible column.

 

[gemma-the-husky]

@M Costumes

The combo box searches and displays the first non-zero width column, so in your case, it will be inventoryid, rather than recordid.

It seems strange having both those terms, as to me, they look very similar. Maybe your users are expecting to search using recordid, and that is why the combo box isn't working as they expect.

once you select an item however the value of the combo box will be column 1, the recordid.

 

[M Costumes]

RecordID is hidden, so the combo will search on the Visible column.

Yes, it does. Except it does not acknowledge the items with 6-digits when I type. But I can select from the drop-down.

 

[M Costumes]

@M Costumes

The combo box searches and displays the first non-zero width column, so in your case, it will be inventoryid, rather than recordid.

It seems strange having both those terms, as to me, they look very similar. Maybe your users are expecting to search using recordid, and that is why the combo box isn't working as they expect.

once you select an item however the value of the combo box will be column 1, the recordid.

I'm the only one using it right now, and I don't use the recordID for anything--I have it so that it is the PK that will never change. There is the possibility that the numbering system for the InventoryID may change years from now, so I didn't want to use the same number for both the PK and what is essentially the serial number for inventoried stock.

As the RecordID is the PK, it is hidden in the cbo, so it shouldn't be searching it.

 

[M Costumes]

@theDBguy Here's a copy of some of the table data the cbo is pulling from. I've tried creating a new cbo, and it does the same thing--the RecordID (PK) is hidden, the InventoryID is column 1 which is bound. It will let me type to search for anything that is 3-digits, but says anything with 6 is "not an item in the list". This did work once upon a time, but it was before I introduced the 3-digit numbers. So I'm wondering if I need to change those to 6-digit? Or if it is something else?

 

[theDBguy]

@theDBguy Here's a copy of some of the table data the cbo is pulling from. I've tried creating a new cbo, and it does the same thing--the RecordID (PK) is hidden, the InventoryID is column 1 which is bound. It will let me type to search for anything that is 3-digits, but says anything with 6 is "not an item in the list". This did work once upon a time, but it was before I introduced the 3-digit numbers. So I'm wondering if I need to change those to 6-digit? Or if it is something else?

Hi. Thanks! Please take a look at the attached modified copy of your db. All I did was create a form and added a combobox using the Wizard.

Try typing a six-digit InventoryID in the Combobox and let us know if you still get the error.

 

[M Costumes]

Hi. Thanks! Please take a look at the attached modified copy of your db. All I did was create a form and added a combobox using the Wizard.

Try typing a six-digit InventoryID in the Combobox and let us know if you still get the error.

It's working. No error. It looks like the only thing different you did was add the ItemName to the cbo?

 

[theDBguy]

It's working. No error. It looks like the only thing different you did was add the ItemName to the cbo?

You can take it out, just to test. I only added it because I wasn't sure what else you actually needed, and I wasn't familiar with your data, so seeing something descriptive helped me make sense of the choices a little bit.

 

[M Costumes]

You can take it out, just to test. I only added it because I wasn't sure what else you actually needed, and I wasn't familiar with your data, so seeing something descriptive helped me make sense of the choices a little bit.

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.

 

[M Costumes]

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.