Combo Box Woes

[Guest]

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

 

[Ken Snell \(MVP\)]

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

 

[Guest]

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

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>

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

 

[Ken Snell \(MVP\)]

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.)

 

[AccessVandal via AccessMonster.com]

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

 

[Guest]

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

 

[Ken Snell \(MVP\)]

Glad to hear it!