Avatar of efurhole
efurhole asked on

Find record w/ combo

I have three tables:  T0, T1, and T2.  

T0, Primary Key = CustomerNumber
T1, Primary Key = LocationNumber
      Foreign Key = Customer Number
T2, Primary Key = PartNumber
      Foreign Key  = LocationNumber

There are One to Many relationships between T1 and T2/T2 and T3.

I have built a form that contains a sub form and a sub-sub form.  
F1 is a subform in F0,
F2 is a sub form in F1.

I have a combobox that is used on my main form, F0, to lookup customer numbers form table T0.  When a customer number is selected from this combobox, the appropriate record from T0 is retrieved and displayed on form F0.  Similarly, the appropriate record(s)s are displayed from table T1 in subform F1; the appropriate record(s) from T2 are displayed in sub-sub form F2.

How do I position a combobox on F0 that will enable me to conduct a lookup function on PartNumber (my primary key in the T2 table)?  Intuitively, I think Ill need to create a combobox that contains the following columns of data:  CustomerNumber, LocationNumber, and PartNumber.  Then, Ill need to write some lookup code that looks up and retrieves these records from the three fields (in a sequence order of CustomerNumber, LocationNumber, and PartNumber).  Does this sound accurate/possible?

It seems that I should be able to display the appropriate record in forms F0 (Customer Number), sub F1 (LocationNumber), and sub F2 since
1) the part number is a primary key in T2,
2) there is only one location number associated with this part number in T1,  and
3) there is only one customer number associated with this location number in T0

Microsoft Access

Avatar of undefined
Last Comment
Hamed Nasr

8/22/2022 - Mon
Rey Obrero (Capricorn1)

there are different ways of doing this,
one way is to use a cascading combo boxes, where you filter the records display in the succeeding combo based on the preceding combo box.

see the example here


post if you encounter any problem

Okay - but this will require the use to select a CustomerNumber, then a location, then a part number (if I read your link's instructions correctly).  Instead, I'd like to enable the user to select a part number and have the cascade up (I hope this is the right terminology) function performed automatically so that the Location and the CustomerNumber records are automatically selected.
Rey Obrero (Capricorn1)

yes that can be done, you just have to reversed the process.
set the values for the location and customer in the afterupdate or change event of the partnumber combo.

create your coding now and post if you need more help building the codes..
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

This is the code that is automatically generated when I use the combo box wizard in order to find one record from table TO on my main form.  After the correct record is called up from the main form (F0, T0), I then need to call up the sub-set records on the sub-Form and the sub-sub form.  Something makes me think that my code will need something special.  Without knowing for sure, I'm guessing that the code below is tailored for the main form - not the sub or sub-sub form.  What will the code look like that tells Access to initiate a Find Command on a sub and sub-sub form (on tables T1 and T2)??
 'Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CustNumber] = " & Str(Nz(Me![PartNumberLookUp], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

Open in new window

Hamed Nasr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question