combobox filled based on another field

[Pierkes]

Hi,"

I have a form called "frm_detail"
On the form i have two combobaxes called

cbo_soort
cbo_fase

cbo_soort has de items (i use dots to seperate the items, cannot put a table in place)

ID_soort.....Soort_traject
1.................nieuw
2.................verlenging
3.................uitbreiding
4.................anders

cbo_fase is based on a Query called "Q_fase" which has the items;

ID_fase.....Fase.......Soort_traject
1................fase 1.....N
2................fase 1a....V
3................fase 1b....B
4................fase 2.....B
5................fase 3.....B

Question: I would like to be able to do the following;

If cbo_soort = "2" then i would like the list in cbo_fase to only show the items where Soort_traject is V or B (so N should be discarded)

If cbo_soort = <> "2" then i would like the list in cbo_fase to only show the items where Soort_traject is "N" or "B"

I am trying to find a solution for the "criteria" part in the Q_fase qury on the field "Soort_traject" but i can't figure out how to do this...

Any help would really be appreciated.
Thanks,
Pierkes

[jimatqsi]

In the After_UPdate event of cbo_soort you want to change the rowsource property of the other combo box. You can fill the rowsource with a query string if the combo box rowsourcetype property is "table/query".

Are you able to use VBA to form the necessary query string?

Jim

[Pierkes]

Hi Jim,

Yes, if nessesary i can use VBA, however, what the string should be...??? i do'nt know, can you help ?

thanks,
Pierkes

[twinnyfo]

Private Sub cbo_soort_AfterUpdate()
    Dim strQL As String
    strSQL = "SELECT * FROM Q_fase "
    If Me.cbo_soort = 2 Then
        strSQL = strSQL & _
            "WHERE Soort_traject = 'V' " & _
            "OR Soort_traject = 'B';"
    Else
        strSQL = strSQL & _
            "WHERE Soort_traject = 'N' " & _
            "OR Soort_traject = 'B';"
    End If
    Me.cbo_fase.RowSource = strSQL
    Me.cbo_fase.Requery
End Sub

You may encounter problems when cbo_soort equals 3 or 4 (what do you want it to do in those situations?

[Pierkes]

Hi twinnyfo,

In case 3 or 4 i want the same as in case 1. The only thing that is differten is case 2.

Regards and thank you very much for your help !,
Pierkes

[twinnyfo]

Then the code provided should either work or get you very close to your solution... Hope it helps!

[Pierkes]

Hi twinnyfo,

Your code works perfectly.
I have only one problem...

When the form now loads, it does not show anything in the cbo_fase field which is not correct because usually there is something in that field in the database. The field in the database is called [tr_fase] which is also the controlsource for cbo_fase.

How can i make it so when the form opens it shows the entry in the [tr_fase] field in the cdb_fase and when i change the value of [Soort traject] the dropdown box gives the wright listing ?

Thanks for your help !

[twinnyfo]

Pierkes,

The combo box should have a row source that you have already established for it. The only thing my code should have changed is how the combo box looks up rows after cbo_soort is updated. Does this make sense?

[Pierkes]

Hi,

No, this does not make sense...the entry is in the field, but i do not see it because the form waits until i come to the afterupdate of the [Soort traject] part.

then the field entry is visible again in the [tr_fase] combobox.

How to move on from here, i do not know...
Any ideas ?

Regards,
Pierkes

[twinnyfo]

When you open your form in deesign view and click on cbo_fase. In its properties, what is its row source? At a minimum, it should be:

SELECT * FROM Q_fase;

You may also run into additional problems with this type of set up with multiple records, because once you select a value for cbo_soort and move to a different record, if that record does not have the same value for cbo_soort, the value for cbo_fase will not show up.

One work-around is to have the code in post #4 in the OnCurrent event for the Form (or as a separate procedure called from the Form's OnCurrent event and the cbo_soort AfterUpdate event.

It can take some playing around, but it is not too difficult. You have to remember that combo boxes, when bound are looking for the row in its row source that meets the value of its control course. If it isn't there, it is blank.

Hope this hepps.

[Pierkes]

Hi twinnyfo,

That was indeed the problem !
Thanks you very much for your help !

I put in;

SELECT Q_Fase.ID_Fase_N, Q_Fase.Verkoop_Fase_N FROM Q_Fase;

And it workt perfectly.

Best regards,
Pierkes

[twinnyfo]

Glad this could help! Hope to see you here again!