Combining 2 combo boxes in a form > 2nd Part

F

Fischkopp

Hi Klaatu and Allen,
thank you both very much for your support yesterday.
I used the way Klaatu described yesterday (Allen confirmed the steps too
yesterday)
BUT...

(Major Columns: COUNTRY and POSITIONCODE .....34 addition. columns)

I designed a Totals query with the GroupByOption. It worked fine on my 1st
combo box.

But I was not able to use following step for my 2nd Query:

To do that,
in the COUNTRY column Criteria, reference the Country combo:
forms!MyFormName!MyCountryCombo

Using the EXPRESSION Builder I put:

Forms![>PosCodes]![Combo324]

into the CRITERIA field in the COUNTRY column but all I get is a PARAMETER
box opening...

NOW, after nearly 5 hours trying I was able to enter the CRITERIA into the
COUNTRY columnas follows:

[Forms]![>PosCodes]![Combo324]

My problem is that not every COUNTRY (1st column) has not the same POSITION
CODES (2nd column) . The construction works fine until I select another
company than the first company in the list.

AIM: The 2nd combo box shall only show these POSITION CODES belonging to
the selected COUNTRY of the 1st combo box.

Thank you very much for your support again. I will be really grateful.

Best regards

Fischkopp
 
S

SteveS

Fischkopp said:
Hi Klaatu and Allen,
thank you both very much for your support yesterday.
I used the way Klaatu described yesterday (Allen confirmed the steps too
yesterday)
BUT...

(Major Columns: COUNTRY and POSITIONCODE .....34 addition. columns)

I designed a Totals query with the GroupByOption. It worked fine on my 1st
combo box.

But I was not able to use following step for my 2nd Query:

To do that,
in the COUNTRY column Criteria, reference the Country combo:
forms!MyFormName!MyCountryCombo

Using the EXPRESSION Builder I put:

Forms![>PosCodes]![Combo324]

into the CRITERIA field in the COUNTRY column but all I get is a PARAMETER
box opening...

NOW, after nearly 5 hours trying I was able to enter the CRITERIA into the
COUNTRY columnas follows:

[Forms]![>PosCodes]![Combo324]

My problem is that not every COUNTRY (1st column) has not the same POSITION
CODES (2nd column) . The construction works fine until I select another
company than the first company in the list.

AIM: The 2nd combo box shall only show these POSITION CODES belonging to
the selected COUNTRY of the 1st combo box.

Thank you very much for your support again. I will be really grateful.

Best regards

Fischkopp
Click to expand...

Maybe this way will work for you.

I will use these names (you will need to change to your names):

table: tblCustRights

form: frmCustAccessRights
fields in the table: txtCountry and txtPosCode

country combo box: cboCountry
Position combo box: cboPositionCode


For the country combo box, set the Row Source to:

Select Distinct txtCountry From tblCustRights


For the Postion code combo box, set the Row Source to:

Select Distinct txtPosCode From tblCustRights Where txtCountry = " &
Forms!frmCustAccessRights!cboCountry


For both combo boxes, set "Limit to List" to TRUE.

I would put the combo boxes in the FORM Header.

The last thing to do is add this code to the cboCountry combo box:

Private Sub cboCountry_AfterUpdate()
Me.cboPositionCode.requery
End Sub


Now you can use these two combo boxes to limit the records in a recordset by
setting a filter or using a Where clause and requerying the form record
source.


Once this is working, then we can deal with how a company fits in.

HTH
 
F

Fischkopp

Hi Steve,

thank you very much for for support I really appreciate it.

I defined my table and form according to your suggestion but I am already
stuck again by selecting the right choice of the combo boxes:

Choices: LOOKUP THE VALUES IN A TABLE OR QUERY or
FIND A RECORD BASED ON THE VALUE I SELECTED IN MY
COMBO BOX

I choosed the FIND A RECORD.... version. ???

Then you gave following advise:

Select Distinct txtCountry From tblCustRights

1. When I type it directly into the ROW SOURCE field or I use the
expression builder and type it straight in SQL then a PARAMETER box opens and
there are no results, even when I type a Country.

2. When I us:

Select Distinct txtPosCode From tblCustRights Where
txtCountry = " &
Forms!frmCustAccessRights!cboCountry

nothing happens at all. There is only a white field on the form, no values
visible.

I have to mention that I try to use a combo box for the first time and I
never did a MS Access course.

Every idea is highly welcome.

Best regards

Fischkopp








SteveS said:
Fischkopp said:
Hi Klaatu and Allen,
thank you both very much for your support yesterday.
I used the way Klaatu described yesterday (Allen confirmed the steps too
yesterday)
BUT...

(Major Columns: COUNTRY and POSITIONCODE .....34 addition. columns)

I designed a Totals query with the GroupByOption. It worked fine on my 1st
combo box.

But I was not able to use following step for my 2nd Query:

To do that,
in the COUNTRY column Criteria, reference the Country combo:
forms!MyFormName!MyCountryCombo

Using the EXPRESSION Builder I put:

Forms![>PosCodes]![Combo324]

into the CRITERIA field in the COUNTRY column but all I get is a PARAMETER
box opening...

NOW, after nearly 5 hours trying I was able to enter the CRITERIA into the
COUNTRY columnas follows:

[Forms]![>PosCodes]![Combo324]

My problem is that not every COUNTRY (1st column) has not the same POSITION
CODES (2nd column) . The construction works fine until I select another
company than the first company in the list.

AIM: The 2nd combo box shall only show these POSITION CODES belonging to
the selected COUNTRY of the 1st combo box.

Thank you very much for your support again. I will be really grateful.

Best regards

Fischkopp
Click to expand...

Maybe this way will work for you.

I will use these names (you will need to change to your names):

table: tblCustRights

form: frmCustAccessRights
fields in the table: txtCountry and txtPosCode

country combo box: cboCountry
Position combo box: cboPositionCode


For the country combo box, set the Row Source to:

Select Distinct txtCountry From tblCustRights


For the Postion code combo box, set the Row Source to:

Select Distinct txtPosCode From tblCustRights Where txtCountry = " &
Forms!frmCustAccessRights!cboCountry


For both combo boxes, set "Limit to List" to TRUE.

I would put the combo boxes in the FORM Header.

The last thing to do is add this code to the cboCountry combo box:

Private Sub cboCountry_AfterUpdate()
Me.cboPositionCode.requery
End Sub


Now you can use these two combo boxes to limit the records in a recordset by
setting a filter or using a Where clause and requerying the form record
source.


Once this is working, then we can deal with how a company fits in.

HTH
Click to expand...
 
S

SteveS

Would you post a few more fields and some sample data for the fields?

Also, what are the names you are using for the table & form?

Did the country combo box show the country names?
Did the position code show the right codes for the country selected?

(If the combo boxes are working, the next step is to use the combo boxes to
limit the rows returned.)


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Fischkopp said:
Hi Steve,

thank you very much for for support I really appreciate it.

I defined my table and form according to your suggestion but I am already
stuck again by selecting the right choice of the combo boxes:

Choices: LOOKUP THE VALUES IN A TABLE OR QUERY or
FIND A RECORD BASED ON THE VALUE I SELECTED IN MY
COMBO BOX

I choosed the FIND A RECORD.... version. ???

Then you gave following advise:

Select Distinct txtCountry From tblCustRights

1. When I type it directly into the ROW SOURCE field or I use the
expression builder and type it straight in SQL then a PARAMETER box opens and
there are no results, even when I type a Country.

2. When I us:

Select Distinct txtPosCode From tblCustRights Where
txtCountry = " &
Forms!frmCustAccessRights!cboCountry

nothing happens at all. There is only a white field on the form, no values
visible.

I have to mention that I try to use a combo box for the first time and I
never did a MS Access course.

Every idea is highly welcome.

Best regards

Fischkopp








SteveS said:
Fischkopp said:
Hi Klaatu and Allen,
thank you both very much for your support yesterday.
I used the way Klaatu described yesterday (Allen confirmed the steps too
yesterday)
BUT...

(Major Columns: COUNTRY and POSITIONCODE .....34 addition. columns)

I designed a Totals query with the GroupByOption. It worked fine on my 1st
combo box.

But I was not able to use following step for my 2nd Query:

To do that,
in the COUNTRY column Criteria, reference the Country combo:
forms!MyFormName!MyCountryCombo

Using the EXPRESSION Builder I put:

Forms![>PosCodes]![Combo324]

into the CRITERIA field in the COUNTRY column but all I get is a PARAMETER
box opening...

NOW, after nearly 5 hours trying I was able to enter the CRITERIA into the
COUNTRY columnas follows:

[Forms]![>PosCodes]![Combo324]

My problem is that not every COUNTRY (1st column) has not the same POSITION
CODES (2nd column) . The construction works fine until I select another
company than the first company in the list.

AIM: The 2nd combo box shall only show these POSITION CODES belonging to
the selected COUNTRY of the 1st combo box.

Thank you very much for your support again. I will be really grateful.

Best regards

Fischkopp
Click to expand...

Maybe this way will work for you.

I will use these names (you will need to change to your names):

table: tblCustRights

form: frmCustAccessRights
fields in the table: txtCountry and txtPosCode

country combo box: cboCountry
Position combo box: cboPositionCode


For the country combo box, set the Row Source to:

Select Distinct txtCountry From tblCustRights


For the Postion code combo box, set the Row Source to:

Select Distinct txtPosCode From tblCustRights Where txtCountry = " &
Forms!frmCustAccessRights!cboCountry


For both combo boxes, set "Limit to List" to TRUE.

I would put the combo boxes in the FORM Header.

The last thing to do is add this code to the cboCountry combo box:

Private Sub cboCountry_AfterUpdate()
Me.cboPositionCode.requery
End Sub


Now you can use these two combo boxes to limit the records in a recordset by
setting a filter or using a Where clause and requerying the form record
source.


Once this is working, then we can deal with how a company fits in.

HTH
Click to expand...
Click to expand...
 
Top