Suggest a record based on user entry

[bell]

Hi all,

Not sure how to word this...or if this is even possible...

I have a form that displays records based on the Part Number (primary key) and Serial Number (secondary key)
I would like Access to suggest records based on the users input for the Part Number and Serial Number

For example, if I start typing 94 into the Part Number field, I want it to bring up the suggestions like 94000 and 94001 etc, and I can pick a suggestion and it will pull up that record (with that Part Number for the first Serial Number)



If its possible, I want the same thing to happen with the Serial Numbers, so if I bring up Part Number 94000 (lets say it brings up Serial Number 1) I want to be able to type into the Serial Number field (lets say 10) and it will bring up suggestions like 10 100 1001 etc....

Could anyone help me with this?

[JoeM]

So, it sounds like you want to select these values from a pre-defined list.
Have you thought about using a Combo Box for these fields? You can either type in the value you want, or select it. If you start typing it, it will jump to the first number that matches what you have typed. If your entries are entered as "Text" and not "Numeric", values like 10, 100, 1001 will all appear together (instead of sorted numerically by value).

[bell]

I dont think combo boxes are good for me because its not predefined, new Part Numbers could be added at any point.

I know Combo Boxes allow for entries that are not predefined, but lets say if I had a Combo box with some Part Numbers, and I added a new Part Number by typing it in (not defining it in the combo boxes Row Source), if I go back to the Form at a later date, that new Part Number wouldnt be in the Combo Box

[JoeM]

if I go back to the Form at a later date, that new Part Number wouldnt be in the Combo Box

It can be, if you do it right.
If you make the Combo Box source be the actual field from the table you are populating, it will work. It will allow you to add new entries, and the next time you go in there, your new entries will be available to select from too.

The SQL code for the Row Source of that Combo Box will look something like:

SELECT DISTINCT TableName.PartNumber 
FROM TableName 
ORDER BY TableName.PartNumber;

[bell]

Ok, I am closer but not quite there...it doesnt look like it opens a record based on the combobox

I have two combo boxes, one for Part Number and one for Serial Number...

I now have this problem...
Let say I am on a record (Part Number 54321) and all the info for this record is displayed on the Form, now when I use the combo box to switch the Part Number to 94000, I get an error
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship...."

Basically, changes in the combo box for the Part Number will not bring up the records that have the same Part Number, it keeps all the other field info for the Part Number 54321 but changes the Part Number to 94000....so I get a duplicate problem because they share the same Serial Number (1 in this case)....its trying to make a new record instead of pulling up an existing one

From the Combo box wizard I picked "I want the list box/combo box to look up the values in a table or query"....should I have picked "Find a record on my form based on the value I selected in my list box/combo box"? I want the first record for the Part Number entered to be displayed, but I also want the ability to enter new Part Numbers

[JoeM]

OK, I may have misunderstood the relationship between your multiple combo boxes. I didn't realize that you want to display other information based on your combo box selection. You may want to looking at using a SubForm, that once you make your Combo Box selection, it populates your SubForm with the data related to that.

[bell]

Ok thank you! The subform idea worked much better!

I have one last question, now when I open the Form I get focus on a field that is below my subform, how can I change it so that on open the form gets focus on either the subform or the unbounded combobox (on top of the subform) ... or just the top of the Form

[Edit]: Never mind I got it