CODEWITHSUNDEEP
vbams-accessreport
barry17
barry17

Reputation: 153

Access queries and reporting with VBA?

I have a few queries that I've created for a report. I'm trying to write some VB code so that I am able create a recordSet from the data in my query and populate tbl_TempTable with that data.

My table is:

tbl_TempTable 
CompanyID     CompanyName     UnitPrice
-------------------------------------------

my qryCompanyInfo has the same columns as the above table, but obviously each column is populated with 30 records or so.

This is my code so far:

dim rs as dao.recordset
dim db as dao.recordset
dim db = currentDB()
dim x as integer

Set rs = db.openrecordset("qryCompanyInfo")
z=rs.recordcount
msgbox z

What happens here is that ONLY 1 record gets selected. Why is it doing that, there should be about 30 records being selected? Do I need to execute the query first before selecting the data? How would I also go about populating my tbl_TempTable with the data that I extract from the qry?

Upvotes: 0

Views: 73

Answers (1)

Stidgeon
Stidgeon

Reputation: 2723

Depending on whether you want to insert from your query into a new table or an existing table, there's always 'SELECT INTO' or 'INSERT INTO', but going from your VBA code as a guide . . .

I'm not certain about some of the lines in your code - there's two variable (x and z), only one declared, so I think these are meant to be the same thing, and I think you want to call that many records from the recordset. There's also an odd declaration of 'db' two times.

Here's an approach that I've used for copying query results to a table, along with a few comments to explain what's what:

' declare two recordsets - one for the query, one for the target table
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

' declare a string which will house your SQL statement
Dim SQL As String

' specify your SQL statement here
SQL = "SELECT * FROM table WHERE conditions"

' set the two recordsets to the query and the target table
Set rs1 = CurrentDb.OpenRecordset(SQL)
Set rs2 = CurrentDb.OpenRecordset("tbl_TempTable")

' step through the query results, one record at a time
' for each entry, copy that info to the 2nd recordset
' stop at End Of File
With rs1
While Not .EOF
    rs2.AddNew
    rs2("CompanyID") = rs1("CompanyID")
    rs2("CompanyName") = rs1("CompanyName")
    rs2("UnitPrice") = rs1("UnitPrice")
    rs2.Update
    .MoveNext
Wend
End With

Upvotes: 2

Related Questions