combos in subform

[Wayne]

I'm posting this problem again in the hope that someone can
help me:

Ok...here's what I have.

I have a main form called PrintRequisition with a subform
called PrintReq_Subform. The subform contains a combo
called Type that gets its values from the following query:

SELECT Print_Type.PrintTypeID, Print_Type.PrintType FROM
Print_Type;

There is another combo called PaperSize with the following
query as the rowsource:

SELECT Print_Fee.PrintFeeID, Print_Fee.PrintTypeID,
Print_Fee.PaperSize FROM Print_Fee WHERE
(((Print_Fee.PrintTypeID)=[Forms]![PrintRequisition]![PrintReq_Subform]![Type]));

And I have Me!PaperSize.Requery on the got focus event of
the PrintSize combo.

So what's supposed to happen is this. I select a print type
from the Type combo and then when I select the PrintSize
combo it only displays the values relevant to the print
type selected. This seems to work but when I click into the
next record and select a different value in the Type combo
the requery makes the previously selected values in the
PaperSize combo disapear. The subform is displayed in
Datasheet view, is that part of the problem?

Steve Schapel made the following suggestion, but I still
ended up with the same results:

I don't know how this works in datasheet view, I have never
used a
datasheet view form. But if you make it a continuous view
form, try
this... On the Enter event of the PaperSize combobox, put
code like this:
Me.PaperSize.RowSource = "SELECT PrintFeeID, PrintTypeID,
PaperSize
FROM Print_Fee WHERE PrintTypeID =" & Me.Type
..... and then on the Exit event of the PaperSize combobox:
Me.PaperSize.RowSource = "SELECT PrintFeeID, PrintTypeID,
PaperSize
FROM Print_Fee"


I hope someone can help.

Thanks

 

[Sandra Daigle]

Hi Wayne,

Here's a solution that will work in a continuous form (it will not work for
a datasheet). Create another textbox control, include the display column of
the combo in the Recordsource query of the form (join the foreign table and
drag in the column). Position and size the new textbox so that you can place
it directly over the combo box allowing only the down-arrow portion of the
combo to show.

Create a GotFocus event for the textbox that only does a SetFocus to the
combo.

This will create the effect that you are wanting - the correct display value
will always show even though the value itself might not be fit the criteria
that is in effect based on the current record.

Here's a KB Article that explains the problem ACC2000: Combo Box in
Continuous Form Shows Incorrect Data
http://support.microsoft.com/default.aspx?scid=kb;en-us;208866

Here's a link to my sample database that offers several different
alternatives for displaying this type of data including the technique
described above:

http://www.daiglenet.com/msaccess.htm

FWIW, I've never had much luck with any other method of solving this
problem.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I'm posting this problem again in the hope that someone can
help me:

Ok...here's what I have.

I have a main form called PrintRequisition with a subform
called PrintReq_Subform. The subform contains a combo
called Type that gets its values from the following query:

SELECT Print_Type.PrintTypeID, Print_Type.PrintType FROM
Print_Type;

There is another combo called PaperSize with the following
query as the rowsource:

SELECT Print_Fee.PrintFeeID, Print_Fee.PrintTypeID,
Print_Fee.PaperSize FROM Print_Fee WHERE
(((Print_Fee.PrintTypeID)=[Forms]![PrintRequisition]![PrintReq_Subform]![Typ
e]));

And I have Me!PaperSize.Requery on the got focus event of
the PrintSize combo.

So what's supposed to happen is this. I select a print type
from the Type combo and then when I select the PrintSize
combo it only displays the values relevant to the print
type selected. This seems to work but when I click into the
next record and select a different value in the Type combo
the requery makes the previously selected values in the
PaperSize combo disapear. The subform is displayed in
Datasheet view, is that part of the problem?

Steve Schapel made the following suggestion, but I still
ended up with the same results:

I don't know how this works in datasheet view, I have never
used a
datasheet view form. But if you make it a continuous view
form, try
this... On the Enter event of the PaperSize combobox, put
code like this:
Me.PaperSize.RowSource = "SELECT PrintFeeID, PrintTypeID,
PaperSize
FROM Print_Fee WHERE PrintTypeID =" & Me.Type
.... and then on the Exit event of the PaperSize combobox:
Me.PaperSize.RowSource = "SELECT PrintFeeID, PrintTypeID,
PaperSize
FROM Print_Fee"


I hope someone can help.

Thanks

 

[Wayne]

Hi Sandra,

Thanks for the suggestion, I think I get the idea. It
sounds like a very creative solution to the problem.
Now all I have to do is find the time to give it a try.

Wayne

-----Original Message-----
Hi Wayne,

Here's a solution that will work in a continuous form (it will not work for
a datasheet). Create another textbox control, include the display column of
the combo in the Recordsource query of the form (join the foreign table and
drag in the column). Position and size the new textbox so that you can place
it directly over the combo box allowing only the down-arrow portion of the
combo to show.

Create a GotFocus event for the textbox that only does a SetFocus to the
combo.

This will create the effect that you are wanting - the correct display value
will always show even though the value itself might not be fit the criteria
that is in effect based on the current record.

Here's a KB Article that explains the problem ACC2000: Combo Box in
Continuous Form Shows Incorrect Data
http://support.microsoft.com/default.aspx?scid=kb;en-us;208866

Here's a link to my sample database that offers several different
alternatives for displaying this type of data including the technique
described above:

http://www.daiglenet.com/msaccess.htm

FWIW, I've never had much luck with any other method of solving this
problem.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I'm posting this problem again in the hope that someone can
help me:

Ok...here's what I have.

I have a main form called PrintRequisition with a subform
called PrintReq_Subform. The subform contains a combo
called Type that gets its values from the following query:

SELECT Print_Type.PrintTypeID, Print_Type.PrintType FROM
Print_Type;

There is another combo called PaperSize with the following
query as the rowsource:

SELECT Print_Fee.PrintFeeID, Print_Fee.PrintTypeID,
Print_Fee.PaperSize FROM Print_Fee WHERE
(((Print_Fee.PrintTypeID)=[Forms]![PrintRequisition]![PrintReq_Subform]![Typ
e]));

And I have Me!PaperSize.Requery on the got focus event of
the PrintSize combo.

So what's supposed to happen is this. I select a print type
from the Type combo and then when I select the PrintSize
combo it only displays the values relevant to the print
type selected. This seems to work but when I click into the
next record and select a different value in the Type combo
the requery makes the previously selected values in the
PaperSize combo disapear. The subform is displayed in
Datasheet view, is that part of the problem?

Steve Schapel made the following suggestion, but I still
ended up with the same results:

I don't know how this works in datasheet view, I have never
used a
datasheet view form. But if you make it a continuous view
form, try
this... On the Enter event of the PaperSize combobox, put
code like this:
Me.PaperSize.RowSource = "SELECT PrintFeeID, PrintTypeID,
PaperSize
FROM Print_Fee WHERE PrintTypeID =" & Me.Type
.... and then on the Exit event of the PaperSize combobox:
Me.PaperSize.RowSource = "SELECT PrintFeeID, PrintTypeID,
PaperSize
FROM Print_Fee"


I hope someone can help.

Thanks

.

 

[Sandra Daigle]

"Creative" is a nice way to put it .

It's a kludge or a hack but it works and it's pretty commonly used
(unfortunately).

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Sandra,

Thanks for the suggestion, I think I get the idea. It
sounds like a very creative solution to the problem.
Now all I have to do is find the time to give it a try.

Wayne

-----Original Message-----
Hi Wayne,

Here's a solution that will work in a continuous form (it will not
work for a datasheet). Create another textbox control, include the
display column of the combo in the Recordsource query of the form
(join the foreign table and drag in the column). Position and size
the new textbox so that you can place it directly over the combo box
allowing only the down-arrow portion of the
combo to show.

Create a GotFocus event for the textbox that only does a SetFocus to
the combo.

This will create the effect that you are wanting - the correct
display value will always show even though the value itself might
not be fit the criteria that is in effect based on the current
record.

Here's a KB Article that explains the problem ACC2000: Combo Box in
Continuous Form Shows Incorrect Data
http://support.microsoft.com/default.aspx?scid=kb;en-us;208866

Here's a link to my sample database that offers several different
alternatives for displaying this type of data including the technique
described above:

http://www.daiglenet.com/msaccess.htm

FWIW, I've never had much luck with any other method of solving this
problem.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I'm posting this problem again in the hope that someone can
help me:

Ok...here's what I have.

I have a main form called PrintRequisition with a subform
called PrintReq_Subform. The subform contains a combo
called Type that gets its values from the following query:

SELECT Print_Type.PrintTypeID, Print_Type.PrintType FROM
Print_Type;

There is another combo called PaperSize with the following
query as the rowsource:

SELECT Print_Fee.PrintFeeID, Print_Fee.PrintTypeID,
Print_Fee.PaperSize FROM Print_Fee WHERE

(((Print_Fee.PrintTypeID)=[Forms]![PrintRequisition]![PrintReq_Subform]![Typ
e]));

And I have Me!PaperSize.Requery on the got focus event of
the PrintSize combo.

So what's supposed to happen is this. I select a print type
from the Type combo and then when I select the PrintSize
combo it only displays the values relevant to the print
type selected. This seems to work but when I click into the
next record and select a different value in the Type combo
the requery makes the previously selected values in the
PaperSize combo disapear. The subform is displayed in
Datasheet view, is that part of the problem?

Steve Schapel made the following suggestion, but I still
ended up with the same results:

I don't know how this works in datasheet view, I have never
used a
datasheet view form. But if you make it a continuous view
form, try
this... On the Enter event of the PaperSize combobox, put
code like this:
Me.PaperSize.RowSource = "SELECT PrintFeeID, PrintTypeID,
PaperSize
FROM Print_Fee WHERE PrintTypeID =" & Me.Type
.... and then on the Exit event of the PaperSize combobox:
Me.PaperSize.RowSource = "SELECT PrintFeeID, PrintTypeID,
PaperSize
FROM Print_Fee"


I hope someone can help.

Thanks

.

 

[Atlas]

Hi Wayne,

Here's a solution that will work in a continuous form (it will not work for
a datasheet). Create another textbox control, include the display column of
the combo in the Recordsource query of the form (join the foreign table and
drag in the column). Position and size the new textbox so that you can place
it directly over the combo box allowing only the down-arrow portion of the
combo to show.

Create a GotFocus event for the textbox that only does a SetFocus to the
combo.

This will create the effect that you are wanting - the correct display value
will always show even though the value itself might not be fit the criteria
that is in effect based on the current record.

This doesn't work for me, here's why :

continuos form with data from one table plus 3 inner joined tables.

mainTable
......some fields....
IDGroup int
IDSubGroup int
IDSubSubGroup int

GroupsTable
ID int
GroupDescription nvarchar

SubGroupsTable
ID int
IDGroup
SubGroupDescription nvarchar

SubSubGroupsTable
ID int
IDSubGroup
SubSubGroupDescription nvarchar

Form recordsource select mainTable + innerjoines to related tables (that is
for description lookup)

Three combo boxes on the continuos form, two cascaded. Those cascaded have a
VBA code that dynamically change the rowsource based upon chagned values.
This works.

Now the problem:

the "fake" textbokes in front of the combobox hold the descriptions from the
respective tables.On form load it works perfectly. But when data changes in
one of the two cascaded combos, even if forcing a textbox.requery, the
textbox continues to show the old value, probably because the form update
hasn't occurred yet. Also can't force a form update immediately, as the user
once updating values in the combos must complete the cascading path to be
sure correct values are entered..