Displaying results from combo boxes to subform

[gmazza via AccessMonster.com]

Hey there,
I'm looking to search from some combo boxes in my form header (with the data
in the combo boxes from table 1), then display the results of the combo box
in a subform (based on table 2), as well as enter more data in the subform,
all being saved to table 2.

I just need a starting point, such as do I make my main form unbound? or
bound to table 1?
What do I have as a recordsource for my subform, cause it needs info from
table 1, yet I am adding to it to populate table 2.
Lastly, how can I search from 4 combo boxes, so combo box 2 only includes
things related from combo box 1 and combo box 3 only includes things related
in combo box 1 and 2, etc.

Thanks for your help, it is appreciated!

 

[NevilleT]

Hi
You need to create a number of queries where the previous combo is part of
the query. This is not too difficult using the query builder as you just
need to put the combo name in the criteria field. Just try one until you get
it working.

Use the Got Focus Event for the combo to requery the combo box. Me.Requery.
That will filter the records. To refine it you may want to put a check in
to make sure that the previous combo has been selected. On cmb2 you might
want to add

If Is Null(Me.cmb1) Then
Msgbox "Please fill in Combo 1", vbCritical, "Missing Data"
Me.cmb1.Setfocus
Goto Exit_cmb1
End If

When all the combos are completed, you will need to requery the subform.
Perhaps you can put in a button to do this. The subform will also have a
query using criteria based on the combo boxes.

Good luck.

Neville Turbit
www.projectperfect.com.au

 

[gmazza via AccessMonster.com]

Thanks for the reply Neville.
I tried implementing some of this as I put the combo name of the first combo
box in the criteria field of my second combo box and when I click the 2nd
combo box it comes up with a parameter, wanting me to enter the value for the
first combo box when there is already a value there on the form?
If I enter a value the combo box does show the correct results but its not
reading that there is a value in the first combo box.
My combo boxes look like this:
combo 1(cboTrademark) - Trademark
combo 2(cboCountry) - Trademark - Criteria: Me.cboTrademark
Country

Thanks!

Hi
You need to create a number of queries where the previous combo is part of
the query. This is not too difficult using the query builder as you just
need to put the combo name in the criteria field. Just try one until you get
it working.

Use the Got Focus Event for the combo to requery the combo box. Me.Requery.
That will filter the records. To refine it you may want to put a check in
to make sure that the previous combo has been selected. On cmb2 you might
want to add

If Is Null(Me.cmb1) Then
Msgbox "Please fill in Combo 1", vbCritical, "Missing Data"
Me.cmb1.Setfocus
Goto Exit_cmb1
End If

When all the combos are completed, you will need to requery the subform.
Perhaps you can put in a button to do this. The subform will also have a
query using criteria based on the combo boxes.

Good luck.

Neville Turbit
www.projectperfect.com.au

Hey there,
I'm looking to search from some combo boxes in my form header (with the data

[quoted text clipped - 11 lines]

Thanks for your help, it is appreciated!

 

[NevilleT]

Did some playing around with this and have another solution which is a bit
easier to set up. For a combo you can only reference the bound column so if
you want to use the country number from the trademark combo, the country has
to be the bound column. This may not be what you want. Instead use the tag
property of the combobox.

Not knowing your data structure, I will have to make this up.

- Assume you have a table called tblTradeMark with fields TrademarkNo,
TradeMarkName, TradeMarkCountryNo

- You have a tblCountry with fields CountryNo, CountryName

- You have a form called frmDetails with cmbTrademark and cmbCountry

First Combo SQL would be "SELECT TrademarkNo, TradeMarkName,
TradeMarkCountryNo FROM tblTradeMark;". Bound column is the 1 and the
column widths are 0; 2.54; 0

Use the After Update event for the cmb1 to set the tag.
Me.cmb1.Tag = Me.cmb1.Column(2) ' Column count starts from zero

You now have the tag value to use as the criteria for cmb2. The SQL for
cmb2 would be: "SELECT CountryNo, CountryName FROM tblCountry WHERE
tblCountry.CountryNo = Forms!frmDetails.cmb1.Tag;"

You will have to put in some checking to make sure there are values in the
tags. Probably use the Form On Current to clear the comboboxes so they have
to select a value which will cause the tag to be set. Me.cmb1 = ""

Think that should work.

Thanks for the reply Neville.
I tried implementing some of this as I put the combo name of the first combo
box in the criteria field of my second combo box and when I click the 2nd
combo box it comes up with a parameter, wanting me to enter the value for the
first combo box when there is already a value there on the form?
If I enter a value the combo box does show the correct results but its not
reading that there is a value in the first combo box.
My combo boxes look like this:
combo 1(cboTrademark) - Trademark
combo 2(cboCountry) - Trademark - Criteria: Me.cboTrademark
Country

Thanks!

Hi
You need to create a number of queries where the previous combo is part of
the query. This is not too difficult using the query builder as you just
need to put the combo name in the criteria field. Just try one until you get
it working.

Use the Got Focus Event for the combo to requery the combo box. Me.Requery.
That will filter the records. To refine it you may want to put a check in
to make sure that the previous combo has been selected. On cmb2 you might
want to add

If Is Null(Me.cmb1) Then
Msgbox "Please fill in Combo 1", vbCritical, "Missing Data"
Me.cmb1.Setfocus
Goto Exit_cmb1
End If

When all the combos are completed, you will need to requery the subform.
Perhaps you can put in a button to do this. The subform will also have a
query using criteria based on the combo boxes.

Good luck.

Neville Turbit
www.projectperfect.com.au

Hey there,
I'm looking to search from some combo boxes in my form header (with the data

[quoted text clipped - 11 lines]

Thanks for your help, it is appreciated!

 

[gmazza via AccessMonster.com]

Tried your new strategy and got a good working version that just needs minor
tweaking. Thanks for all your help!

Did some playing around with this and have another solution which is a bit
easier to set up. For a combo you can only reference the bound column so if
you want to use the country number from the trademark combo, the country has
to be the bound column. This may not be what you want. Instead use the tag
property of the combobox.

Not knowing your data structure, I will have to make this up.

- Assume you have a table called tblTradeMark with fields TrademarkNo,
TradeMarkName, TradeMarkCountryNo

- You have a tblCountry with fields CountryNo, CountryName

- You have a form called frmDetails with cmbTrademark and cmbCountry

First Combo SQL would be "SELECT TrademarkNo, TradeMarkName,
TradeMarkCountryNo FROM tblTradeMark;". Bound column is the 1 and the
column widths are 0; 2.54; 0

Use the After Update event for the cmb1 to set the tag.
Me.cmb1.Tag = Me.cmb1.Column(2) ' Column count starts from zero

You now have the tag value to use as the criteria for cmb2. The SQL for
cmb2 would be: "SELECT CountryNo, CountryName FROM tblCountry WHERE
tblCountry.CountryNo = Forms!frmDetails.cmb1.Tag;"

You will have to put in some checking to make sure there are values in the
tags. Probably use the Form On Current to clear the comboboxes so they have
to select a value which will cause the tag to be set. Me.cmb1 = ""

Think that should work.

Thanks for the reply Neville.
I tried implementing some of this as I put the combo name of the first combo

[quoted text clipped - 41 lines]

 

[NevilleT]

Glad to assist.

Neville Turbit
www.projectperfect.com.au

Tried your new strategy and got a good working version that just needs minor
tweaking. Thanks for all your help!

Did some playing around with this and have another solution which is a bit
easier to set up. For a combo you can only reference the bound column so if
you want to use the country number from the trademark combo, the country has
to be the bound column. This may not be what you want. Instead use the tag
property of the combobox.

Not knowing your data structure, I will have to make this up.

- Assume you have a table called tblTradeMark with fields TrademarkNo,
TradeMarkName, TradeMarkCountryNo

- You have a tblCountry with fields CountryNo, CountryName

- You have a form called frmDetails with cmbTrademark and cmbCountry

First Combo SQL would be "SELECT TrademarkNo, TradeMarkName,
TradeMarkCountryNo FROM tblTradeMark;". Bound column is the 1 and the
column widths are 0; 2.54; 0

Use the After Update event for the cmb1 to set the tag.
Me.cmb1.Tag = Me.cmb1.Column(2) ' Column count starts from zero

You now have the tag value to use as the criteria for cmb2. The SQL for
cmb2 would be: "SELECT CountryNo, CountryName FROM tblCountry WHERE
tblCountry.CountryNo = Forms!frmDetails.cmb1.Tag;"

You will have to put in some checking to make sure there are values in the
tags. Probably use the Form On Current to clear the comboboxes so they have
to select a value which will cause the tag to be set. Me.cmb1 = ""

Think that should work.

Thanks for the reply Neville.
I tried implementing some of this as I put the combo name of the first combo

[quoted text clipped - 41 lines]

Thanks for your help, it is appreciated!