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.
F0,
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

Thanks

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

http://www.mvps.org/access/forms/frm0028.htm

post if you encounter any problem

efurhole

ASKER

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..

efurhole

ASKER

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

ASKER CERTIFIED SOLUTION

Hamed Nasr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial