Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-17-2018, 12:55 PM   #1
NebraskaUser
Newly Registered User
 
Join Date: Aug 2018
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
NebraskaUser is on a distinguished road
Merging 3 tables into a grand totals table

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

NebraskaUser is offline   Reply With Quote
Old 08-17-2018, 01:08 PM   #2
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 590
Thanks: 0
Thanked 136 Times in 136 Posts
June7 will become famous soon enough
Re: Merging 3 tables into a grand totals table

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.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 08-17-2018 at 01:40 PM.
June7 is offline   Reply With Quote
Old 08-17-2018, 01:23 PM   #3
Insane_ai
Newly Registered User
 
Join Date: Mar 2009
Location: Cleveland, OH USA
Posts: 213
Thanks: 9
Thanked 21 Times in 18 Posts
Insane_ai is on a distinguished road
Re: Merging 3 tables into a grand totals table

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.

__________________
I actually know a few things, just not all of them.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Insane_ai is offline   Reply With Quote
Old 08-17-2018, 07:46 PM   #4
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,321
Thanks: 2
Thanked 1,959 Times in 1,915 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Merging 3 tables into a grand totals table

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.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 08-17-2018, 08:56 PM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,178
Thanks: 58
Thanked 1,127 Times in 1,029 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Merging 3 tables into a grand totals table

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?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-17-2018, 10:52 PM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,583
Thanks: 55
Thanked 2,093 Times in 2,005 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Merging 3 tables into a grand totals table

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
__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 08-17-2018 at 11:03 PM.
arnelgp is offline   Reply With Quote
Old 08-22-2018, 10:17 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,334
Thanks: 13
Thanked 1,403 Times in 1,337 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Merging 3 tables into a grand totals table

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?

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-23-2018, 10:07 AM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Merging 3 tables into a grand totals table

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.
Mark_ is offline   Reply With Quote
Old 08-23-2018, 10:50 AM   #9
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,285 Times in 1,224 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
OP wants to hang himself. ArnelGP wants to help him improve the knot.

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Grand totals on a single view JaneJohnstone General 2 02-21-2014 08:48 AM
Totals Query/sums/grand totals thsoundman Queries 17 04-16-2013 11:24 AM
Grand Totals ewong1 Reports 2 03-16-2005 11:37 PM
Subreport and grand totals thart21 Reports 0 03-24-2004 09:29 AM
Grand Totals at Top Mmattson Reports 4 01-27-2003 01:58 PM




All times are GMT -8. The time now is 03:06 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World