Problem with Code Because I already have an Open Query

A

Ange Kappas

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
 
J

J_Goddard via AccessMonster.com

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 said:
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
Click to expand...
 
A

Ange Kappas

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

J_Goddard via AccessMonster.com

Hi -

I am not sure what you mean by "query ACCOUNTS...is open due to filter from a
form"; queries are not used as filters - they are used as a recordsource for
forms or reports.

What exactly is the text of the error message you are getting, and how are
you using "Accounts" ?

John


Ange said:
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
Hi -
Click to expand...
[quoted text clipped - 60 lines]
Click to expand...

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

Message posted via AccessMonster.com
 
J

Jeanette Cunningham

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
A

Ange Kappas

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

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 said:
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 said:
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 said:
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



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
Click to expand...
Click to expand...
Click to expand...
 
A

Ange Kappas

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 said:
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 said:
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 said:
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


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



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
Click to expand...
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Ange,
Make a copy of the Accounts query to work with call it qryAccountsCopy.
Change qryAccountsCopy by removing the totals completely so that it is a
simple select query, make sure it still has the criteria
=[Forms]![ACCOUNTS SEARCH]![PICK COMPANY])
=[Forms]![ACCOUNTS SEARCH]![PICK ARRIVAL])
=[Forms]![ACCOUNTS SEARCH]![PICK DEPARTURE])
in the appropriate fields.

Open the main form and make your selections from the 3 pick combos, open
qryAccountsCopy and check that it can find the values for company, arrival
and departure from the form.

If the qryAccountsCopy does show the records for the values of company,
arrival and departure shown on the form,
--Switch qryAccountsCopy to design view.
--Change it to a totals query (but not an append query).
--Switch it to datasheet view and check if it shows the records for the
company, arrival and departure shown on the form,

Jeanette Cunningham

Ange Kappas said:
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 said:
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 said:
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





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


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



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
Click to expand...
Click to expand...
Click to expand...
 
A

Ange Kappas

Thanks Jeannette, results at last !!!
Ange


Jeanette Cunningham said:
Ange,
Make a copy of the Accounts query to work with call it qryAccountsCopy.
Change qryAccountsCopy by removing the totals completely so that it is a
simple select query, make sure it still has the criteria
=[Forms]![ACCOUNTS SEARCH]![PICK COMPANY])
=[Forms]![ACCOUNTS SEARCH]![PICK ARRIVAL])
=[Forms]![ACCOUNTS SEARCH]![PICK DEPARTURE])
in the appropriate fields.

Open the main form and make your selections from the 3 pick combos, open
qryAccountsCopy and check that it can find the values for company, arrival
and departure from the form.

If the qryAccountsCopy does show the records for the values of company,
arrival and departure shown on the form,
--Switch qryAccountsCopy to design view.
--Change it to a totals query (but not an append query).
--Switch it to datasheet view and check if it shows the records for the
company, arrival and departure shown on the form,

Jeanette Cunningham

Ange Kappas said:
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 said:
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

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





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


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



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
Click to expand...
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

You're very welcome.

Jeanette Cunningham

Ange Kappas said:
Thanks Jeannette, results at last !!!
Ange


Jeanette Cunningham said:
Ange,
Make a copy of the Accounts query to work with call it qryAccountsCopy.
Change qryAccountsCopy by removing the totals completely so that it is a
simple select query, make sure it still has the criteria
=[Forms]![ACCOUNTS SEARCH]![PICK COMPANY])
=[Forms]![ACCOUNTS SEARCH]![PICK ARRIVAL])
=[Forms]![ACCOUNTS SEARCH]![PICK DEPARTURE])
in the appropriate fields.

Open the main form and make your selections from the 3 pick combos, open
qryAccountsCopy and check that it can find the values for company,
arrival and departure from the form.

If the qryAccountsCopy does show the records for the values of company,
arrival and departure shown on the form,
--Switch qryAccountsCopy to design view.
--Change it to a totals query (but not an append query).
--Switch it to datasheet view and check if it shows the records for the
company, arrival and departure shown on the form,

Jeanette Cunningham

Ange Kappas said:
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






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

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





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


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



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
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

Copying All Records from a Query to a Table 2
Copying Specific Records From A Field According to another Field By Code 2
Code Stops Due to No Values In Query 5
Checking a Status Control from a Form Before Proceeding2 1
Date Difference In Query 2
Problem looping with Dir 3
Living With a Computer 3

Top