Datasheet combobox population by query

[Timo van Esch]

Hi,

First of all: Thanks to all posters.
I've learned a lot from this forum.

Currently I'm stuck with a datasheet-form.
The datasheet is populated in a subform, based upon a table.

SELECT DEV_List.*
FROM DEV_List;

Within this datasheet, I have several combo boxes.
It is easy to give them a query to show all values possible.

SELECT tbl_PPV_RPM.Createdby
FROM tbl_PPV_RPM
GROUP BY tbl_PPV_RPM.Createdby;

This gives me a list of all possible values in "tbl_PPV_RPM.Createdby".

What I want, is to be able to select only the relevant values in this combobox, based upon a materialnumber in the same row.

"tbl_PPV_RPM" contains a row named "Material". The datasheet also contains a row named "Material".
These need to be linked. When I try to link them through the query builder, I get this:

SELECT tbl_PPV_RPM.Createdby
FROM tbl_PPV_RPM
WHERE (((tbl_PPV_RPM.Material)=[Forms]![frmSub_TD_List_Edit].[Material]))
GROUP BY tbl_PPV_RPM.Createdby;

But then I get a popup box, requesting for the Material number in "frmSub_TD_List_Edit".
So, it does not recognise the Materialnumber in the row I'm trying to select a value.

Anyone any idea how to solve this?

 

[CJ_London]

Not quite clear what you are saying but a couple of suggestions

SELECT tbl_PPV_RPM.Createdby
FROM tbl_PPV_RPM
GROUP BY tbl_PPV_RPM.Createdby;

suggest this would be better

SELECT DISTINCT tbl_PPV_RPM.Createdby
FROM tbl_PPV_RPM;

You only really need to use group by if your query is also summing, counting etc

"tbl_PPV_RPM" contains a row named "Material". The datasheet also contains a row named "Material".

Do you mean column or field? Or is Material one of the values in a column?

Assuming you mean column or field then you need to put this VBA code in the enter event of the combo control

Private Sub myCombo_Enter()
    myCombo.Rowsource-"SELECT DISTINCT Createdby FROM tbl_PPV_RPM WHERE Material=" & [Material]
End Sub

The problem is that the rowsource applies to all the rows in the column, so you will find that other rows with different materials will seem to go blank because whatever has been selected is not in the list for the material for the current row.

To partly counter this, the 'default' rowsource for everything can be set in both the form current event and the myCombo Exit event - the default being

myCombo.Rowsource-"SELECT DISTINCT Createdby FROM tbl_PPV_RPM"

So when you leave the combo, the other values will reappear.

If you still want to see the other values whilst the combo is being edited you will either need to make the combo unbound and have a locked bound control for the value (both will show) or you will need to use a continious form and use some vba 'trickery' to hide or display the combo box or the textbox

 

[Timo van Esch]

Hi CJ_London,

Thanks for your reply!
Meanwhile we were looking for a solution too and found indeed the same thing as you did:

ComboPOC.RowSource = "SELECT tbl_RPM.Createdby FROM tbl_RPM
GROUP BY tbl_RPM.Createdby, tbl_RPM.Material
HAVING (((tbl_RPM.Material) = '" & Me.Material & "'));"
ComboPOC.Requery

This works like a charm!
Thanx mate