VBA SQL Statement

[jsdhanoa@gmail.com]

I am a newbie in Access and I have created a unbound report and am
trying to get data from 2 different queries. Previously, I had used the
ADODB recordset method to get data from tables and I have applied the
same logic to this case but using a query. But, when I try to run the
report I get the following error message: "No value given for one or
more required parameters." My code is as follows:

Dim rsPoles As New ADODB.Recordset
Dim SQLStmt As String

SQLStmt = "SELECT Customer, Poles, Rate from qryPoleTotal"
rsPoles.Open SQLStmt, CurrentProject. Connection, adOpenDynamic,
adLockOptimisti c
rsPoles![Customer] = Me.Customer
rsPoles![Poles] = Me.SumOfPoles_
rsPoles![Rate] = Me.Rate
rsPoles.Update
rsPoles.Close
Set rsPoles = Nothing

(The coding is for only one query because I wanted to test it with one
before I added the second.)

I didn't use a WHERE clause because the query already has that logic
and I didn't use a union query because the two queries have different
fields and different data.

Can someone please help me with this problem.

Thank you in advance.

[Svetlana]

Re: VBA SQL Statement

Why don't you create the query you wish and bound the record source of
your report to it?

[Albert D. Kallal]

Re: VBA SQL Statement

in your example code you open up a reocrdset, but what particular record are
you trying to update (your code example
does not define any particular record you are updating with those form
values. As the code stands, you would ALWAYS be updating the first record in
the qryPoleTotal. Further, is qryPoleTotal a updatable query?

Further, you can't really have a un-bound report, ad do anything useful.
Further, you made no case as to why your report needs to be un-bound.

Perhaps you are trying to append data to a temporary table that qryPoleTotal
is based on? And then base the report on that table? This would NOT be what
we call a un-bound report, but simply a report bound to a table that we plan
to send data to. This is possible, but a un-bound report is not useable..

I didn't use a WHERE clause because the query already has that logic

That does not matter. If you send the report a where clause...it will simply
operate on the report, regardless if that query the report is based on
already has some conditions. So, once again, the above assumption don't
support not using a where clause.

Your code example (if we get it to work) simply updates the first record in
the table that qryPoleTotal is based on.

What good comes from simply modifying the first record of qryPoleTotal?
Further, is there any existing records that the qeryPoletotal returns?

Is qryPoleTotal based on a empty table? Are you trying to update just one
record here...or many?

If you need the report to display data from more then one table, you have
several choices

If the data is columnar in nature, and you have many records from the
two tables, but can match up the fields, then a union query is a good
choice. another choice would be to use the temp table, and use two append
queries to merge the data into a single table. Both of these approaches can
deal with different field names, and map them to a common set of fields for
the report (however, this approach does increase the amount of maintains and
as a good rule, you should avoid temp tables).

If the two tables are really different data and require somewhat
different report format, then in a sense you want to display two different
reports on one report. If this is your case, then simply place/use two
sub-reports on a main report. This works quite well.

Do feel free to expand a bit here, as I may have miss-understood you
goal/problem here....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[jsdhanoa@gmail.com]

Re: VBA SQL Statement

I didn't realize that the method I was using was for only updating
records. I tried out the subreport method an it worked. Thank You.


Albert D. Kallal wrote:

in your example code you open up a reocrdset, but what particular record are
you trying to update (your code example
does not define any particular record you are updating with those form
values. As the code stands, you would ALWAYS be updating the first record in
the qryPoleTotal. Further, is qryPoleTotal a updatable query?
>
Further, you can't really have a un-bound report, ad do anything useful.
Further, you made no case as to why your report needs to be un-bound.
>
Perhaps you are trying to append data to a temporary table that qryPoleTotal
is based on? And then base the report on that table? This would NOT be what
we call a un-bound report, but simply a report bound to a table that we plan
to send data to. This is possible, but a un-bound report is not useable..
>

I didn't use a WHERE clause because the query already has that logic

>
That does not matter. If you send the report a where clause...it will simply
operate on the report, regardless if that query the report is based on
already has some conditions. So, once again, the above assumption don't
support not using a where clause.
>
Your code example (if we get it to work) simply updates the first record in
the table that qryPoleTotal is based on.
>
What good comes from simply modifying the first record of qryPoleTotal?
Further, is there any existing records that the qeryPoletotal returns?
>
Is qryPoleTotal based on a empty table? Are you trying to update just one
record here...or many?
>
If you need the report to display data from more then one table, you have
several choices
>
If the data is columnar in nature, and you have many records from the
two tables, but can match up the fields, then a union query is a good
choice. another choice would be to use the temp table, and use two append
queries to merge the data into a single table. Both of these approaches can
deal with different field names, and map them to a common set of fields for
the report (however, this approach does increase the amount of maintains and
as a good rule, you should avoid temp tables).
>
If the two tables are really different data and require somewhat
different report format, then in a sense you want to display two different
reports on one report. If this is your case, then simply place/use two
sub-reports on a main report. This works quite well.
>
Do feel free to expand a bit here, as I may have miss-understood you
goal/problem here....
>
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com