First, a word of thanks to all those who have previously helped with
problems!! Much appreciated!!
I have read many of the posts on cascading combo boxes and filtering with
combo boxes and cannot get the following to work. I have a main form with a
combo box with three types of assemblies listed. I have a subform on the
main form with a combo box of descriptions based on each type of assembly.
If I choose one assembly from main form combo, the subform combo is populated
with that type. If I choose another, it holds the first selection until I
close the form and go back to it. I have tried requerying and refreshing and
still nothing. I will list the coding for each aspect.
Combo Box on main form
Control Source = Type
Row Source Type = Table/Query
Row Source SELECT tLookupType.Type FROM tType
After Update Me.ComboType.Requery

Assembly Subform
Source Object = qAssemblySubform
Link Child=Type
Link Master =Type
On Current DoCmd.Requery

Combo on Subform ComboDescription
Control Source=Description
Row Source = Table/Query
Row Source
SELECT tAssemblyDescriptions.RecordNumber,
tAssemblyDescriptions.Description, tAssemblyDescriptions.Type FROM
tAssemblyDescriptions WHERE
(((tAssemblyDescriptions.Type)=Forms![f*GeneralInformationWITHQUOTE]!Type));

I hope this isn't too long or confusing. I've already spent WAY TOO MUCH
time with this and am becoming very frustrated. Any help would be very much
appreciated!
Thanks,
Phisaw

Is this code step supposed to requery the subform's combo box:
Me.ComboType.Requery

If yes, then the syntax is not correct. You have to refer to a control on a
subform using this syntax:
Me!SubformName!ControlOnSubform

where SubformName is the name of the subform control (the control that holds
the subform).

Alternatively, these syntaces could be used:
Me!SubformName.Form!ControlOnSubform
Me.SubformName.Form.ControlOnSubform

--

Ken Snell
<MS ACCESS MVP>





"PHisaw" <(E-Mail Removed)> wrote in message
news:84F4028C-0427-47EC-AA88-(E-Mail Removed)...
> First, a word of thanks to all those who have previously helped with
> problems!! Much appreciated!!
> I have read many of the posts on cascading combo boxes and filtering with
> combo boxes and cannot get the following to work. I have a main form with
> a
> combo box with three types of assemblies listed. I have a subform on the
> main form with a combo box of descriptions based on each type of assembly.
> If I choose one assembly from main form combo, the subform combo is
> populated
> with that type. If I choose another, it holds the first selection until I
> close the form and go back to it. I have tried requerying and refreshing
> and
> still nothing. I will list the coding for each aspect.
> Combo Box on main form
> Control Source = Type
> Row Source Type = Table/Query
> Row Source SELECT tLookupType.Type FROM tType
> After Update Me.ComboType.Requery
>
> Assembly Subform
> Source Object = qAssemblySubform
> Link Child=Type
> Link Master =Type
> On Current DoCmd.Requery
>
> Combo on Subform ComboDescription
> Control Source=Description
> Row Source = Table/Query
> Row Source
> SELECT tAssemblyDescriptions.RecordNumber,
> tAssemblyDescriptions.Description, tAssemblyDescriptions.Type FROM
> tAssemblyDescriptions WHERE
> (((tAssemblyDescriptions.Type)=Forms![f*GeneralInformationWITHQUOTE]!Type));
>
> I hope this isn't too long or confusing. I've already spent WAY TOO MUCH
> time with this and am becoming very frustrated. Any help would be very
> much
> appreciated!
> Thanks,
> Phisaw

Thanks, Ken for the reply. I've changed this around so many times trying to
get something to work and I guess I didn't complet the code for the subform.
I tried a different route with this and at least have it populating the combo
boxes. Now it doesn't want to fill in the tables correctly. When I select
"type" in the first combo on the main form, it gives the related descriptions
in the second combo on the subform. After making selections needed, I check
the subform table and there are no records, but if I check the table that the
combo boxes are based on with "RecordNumber", "Type", and "Description",
entries are made duplicating the descriptions with record numbers and blank
types. It's throwing all the entries I select on the subform to this table.
Not sure what I'm doing wrong, but any help is greatly appreciated!! Here is
the code for each box and form:

Combo Box #1 on main form
Name: ComboType
Control Source: Type
Row Source:

SELECT DISTINCT tAssemblyDescriptions.Type FROM tAssemblyDescriptions ORDER
BY tAssemblyDescriptions.Type;

After Update:
Private Sub ComboType_AfterUpdate()
On Error Resume Next
Me!fAssemblySubform.Form!ComboDescription.RowSource = "Select
tAssemblyDescriptions.Description " & _
"FROM tAssemblyDescriptions " & _
"WHERE tAssemblyDescriptions.Type = '" & ComboType.Value & "'" & _
"ORDER BY tAssemblyDescriptions.Description;"

End Sub
ComboBox #2 on subform:
Name: Description
Control Source: Description
Row Source:

Select tDescriptions.Description FROM tAssemblyDescriptions WHERE
tAssemblyDescriptions.Type = 'COMPLETE PUMP'ORDER BY
tAssemblyDescriptions.Description;

Once again, I would appreciate any help with resolving this.
Thanks, PHisaw

"Ken Snell (MVP)" wrote:

> Is this code step supposed to requery the subform's combo box:
> Me.ComboType.Requery
>
> If yes, then the syntax is not correct. You have to refer to a control on a
> subform using this syntax:
> Me!SubformName!ControlOnSubform
>
> where SubformName is the name of the subform control (the control that holds
> the subform).
>
> Alternatively, these syntaces could be used:
> Me!SubformName.Form!ControlOnSubform
> Me.SubformName.Form.ControlOnSubform
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
>
>
>
> "PHisaw" <(E-Mail Removed)> wrote in message
> news:84F4028C-0427-47EC-AA88-(E-Mail Removed)...
> > First, a word of thanks to all those who have previously helped with
> > problems!! Much appreciated!!
> > I have read many of the posts on cascading combo boxes and filtering with
> > combo boxes and cannot get the following to work. I have a main form with
> > a
> > combo box with three types of assemblies listed. I have a subform on the
> > main form with a combo box of descriptions based on each type of assembly.
> > If I choose one assembly from main form combo, the subform combo is
> > populated
> > with that type. If I choose another, it holds the first selection until I
> > close the form and go back to it. I have tried requerying and refreshing
> > and
> > still nothing. I will list the coding for each aspect.
> > Combo Box on main form
> > Control Source = Type
> > Row Source Type = Table/Query
> > Row Source SELECT tLookupType.Type FROM tType
> > After Update Me.ComboType.Requery
> >
> > Assembly Subform
> > Source Object = qAssemblySubform
> > Link Child=Type
> > Link Master =Type
> > On Current DoCmd.Requery
> >
> > Combo on Subform ComboDescription
> > Control Source=Description
> > Row Source = Table/Query
> > Row Source
> > SELECT tAssemblyDescriptions.RecordNumber,
> > tAssemblyDescriptions.Description, tAssemblyDescriptions.Type FROM
> > tAssemblyDescriptions WHERE
> > (((tAssemblyDescriptions.Type)=Forms![f*GeneralInformationWITHQUOTE]!Type));
> >
> > I hope this isn't too long or confusing. I've already spent WAY TOO MUCH
> > time with this and am becoming very frustrated. Any help would be very
> > much
> > appreciated!
> > Thanks,
> > Phisaw
>
>
>

I am not following your description of the problem here, but I think what
you're saying is that your selections in the subform do not get stored to
the table?

Are all the controls in the subform bound to fields in the subform's
RecordSource?

From what you say, the Type is being set by the combo box in the main form?
If yes, that will not store to the subform's table automatically. You will
need to have a Type field in the subform as well, and then you'll need code
in the subform's Current event to read the value from the main form and
write it into the Type field in the subform. (It may be possible to use the
LinkChildFields and LinkMasterFields properties of the subform control to do
something similar, but I'm guessing that that might cause the wrong or too
many records to be in the subform, so I won't suggest this setup.)
--

Ken Snell
<MS ACCESS MVP>



"PHisaw" <(E-Mail Removed)> wrote in message
news:2040F581-6435-409F-BA70-(E-Mail Removed)...
> Thanks, Ken for the reply. I've changed this around so many times trying
> to
> get something to work and I guess I didn't complet the code for the
> subform.
> I tried a different route with this and at least have it populating the
> combo
> boxes. Now it doesn't want to fill in the tables correctly. When I
> select
> "type" in the first combo on the main form, it gives the related
> descriptions
> in the second combo on the subform. After making selections needed, I
> check
> the subform table and there are no records, but if I check the table that
> the
> combo boxes are based on with "RecordNumber", "Type", and "Description",
> entries are made duplicating the descriptions with record numbers and
> blank
> types. It's throwing all the entries I select on the subform to this
> table.
> Not sure what I'm doing wrong, but any help is greatly appreciated!! Here
> is
> the code for each box and form:
>
> Combo Box #1 on main form
> Name: ComboType
> Control Source: Type
> Row Source:
>
> SELECT DISTINCT tAssemblyDescriptions.Type FROM tAssemblyDescriptions
> ORDER
> BY tAssemblyDescriptions.Type;
>
> After Update:
> Private Sub ComboType_AfterUpdate()
> On Error Resume Next
> Me!fAssemblySubform.Form!ComboDescription.RowSource = "Select
> tAssemblyDescriptions.Description " & _
> "FROM tAssemblyDescriptions " & _
> "WHERE tAssemblyDescriptions.Type = '" & ComboType.Value & "'" & _
> "ORDER BY tAssemblyDescriptions.Description;"
>
> End Sub
> ComboBox #2 on subform:
> Name: Description
> Control Source: Description
> Row Source:
>
> Select tDescriptions.Description FROM tAssemblyDescriptions WHERE
> tAssemblyDescriptions.Type = 'COMPLETE PUMP'ORDER BY
> tAssemblyDescriptions.Description;
>
> Once again, I would appreciate any help with resolving this.
> Thanks, PHisaw
>

PHisaw wrote:
>
>Combo Box #1 on main form
>Name: ComboType
>Control Source: Type
>Row Source:
>
>SELECT DISTINCT tAssemblyDescriptions.Type FROM tAssemblyDescriptions ORDER
>BY tAssemblyDescriptions.Type;
>
>After Update:
>Private Sub ComboType_AfterUpdate()
> On Error Resume Next
> Me!fAssemblySubform.Form!ComboDescription.RowSource = "Select
>tAssemblyDescriptions.Description " & _
> "FROM tAssemblyDescriptions " & _
> "WHERE tAssemblyDescriptions.Type = '" & ComboType.Value & "'" & _
> "ORDER BY tAssemblyDescriptions.Description;"
>
>End Sub
>ComboBox #2 on subform:
>Name: Description
>Control Source: Description
>Row Source:
>
>Select tDescriptions.Description FROM tAssemblyDescriptions WHERE
>tAssemblyDescriptions.Type = 'COMPLETE PUMP'ORDER BY
>tAssemblyDescriptions.Description;
>
Add as below, try "Forms!" instead of "Me!".

Private Sub ComboType_AfterUpdate()
On Error Resume Next

Forms!fAssemblySubform.Form!ComboDescription.RowSource = "Select
tAssemblyDescriptions.Description " & _
"FROM tAssemblyDescriptions " & _
"WHERE tAssemblyDescriptions.Type = '" & ComboType.Value & "'" & _
"ORDER BY tAssemblyDescriptions.Description;"

Forms!fAssemblySubform.Form!ComboDescription.Requery

End Sub

And in the subform, on the current event add this

Me.ComboDescription.Requery

--
Message posted via http://www.accessmonster.com

Thanks, Ken for the reply. After much rearranging and coding, it finally
works! Thanks for all your help!

"Ken Snell (MVP)" wrote:

> I am not following your description of the problem here, but I think what
> you're saying is that your selections in the subform do not get stored to
> the table?
>
> Are all the controls in the subform bound to fields in the subform's
> RecordSource?
>
> From what you say, the Type is being set by the combo box in the main form?
> If yes, that will not store to the subform's table automatically. You will
> need to have a Type field in the subform as well, and then you'll need code
> in the subform's Current event to read the value from the main form and
> write it into the Type field in the subform. (It may be possible to use the
> LinkChildFields and LinkMasterFields properties of the subform control to do
> something similar, but I'm guessing that that might cause the wrong or too
> many records to be in the subform, so I won't suggest this setup.)
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
>
> "PHisaw" <(E-Mail Removed)> wrote in message
> news:2040F581-6435-409F-BA70-(E-Mail Removed)...
> > Thanks, Ken for the reply. I've changed this around so many times trying
> > to
> > get something to work and I guess I didn't complet the code for the
> > subform.
> > I tried a different route with this and at least have it populating the
> > combo
> > boxes. Now it doesn't want to fill in the tables correctly. When I
> > select
> > "type" in the first combo on the main form, it gives the related
> > descriptions
> > in the second combo on the subform. After making selections needed, I
> > check
> > the subform table and there are no records, but if I check the table that
> > the
> > combo boxes are based on with "RecordNumber", "Type", and "Description",
> > entries are made duplicating the descriptions with record numbers and
> > blank
> > types. It's throwing all the entries I select on the subform to this
> > table.
> > Not sure what I'm doing wrong, but any help is greatly appreciated!! Here
> > is
> > the code for each box and form:
> >
> > Combo Box #1 on main form
> > Name: ComboType
> > Control Source: Type
> > Row Source:
> >
> > SELECT DISTINCT tAssemblyDescriptions.Type FROM tAssemblyDescriptions
> > ORDER
> > BY tAssemblyDescriptions.Type;
> >
> > After Update:
> > Private Sub ComboType_AfterUpdate()
> > On Error Resume Next
> > Me!fAssemblySubform.Form!ComboDescription.RowSource = "Select
> > tAssemblyDescriptions.Description " & _
> > "FROM tAssemblyDescriptions " & _
> > "WHERE tAssemblyDescriptions.Type = '" & ComboType.Value & "'" & _
> > "ORDER BY tAssemblyDescriptions.Description;"
> >
> > End Sub
> > ComboBox #2 on subform:
> > Name: Description
> > Control Source: Description
> > Row Source:
> >
> > Select tDescriptions.Description FROM tAssemblyDescriptions WHERE
> > tAssemblyDescriptions.Type = 'COMPLETE PUMP'ORDER BY
> > tAssemblyDescriptions.Description;
> >
> > Once again, I would appreciate any help with resolving this.
> > Thanks, PHisaw
> >
>
>
>

Glad to hear it!

--

Ken Snell
<MS ACCESS MVP>

"PHisaw" <(E-Mail Removed)> wrote in message
news:B91B424D-5198-4A4F-A8FB-(E-Mail Removed)...
> Thanks, Ken for the reply. After much rearranging and coding, it finally
> works! Thanks for all your help!
>