Merging 3 tables into a grand totals table (1 Viewer)

NebraskaUser · Aug 17, 2018

Hello everyone - I'm pretty new to VBA. I'm trying to create a table definition and then add values from 3 other tables to that definition. Here's my code and I keep getting a "Data Type Conversion" error with the line reading:
Set TOT_R = tdfNew.OpenRecordset("RSC_Totals", dbOpenDynaset)

Sub CreateTable()
Dim tdfNew As TableDef
Dim RSC_R As Recordset
Dim LSP_R As Recordset
Dim CHC_R As Recordset

Set tdfNew = CurrentDb.CreateTableDef("RSC_Totals")
With tdfNew
.Fields.Append .CreateField("RSC_Total_Date", dbDate)
.Fields.Append .CreateField("RSC_Total_Amount", dbDouble)
.Fields.Append .CreateField("RSC_Total_Type", dbText)
End With

Set RSC_R = CurrentDb.OpenRecordset("RSC_Envelopes_T", dbOpenDynaset)
RSC_R.FindFirst "[RSC_Env_Date] = #08/12/2018#"

Set LSP_R = CurrentDb.OpenRecordset("RSC_LoosePlate_T", dbOpenDynaset)
LSP_R.FindFirst "[RSC_LSP_Date] = #08/12/2018#"

Set CHC_R = CurrentDb.OpenRecordset("RSC_Childrens_T", dbOpenDynaset)
CHC_R.FindFirst "[RSC_CHC_Date] = #08/12/2018#"

Dim TOT_R As Recordset
Set TOT_R = tdfNew.OpenRecordset("RSC_Totals", dbOpenDynaset)
With TOT_R
.AddNew
.Fields("RSC_Total_Date") = RSC_R.Fields("RSC_Env_Date")
.Fields("RSC_Total_Amount") = RSC_R.Fields("RSC_Env_Amount")
.Fields("RSC_Total_Type") = RSC_R.Fields("RSC_Env_Type")
.Update
End With

Set tdfNew = Nothing
Set RSC_R = Nothing
Set LSP_R = Nothing
Set CHC_R = Nothing
End Sub

June7 · Aug 17, 2018

Why don't you just build query that joins tables/queries? Or a report using Sorting & Grouping features with aggregate calcs?

Why create a table?
I advise against code that routinely modifies db structure.

Insane_ai · Aug 17, 2018

Creating the table and populating it with data are two different things.

A Make table query could make this so simple but if you insist on doing it the long way,

I would start by explaining what you thought you were doing in this line:

Code:

Set TOT_R = tdfNew.OpenRecordset("RSC_Totals", dbOpenDynaset)

tdfNew is the table definition object and can be used to manipulate table structure.
The OpenRecordset method grabs data for use and I usually see it in statements like
db.openrecordset([strSQL or name of existing query]) where db is the DAO or ADO Database object.

JHB · Aug 18, 2018

If you want to stick to your way of doing it, then change:

Code:

Set TOT_R = tdfNew.OpenRecordset("RSC_Totals", dbOpenDynaset)

To:

Code:

Set TOT_R = CurrentDb.OpenRecordset("RSC_Totals", dbOpenDynaset)

But as June7 pointed out, why not use a query, that is the normal way.

The_Doc_Man · Aug 18, 2018

Concur with June7 - not often a good idea to muck about with table structure via programs. It is too easy to screw it up by accidentally running the process twice.

You showed us some code all this does is assure that you have a couple of specific field names here. If this is a mental exercise, fine and dandy. But if this is "for real" then it should have been done by hand as a one-time operation. Much as I absolutely LOVE to write code, even I don't write that many one-offs.

So what is it about this problem that would make you want to code it?

arnelgp · Aug 18, 2018

here is an SQL that will do the equivalent.
also to be more generic, you passed a date to the subroutine:

Code:

 Call CreateTable(#8/12/22018#)

your subroutine:

Code:

Sub CreateTable(Byval thisDate As Date)
 dim sql1 as string
 dim sql2 as string
 dim sql3 as string
 ' Create table query. add RSC_Envlopes_T record
 sql1 = _
  "SELECT TOP 1 " & _
   "RSC_Env_Date AS RSC_Total_Date,  " & _
   "RSC_env_Amount As RSC_Total_Amount, " & _
   "RSC_Env_Type AS RSC_Total_Type " & _
  "INTO " & _
   "RSC_ToTals " & _
  "FROM " & _
   "RSC_Envlopes_T " & _
  "WHERE " & _
   "RSC_Env_Date = #" & Format(thisDate, "mm/dd/yyyy") & "#;"
 ' add RSC_LoosePlate_T record
 sql2 = _
  "INSERT INTO " & _
   "RSC_Totals (" & _
    "RSC_Total_Date, " & _
    "RSC_Total_Amount, " & _
    "RSC_Total_Type) " & _
   "SELECT TOP 1 " & _
    "RSC_LSP_Date, " & _
    "RSC_LSP_Amount, " & _
    "RSC_LSP_Type " & _
   "FROM " & _
    "RSC_LoosePlate_T " & _
   "WHERE " & _
    "RSC_LSP_Date = #" & Format(thisDate, "mm/dd/yyyy") & "#;"
  
 ' add RSC_Childrens_T record
 sql3 = _
  "INSERT INTO " & _
   "RSC_Totals (" & _
    "RSC_Total_Date, " & _
    "RSC_Total_Amount, " & _
    "RSC_Total_Type) " & _
   "SELECT TOP 1 " & _
    "RSC_CHC_Date, " & _
    "RSC_CHC_Amount, " & _
    "RSC_CHC_Type " & _
   "FROM " & _
    "RSC_Childrens_T " & _
   "WHERE " & _
    "RSC_CHC_Date = #" & Format(thisDate, "mm/dd/yyyy") & "#;"
 With CurrentDb
  .Execute sql1, dbFailOnError
  .Execute sql2, dbFailOnError
  .Execute sql3, dbFailOnError
 End With
End Sub

or using a Union Query

Code:

Sub CreateTable(Byval thisDate As Date)
 dim sql1 as string
 Dim sql2 As String
 Dim sql3 As String
 Dim sqlUnion As String
 ' Create table query. add RSC_Envlopes_T record
 sql1 = _
  "SELECT TOP 1 " & _
   "RSC_Env_Date AS RSC_Total_Date,  " & _
   "RSC_env_Amount As RSC_Total_Amount, " & _
   "RSC_Env_Type AS RSC_Total_Type " & _
  "FROM " & _
   "RSC_Envlopes_T " & _
  "WHERE " & _
   "RSC_Env_Date = #" & Format(thisDate, "mm/dd/yyyy") & "#;"
 sql2 = _
  "SELECT " & _
   "RSC_LSP_Date, " & _
   "RSC_LSP_Amount, " & _
   "RSC_LSP_Type " & _
  "FROM " & _
   "RSC_LoosePlate_T " & _
  "WHERE " & _
   "RSC_LSP_Date = #" & Format(thisDate, "mm/dd/yyyy") & "#;"
  
 sql3 = _
  "SELECT " & _
   "RSC_CHC_Date, " & _
   "RSC_CHC_Amount, " & _
   "RSC_CHC_Type " & _
  "FROM " & _
   "RSC_Childrens_T " & _
  "WHERE " & _
   "RSC_CHC_Date = #" & Format(thisDate, "mm/dd/yyyy") & "#;"
 sqlUnion = sql1 & " UNION " & sql2 & " UNION " & sql3
 sqlUnion = "SELECT RSC_Total_Date, RSC_Total_Amount, "RSC_Total_Type " & _
   "INTO RSC_Totals FROM (" & sqlUnion & ");"
 With CurrentDb.Execute sqlUnion, dbFailOnError
End Sub

Pat Hartman · Aug 23, 2018

If you are building this application for others, you would not use this method. The user should be given a database that has all objects already built so you can distribute the FE as an .accde and run it with the Runtime engine.

Why do you need to build the table with code? Why not make a permanent table or use a Make Table query?

Mark_ · Aug 23, 2018

Concurring with June and Pat.

What is the end use for this table?

IF you are getting ready to archive old data, you would normally have a clearly defined structure for your totals and a rather in depth plan to execute. This would normally NOT be something given to someone "Pretty new to VBA".

IF you are NOT doing an archive then there really wouldn't be a reason for something like this unless it is a regulatory requirement, once more not something assigned to someone "Pretty new to VBA".

As there are some valid reasons for doing this, but your post indicates this wouldn't be one of them, giving us the business rule you are working on will help us guide you to a solution that works and is supportable.

MarkK · Aug 23, 2018

OP wants to hang himself. ArnelGP wants to help him improve the knot.

Merging 3 tables into a grand totals table (1 Viewer)

NebraskaUser

Registered User.

June7

AWF VIP

Insane_ai

Not Really an A.I.

JHB

Have been here a while

The_Doc_Man

Immoderate Moderator

arnelgp

..forever waiting... waiting for jellybean!

Pat Hartman

Super Moderator

Mark_

Longboard on the internet

MarkK

bit cruncher

Similar threads

Users who are viewing this thread