Find a record with more than 1 primary key

[BarbQb]

Hi All.

I am working with a form to lookup records in one of my tables. The table has 3 primary keys and is not pulling up the records correctly.

Form1 is bound to Table1 (a list of Customers and CustomerID's) ComboBox1 looks up the Customer.

SELECT tblCustomers.CorpID, tblCustomers.Customer 
FROM tblCustomers 
ORDER BY tblCustomers.Customer;

SubForm2 is bound to Table2 (list of Products by CustomerID) ListBox2 gives a list of products per customer.

SELECT tblCustReq.Product_ID, tblCustReq.CORP_ID, tblCustReq.ShipToID 
FROM tblCustReq 
WHERE Forms!ProductReq2!Customer=tblCustReq.CORP_ID ORDER BY tblCustReq.Product_ID;

The problem is when there 2 of the same ProductID's per customer then the subform will only show the record information of one of the Products no matter which one I choose.

Table2 has 3 Primary keys: CustomerID, ProductID, & ShipPoint. So the problem is when I am choosing between two products that have the same CustomerID & ProductID.

I have the form requery on current and after update. The recordset is

Private Sub ProductList_AfterUpdate()
    ' Find the record that matches the control.
 
    Dim ts As Object
 
    Set ts = Me.Recordset.Clone
    ts.FindFirst "[Product_ID] = '" & Me![ProductList] & "'"
    If Not ts.EOF Then Me.Bookmark = ts.Bookmark
 
'Me.Requery
 
End Sub

I tried

Private Sub ProductList_AfterUpdate()
    ' Find the record that matches the control.
 
    Dim ts As Object
 
    Set ts = Me.Recordset.Clone
    ts.FindFirst "[Product_ID] = '" & Me![ProductList] & "'"
    ts.FindNext "[ShipToID] = '" & Me![ShipToID] & "'"
    If Not ts.EOF Then Me.Bookmark = ts.Bookmark
 
'Me.Requery
 
End Sub

but it didn't do anything.

Any help would be great.

[BarbQb]

Is the problem really obvious and I'm just not getting it? Or does no one know how to do this?

[NeoPa]

I expect this is because your question is so hard to understand. I knew it would be complicated when I saw the bit about multiple Primary Keys. All of us looking to answer questions know that multple PKs is not possible. Generally, a question left for three days is a strong indication that it's difficult to understand. 95% of the luck involved in getting answers, isn't luck at all, but is how the question is presented.

I was further confused when I read on because your description includes a reference to Table1, whereas the SQLs posted refer to [tblCustomers] & [tblCustReq]. I struggled on but when I got to the end I found I understood very little and wasn't in a position to offer any guidance. I'd be surprised if I were the first one to get to that point.

[dsatino]

I'll have to agree with NeoPa on that one. Your question is a bit hard to understand. Here's my best interpretation of what you problem is:

For starters, as NeoPa mentions, you can't have 3 primary keys so I'm guessing your primary key is a composite of these three fields.

As for your forms it sounds like you should have something like this:

- A form based on query that is filtered by combobox1.
- A subform that is based on a query that is filtered by combobox2. The default view of this subform should be set to continuous.

As longs as you have the correct parent/child relationships between the forms you really don't need a bunch of code.

[BarbQb]

NeoPa - Thanks for letting me know. I will be more clear next time.

dsatino - Thanks for responding. I definitely used the wrong terms when I asked my question. My primary key is 3 fields, and my form has the correct parent/child but it still is not giving me the correct record.
When I choose a Customer from ComboBox1, it filters the ProductID's in ComboBox2, which filters the subform. The problem is when there are two of the same ProductID's for one customer, I will only see the info for one of the ProductID's no matter which one I choose.

[NeoPa]

Your lines #7 & #8 of your last attempted code do a FindFirst() then a FindNext(). This is logically not what you are looking for. You need a single FindFirst() where the criteria parameter specifies both field values you're looking for.

Something like

    ts.FindFirst "([Product_ID] = '" & Me.[ProductList] & "') AND " _
    "([ShipToID] = '" & Me.[ShipToID] & "')"

This will find a record that matches both the [Product_ID] and the [ShipToID], rather than a record that matches the [ShipToID] and is found after the first record to match the [Product_ID].

PS. Actually you'd probably want to include the third field in the criteria too, but I don't believe we have any info on that so you'll have to do that yourself. The fundamentals are all there so it shouldn't be complicated if you have access to all the relevant information.

[dsatino]

Can you attach your db?

[BarbQb]

dsatino - Thank you for still having an interest in this. I haven't had a chance to work on the DB today, but I will post it as soon as I can.

NeoPa - Thanks for all of your help so far. I did try the code that you suggested, but it did not seem to make a difference.

[NeoPa]

BarbQb:
NeoPa - Thanks for all of your help so far. I did try the code that you suggested, but it did not seem to make a difference.

The code was only an indicator. I don't have enough reliable information to code it for you (not that doing it for you is my wish even). I suggest you post what you actually tried and we can look to see if you've missed anything. I would not expect a simple copy-paste of my code to work for you (as explained in the post itself).

[BarbQb]

NeoPa - I will post the different variations of the code that I actually used.

The 3 fields of the Primary Key are CorpID, ProductID and ShipToID

I have attached the DB. Any comments and advice are appreciated.

[mshmyob]

It could be that your table design looks wrong. The tables do not appear related to each other properly.

I would suggest reading up on normalization.

cheers,

[NeoPa]

BarbQb:
NeoPa - I will post the different variations of the code that I actually used.

Whenever you're ready then...

I hope you don't consider an attached database as posting the code. That's just an attached database. I never even open them myself unless I've asked for, or it's not possible for some reason to continue the thread in the thread itself for some reason. I see no such reason here. Please post the code here so it can be viewed easily.