Rowsource of unbound object

N

NevilleT

This should be very simple but a few hours later ... well you know how it is.

I have a form which has a Gantt chart. I want to produce a printed version
so created a report. The Gantt chart can be sorted by sequence number, start
or finish date. You select the sort order using an option group on the form.

What I want to do is to change the report rowsource when the report opens to
use one of three query sorted in one of three ways - sequence, start or end.
I can do it manually and it works. I enter a different query as the
rowsource of the unbound object and it displays just as it should. When I
try to do it using code it fails telling me
"You entered an expression that has an invalid reference to the property
rowsource."

The code is:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms!frmGanttChart.opgSortBy
Case Is = 1
Me.oleGraph.RowSource = "qryGanttGraphSeqOrder"
Case Is = 2
Me.oleGraph.RowSource = "qryGanttGraphStartOrder"
Case Is = 3
Me.oleGraph.RowSource = "qryGanttGraphFinishOrder"
End Select
End Sub
 
D

Dale Fye

have you tried using the reports Load event?

Another place to try this would be in the reports Detail sections Format
event (assuming that chart object is in the detail section).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
N

NevilleT

Hi Dale

Same error using on activate.
If I use the page event I get a message "You can't set the row source
property in print preview or after printing has started."

Same error if I use the on format
 
D

Dale Fye

I'm not sure I understand your response.

Does this mean you tried the Report_Load and Detail_Format events?

I didn't say anthing about the Report_Activiate or Report_Page events.

--

Dale

email address is invalid
Please reply to newsgroup only.
 
N

NevilleT

Hi Dale
The report events are Open, Close, Activate, Deactivate, No Data, Page,
Error. There is no Load event as there is for a form. The detail events are
Format, Print and Retreat.
 
D

Dale Fye

How about this.

Try modifying the query that is the source for your chart, to something
like:

SELECT ...
FROM ...
WHERE ...
ORDER BY Choose([Forms]![frmGanttChart].opgSortBy] , [SeqOrder],
[StartOrder], [FinishOrder])

This works in a query, not sure how it will work for your Gantt Chart

Dale
 
N

NevilleT

Thanks Dale. That put me on the right track. I have one remaining problem.
The Val function does not seem to work. I used format with the dates and
that is fine but it ignores the Val function and sorts alphabetically (1, 10,
11, 2, etc). The sequence number is MSPID which is a long integer anyway.

ORDER BY
Choose([Forms]![frmGanttChart].[opgSortBy],Val([MSPID]),Format([StartDate],"dd/mm/yyyy"),Format([FinishDate],"dd/mm/yyyy"));

Dale Fye said:
How about this.

Try modifying the query that is the source for your chart, to something
like:

SELECT ...
FROM ...
WHERE ...
ORDER BY Choose([Forms]![frmGanttChart].opgSortBy] , [SeqOrder],
[StartOrder], [FinishOrder])

This works in a query, not sure how it will work for your Gantt Chart

Dale

NevilleT said:
Hi Dale
The report events are Open, Close, Activate, Deactivate, No Data, Page,
Error. There is no Load event as there is for a form. The detail events
are
Format, Print and Retreat.
Click to expand...
Click to expand...
 
D

Dale Fye

Neville,

That is because the Format function returns a string. Since your OrderBy
clause will have nothing to do with the value of the fields returned in the
query, just use [StartDate] and [FinishDate] for those two. What is in your
[MSPID] field. If it is numeric (has no special characters, spaces, alpha
characters), then VAL([MSPID]) should work. If there is a chance that it
might contain NULLs, then try Val(NZ([MSPID], "99999")

HTH
Dale

NevilleT said:
Thanks Dale. That put me on the right track. I have one remaining
problem.
The Val function does not seem to work. I used format with the dates and
that is fine but it ignores the Val function and sorts alphabetically (1,
10,
11, 2, etc). The sequence number is MSPID which is a long integer anyway.

ORDER BY
Choose([Forms]![frmGanttChart].[opgSortBy],Val([MSPID]),Format([StartDate],"dd/mm/yyyy"),Format([FinishDate],"dd/mm/yyyy"));

Dale Fye said:
How about this.

Try modifying the query that is the source for your chart, to something
like:

SELECT ...
FROM ...
WHERE ...
ORDER BY Choose([Forms]![frmGanttChart].opgSortBy] , [SeqOrder],
[StartOrder], [FinishOrder])

This works in a query, not sure how it will work for your Gantt Chart

Dale

NevilleT said:
Hi Dale
The report events are Open, Close, Activate, Deactivate, No Data, Page,
Error. There is no Load event as there is for a form. The detail
events
are
Format, Print and Retreat.


:

I'm not sure I understand your response.

Does this mean you tried the Report_Load and Detail_Format events?

I didn't say anthing about the Report_Activiate or Report_Page events.

--

Dale

email address is invalid
Please reply to newsgroup only.



:

Hi Dale

Same error using on activate.
If I use the page event I get a message "You can't set the row
source
property in print preview or after printing has started."

Same error if I use the on format

:

have you tried using the reports Load event?

Another place to try this would be in the reports Detail sections
Format
event (assuming that chart object is in the detail section).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

This should be very simple but a few hours later ... well you
know
how it is.

I have a form which has a Gantt chart. I want to produce a
printed
version
so created a report. The Gantt chart can be sorted by sequence
number, start
or finish date. You select the sort order using an option group
on
the form.

What I want to do is to change the report rowsource when the
report
opens to
use one of three query sorted in one of three ways - sequence,
start or end.
I can do it manually and it works. I enter a different query as
the
rowsource of the unbound object and it displays just as it
should.
When I
try to do it using code it fails telling me
"You entered an expression that has an invalid reference to the
property
rowsource."

The code is:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms!frmGanttChart.opgSortBy
Case Is = 1
Me.oleGraph.RowSource = "qryGanttGraphSeqOrder"
Case Is = 2
Me.oleGraph.RowSource = "qryGanttGraphStartOrder"
Case Is = 3
Me.oleGraph.RowSource = "qryGanttGraphFinishOrder"
End Select
End Sub
Click to expand...
Click to expand...
Click to expand...
 
N

NevilleT

Hi Dale

Finally sorted it out. Posted another message and Allen Browne put me on
the right track. Val on the MSPID field did not work for some reason. I put
it around the whole thing. Also changed the date format to yyyymmdd. Thanks
very much for getting me onto the right path. It has saved me hours.

Expr1: Val(Choose(Round([Forms]![frmGanttChart].[opgSortBy],0),
Val([MSPID]),
Format([StartDate],"yyyymmdd"),
Format([FinishDate],"yyyymmdd")))

Dale Fye said:
Neville,

That is because the Format function returns a string. Since your OrderBy
clause will have nothing to do with the value of the fields returned in the
query, just use [StartDate] and [FinishDate] for those two. What is in your
[MSPID] field. If it is numeric (has no special characters, spaces, alpha
characters), then VAL([MSPID]) should work. If there is a chance that it
might contain NULLs, then try Val(NZ([MSPID], "99999")

HTH
Dale

NevilleT said:
Thanks Dale. That put me on the right track. I have one remaining
problem.
The Val function does not seem to work. I used format with the dates and
that is fine but it ignores the Val function and sorts alphabetically (1,
10,
11, 2, etc). The sequence number is MSPID which is a long integer anyway.

ORDER BY
Choose([Forms]![frmGanttChart].[opgSortBy],Val([MSPID]),Format([StartDate],"dd/mm/yyyy"),Format([FinishDate],"dd/mm/yyyy"));

Dale Fye said:
How about this.

Try modifying the query that is the source for your chart, to something
like:

SELECT ...
FROM ...
WHERE ...
ORDER BY Choose([Forms]![frmGanttChart].opgSortBy] , [SeqOrder],
[StartOrder], [FinishOrder])

This works in a query, not sure how it will work for your Gantt Chart

Dale

Hi Dale
The report events are Open, Close, Activate, Deactivate, No Data, Page,
Error. There is no Load event as there is for a form. The detail
events
are
Format, Print and Retreat.


:

I'm not sure I understand your response.

Does this mean you tried the Report_Load and Detail_Format events?

I didn't say anthing about the Report_Activiate or Report_Page events.

--

Dale

email address is invalid
Please reply to newsgroup only.



:

Hi Dale

Same error using on activate.
If I use the page event I get a message "You can't set the row
source
property in print preview or after printing has started."

Same error if I use the on format

:

have you tried using the reports Load event?

Another place to try this would be in the reports Detail sections
Format
event (assuming that chart object is in the detail section).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

This should be very simple but a few hours later ... well you
know
how it is.

I have a form which has a Gantt chart. I want to produce a
printed
version
so created a report. The Gantt chart can be sorted by sequence
number, start
or finish date. You select the sort order using an option group
on
the form.

What I want to do is to change the report rowsource when the
report
opens to
use one of three query sorted in one of three ways - sequence,
start or end.
I can do it manually and it works. I enter a different query as
the
rowsource of the unbound object and it displays just as it
should.
When I
try to do it using code it fails telling me
"You entered an expression that has an invalid reference to the
property
rowsource."

The code is:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms!frmGanttChart.opgSortBy
Case Is = 1
Me.oleGraph.RowSource = "qryGanttGraphSeqOrder"
Case Is = 2
Me.oleGraph.RowSource = "qryGanttGraphStartOrder"
Case Is = 3
Me.oleGraph.RowSource = "qryGanttGraphFinishOrder"
End Select
End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

Neville,

Since dates are stored as double precision numbers, you probably could have
also used:

Choose([Forms]![frmGanttChart].[opgSortBy], [MSPID], INT([StartDate]),
INT([FinishDate]))

Since all three options for the Choose functions would return numeric
values, this probably would have worked.

Dale

NevilleT said:
Hi Dale

Finally sorted it out. Posted another message and Allen Browne put me on
the right track. Val on the MSPID field did not work for some reason. I
put
it around the whole thing. Also changed the date format to yyyymmdd.
Thanks
very much for getting me onto the right path. It has saved me hours.

Expr1: Val(Choose(Round([Forms]![frmGanttChart].[opgSortBy],0),
Val([MSPID]),
Format([StartDate],"yyyymmdd"),
Format([FinishDate],"yyyymmdd")))

Dale Fye said:
Neville,

That is because the Format function returns a string. Since your OrderBy
clause will have nothing to do with the value of the fields returned in
the
query, just use [StartDate] and [FinishDate] for those two. What is in
your
[MSPID] field. If it is numeric (has no special characters, spaces,
alpha
characters), then VAL([MSPID]) should work. If there is a chance that it
might contain NULLs, then try Val(NZ([MSPID], "99999")

HTH
Dale

NevilleT said:
Thanks Dale. That put me on the right track. I have one remaining
problem.
The Val function does not seem to work. I used format with the dates
and
that is fine but it ignores the Val function and sorts alphabetically
(1,
10,
11, 2, etc). The sequence number is MSPID which is a long integer
anyway.

ORDER BY
Choose([Forms]![frmGanttChart].[opgSortBy],Val([MSPID]),Format([StartDate],"dd/mm/yyyy"),Format([FinishDate],"dd/mm/yyyy"));

:

How about this.

Try modifying the query that is the source for your chart, to
something
like:

SELECT ...
FROM ...
WHERE ...
ORDER BY Choose([Forms]![frmGanttChart].opgSortBy] , [SeqOrder],
[StartOrder], [FinishOrder])

This works in a query, not sure how it will work for your Gantt Chart

Dale

Hi Dale
The report events are Open, Close, Activate, Deactivate, No Data,
Page,
Error. There is no Load event as there is for a form. The detail
events
are
Format, Print and Retreat.


:

I'm not sure I understand your response.

Does this mean you tried the Report_Load and Detail_Format events?

I didn't say anthing about the Report_Activiate or Report_Page
events.

--

Dale

email address is invalid
Please reply to newsgroup only.



:

Hi Dale

Same error using on activate.
If I use the page event I get a message "You can't set the row
source
property in print preview or after printing has started."

Same error if I use the on format

:

have you tried using the reports Load event?

Another place to try this would be in the reports Detail
sections
Format
event (assuming that chart object is in the detail section).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

This should be very simple but a few hours later ... well you
know
how it is.

I have a form which has a Gantt chart. I want to produce a
printed
version
so created a report. The Gantt chart can be sorted by
sequence
number, start
or finish date. You select the sort order using an option
group
on
the form.

What I want to do is to change the report rowsource when the
report
opens to
use one of three query sorted in one of three ways -
sequence,
start or end.
I can do it manually and it works. I enter a different query
as
the
rowsource of the unbound object and it displays just as it
should.
When I
try to do it using code it fails telling me
"You entered an expression that has an invalid reference to
the
property
rowsource."

The code is:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms!frmGanttChart.opgSortBy
Case Is = 1
Me.oleGraph.RowSource = "qryGanttGraphSeqOrder"
Case Is = 2
Me.oleGraph.RowSource = "qryGanttGraphStartOrder"
Case Is = 3
Me.oleGraph.RowSource =
"qryGanttGraphFinishOrder"
End Select
End Sub
Click to expand...
Click to expand...
Click to expand...
 
N

NevilleT

The problem I had Dale was that even using Val(MSPID) it returned text. I
also tried Round, cInt and it was not until I put a Val around the whole
thing it worked. Allen Browne mentioned that if I looked at the datasheet
view of the query, if it was left aligned it was text, and right aligned it
was numeric. The only way it was right aligned was to use the Val around
everything. I had found another web site that suggested just what you did
but it would not work for some reason.

Very strange.

Dale Fye said:
Neville,

Since dates are stored as double precision numbers, you probably could have
also used:

Choose([Forms]![frmGanttChart].[opgSortBy], [MSPID], INT([StartDate]),
INT([FinishDate]))

Since all three options for the Choose functions would return numeric
values, this probably would have worked.

Dale

NevilleT said:
Hi Dale

Finally sorted it out. Posted another message and Allen Browne put me on
the right track. Val on the MSPID field did not work for some reason. I
put
it around the whole thing. Also changed the date format to yyyymmdd.
Thanks
very much for getting me onto the right path. It has saved me hours.

Expr1: Val(Choose(Round([Forms]![frmGanttChart].[opgSortBy],0),
Val([MSPID]),
Format([StartDate],"yyyymmdd"),
Format([FinishDate],"yyyymmdd")))

Dale Fye said:
Neville,

That is because the Format function returns a string. Since your OrderBy
clause will have nothing to do with the value of the fields returned in
the
query, just use [StartDate] and [FinishDate] for those two. What is in
your
[MSPID] field. If it is numeric (has no special characters, spaces,
alpha
characters), then VAL([MSPID]) should work. If there is a chance that it
might contain NULLs, then try Val(NZ([MSPID], "99999")

HTH
Dale

Thanks Dale. That put me on the right track. I have one remaining
problem.
The Val function does not seem to work. I used format with the dates
and
that is fine but it ignores the Val function and sorts alphabetically
(1,
10,
11, 2, etc). The sequence number is MSPID which is a long integer
anyway.

ORDER BY
Choose([Forms]![frmGanttChart].[opgSortBy],Val([MSPID]),Format([StartDate],"dd/mm/yyyy"),Format([FinishDate],"dd/mm/yyyy"));

:

How about this.

Try modifying the query that is the source for your chart, to
something
like:

SELECT ...
FROM ...
WHERE ...
ORDER BY Choose([Forms]![frmGanttChart].opgSortBy] , [SeqOrder],
[StartOrder], [FinishOrder])

This works in a query, not sure how it will work for your Gantt Chart

Dale

Hi Dale
The report events are Open, Close, Activate, Deactivate, No Data,
Page,
Error. There is no Load event as there is for a form. The detail
events
are
Format, Print and Retreat.


:

I'm not sure I understand your response.

Does this mean you tried the Report_Load and Detail_Format events?

I didn't say anthing about the Report_Activiate or Report_Page
events.

--

Dale

email address is invalid
Please reply to newsgroup only.



:

Hi Dale

Same error using on activate.
If I use the page event I get a message "You can't set the row
source
property in print preview or after printing has started."

Same error if I use the on format

:

have you tried using the reports Load event?

Another place to try this would be in the reports Detail
sections
Format
event (assuming that chart object is in the detail section).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

This should be very simple but a few hours later ... well you
know
how it is.

I have a form which has a Gantt chart. I want to produce a
printed
version
so created a report. The Gantt chart can be sorted by
sequence
number, start
or finish date. You select the sort order using an option
group
on
the form.

What I want to do is to change the report rowsource when the
report
opens to
use one of three query sorted in one of three ways -
sequence,
start or end.
I can do it manually and it works. I enter a different query
as
the
rowsource of the unbound object and it displays just as it
should.
When I
try to do it using code it fails telling me
"You entered an expression that has an invalid reference to
the
property
rowsource."

The code is:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms!frmGanttChart.opgSortBy
Case Is = 1
Me.oleGraph.RowSource = "qryGanttGraphSeqOrder"
Case Is = 2
Me.oleGraph.RowSource = "qryGanttGraphStartOrder"
Case Is = 3
Me.oleGraph.RowSource =
"qryGanttGraphFinishOrder"
End Select
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Drop down list control value in vba 2
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Keep First Letters, with exceptions 0
Printing a specific date & hour in project 0
Improving Macro That Keeps First Letter 0
Creating a Search using a combo box and then creating a report. 4
Gantt chart duration not showing 0
Print an object from a form 5

Top