Combo Box Error in Row Source

[Proaccesspro]

I have a subform with 2 drop down boxes. The choices in the 2nd drop down box depend on the value of the first . When I run the subform on its own, it all works beautifully. However, when the I run the main form that calls the subform, I get a one of those parameter input required messages. As Greg Brady would say, "what gives"? Here is the code (query) from the subform:

SELECT SecondaryDisorder.[Secondary Disorder]
FROM SecondaryDisorder
WHERE (((SecondaryDisorder.[Primary Disorder])=[forms]![case subform]![combo86]));

[Rabbit]

I have a subform with 2 drop down boxes. The choices in the 2nd drop down box depend on the value of the first . When I run the subform on its own, it all works beautifully. However, when the I run the main form that calls the subform, I get a one of those parameter input required messages. As Greg Brady would say, "what gives"? Here is the code (query) from the subform:

SELECT SecondaryDisorder.[Secondary Disorder]
FROM SecondaryDisorder
WHERE (((SecondaryDisorder.[Primary Disorder])=[forms]![case subform]![combo86]));

The syntax to refer something on a subform is Forms!SubformControl.Form.Control

[Proaccesspro]

The syntax to refer something on a subform is Forms!SubformControl.Form.Control

I updated the SQL on the subform......same error!!!

SELECT SecondaryDisorder.[Secondary Disorder]
FROM SecondaryDisorder
WHERE (((SecondaryDisorder.[Primary Disorder])=[forms]![SubformControl].[case subform].[combo86]));

[Rabbit]

I updated the SQL on the subform......same error!!!

SELECT SecondaryDisorder.[Secondary Disorder]
FROM SecondaryDisorder
WHERE (((SecondaryDisorder.[Primary Disorder])=[forms]![SubformControl].[case subform].[combo86]));

[case subform] is the subform control.
Form stays as is.

I should've been more clear.

[Forms]![case subform].Form.[combo86]

[Proaccesspro]

[case subform] is the subform control.
Form stays as is.

I should've been more clear.

[Forms]![case subform].Form.[combo86]

I'm still confused......In your example, what goes in place of the word Form? Would it be the the Main form??

In my DB, Main Form is the Main form and Case Subform is the subform.

[Rabbit]

I'm still confused......In your example, what goes in place of the word Form? Would it be the the Main form??

In my DB, Main Form is the Main form and Case Subform is the subform.

Nothing would go in Form. Form stays as is.

[FishVal]

Hi!

It's very simple.
When you open form as subform, it will not be added to collection 'Forms".
So referencing like "Forms!frmSubForm.Controls!ctrName" will return value when frmName is open as independant form.
If it is open as subform the valid reference will be "Forms!frmMainForm.Controls!frmSubForm.Form!Contro ls!ctrName".

[Proaccesspro]

Nothing would go in Form. Form stays as is.

Well....Access put brackets around FORM, but it still does not work. Here is the code in the query:

[Forms]![case subform].[Form].[combo86]

[Rabbit]

Are you trying to do this from the after update event of the combobox? Because that's where you have to do it from.

You can't just set the control source of the combobox to that SQL statement because it will never update.

And you said the "code (query) from the subform", I assume this is in reference to the row source of the combo box.

[Proaccesspro]

Hi!

It's very simple.
When you open form as subform, it will not be added to collection 'Forms".
So referencing like "Forms!frmSubForm.Controls!ctrName" will return value when frmName is open as independant form.
If it is open as subform the valid reference will be "Forms!frmMainForm.Controls!frmSubForm.Form!Contro ls!ctrName".

Where would I place this code??

[Rabbit]

You would put it in the after update event of the combo box that should trigger a change.

Take a look at this tutorial.

[Proaccesspro]

Are you trying to do this from the after update event of the combobox? Because that's where you have to do it from.

You can't just set the control source of the combobox to that SQL statement because it will never update.

And you said the "code (query) from the subform", I assume this is in reference to the row source of the combo box.

Yes.... the first box is titled combo86...I have this code in the after update event. The second combo box is titled combo90. what do i need to add?

Me![Combo90] = Null
Me![Combo90].Requery


[Forms]![case subform].Form.[Combo86]

End Sub

[Proaccesspro]

Yes.... the first box is titled combo86...I have this code in the after update event. The second combo box is titled combo90. what do i need to add?

Me![Combo90] = Null
Me![Combo90].Requery


[Forms]![case subform].Form.[Combo86]

End Sub

Here is the code I created.....It's not quite working...

Private Sub Combo86_AfterUpdate()
With Me![Combo86]
If IsNull(Me!Combo90) Then
.RowSource = ""
Else
.RowSource = "Select[Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]=" & Me!Combo86

End If
Call .Requery
End With


Ed Sub

[Proaccesspro]

Here is the code I created.....It's not quite working...

Private Sub Combo86_AfterUpdate()
With Me![Combo86]
If IsNull(Me!Combo90) Then
.RowSource = ""
Else
.RowSource = "Select[Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]=" & Me!Combo86

End If
Call .Requery
End With


Ed Sub

Here is my latest code....When run, the second combo box does not have any choices...in other words, i guess it is not finding a match

[Rabbit]

You're using the wrong combo boxes. And some of your spacing is wrong. And this assumes that Combo86 and [Primary Disorder] are numbers.

Private Sub Combo86_AfterUpdate()
With Me![Combo90]
   If IsNull(Me!Combo86) Then
      .RowSource = ""
   Else
      .RowSource = "Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]=" & Me!Combo86
   End If
   Call .Requery
End With
End Sub

[Proaccesspro]

You're using the wrong combo boxes. And some of your spacing is wrong. And this assumes that Combo86 and [Primary Disorder] are numbers.

Private Sub Combo86_AfterUpdate()
With Me![Combo90]
   If IsNull(Me!Combo86) Then
      .RowSource = ""
   Else
      .RowSource = "Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]=" & Me!Combo86
   End If
   Call .Requery
End With
End Sub

They are NOT numbers.

[Rabbit]

They are NOT numbers.

That's what your original code implied.

You'll need to use single quotes to tell it that it's text.

"Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='" & Me!Combo86 & "'"

[Proaccesspro]

That's what your original code implied.

You'll need to use single quotes to tell it that it's text.

"Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='" & Me!Combo86 & "'"

Here is what I have:

Private Sub Combo86_AfterUpdate()
With Me![Combo90]
If IsNull(Me!Combo86) Then
.RowSource = ""
Else
.RowSource = "Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='" & Me!Combo86 & "'"
End If
Call .Requery
End With
End Sub

[Proaccesspro]

That's what your original code implied.

You'll need to use single quotes to tell it that it's text.

"Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='" & Me!Combo86 & "'"

Here is what I have. Still no data in Combo90

Private Sub Combo86_AfterUpdate()
With Me![Combo90]
If IsNull(Me!Combo86) Then
.RowSource = ""
Else
.RowSource = "Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='" & Me!Combo86 & "'"
End If
Call .Requery
End With
End Sub

[Proaccesspro]

Here is what I have. Still no data in Combo90

Private Sub Combo86_AfterUpdate()
With Me![Combo90]
If IsNull(Me!Combo86) Then
.RowSource = ""
Else
.RowSource = "Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='" & Me!Combo86 & "'"
End If
Call .Requery
End With
End Sub

I copied EXACTLY what you wrote.....still NO cigar!!!

Private Sub Combo86_AfterUpdate()
With Me![Combo90]
If IsNull(Me!Combo86) Then
.RowSource = ""
Else
.RowSource = "Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='" & Me!Combo86 & "'"
End If
Call .Requery
End With
End Sub

[Rabbit]

What is the row source of combo86?
What is the metadata of your two tables?

[Proaccesspro]

What is the row source of combo86?
What is the metadata of your two tables?

Combo86 Row Source = table Primary Disorder field Disorder

Metadata of 2 tables = Primary Disorder has 1 field named disorder. it is designated as text

Secondary Disorder has 2 fields named primary disorder and secondary disorder. Both are designated as text

[FishVal]

I copied EXACTLY what you wrote.....still NO cigar!!!

Private Sub Combo86_AfterUpdate()
With Me![Combo90]
If IsNull(Me!Combo86) Then
.RowSource = ""
Else
.RowSource = "Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='" & Me!Combo86 & "'"
End If
Call .Requery
End With
End Sub

Hi!

Actually you have a small mistake in your SQL query string. From some reasons Access will appreciate SQL string terminated with ";". So your RowSource property should be set to
Me![Combo90].RowSource="Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='" & Me!Combo86 & "';"


If it is not helpful then trace your sub.

Toggle breakpoint on the row (<F9> key)
Private Sub Combo86_AfterUpdate()
When the debugger will popup do the following:
1) type "? Me!Combo86" in Immediate pane and press enter, see what does it actually return
2) proceed stepwise (<F8> key) to the row
End If
3) type "? .RowSource" in Immediate pane and press return
4) copy the SQL string and paste it in query builder
5) run query and see what it does return

Good Luck

[Rabbit]

I'm stretching here, try taking the brackets off of combo90 in the code. I don't think the semicolon is a problem here. You can do without it most of the time.

[Rabbit]

You have a subform on this right?
Where are the combo boxes located?

[Proaccesspro]

Hi!

Actually you have a small mistake in your SQL query string. From some reasons Access will appreciate SQL string terminated with ";". So your RowSource property should be set to
Me![Combo90].RowSource="Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='" & Me!Combo86 & "';"


If it is not helpful then trace your sub.

Toggle breakpoint on the row (<F9> key)
Private Sub Combo86_AfterUpdate()
When the debugger will popup do the following:
1) type "? Me!Combo86" in Immediate pane and press enter, see what does it actually return
2) proceed stepwise (<F8> key) to the row
End If
3) type "? .RowSource" in Immediate pane and press return
4) copy the SQL string and paste it in query builder
5) run query and see what it does return

Good Luck

I think I might have discovered something.....I failed to mention that this is a one to many relationship...the primary disorder will generate multple choices for the secondary disorder.....I ran the de-bugger and it all seems to work OK...when I got to the end if statement, the row source read

Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='Immuno Disorder';

In the secondary disorder table I have this

Primary Disorder Secondary Disorder
Immuno Disorder Chron Fatique Synd
Immuno Disorder Fibromyalgia
Immuno Disorder Lupus
Immuno Disorder Rhematism
Immuno Disorder Rheumatoid Arthritis
Immuno Disorder Scleroderma
Immuno Disorder Other

So the drop down box (combo90) should list the above choices but instead it is blank, as is my idea on what's wrong!!

[Proaccesspro]

You have a subform on this right?
Where are the combo boxes located?

Yes...there are actually 2 subforms on the main form.....so I have a main form, subform1, and subform2. The combo boxes are on subform 1 (titled case subform)

[Proaccesspro]

I'm stretching here, try taking the brackets off of combo90 in the code. I don't think the semicolon is a problem here. You can do without it most of the time.

took out the brackets...no difference

[FishVal]

I think I might have discovered something.....I failed to mention that this is a one to many relationship...the primary disorder will generate multple choices for the secondary disorder.....I ran the de-bugger and it all seems to work OK...when I got to the end if statement, the row source read

Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='Immuno Disorder';

In the secondary disorder table I have this

Primary Disorder Secondary Disorder
Immuno Disorder Chron Fatique Synd
Immuno Disorder Fibromyalgia
Immuno Disorder Lupus
Immuno Disorder Rhematism
Immuno Disorder Rheumatoid Arthritis
Immuno Disorder Scleroderma
Immuno Disorder Other

So the drop down box (combo90) should list the above choices but instead it is blank, as is my idea on what's wrong!!

You may do the following:

1) Check the properties of Combo90:
ColumnCount=1,
ColumnWidth>0 or nothing
BoundColumn=1,
RowSourceType=Table/Query
... maybe something else

2) Manually set RowSource in design view and see whether Combo90 is at all able to reflect query

3) :) or simply add a new combobox and try with it

Good Luck.

[Proaccesspro]

You may do the following:

1) Check the properties of Combo90:
ColumnCount=1,
ColumnWidth>0 or nothing
BoundColumn=1,
RowSourceType=Table/Query
... maybe something else

2) Manually set RowSource in design view and see whether Combo90 is at all able to reflect query

3) :) or simply add a new combobox and try with it

Good Luck.

The problem was in COMBO90's properties!! THANKS!!!

Other questions for you. Are you able to modify the font size and color of the tabs when using the tab control function?? Also, can you modify the font on a form diplayed in datasheet view?

[Rabbit]

The problem was in COMBO90's properties!! THANKS!!!

Other questions for you. Are you able to modify the font size and color of the tabs when using the tab control function?? Also, can you modify the font on a form diplayed in datasheet view?

No to question 1, maybe for question 2. I think whatever your defaults are for tables will affect your datasheet view. So if you change your default fonts for everything else, it may affect the datasheet view.

[Proaccesspro]

No to question 1, maybe for question 2. I think whatever your defaults are for tables will affect your datasheet view. So if you change your default fonts for everything else, it may affect the datasheet view.

Thanks for all your help, Rabbit!

[Rabbit]

Thanks for all your help, Rabbit!

Not a problem, good luck.

[FishVal]

The problem was in COMBO90's properties!! THANKS!!!

Other questions for you. Are you able to modify the font size and color of the tabs when using the tab control function?? Also, can you modify the font on a form diplayed in datasheet view?

Glad to help you.

What concerns your questions:

1) Form datasheet view.

There is no opportunity to change Datasheet appearance in design view.
However you can make it programmatically through Form object. Go to object browser and see numerous properties within object Form having names "Datasheet..." which allow you to change the appearance of your datasheet.

2) Tab control.

You can change tab font and tab page backcolor in design view.

Good Luck

[Rabbit]

Glad to help you.

What concerns your questions:

1) Form datasheet view.

There is no opportunity to change Datasheet appearance in design view.
However you can make it programmatically through Form object. Go to object browser and see numerous properties within object Form having names "Datasheet..." which allow you to change the appearance of your datasheet.

2) Tab control.

You can change tab font and tab page backcolor in design view.

Good Luck

In regards to #2, whenever I've tried to set those properties they never work and just keep their default values.

[jmunoz66]

CASCADING SELECTION COMBOS INSIDE OF A SUBFORM

Sorry guys, i followed up this thread, but i loose my way.

I Have the same system and Error you discussed above:

1 Main Form....... CentrosDeEstudio
2 SubForm..........Centros_Det
3 4 Comboboxes (Region,Provincia,Distrito,Municipo) on cacaded selection inside of Centros_Det :
Region delimites
Provincia delimites
Distrito delimites
Municipio (they all together defines a Geo location)
4 Rowsource type for them (comboboxes) is Table/Query
5 The four comboboxes binds to catalogs that have the fields ID (numeric primary key), nombre (used for description string) and a field used to link as child on a one to many relationship to the preceeding catalog .

6 When i Choose Region = 1 from the first combo box (REGION), i expected to receive a list of options corresponding to the linked Provincias on Provincia ComoBox Instead of, i received an empty list, ..... and so.

7 on PROVINCIA Rowsource i built "Select provincia.id,provincia.Nombre From Provincias where provincia.region = Forms!CentrosDeEstudio!Centros_det.Form!Region which syntax it is accepted by the wizard

8 On Region AfterUptate Event, i shooted a trigger that basically does Me.Provincia.Requery intended to update the next combobox selection

I´ve tryed all the sorts of refferences to the combobox objects i've found, but Nothing works eventhough they do when i run Centros_det as an independent Form.

Could you help please??? i´ll appreciate your coments.

PD I Apologize by my English

Thanks

[jmunoz66]

!!! Proof and Error !!!

I have solved my problem yet, and i hope you do too.

I used:
Forms![MainForm].[Form]![Subform].[Form]![ControlName] ....
from wherever I´m.... and its works,

See You Guys and Girls