Results 1 to 6 of 6
  1. #1
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19

    VBA Code to Auto Upload Data Based on Date

    Okay --



    So I am a very new user to VBA and I am taking online lessons while trying to teach myself at the same time.

    I have a db that tracks pricing data for multiple parts. Each month, on the 1st, I upload the new price for each part number, even if its the same as last months.

    I want to create code that automates this process on the click of a button (or some other activation procedure).

    I have a couple of ideas, but am unsure of what the code might look like as I am still very new to VBA.

    The simplest idea I have, I think, would be to create a code module that copies all the data for a particular month, changes the date to the immediate next month, then appends the new data to the same table. This is preferable because most prices are relatively constant. If there were some changes, I could go in and change them individually.

    Are there any experienced users here who can tackle a problem such as this one?

    Any guidance and insight offered is appreciated.

    Thanks,
    -Brian

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    First you must determine your requirements, list the processes that must happen in their sequence. E.g. (1) import file (2) update prices, etc. Then we can figure out what the code should look like.

    Importing the file - is it the same file every month or how will the database know which file to import? Such as the need to browse for the file each time.

  3. #3
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Thanks for your reply!

    My idea would take the data straight out of the current access db. So in my mind the processes would look something like this:

    1) Copy data for this month in current db in tblPrice
    2) Change date from this month to next month
    3) Append new data to tblPrice

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    1) copy to where? It would be a query or maybe a copy of the whole table?
    2) update query
    3) query - where is the new data coming from?

  5. #5
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    I suppose copying to a query could work.

    All of the data is the same. The only thing that I want to change is month. So if we have an array of data for Oct, I want this code module to copy all of it and change the date to Nov, then append to the original table so I would have the data for Oct AND Nov.

    I hope I am making sense.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Misunderstanding! You need to be very specific with what you want to do.

    1 - "I want to copy ______ fields from ______ table to ______" (what is the purpose of this?)
    3 - "I want to update ____ fields on _____table from _______ where?"

    Once all of this has been defined then queries and code can be created and put into a macro or on to a form.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. macro to auto upload data
    By chr1stoper1 in forum Macros
    Replies: 2
    Last Post: 08-05-2015, 09:21 AM
  2. Replies: 2
    Last Post: 07-07-2014, 09:19 AM
  3. Replies: 3
    Last Post: 01-10-2014, 02:35 PM
  4. FTP Upload Code
    By Subwind in forum Programming
    Replies: 7
    Last Post: 11-15-2013, 06:57 AM
  5. Auto date based on check box
    By bkirsch in forum Access
    Replies: 7
    Last Post: 01-15-2012, 11:38 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums