By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Combo Box Error in Row Source

100+
P: 132
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]));
Jun 4 '07 #1
Share this Question
Share on Google+
36 Replies


Rabbit
Expert Mod 10K+
P: 12,315
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
Jun 4 '07 #2

100+
P: 132
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]));
Jun 4 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
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]
Jun 4 '07 #4

100+
P: 132
[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.
Jun 4 '07 #5

Rabbit
Expert Mod 10K+
P: 12,315
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.
Jun 4 '07 #6

FishVal
Expert 2.5K+
P: 2,653
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".
Jun 4 '07 #7

100+
P: 132
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]
Jun 4 '07 #8

Rabbit
Expert Mod 10K+
P: 12,315
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.
Jun 4 '07 #9

100+
P: 132
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??
Jun 4 '07 #10

Rabbit
Expert Mod 10K+
P: 12,315
You would put it in the after update event of the combo box that should trigger a change.

Take a look at this tutorial.
Jun 4 '07 #11

100+
P: 132
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
Jun 4 '07 #12

100+
P: 132
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
Jun 4 '07 #13

100+
P: 132
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
Jun 4 '07 #14

Rabbit
Expert Mod 10K+
P: 12,315
You're using the wrong combo boxes. And some of your spacing is wrong. And this assumes that Combo86 and [Primary Disorder] are numbers.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo86_AfterUpdate()
  2. With Me![Combo90]
  3.    If IsNull(Me!Combo86) Then
  4.       .RowSource = ""
  5.    Else
  6.       .RowSource = "Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]=" & Me!Combo86
  7.    End If
  8.    Call .Requery
  9. End With
  10. End Sub
Jun 5 '07 #15

100+
P: 132
You're using the wrong combo boxes. And some of your spacing is wrong. And this assumes that Combo86 and [Primary Disorder] are numbers.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo86_AfterUpdate()
  2. With Me![Combo90]
  3.    If IsNull(Me!Combo86) Then
  4.       .RowSource = ""
  5.    Else
  6.       .RowSource = "Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]=" & Me!Combo86
  7.    End If
  8.    Call .Requery
  9. End With
  10. End Sub
They are NOT numbers.
Jun 5 '07 #16

Rabbit
Expert Mod 10K+
P: 12,315
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.

Expand|Select|Wrap|Line Numbers
  1. "Select [Secondary Disorder] FROM SecondaryDisorder WHERE [Primary Disorder]='" & Me!Combo86 & "'"
Jun 5 '07 #17

100+
P: 132
That's what your original code implied.

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

Expand|Select|Wrap|Line Numbers
  1. "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
Jun 5 '07 #18

100+
P: 132
That's what your original code implied.

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

Expand|Select|Wrap|Line Numbers
  1. "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
Jun 5 '07 #19

100+
P: 132
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
Jun 5 '07 #20

Rabbit
Expert Mod 10K+
P: 12,315
What is the row source of combo86?
What is the metadata of your two tables?
Jun 5 '07 #21

100+
P: 132
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
Jun 5 '07 #22

FishVal
Expert 2.5K+
P: 2,653
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
Jun 5 '07 #23

Rabbit
Expert Mod 10K+
P: 12,315
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.
Jun 5 '07 #24

Rabbit
Expert Mod 10K+
P: 12,315
You have a subform on this right?
Where are the combo boxes located?
Jun 5 '07 #25

100+
P: 132
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!!
Jun 5 '07 #26

100+
P: 132
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)
Jun 5 '07 #27

100+
P: 132
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
Jun 5 '07 #28

FishVal
Expert 2.5K+
P: 2,653
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.
Jun 6 '07 #29

100+
P: 132
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?
Jun 6 '07 #30

Rabbit
Expert Mod 10K+
P: 12,315
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.
Jun 6 '07 #31

100+
P: 132
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!
Jun 6 '07 #32

Rabbit
Expert Mod 10K+
P: 12,315
Thanks for all your help, Rabbit!
Not a problem, good luck.
Jun 6 '07 #33

FishVal
Expert 2.5K+
P: 2,653
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
Jun 7 '07 #34

Rabbit
Expert Mod 10K+
P: 12,315
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.
Jun 7 '07 #35

jmunoz66
P: 2
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

Ive 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??? ill appreciate your coments.

PD I Apologize by my English

Thanks
Jun 16 '07 #36

jmunoz66
P: 2
!!! 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 Im.... and its works,

See You Guys and Girls
Jun 24 '07 #37

Post your reply

Sign in to post your reply or Sign up for a free account.