4 Tier Cascading Combo where last combo looks up a record (1 Viewer)

[jleval]

:banghead:
Okay, I have been at this for some time, searching for a way to put these cascading combo boxes into practice. I keep getting all kinds of problems. Can someone give me a detailed description as to how to put these cascading combo boxes into practice.:banghead:

Note: I am using a GUID as a primary key

I have a form that has four combo boxes. What I wish to do is have my last combo box selection look up a record.

My form is named batch and pulls from 3 tables
My 1st combo box is named cboPartNumber
My 2nd, cboTraceNumber
My 3rd, cboPONumber
My 4th, cboInvoiceNumber

My first one is supposed to hold what where?
My second one is supposed to hold what where?
My third last one is supposed to hold what where?

I have been referred to several other links and none seem to work right.

PLEASE, PLEASE Help. :banghead:

 

[jleval]

Is this question really that difficult. I have really been trying to put this together. I have tried all kinds of methods. Here is my code. What Am I doing wrong?

Private Sub cboPartNumber_AfterUpdate()
cboTraceNumber.Requery
End Sub

RowSource in cboTraceNumber
Select BatchID, PartNumber FROM Batch Where PartNumber = cboPartNumber

My Table Structure(Batch) is as follows:

BatchID -Replication ID
ParNumber-text
TraceNumber - int
PONumber - int
InvoiceNO - int

I mean, why wont my combo box pull data from the other one?:banghead:

 

[pbaldy]

If that's the actual row source of cboTraceNumber, you need to full form reference to the first combo:

http://access.mvps.org/access/forms/frm0031.htm

 

[jleval]

SELECT Batch.TraceNumber, Batch.PartNumber, Batch.TraceNumber FROM Batch WHERE (((Batch.PartNumber)=[Forms]![Batch]![cboPartNumber]));

This is what I have and it still pulls up a blank. When I take out the WHERE in my query it gives me the trace numbers. I just cant get it to reference the combo box

 

[pbaldy]

I would suspect a mismatch between what the combo is returning (the bound column) and the value expected by the query.

 

[jleval]

Well, Its finally been figured out.
Here is the code within the module

Private Sub btnClearSearch_Click()
Me.cboInvoiceNumber = Null
Me.cboPartNumber = Null
Me.cboPONumber = Null
Me.cboTraceNumber = Null
Me.Recalc
Me.Requery
End Sub

Private Sub cboInvoiceNumber_AfterUpdate()
Me.Recalc
Me.Requery
End Sub

Private Sub cboPartNumber_AfterUpdate()
Me.cboPONumber.Requery
Me.cboPONumber.Requery
Me.cboTraceNumber.Requery
Me.Requery
End Sub

Private Sub cboPONumber_AfterUpdate()
Me.cboPartNumber.Requery
Me.Requery
End Sub

Private Sub cboTraceNumber_AfterUpdate()
Me.Recalc
Me.Requery
End Sub

Here is the code in my first combo box

SELECT DISTINCT Batch.PartNumber FROM Batch WHERE (((Batch.TraceNumber) Like Nz(Forms!Batch!cboTraceNumber,"*")) And ((Batch.PurchaseOrderNumber) Like Nz(Forms!Batch!cboPONumber,"*")) And ((Batch.InvoiceNumber) Like Nz(Forms!Batch!cboInvoiceNumber,"*"))) ORDER BY Batch.PartNumber;

Here is the code in my second combo box

SELECT DISTINCT Batch.TraceNumber FROM Batch WHERE (((Batch.PartNumber) Like Nz(Forms!Batch!cboPartNumber,"*")) And ((Batch.PurchaseOrderNumber) Like Nz(Forms!Batch!cboPONumber,"*")) And ((Batch.InvoiceNumber) Like Nz(Forms!Batch!cboInvoiceNumber,"*"))) ORDER BY Batch.TraceNumber;

The Code in the Third:

SELECT DISTINCT Batch.PurchaseOrderNumber FROM Batch WHERE (((Batch.PartNumber) Like Nz(Forms!Batch!cboPartNumber,"*")) And ((Batch.TraceNumber) Like Nz(Forms!Batch!cboTraceNumber,"*")) And ((Batch.InvoiceNumber) Like Nz(Forms!Batch!cboInvoiceNumber,"*"))) ORDER BY Batch.PurchaseOrderNumber;

This allows any combo box to be picked and filters the record down

 

[pbaldy]

Glad you got it sorted out.

 

[MikeNificent]

I've got a question here, what code goes into the row source for the fourth combo box (cboInvoiceNumber)????