Subform with two comboboxes

[Peter Kinsman]

I am having big problems with a subform to create Bill of Materials records.
The first combo box
selects the Type and the other selects the Item Description - whose
RowSource is controlled by the
Type combobox. The RowSource is set to one of two queries:

qryAllDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
ORDER BY tblCodes.Description;

qryFilteredDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
WHERE
(((tblTypes.Short)=[Forms]![frmUpdateProduct]![sfrmUpdateProduct].[Form]![cboType]))
ORDER BY tblCodes.Description;

With the RowSource set initially to qryAllDescriptions, existing records
appear correctly, but as soon as
I select the Type for a new record, it applies the filter to all the
Descriptions on the subform.

Hopefully during the refresh of the subform there will be an event when I
can set the appropriate RowSource,
but does anyone know what it is please?

Many thanks

Peter Kinsman

 

[Peter Kinsman]

Daniel

Thanks for the link. The problem I am having is not in linking the
comboboxes, but the fact that the same RowSource seems to be used for all
rows of the subform. Until I can find whether what I want to do is
possible, I might try changing the RowSource to qryAllDescriptions in the
cboDesc_Click() subroutine.

Thanks again

Peter

Peter,

Microsoft has a good tutorial on how to create linked combo boxes. It
should provide you with answers.
http://office.microsoft.com/en-us/access/HA011730581033.aspx?pid=CH063650361033

Daniel

I am having big problems with a subform to create Bill of Materials
records.
The first combo box
selects the Type and the other selects the Item Description - whose
RowSource is controlled by the
Type combobox. The RowSource is set to one of two queries:

qryAllDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
ORDER BY tblCodes.Description;

qryFilteredDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
WHERE
(((tblTypes.Short)=[Forms]![frmUpdateProduct]![sfrmUpdateProduct].[Form]![cboType]))
ORDER BY tblCodes.Description;

With the RowSource set initially to qryAllDescriptions, existing records
appear correctly, but as soon as
I select the Type for a new record, it applies the filter to all the
Descriptions on the subform.

Hopefully during the refresh of the subform there will be an event when I
can set the appropriate RowSource,
but does anyone know what it is please?

Many thanks

Peter Kinsman

 

[Marshall Barton]

I am having big problems with a subform to create Bill of Materials records.
The first combo box
selects the Type and the other selects the Item Description - whose
RowSource is controlled by the
Type combobox. The RowSource is set to one of two queries:

qryAllDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
ORDER BY tblCodes.Description;

qryFilteredDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
WHERE
(((tblTypes.Short)=[Forms]![frmUpdateProduct]![sfrmUpdateProduct].[Form]![cboType]))
ORDER BY tblCodes.Description;

With the RowSource set initially to qryAllDescriptions, existing records
appear correctly, but as soon as
I select the Type for a new record, it applies the filter to all the
Descriptions on the subform.

Hopefully during the refresh of the subform there will be an event when I
can set the appropriate RowSource,
but does anyone know what it is please?

There is no way to get a combo box to work with different
filters on different records in a datasheet or continuous
form. (Technically, it won't work in single view either,
but since you can't see the other records, who cares.)

The general workaround is to add a join to the form's record
source query and include the desired display field. THen
bind a text box to the field and place the text box on top
of the combo box's text part. To prevent user confusion if
they should click on the text box, you should set its
GotFocus event to a line of code that shifts the focus to
the combo box:
Me.thecombobox.SetFocus

 

[Peter Kinsman]

Marshall

I was afraid that would be the answer. Rather like my earlier question
about having differing numbers of decimal places on different rows of a
subform - although I suppose that could be achieved through the use of a
format function in the underlying query. I have got round it by using
qryAllDescriptions except between selecting the Type and selecting the Item.

Many thanks

Peter

I am having big problems with a subform to create Bill of Materials
records.
The first combo box
selects the Type and the other selects the Item Description - whose
RowSource is controlled by the
Type combobox. The RowSource is set to one of two queries:

qryAllDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
ORDER BY tblCodes.Description;

qryFilteredDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
WHERE
(((tblTypes.Short)=[Forms]![frmUpdateProduct]![sfrmUpdateProduct].[Form]![cboType]))
ORDER BY tblCodes.Description;

With the RowSource set initially to qryAllDescriptions, existing records
appear correctly, but as soon as
I select the Type for a new record, it applies the filter to all the
Descriptions on the subform.

Hopefully during the refresh of the subform there will be an event when I
can set the appropriate RowSource,
but does anyone know what it is please?

There is no way to get a combo box to work with different
filters on different records in a datasheet or continuous
form. (Technically, it won't work in single view either,
but since you can't see the other records, who cares.)

The general workaround is to add a join to the form's record
source query and include the desired display field. THen
bind a text box to the field and place the text box on top
of the combo box's text part. To prevent user confusion if
they should click on the text box, you should set its
GotFocus event to a line of code that shifts the focus to
the combo box:
Me.thecombobox.SetFocus

 

[marius]

Peter,

In this link www.mvp-access.com/marius/ejemplos/awforms.zip you can find a
continous form with 2 combos. It is not exactly what you request, but I
think that it can be an alternative.

--
HTH
Màrius

Marshall

I was afraid that would be the answer. Rather like my earlier question
about having differing numbers of decimal places on different rows of a
subform - although I suppose that could be achieved through the use of a
format function in the underlying query. I have got round it by using
qryAllDescriptions except between selecting the Type and selecting the Item.

Many thanks

Peter

(((tblTypes.Short)=[Forms]![frmUpdateProduct]![sfrmUpdateProduct].[Form]![ cboType]))

ORDER BY tblCodes.Description;

With the RowSource set initially to qryAllDescriptions, existing records
appear correctly, but as soon as
I select the Type for a new record, it applies the filter to all the
Descriptions on the subform.

Hopefully during the refresh of the subform there will be an event when I
can set the appropriate RowSource,
but does anyone know what it is please?

There is no way to get a combo box to work with different
filters on different records in a datasheet or continuous
form. (Technically, it won't work in single view either,
but since you can't see the other records, who cares.)

The general workaround is to add a join to the form's record
source query and include the desired display field. THen
bind a text box to the field and place the text box on top
of the combo box's text part. To prevent user confusion if
they should click on the text box, you should set its
GotFocus event to a line of code that shifts the focus to
the combo box:
Me.thecombobox.SetFocus

 

[Peter Kinsman]

Marius

That link looks very interesting - I had not thought of using continuous
forms instead of a datasheet. It hdlps that I studied Spanish at school -
even if it was many years ago.

Peter

Peter,

In this link www.mvp-access.com/marius/ejemplos/awforms.zip you can find a
continous form with 2 combos. It is not exactly what you request, but I
think that it can be an alternative.

--
HTH
Màrius

Marshall

I was afraid that would be the answer. Rather like my earlier question
about having differing numbers of decimal places on different rows of a
subform - although I suppose that could be achieved through the use of a
format function in the underlying query. I have got round it by using
qryAllDescriptions except between selecting the Type and selecting the Item.

Many thanks

Peter

Peter Kinsman wrote:

I am having big problems with a subform to create Bill of Materials
records.
The first combo box
selects the Type and the other selects the Item Description - whose
RowSource is controlled by the
Type combobox. The RowSource is set to one of two queries:

qryAllDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
ORDER BY tblCodes.Description;

qryFilteredDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
WHERE

(((tblTypes.Short)=[Forms]![frmUpdateProduct]![sfrmUpdateProduct].[Form]![ cboType]))
ORDER BY tblCodes.Description;

With the RowSource set initially to qryAllDescriptions, existing
records
appear correctly, but as soon as
I select the Type for a new record, it applies the filter to all the
Descriptions on the subform.

Hopefully during the refresh of the subform there will be an event when I
can set the appropriate RowSource,
but does anyone know what it is please?


There is no way to get a combo box to work with different
filters on different records in a datasheet or continuous
form. (Technically, it won't work in single view either,
but since you can't see the other records, who cares.)

The general workaround is to add a join to the form's record
source query and include the desired display field. THen
bind a text box to the field and place the text box on top
of the combo box's text part. To prevent user confusion if
they should click on the text box, you should set its
GotFocus event to a line of code that shifts the focus to
the combo box:
Me.thecombobox.SetFocus

 

[marius]

Peter,

You can do the same (2 combos) in datasheet/continuous form view. Look for
"CombosEnContinuos" form, you can switch from one view to other.


(If you need any translation, I will try to explain it in english.)

--
HTH,
Màrius - http://www.mvp-access.com/foro/default.asp

Marius

That link looks very interesting - I had not thought of using continuous
forms instead of a datasheet. It hdlps that I studied Spanish at school -
even if it was many years ago.

Peter

Peter,

In this link www.mvp-access.com/marius/ejemplos/awforms.zip you can find a
continous form with 2 combos. It is not exactly what you request, but I
think that it can be an alternative.

--
HTH
Màrius

Marshall

I was afraid that would be the answer. Rather like my earlier question
about having differing numbers of decimal places on different rows of a
subform - although I suppose that could be achieved through the use of a
format function in the underlying query. I have got round it by using
qryAllDescriptions except between selecting the Type and selecting the Item.

Many thanks

Peter

Peter Kinsman wrote:

I am having big problems with a subform to create Bill of Materials
records.
The first combo box
selects the Type and the other selects the Item Description - whose
RowSource is controlled by the
Type combobox. The RowSource is set to one of two queries:

qryAllDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
ORDER BY tblCodes.Description;

qryFilteredDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
WHERE

(((tblTypes.Short)=[Forms]![frmUpdateProduct]![sfrmUpdateProduct].[Form]!

[
cboType]))

ORDER BY tblCodes.Description;

With the RowSource set initially to qryAllDescriptions, existing
records
appear correctly, but as soon as
I select the Type for a new record, it applies the filter to all the
Descriptions on the subform.

Hopefully during the refresh of the subform there will be an event

when
I

can set the appropriate RowSource,
but does anyone know what it is please?


There is no way to get a combo box to work with different
filters on different records in a datasheet or continuous
form. (Technically, it won't work in single view either,
but since you can't see the other records, who cares.)

The general workaround is to add a join to the form's record
source query and include the desired display field. THen
bind a text box to the field and place the text box on top
of the combo box's text part. To prevent user confusion if
they should click on the text box, you should set its
GotFocus event to a line of code that shifts the focus to
the combo box:
Me.thecombobox.SetFocus

 

[Peter Kinsman]

Marius

There are I think two things I do not understand about the form Combos En
Continuos.
1) How do you make it look like a datasheet when it the default view is set
to Continuous Forms? I have now found the docmd.runcommand line when the
option box value is 22.
2) The exact purpose of the line
If Screen.PreviousControl.Name = Me.ActiveControl.Name Then '

I have tried converting my datasheet to a continuous form, but I still have
the same problem with the descriptions disappearing. Maybe a better
approach would be to start with your form and then dress it up to look like
mine.

Regards

Peter

Peter,

You can do the same (2 combos) in datasheet/continuous form view. Look for
"CombosEnContinuos" form, you can switch from one view to other.


(If you need any translation, I will try to explain it in english.)

--
HTH,
Màrius - http://www.mvp-access.com/foro/default.asp

Marius

That link looks very interesting - I had not thought of using continuous
forms instead of a datasheet. It hdlps that I studied Spanish at
school -
even if it was many years ago.

Peter

Peter,

In this link www.mvp-access.com/marius/ejemplos/awforms.zip you can
find a
continous form with 2 combos. It is not exactly what you request, but I
think that it can be an alternative.

--
HTH
Màrius

"Peter Kinsman" <[email protected]> escribió en el mensaje
Marshall

I was afraid that would be the answer. Rather like my earlier
question
about having differing numbers of decimal places on different rows of
a
subform - although I suppose that could be achieved through the use of a
format function in the underlying query. I have got round it by using
qryAllDescriptions except between selecting the Type and selecting the
Item.

Many thanks

Peter

Peter Kinsman wrote:

I am having big problems with a subform to create Bill of Materials
records.
The first combo box
selects the Type and the other selects the Item Description - whose
RowSource is controlled by the
Type combobox. The RowSource is set to one of two queries:

qryAllDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
ORDER BY tblCodes.Description;

qryFilteredDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
WHERE

(((tblTypes.Short)=[Forms]![frmUpdateProduct]![sfrmUpdateProduct].[Form]! [
cboType]))
ORDER BY tblCodes.Description;

With the RowSource set initially to qryAllDescriptions, existing
records
appear correctly, but as soon as
I select the Type for a new record, it applies the filter to all the
Descriptions on the subform.

Hopefully during the refresh of the subform there will be an event when
I
can set the appropriate RowSource,
but does anyone know what it is please?


There is no way to get a combo box to work with different
filters on different records in a datasheet or continuous
form. (Technically, it won't work in single view either,
but since you can't see the other records, who cares.)

The general workaround is to add a join to the form's record
source query and include the desired display field. THen
bind a text box to the field and place the text box on top
of the combo box's text part. To prevent user confusion if
they should click on the text box, you should set its
GotFocus event to a line of code that shifts the focus to
the combo box:
Me.thecombobox.SetFocus

 

[marius]

1) You can use "DoCmd.RunCommand acCmdSubformDatasheet" when subform have
the focus (or set it in the property sheet)

2) This is for preventing combo "repainting"; combo filter (rowsource
change) is only activated when the control get the focus (gotfocus event).
Then, in the enter event, we test if the previous control is the same object
(the combo) and reset the filter and move focus to another object. If you
don't do this, the combo refreshes and disappears the text.

--
HTH,
Màrius - http://www.mvp-access.com/foro/default.asp

Marius

There are I think two things I do not understand about the form Combos En
Continuos.
1) How do you make it look like a datasheet when it the default view is set
to Continuous Forms? I have now found the docmd.runcommand line when the
option box value is 22.
2) The exact purpose of the line
If Screen.PreviousControl.Name = Me.ActiveControl.Name Then '

I have tried converting my datasheet to a continuous form, but I still have
the same problem with the descriptions disappearing. Maybe a better
approach would be to start with your form and then dress it up to look like
mine.

Regards

Peter

Peter,

You can do the same (2 combos) in datasheet/continuous form view. Look for
"CombosEnContinuos" form, you can switch from one view to other.


(If you need any translation, I will try to explain it in english.)

--
HTH,
Màrius - http://www.mvp-access.com/foro/default.asp

Marius

That link looks very interesting - I had not thought of using continuous
forms instead of a datasheet. It hdlps that I studied Spanish at
school -
even if it was many years ago.

Peter

Peter,

In this link www.mvp-access.com/marius/ejemplos/awforms.zip you can
find a
continous form with 2 combos. It is not exactly what you request, but I
think that it can be an alternative.

--
HTH
Màrius

"Peter Kinsman" <[email protected]> escribió en el mensaje
Marshall

I was afraid that would be the answer. Rather like my earlier
question
about having differing numbers of decimal places on different rows of
a
subform - although I suppose that could be achieved through the use

of
a

format function in the underlying query. I have got round it by using
qryAllDescriptions except between selecting the Type and selecting the
Item.

Many thanks

Peter

Peter Kinsman wrote:

I am having big problems with a subform to create Bill of Materials
records.
The first combo box
selects the Type and the other selects the Item Description - whose
RowSource is controlled by the
Type combobox. The RowSource is set to one of two queries:

qryAllDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
ORDER BY tblCodes.Description;

qryFilteredDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
WHERE


(((tblTypes.Short)=[Forms]![frmUpdateProduct]![sfrmUpdateProduct].[Form]

!
[

cboType]))
ORDER BY tblCodes.Description;

With the RowSource set initially to qryAllDescriptions, existing
records
appear correctly, but as soon as
I select the Type for a new record, it applies the filter to all the
Descriptions on the subform.

Hopefully during the refresh of the subform there will be an event when
I
can set the appropriate RowSource,
but does anyone know what it is please?


There is no way to get a combo box to work with different
filters on different records in a datasheet or continuous
form. (Technically, it won't work in single view either,
but since you can't see the other records, who cares.)

The general workaround is to add a join to the form's record
source query and include the desired display field. THen
bind a text box to the field and place the text box on top
of the combo box's text part. To prevent user confusion if
they should click on the text box, you should set its
GotFocus event to a line of code that shifts the focus to
the combo box:
Me.thecombobox.SetFocus

 

[Peter Kinsman]

Marsh

I have rested this one for a while, but the form would be so much easier to
use than the current arrangement if I could get it to work. As you have
probably seen, Marius has been trying to help using continuous forms rather
than a datasheet.
To go back to your original message, I am not sure how to "place the text
box on top of the combo box's text part". I have tried it as separate
fields on the subform, and have added some code in the OnCurrent event of
the subform to make one or other invisible according to whether an
ingredient exists on that row. I was hoping to change the Left property of
the control that is to be displayed, but they seem to be the same - I assume
that this is because the form is columnar in design but displayed as a
datasheet.
If you could explain the "on top" bit, I should be away.

Regards and all the best for 2007

Peter

I am having big problems with a subform to create Bill of Materials
records.
The first combo box
selects the Type and the other selects the Item Description - whose
RowSource is controlled by the
Type combobox. The RowSource is set to one of two queries:

qryAllDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
ORDER BY tblCodes.Description;

qryFilteredDescriptions
SELECT tblCodes.Rec_ID, tblCodes.Description,
tblCodes.OrigCode AS Recipe, tblTypes.Short
FROM tblCodes INNER JOIN tblTypes ON tblCodes.MType = tblTypes.Type
WHERE
(((tblTypes.Short)=[Forms]![frmUpdateProduct]![sfrmUpdateProduct].[Form]![cboType]))
ORDER BY tblCodes.Description;

With the RowSource set initially to qryAllDescriptions, existing records
appear correctly, but as soon as
I select the Type for a new record, it applies the filter to all the
Descriptions on the subform.

Hopefully during the refresh of the subform there will be an event when I
can set the appropriate RowSource,
but does anyone know what it is please?

There is no way to get a combo box to work with different
filters on different records in a datasheet or continuous
form. (Technically, it won't work in single view either,
but since you can't see the other records, who cares.)

The general workaround is to add a join to the form's record
source query and include the desired display field. THen
bind a text box to the field and place the text box on top
of the combo box's text part. To prevent user confusion if
they should click on the text box, you should set its
GotFocus event to a line of code that shifts the focus to
the combo box:
Me.thecombobox.SetFocus