Repost re Using Macro (vs VBA) to Copy Master Table for User Data Submission (1 Viewer)

[TGHockett]

Hi all ... I posted a detailed description last night under the General topic (thinking more folks would see it there, but no luck).

Anyway... for reasons explained in the General Topics note, I want to use a macro to copy a MasterNewEntry table for each user on a server that can be passed on to other Access dbs for processing (similar in concept to the Excel-process I am basing this application on).

For example the field sales reps filled out an Excel workbook, emailed it to the sales admin asst at headquarters, she made changes that the workbook didnt/couldn't store (e.g., historical costs needed for the contribution margin calculations), she prints out the hardcopy for review, routes it for mgmt approval etc.

So the idea is that when a field rep submits a new spending proposal in Acess2K, the MasterTable is copied and renamed based on a unique ID based on the Dealer name, and unique logon/autonumber and date of submission.

Can this be done via a macro? Or do I need to dig into VBA?

Thanks in advance .... TGH

 

[Pat Hartman]

I wouldn't make separately named tables/forms/databases for each field rep. If you must continue the email process, I would do one of two things.
1. allow the field rep to continue using a spreadsheet and provide an import process for the admin to use to import the data into the Access database. The spreadsheet should include the identifying information that will provide the unique ID Access needs.
2. create a special database that contains only the table and form the field reps need. The table should be empty. You can create an export process for the field rep to use to export the latest spending proposal to a spreadsheet. He can then email the spreadsheet and the admin can import it. If the database stays small, the field rep can send a copy of the entire db and the admin can extract the necessary data.

I would not create the objects needed by the field reps on the fly.

 

[Pat Hartman]

PS
If you click on the third icon to the right of your message (the one that looks like paper and pencil), you can delete or edit a message provided you are the poster.

 

[TGHockett]

Thanks Pat ... two great suggestions, but I /we (with the project manager) figured out a way to do accomplish the objective while avoiding the previous bs workoround which you so rightly thought there are better solutions... basically the reps will enter via a logonform, and via popups will gather the reps id info, program type etc and with that info create a unique MSP submission ID, and then the reps will start entering program data by model number in a master msp table (with various queries for the forms to be based on).... more Qs to follow probably

Thanks again... TGH

PS and thanks for the editing tip for this board