Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Macros to Modules

    A new database that I am working on utilizes a number of links-creations, append queries, and link-deletes. I created a macro to run the process automatically, but there is one problem that I havn't been able to overcome.

    The link creations are set up to link to files that, over a period of time, may or may not exist at the time the macro is run, causing the macro to squack back errors. Ditto for the append queries (source table may not have been created) as well as for the link-deletes.

    I converted the macro to a module, and inserted some error controls (on error, it just skips to the next command line and continues processing). I was able to successfully run the module in the VBA immediate window, but I havn't been able to get it to run separately. (The "run module" option is greyed-out as unavailable for the module.)

    How can I get this module to run, preferrably as a button on a switchboard?

    I'm using Access 2000 & Win98, but another consideration is that the files being linked to are DBF files... If there were a way to combine the multiple files into one large file, and only link to and append from the one file, this whole issue would be moot. So far our attempts (simple DOS copy to combine) yield the one large file, but due to the multiple headers, only the information from the first part of this file can be read.

    Any suggestions would be appreciated.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macros to Modules

    It depends what the structures of the tables in the dbf files are. Can you just import them into access? Either as separate tables or as one table?

    If this does not give you enough to go on can you let us have some more info about the dbf files and why they would perhaps not exist?
    David Grugeon
    Brisbane Australia

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros to Modules

    Create your Sub Procedure in a module. Then go to the Switch Board form and create a button in design mode. Right click on the button and select properties. Scroll the list down and slelect the "On Click" property. Click on the little box with three dots in it that appears next to the property name. That should put you in the VBA Editor for the Click event for the button. Just insert a call to your Sub Procedure. If your Sub was named MySub, then that would look like:

    <pre> Call MySub
    </pre>


    Now when you click on the button, your sub should execute.
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros to Modules

    Or:

    Create a function in a module, lets say fnDoIt().
    Create the button on your form.
    Go to the properties window and search the On Click property.
    Put the line =fnDoIt() in the properies window right to the On Click property.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macros to Modules

    I think a more elaborate explanation of what we are trying to accomplish with the dbf files is needed...

    We have a number of distribution centers, each with a different number of delivery drivers. Each delivery route is assigned a unique route number, which serves as the basis for the filename for their weekly delivery data. The route numbers are grouped by DC, so that route numbers within a given range will always be assigned to the same DC. As routes are added & deleted, merged & split, the route numbers within each DC may develop gaps in the numbering; existing routes are never renumbered.

    Each set of weekly data files are saved in a different folder, WK01 to WK52. When the new data files are to be loaded into Access, the current week's files are copied to a transfer directory. The TransferDatabase macro commands are intended to create links to this stationary target directory, linking each file that _may_be_in_use_ for the week being transferred into the permanent data table.

    I feel linking is the better option over importing, if only to keep the overall size of the database from ballooning out of control. The "TransferDatabase / AppendQuery / Delete Linked Table" macro works, but whenever it runs up against a file that does not exist the error message boxes start popping up. Convert the macro into a module, and error handling controls enable the whole process to run uninterrupted.

    We need the process to be push-button simple, since it will be loaded up at each distribution center and used by non-Access'ers. It must also be flexible enough to handle the range of route numbers needed without having to be re-programmed continuously.

    I havn't had a chance to try creating the "button" yet to run the module as a function, but on the surface it does look do-able. (I'm very much a novice when it comes to VBA programming, and most of the rest of what I do with Access is self-taught.)

    Thanks for the help so far.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macros to Modules

    I would still have all the files in Acess (or SQL)databases. The old records could be exported to a linked table in another database. IMHO this is easier to manage than having files in other formats.
    David Grugeon
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •