Results 1 to 5 of 5
  1. #1
    MFriend is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Posts
    22

    Data Macro's not working... please help


    Hi Folks:

    I could really use some help with setting up Data Macro’s in my Access 2013 database. I’m new to using them (and have no VBA knowledge) so could use help.

    This database’s goal is farming related. They want to track Hay bales that are harvested (hence the BALES_HARVESTED table) as well as a running total of bales left at various locations (called stackyards).

    When the hay is harvested the BALES_HARVESTED table (a child table of BALES_DATA) is filled out. I’d like the data macros to run and either create a new record in the BALES_STORED table (if the primary key requirements are not met) or edit the number of bales in a record that does have the same composite primary key.

    The first step is to determine if there is already a matching key/record in the Bales_Stored table that already contains the composite primary key (composed of Stackyard, Crop, Cutting). If the primary key exists already in the Bales_Stored table then the amount/number of bales needs to be updated (the new bales from the Bales_Harvested table should be added to the count that already exists in the Bales_Stored table.
    Else if there is no matching key: if there is not already a record containing the composite primary key in the Bales_Stored table, then a new record should be created in the Bales_Stored table containing the Stackyard, Crop, Cutting, and number of bales.

    Terms:
    STACKYARD: a storage location for the bales of hay/alfalfa/grass, etc.
    CROP: the type of hay: grass, alfalfa, corn stock, etc.
    CUTTING: 1st, 2nd, 3rd, 4th, etc. (1st cuttings for instance are normally better hay than later cuttings).
    In the attached database, if you look at the BALES_HARVESTED table I have some ideas for the data macro(s) that I have tried but not succeeded at.

    Thank you in advance for any help.
    Matthew

    The .jpg is a screen pic of the tables.
    the .zip file contains the accdb database
    Attached Thumbnails Attached Thumbnails BALES_HARVESTED_BALES_STORED.JPG  
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,596
    Ideally, a database should not store calculated data, especially aggregate data. A principle of relational database is to store raw data and calculate when needed. Conventional approach would be to calculate a difference of the sums of transaction records for bales in and bales out of the storage areas. This is inventory tracking. Review http://allenbrowne.com/AppInventory.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    MFriend is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Posts
    22
    Hi June:

    The page you suggested on the allenbrowne site has some good info but looks very confusing to me (I have very little/ almost no VBA knowledge). My database would be more complicated than the example they give I think because I also (eventually in the Bales_movement table.... or some other table) have to track if/when the bales are moved to another stackyard, to a poleshed (its like a barn) or fed out to the cattle.

    I have three tables currently to track the data: the Bales_Harvested table gives me the ability to understand exactly what the production was for the various fields, land, etc. The data does not change in it except adding new records as new cuttings/harvest occurs. The Bales_Movement table is the table that would track individual movement of bales between storage locations (if I can figure out the data macros for the first two tables - bales_stored and bales_harvested, then I can have a blue print to work on the data macros on the bales_movement table) and if they have been 'used' (fed).

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,596
    I don't use macros, only VBA. Macros are required for web deployment of db, otherwise better to use VBA.

    User should not interact directly with tables and queries, only forms and reports. What form should I look at?

    What table has Data Macros and which macro?

    So much easier to debug VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    MFriend is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Posts
    22
    Hi June:

    I understand it would be better to use VBA (and I've ordered a couple of books on it: the Access 2013 Programming by example with VBA... by Korol and Professional Access 2013 Programming by Hennig, etc) but that would take longer than I have to get this database done (though I am very interested in learning VBA over the summer/fall).

    When the database is done the users will only be interacting with forms and reports (they will be running the access 2013 runtime).

    The table that has the data macro I was working on is attached to the BALES_HARVESTED table. I haven't started on a form for it yet since (my understanding is) the data could be tested by entering it directly into the table (since the data macro is linked/part of the table and wouldn't be a part of a form like a UI macro would be). The data macro is an after insert macro (after the record is inserted into the BALES_HARVESTED table, then it runs to see if there is a record in the BALES_STORED table and if there is updates it. If there is not, then a new record is created in the BALES_STORED table.

    Matt

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

Similar Threads

  1. Replies: 14
    Last Post: 07-17-2013, 06:46 AM
  2. Replies: 7
    Last Post: 08-10-2012, 03:09 PM
  3. macro not working in Windows 7 machine
    By vemi007 in forum Programming
    Replies: 2
    Last Post: 04-19-2012, 01:45 AM
  4. Macro Filter not working....
    By avarusbrightfyre in forum Access
    Replies: 2
    Last Post: 06-18-2011, 04:09 PM
  5. RunCode in macro not working
    By LilMissAttack in forum Access
    Replies: 0
    Last Post: 05-27-2010, 05:01 PM

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