Solved

NEED TO AUTO INCREMENT FILE NAMES WHILE SAVING TABLES IN A MACRO

Posted on 2004-08-24
4
263 Views
Last Modified: 2012-05-05
Hi.  I am trying to find a way to Auto Increment or use the Current date when saving or copying tables in an Access Macro.
I have a macro that does the following:

1.) COPY OBJECT:
    a.) Destination Database: _____blank________
    b.) New Name: Results Copy
    c.) Source Object Type: Table
    d.) Source Object Name: MEETING RESULTS

2.) DELETE OBJECT:
    a:) Object Type: Table
    b.) Object Name: MEETING RESULTS

3.) OpenQuery:
    a:) Query Name: LAPTOP DATA QUERY
    B.) View: Datasheet
    c.) Read Only


 *********This query is a Make Table query that creates a table named: Meeting Results.  I want this table saved with a unique name if possible, or renamed prior to the DELETE OBJECT step in the Macro. The Copy Object name "Results Copy" was created so I would not lose the previous results, but I want to retain ALL previous Results... I would love for the file name to be: Meeting Results XXYYZZ, where XX is the Month, YY is the Day, and ZZ is the two digit year.  If this isn't possible, it could simply be Meeting Results01, 02, 03, etc..

Of importance however, is that I run a report based on the "Meeting Results" table generated in the Make Table Query.  The final step of the Macro is to Generate the report, so I need to be sure that the OpenReport statement will point to the most recent Table..

Thanks!!!! The faster the better!!
0
Comment
Question by:InDashMP3
  • 2
4 Comments
 
LVL 12

Expert Comment

by:fulscher
Comment Utility
You probably should rewrite your Macros to VBA.

Public Sub SaveTable()
  Dim TableName As String
 
  TableName = "Meeting Results " & Now
  DoCmd.TransferDatabase acExport, , , acTable, "Meeting Reports", TableName, False, False
End Sub

This will create a new table as a copy of the table "Meeting Reports" with the current date as the table name.  

I don't believe that I understood your other requirements though. Could you explain a bit more?

Jan
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
Try this change:

    b.) New Name: ="Meeting Results " & Format(Date(),"mmddyy")

Your second request cannot be done in a macro.
But you could copy your newly created and dated table to a third table with a fixed name like "Meeting Results View" and base your query on this table. Kind of a "shoemaker solution" as we call it here ... but it will work.

I'll leave it to you to write that macro line ...

/gustav
 
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
Hi ,

Add the following to the Table name and the Report name:
Format(Date(), "MM,DD,YY")

so
if you create your Table like this:
DoCmd.RunSQL ("CREATE TABLE " & "Meeting Results" & " ([A] Text,[B] Text,[C] Text,[D] Integer);")
change it to
DoCmd.RunSQL ("CREATE TABLE " & "Meeting Results " & Format(Date(), "MM,DD,YY") & " ([A] Text,[B] Text,[C] Text,[D] Integer);")

You will have to explain the Report part "OpenReport statement will point to the most recent Table"


I hope this helps.

jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
Hey gustav

Great minds think alike, but one minute later. LOL

jaffer
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now