Table field1 lookup query filtered by value of field2

[spiessa]

Hi
I use a query in row source property in table lookup.

Table FilterSets
DocType
Description

Table Probe
Probe Name
DocType

I would like, that the lookup function only shows Filterset descriptions which have the same DocType as the Probe record currently under work

If I use the following query, I get all valid combinations of all DocType in both tables, not only the one of the current Probe record.

SELECT   [19_FilterSets].ID
       , [19_FilterSets].DocType
       , [19_FilterSets].description
FROM     05_Probe
         INNER JOIN
         19_FilterSets
  ON     [05_Probe].DocType = [19_FilterSets].DocType
WHERE ((([19_FilterSets].DocType)=[05_Probe]![DocType]))
ORDER BY [19_FilterSets].description;

I think, I should replace =[Probe]![DocType] by the real value of DocType of the current Probe record.

Any help appreciated
Andreas

[NeoPa]

Line #8 is entirely redundant for a start.

Otherwise I'm not sure what you're asking.

Why is [05_Probe] even included in this?

[spiessa]

Hi NeoPa

you are right with line 8. It was intended as explanation.

My problem in other words: I am editing table Probe and want to add content to the field [Description]. I would now like to get a combo box with all records of table FilterSets which contain the same content of the field [DocType] as the content of {DocType] in the currently edited record of table Probe,
e.g. if [DocType] of currently edited record of Probe is "SalesOrder ", I would like all FilterSets with [DocType] "SalesOrder ". Currently, I get many more records (also with "Deliveries " and "Invoice" DocType (which is obvious with the current SELECT statement). I thought, with a better WHERE clause the problem could be solved...

Regards
andreas

[NeoPa]

I'm sorry for the delay in responding. I was away all last week.

Now I've read your response I'm struggling to understand what you're trying to say. It may make more sense if I had your db in front of me, but without that it makes little I'm afraid.

I guess you're looking at restricting the values shown in a ComboBox. As the information available in the explanation is so sparse, perhaps you could post the current RowSource value for the control you would like changed and we can start from there.

[TheSmileyCoder]

I believe what your looking for is "cascading combobox", which you can find alot of good information on, if you search this forum.

A few changes, lets assume your form is called frm_Filter, and you select your DocType in a combobox called cmb_Type, then modify the rowsource of your combobox to:

SELECT   [19_FilterSets].ID 
       , [19_FilterSets].DocType 
       , [19_FilterSets].description 
FROM     19_FilterSets 
WHERE ((([19_FilterSets].DocType)=Forms![frm_Filter]![cmb_Type])) 
ORDER BY [19_FilterSets].description;

I think, I should replace =[Probe]![DocType] by the real value of DocType of the current Probe record

That is where the Forms![frm_Filter]![cmb_Type] comes in.

Now this is not enough, as recordsets of comboboxes are only requeried when the form loads. So we have to add some code to the cmb_Type_AfterU pdate, such as:

Private Sub cmb_Type_AfterUpdate()
  Me.cmb_XXX.Requery
End Sub

where cmb_XXX is whatever combobox you have your recordsource in.