Copying All Records from a Query to a Table

[Ange Kappas]

Hi All,
Here is another one!
I have a code which copies one record from a query(TODAY CHARGES) to another
table(RESPEL TEST ALL CHARGES).
I want the code to copy all the records in the current query to the table.

Here is the code I have:

Private Sub Toggle1_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

DoCmd.
Set db1 = CurrentDb()
Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL TEST ALL CHARGE")
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![HOTELAPT] = Myset![HOTELAPT]
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![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = Myset![DAILY CHARGE]
Myset2.Update
Myset2.Close
End Sub

What do I have to add to the above to copy all the records.
Help Much Appreciated
Ange

 

[Douglas J. Steele]

Hopefully you have a legitimate reason for storing data redundantly (it's
seldom a good idea). If you do, use an INSERT INTO query rather than VBA.
It's almost always significantly better to use SQL than VBA.

Private Sub Toggle1_Click()
Dim strSQL As String

strSQL = "INSERT INTO [RESPEL TEST ALL CHARGE] " & _
"([Date], PELNMB, RESNAME, COMPANY, HOTELAPT, " & _
"ROOMNO, ROOMTYPE, BASIS, ARRIVAL, DAYS, " & _
"DEPARTURE, SURNAME, [NAME], [DAILY CHARGE]) " & _
"SELECT [Date], PELNMB, RESNAME, COMPANY, HOTELAPT, " & _
"ROOMNO, ROOMTYPE, BASIS, ARRIVAL, DAYS, " & _
"DEPARTURE, SURNAME, [NAME], [DAILY CHARGE] " & _
"FROM [TODAY CHARGES]"
CurrentDb.Execute strSQL, dbFailOnError

End Sub

A few comments on what you presented.

Dim Msg, Style, Title, Response, MyString

means that all five variables are declared as Variants. While there's a
distinct place for the Variant data type, I doubt this is one of them. I'm
assuming that should be

Dim Msg As String, Style As Long, Title As String, Response As String,
MyString As String

(Note that you cannot "short circuit" declarations: Dim Msg, Title As String
only declares Title as a string)

More important, though, is that you've got a couple of bad choices for field
names. Both Date and Name are reserved words, so should not be used as field
names. If you cannot (or will not) rename those fields, at least put square
brackets around them as I did in my sample code. For a good discussion on
names to avoid in Access, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

Finally, unless you did an incomplete job of copy-and-paste of your code,
you would appear not to have set VBA up to require declaration of all
variables (your sample uses db1, Myset and Myset2 without having declared
them). You'd be well advised to require variable declaration: it can save
you untold hours of debugging when you make a slight typo in a variable
name. Make sure Option Explicit appears at the top of each existing module.
To have VBA automatically include that on all future modules, go into Tools
| Options while in the VB Editor, look on the Editor tab. Make sure Require
Variable Declaration is checked.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi All,
Here is another one!
I have a code which copies one record from a query(TODAY CHARGES) to
another table(RESPEL TEST ALL CHARGES).
I want the code to copy all the records in the current query to the table.

Here is the code I have:

Private Sub Toggle1_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

DoCmd.
Set db1 = CurrentDb()
Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL TEST ALL CHARGE")
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![HOTELAPT] = Myset![HOTELAPT]
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![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = Myset![DAILY CHARGE]
Myset2.Update
Myset2.Close
End Sub

What do I have to add to the above to copy all the records.
Help Much Appreciated
Ange

 

[Ange Kappas]

Thanks for your advice, really has helped

Ange

Hopefully you have a legitimate reason for storing data redundantly (it's
seldom a good idea). If you do, use an INSERT INTO query rather than VBA.
It's almost always significantly better to use SQL than VBA.

Private Sub Toggle1_Click()
Dim strSQL As String

strSQL = "INSERT INTO [RESPEL TEST ALL CHARGE] " & _
"([Date], PELNMB, RESNAME, COMPANY, HOTELAPT, " & _
"ROOMNO, ROOMTYPE, BASIS, ARRIVAL, DAYS, " & _
"DEPARTURE, SURNAME, [NAME], [DAILY CHARGE]) " & _
"SELECT [Date], PELNMB, RESNAME, COMPANY, HOTELAPT, " & _
"ROOMNO, ROOMTYPE, BASIS, ARRIVAL, DAYS, " & _
"DEPARTURE, SURNAME, [NAME], [DAILY CHARGE] " & _
"FROM [TODAY CHARGES]"
CurrentDb.Execute strSQL, dbFailOnError

End Sub

A few comments on what you presented.

Dim Msg, Style, Title, Response, MyString

means that all five variables are declared as Variants. While there's a
distinct place for the Variant data type, I doubt this is one of them. I'm
assuming that should be

Dim Msg As String, Style As Long, Title As String, Response As String,
MyString As String

(Note that you cannot "short circuit" declarations: Dim Msg, Title As
String only declares Title as a string)

More important, though, is that you've got a couple of bad choices for
field names. Both Date and Name are reserved words, so should not be used
as field names. If you cannot (or will not) rename those fields, at least
put square brackets around them as I did in my sample code. For a good
discussion on names to avoid in Access, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

Finally, unless you did an incomplete job of copy-and-paste of your code,
you would appear not to have set VBA up to require declaration of all
variables (your sample uses db1, Myset and Myset2 without having declared
them). You'd be well advised to require variable declaration: it can save
you untold hours of debugging when you make a slight typo in a variable
name. Make sure Option Explicit appears at the top of each existing
module. To have VBA automatically include that on all future modules, go
into Tools | Options while in the VB Editor, look on the Editor tab. Make
sure Require Variable Declaration is checked.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi All,
Here is another one!
I have a code which copies one record from a query(TODAY CHARGES) to
another table(RESPEL TEST ALL CHARGES).
I want the code to copy all the records in the current query to the
table.

Here is the code I have:

Private Sub Toggle1_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

DoCmd.
Set db1 = CurrentDb()
Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL TEST ALL CHARGE")
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![HOTELAPT] = Myset![HOTELAPT]
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![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = Myset![DAILY CHARGE]
Myset2.Update
Myset2.Close
End Sub

What do I have to add to the above to copy all the records.
Help Much Appreciated
Ange