Another combo box duplicate problem

[Sp3aK]

Before I posted I did do a search and came up with most of my answer, but now I'm having a problem with the execution of it. I have a form called "storage_form" which has a combo box on it for the DESCRIPTION field of my table. Like alot of people I want to have duplicates eliminated because there are alot of redundant parts, etc. The main reason for a type-in combo box was to have the AutoComplete, again because there are alot of redundant parts.

Anyways my row source for the combo box is such:

SELECT DISTINCT storage_table.[Primary Key], storage_table.Description FROM storage_table ORDER BY [Description]

I have Column Count set to 2, Column Width set to 0";1.5417", Bound Column set to 2, Limit To List set to NO, and AutoExpand set to YES. I set the bound column to 2 because I was getting an error message saying that, "The value you entered isn't valid for this field"

The other problem I am encountering is that if I type something in the combo box other than what is already listed, and then add that record, when I am adding the next record that value is not available in the list until I close the form and open it again.

So what am I doing wrong?

 

[Fernando]

Why are u using DISTINCT for a primary key?

If you want to show the Description value but Store the [Primary Key] value you properties must be set like

Column Count..... 2
Column Widths.... 0
Bound Column..... 1

Limit to list must be set to Yes

Use the NotInList event of your combobox if you want to do something with the data that is not in the list

(as a recomendation if you can change the name of your field [Primary Key] to maybe StorageID or fldStorageID it will save you headaches in the future)

 

[Sp3aK]

Uhmmm, I can't explain that. I just read on another forum post that you can use SELECT DISTINCT. So the Row Source was previously setup the same as that minus the DISTINCT. I just assumed I could add that bit in there. I just changed it to the following:

SELECT DISTINCT storage_table.Description FROM storage_table ORDER BY [Description];

When I opned the form the value for the previously entered records are there but if I navigate to a new record (or click the drop down arrow for a previous record) there is nothing being shown in the drop down area for the combo box.

*EDIT*
And if I leave it like I had it in the first post it still just shows the duplicates (or all entries, whatever).

*EDIT #2*
Just noticed the Row Source Type is set to Table/Query -- Could this be a problem?

 

[Fernando]

Thats fast, i edited my post above, see if some of that helps u.

Is you combobox bounded? Are you trying to store the same description of other records on new records?

 

[Sp3aK]

I see now how I could be running into a problem, I just didn't think about it before.

To setup this combo box I just used the wizard. The primary key has nothing to do with this combo box, so I don't know why it's there. In my table there is an autonumber set for the primary key, because these are going to be parts kept in our warehouse. Each box contains any number of parts, which is why each record gets an autonumber as a primary key. So for instance I have box number 2000 that contains 3 parts with different part numbers and quantities, but all the same description.

OK NOW! I believe I just figured it out... Since the Primary Key has nothing to do with this (I thought it was there because it needed to be, and thats how the combo box wizard set it up even though I only selected DESCRIPTION as a field to choose from). To solve this I changed to the following:

Column Count : 1
Column Width: 1.5417"
Bound Column: 1
Limit To List: No
Row Source: SELECT DISTINCT storage_table.Description FROM storage_table ORDER BY [Description];

I see now how what I posted originally is quite confusing as I didn't completely explain the purpose of this combo box.

Now, I'm on to my second "dilmena" which is: I have the form open, I enter parts in, but I get to a point where I have a part description that I haven't had before. I type it in for that record, add the record, but on the next new record if I have a part with that same description as the previous entry, it doesn't show up in the drop down list until I close the form and re-open it.

*EDIT*
I did a search on the forum and it appears I would need to have something in the AFTER UPDATE event area, but I can't narrow down what exactly I need to put there to get the combo box to update after I add a new record.

*EDIT #2*
Ok well I just took a stab it again and checked out the VB Help on ReQuery. I just made a VERY simple EventProcedure in the AfterUpdate area that is as follows:

Private Sub combo_box_AfterUpdate()

DoCmd.Requery "combo_box"

End Sub

And I know combo_box is a stupid name for that so I'm going to rename it now that I have everything working, because this was originally just a text field. Thanks for all the help!

 

[Fernando]

gg
you can also use instead of

DoCmd.Requery "combo_box"

the following

combo_box.Requery

 

[Sp3aK]

I'll keep that in mind, thanks again.

I definately have this forum bookmarked as I think it will come in handy later on. I am currently just adding new features to my very simple database to help the data entry process (since we have over 700 boxes of parts).