Change RowSource Expression

[Spidey3721]

I am experimenting with expressions - trying to get the rowsource of a form
to change based on which tab is selected on a SEPARATE form's tabcontrol.
For each page in the tabcontrol, I have set the onclick event to a variation
of the following expression

[Forms]![name of form]![Combobox].[RowSource]=SELECT .....FROM .....WHERE

The only variation between the different expressions (for each page) is in
the WHERE statement, which simply changes the criteria of the query for the
rowsource...

Question: Is the structure of this expression acceptable - I am getting an
error message when I click a page, saying that Access cannot find the Macro
"[Forms]![name of form]![Combobox].[RowSource]"

Is there a way to do what I am trying to do with an expression ?

Thanks for the help...

 

[Guest]

the syntax of the expression you are showing, shows that
you are trying to change the rowsource for a control, not
the "recordsource" for the form. To change the forms
recordsource you would use: forms![Name of
Form].RecordSource ="....." and then do a forms![name of
form].requery

 

[Dirk Goldgar]

I am experimenting with expressions - trying to get the rowsource of
a form to change based on which tab is selected on a SEPARATE form's
tabcontrol. For each page in the tabcontrol, I have set the onclick
event to a variation of the following expression

[Forms]![name of form]![Combobox].[RowSource]=SELECT .....FROM
.....WHERE

The only variation between the different expressions (for each page)
is in the WHERE statement, which simply changes the criteria of the
query for the rowsource...

Question: Is the structure of this expression acceptable - I am
getting an error message when I click a page, saying that Access
cannot find the Macro "[Forms]![name of form]![Combobox].[RowSource]"

Is there a way to do what I am trying to do with an expression ?

Thanks for the help...

You say "the rowsource of a form", but there's no such thing. It looks
like you're trying to set the rowsource of a combo box on the form; is
that correct?

From the error message you're getting, I guess that you've put your
expression directly into the OnClick property of a tab page. That won't
work for two reasons: first, because the Click event is not the right
event (it doesn't fire when the tab control changes pages) and second,
because you need to do this in an event procedure, not as an expression
stopred in the OnClick property.

Use the Change event of the tab control itself, not its individual
pages. The value of the tab control will be the page index of the
currently selected page. Set the OnChange property line to "[Event
Procedure]", and then build an event procedure along these lines:

'---- start of example code ----
Private Sub MyTabControl_Change()

Dim strRowSource As String

Select Case Me!MyTabControl
Case 0
strRowSource = "SELECT ... FROM ... WHERE ..."
Case 1
strRowSource = "SELECT ... FROM ... WHERE ..."
Case 2
strRowSource = "SELECT ... FROM ... WHERE ..."
' ... and so on for all pages ...
End Select

Forms![name of form]![Combobox].RowSource = strRowSource

End Sub
'---- end of example code ----

Obviously, you need to substitute the appropriate control names and SQL
strings in the above code.

 

[Spidey3721]

I apologize - I actually am intending to change the rowsource of a control.

ALSO - Can you explain what you mean by : "and then do a forms![name of

form].requery "?

the syntax of the expression you are showing, shows that
you are trying to change the rowsource for a control, not
the "recordsource" for the form. To change the forms
recordsource you would use: forms![Name of
Form].RecordSource ="....." and then do a forms![name of
form].requery

-----Original Message-----
I am experimenting with expressions - trying to get the rowsource of a form
to change based on which tab is selected on a SEPARATE form's tabcontrol.
For each page in the tabcontrol, I have set the onclick event to a variation
of the following expression

[Forms]![name of form]![Combobox].[RowSource] =SELECT .....FROM .....WHERE

The only variation between the different expressions (for each page) is in
the WHERE statement, which simply changes the criteria of the query for the
rowsource...

Question: Is the structure of this expression acceptable - I am getting an
error message when I click a page, saying that Access cannot find the Macro
"[Forms]![name of form]![Combobox].[RowSource]"

Is there a way to do what I am trying to do with an expression ?

Thanks for the help...


.

 

[Dirk Goldgar]

I apologize - I actually am intending to change the rowsource of a
control.

ALSO - Can you explain what you mean by : "and then do a forms![name
of

form].requery "?

That would be a call to the form's Requery method, which re-runs the
form's RecordSource query. Don't worry about it, though, because (a)
you're not going to be changing the form's recordsource after all, and
(b) it's not necessary to call the Requery method when you change a
form's RecordSource property (or a control's RowSource property) -- that
is done automatically.

 

[Larry]

after you make a change to either the rowsource of a combo
box or the recordSource of a form, you must force the
control to be requeried in order for the values shown in
the combobox to change and be based upon the new source.

-----Original Message-----
I apologize - I actually am intending to change the rowsource of a control.

ALSO - Can you explain what you mean by : "and then do a forms![name of

form].requery "?

the syntax of the expression you are showing, shows that
you are trying to change the rowsource for a control, not
the "recordsource" for the form. To change the forms
recordsource you would use: forms![Name of
Form].RecordSource ="....." and then do a forms![name of
form].requery

-----Original Message-----
I am experimenting with expressions - trying to get the rowsource of a form
to change based on which tab is selected on a SEPARATE form's tabcontrol.
For each page in the tabcontrol, I have set the onclick event to a variation
of the following expression

[Forms]![name of form]![Combobox].[RowSource] =SELECT .....FROM .....WHERE

The only variation between the different expressions

(for
each page) is in

the WHERE statement, which simply changes the criteria

of
the query for the

rowsource...

Question: Is the structure of this expression

acceptable -
I am getting an

error message when I click a page, saying that Access cannot find the Macro
"[Forms]![name of form]![Combobox].[RowSource]"

Is there a way to do what I am trying to do with an expression ?

Thanks for the help...


.

.

 

[Van T. Dinh]

No. See Dirk's response which I agree completely.

HTH
Van T. Dinh
MVP (Access)