Case Statement to retreive certain records

[Desitech]

I have a table entitled "Parts". In that table I have a field entitled "PartDoc" and four fields entitled "Product1", Product2", "Product3", and Product4". The datatype for these fields is Number (look-up). (the number represents a product)
So each record in the table could look like this.

PartID         PartDoc              Product1       Product2      Product3    Product4  
12               P-7824                    1                  5                  7               9

I want to do a case statement something like this.

Private Sub CmdReturn5_Click()

    Select Case ?????????
    
        Case 1
            DoCmd.OpenForm "PARTS", , , "[PRODUCT1]='5'"
        Case 2
            DoCmd.OpenForm "PARTS", , , "[PRODUCT2]='5'"
        Case 3
            DoCmd.OpenForm "PARTS", , , "[PRODUCT3]='5"
        Case 4
            DoCmd.OpenForm "PARTS", , , "[PRODUCT4]='5'"
        Case Else
            MsgBox "NO DOCUMENTS FOUND"
End Select

Exit Sub

I don't know if this is the right approach. I am new to this as a couple of you know. And I want to thank them for the help with the combobox. I have it working thanks to thier help.

Hope you can help again. Thanks

[RuralGuy]

The repeating fields look like a normalization issue to me. You might want to review this article on this site.

[NeoPa]

Desi, I suggest you :

1. Add your thanks to the thread where the responses are (Select Case Statement with ComboBox).
2. Pay some attention to the edit comments by some of your posts. I notice that you've posted before without using the [ CODE ] tags, and these have been fixed with a comment to call your attention to it. Please remember to use them in future.
3. Try rephrasing your question. You currently request code to do what your code does. Expressed that way we can only assume your code already does it perfectly.

You don't seem to be putting much effort into your posts, and this (wasting people's time) can be considered quite impolite in a place such as this where people volunteer their time for free.