Re: Problem with Code Because I already have an Open Query

Hi Jeanette,
The SQL of the ACCOUNTS Query is:

SELECT [RESPEL ALL CHARGES].DATE, [RESPEL ALL CHARGES].ROOMNO, [RESPEL ALL
CHARGES].ROOMTYPE, RESERVATIONS.PERSONS, [RESPEL ALL CHARGES].BASIS, [RESPEL
ALL CHARGES].RESNO, [RESPEL ALL CHARGES].RESNAME, [RESPEL ALL
CHARGES].COMPANY, [RESPEL ALL CHARGES].ARRIVAL, ([RESPEL ALL
CHARGES].DEPARTURE-[RESPEL ALL CHARGES].ARRIVAL) AS DAYS, [RESPEL ALL
CHARGES].DEPARTURE, Sum([RESPEL ALL CHARGES].[DAILY CHARGE]) AS [DAILY
CHARGE]
FROM [RESPEL ALL CHARGES] INNER JOIN RESERVATIONS ON [RESPEL ALL
CHARGES].RESNO = RESERVATIONS.RESNO
GROUP BY [RESPEL ALL CHARGES].DATE, [RESPEL ALL CHARGES].ROOMNO, [RESPEL ALL
CHARGES].ROOMTYPE, RESERVATIONS.PERSONS, [RESPEL ALL CHARGES].BASIS, [RESPEL
ALL CHARGES].RESNO, [RESPEL ALL CHARGES].RESNAME, [RESPEL ALL
CHARGES].COMPANY, [RESPEL ALL CHARGES].ARRIVAL, ([RESPEL ALL
CHARGES].DEPARTURE-[RESPEL ALL CHARGES].ARRIVAL), [RESPEL ALL
CHARGES].DEPARTURE
HAVING ((([RESPEL ALL CHARGES].COMPANY)=[Forms]![ACCOUNTS SEARCH]![PICK
COMPANY]) AND (([RESPEL ALL CHARGES].ARRIVAL)=[Forms]![ACCOUNTS
SEARCH]![PICK ARRIVAL]) AND (([RESPEL ALL
CHARGES].DEPARTURE)=[Forms]![ACCOUNTS SEARCH]![PICK DEPARTURE]));

Regards
Ange






"Jeanette Cunningham" <nnn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:uA8YiRjaIHA.300@xxxxxxxxxxxxxxxxxxxxxxx
Ange,
The too few parameters error means the query is missing values for 3
fields.
My guess that the 3 values are the 'unbound linked Master fields'.
If I understand correctly, the records to be appended to AccountsCharged
are the same ones filtered by the main form.
Could you post the sql of the query that is giving the error.

Note about your field named Date - the word Date is what is called a
reserved word in Access - reserved for Access to use internally.
You need to change the name of this field to something like ChargeDate or
GuestDate to avoid errors when using the field for date.

Jeanette Cunningham

"Ange Kappas" <angekap@xxxxxx> wrote in message
news:fogss0$9kv$1@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,
Thanks for your comments and help, because of time difference I
just saw the messages.
Probably the problem is in the setup and I guess what I'm trying to do.
You see I have a QUERY which has records where every day a Guest is
charged with a daily rate.
This QUERY comprises of the basic fields:
DATE
NAME
ROOMNO
COMPANY
ARRIVAL
DEPARTURE
DAILYCHARGE

I use a QUERYcalled "ACCOUNTS" because it takes data from different
tables
and groups them accordingly.

In the above table the guest is charged a daily rate, as the program
moves
on to a next day where the field DATE is changed to the following day but
keeping the rest of the data in the other fields the same:
For Example:
DATE NAME ROOMNO COMPANY ARRIVAL DEPARTURE
DAILYCHARGE
3-5-07 Thomas 101 Hgh
3-5-07 10-5-07 15
3-5-07 Peters 102 PTV
3-5-07 6-5-07 20
4-5-07 Thomas 101 Hgh
3-5-07 10-5-07 15
4-5-07 Peters 102 PTV 3-5-07
6-5-07 20

In the QUERY "ACCOUNTS" under the fields COMPANY. ARRIVAL and DEPARTURE
fields in Design View I have as criteria Forms![ACCOUNT SEARCH]![PICK
COMPANY] and Forms![ACCOUNT SEARCH]![PICK ARRIVAL] and Forms![ACCOUNT
SEARCH]![PICK DEPARTURE]
which
Now from there I use a Form "ACCOUNTS SEARCH" with a SubForm to filter
out
the the Query "ACCOUNTS"according to the criteria I base my unbound Link
Master Fileds. These fields Link [PICK COMPANY] to [COMPANY] and [PICK
ARRIVAL] to [ARRIVAL] and [PICK DEPARTURE] to [DEPARTURE](The PICK
Controls are the MASTER). All this is fine because the subform returns
the
results I want and I have a button which uses the Filtered out Query to
generate a report.
The Problem is this filtered out Query I need to save in a TABLE called
ACCOUNTS CHARGED because I want to add an ACCOUNT NUMBER to all the
records which were filtered out. that is why I am using code attached to
a
button. Here because the query is already opened I have run time error
3061 saying too few parameters expected 3.

I hope I have made some sense out of it all, maybe I am going the wrong
way about it.

Thanks
Ange





"Jeanette Cunningham" <nnn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:u2faDdcaIHA.4180@xxxxxxxxxxxxxxxxxxxxxxx
Ange,
why go to the trouble of opening a recordset when you could just use an
append query?
Why even put the data into a new table? I don't know your setup, but a
suggestion is to have a Yes/No field in you Accounts table that records
when an account has been charged. That way when you need to know which
accounts have been charged, you just run a query that picks up the
charged accounts.

Jeanette Cunningham


"Ange Kappas" <angekap@xxxxxx> wrote in message
news:fofnhu$j82$1@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thanks John,
But I still get an error on the line:

Set MySet = CurrentDb.OpenRecordset("ACCOUNTS")

because the query ACCOUNTS is open due to filter from a form.

Ange



"J_Goddard via AccessMonster.com" <u37558@uwe> wrote in message
news:7f630c5cacbf6@xxxxxx
Hi -

By 'stuck', do you mean runtime or compile error?


You need

Dim MySet as DAO.recordset, myset2 as DAO.recordset

and then
'
' Queries are opened into recordsets just as tables are
'
Set Myset = CurrentDb.openrecordset("ACCOUNTS")
Set Myset2 = db1.OpenRecordset("ACCOUNTS CHARGED")

I assume that db1 is another database that you have established a link
to?

The code looks fine otherwise.

John



Ange Kappas wrote:
Hi,
I have already open a Query named ACCOUNTS and I want to copy
the
already filtered results into a Table name ACCOUNTS CHARGED.
The code is stuck on the Set Myset=CurrentDb("ACCOUNTS")

What do I have to adjust to make it work?

Thanks

Private Sub ACCOUNTS_CHARGE_Click()

Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set Myset = CurrentDb("ACCOUNTS")
Set Myset2 = db1.OpenRecordset("ACCOUNTS CHARGED")

With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![DAILY CHARGE] = Myset![DAILYRATE]
Myset2.Update
.MoveNext
Loop
End With
Myset2.Close

End Sub

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via http://www.accessmonster.com












.

Loading