Results 1 to 14 of 14

Code Problem

  1. #1
    chuckdarwin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    15

    Code Problem

    I am not a coder so Having issues with this. Probably something obvious but if someone could tell me what is wrong with this i would really appretiate it.



    Private Sub Feed_Log_Update_Click()
    Update [Broods]
    Set [Protein Fed to Date] = [Protein Fed Today] + [Protein Fed to Date]
    Set [Produce Fed to Date] = [Produce Fed Today] + [Produce Fed to Date]
    Set [Last Fed] = Now
    End Sub

    Its a click event on a log entry

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,371
    What is Update a reference to? Don't use Set just to populate fields or textboxes.

    Me![Protein Fed to Date] = Me.[Protein Fed Today] + Me.[Protein Fed to Date]


    Saving calculated data, especially aggregate data which is what this calc is, is usually a bad design.


    Ideally, should enter transaction records then calculate the aggregates when needed.


    Instead of VBA to populate date in new record why don't you use the DefaultValue property?



    Advise no spaces nor punctuation/special characters (underscore only exception) in naming convention.
    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
    chuckdarwin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    15
    I am just a farmer setting up a databse to rack my insect stock so i am seriously lacking skills here lol

    Every brood has a last fed entry and a total fed entry that i need updated when a log entry is entered. I was attempting to make those modifications when they save the log entry. This is all in a subform on the brood management page

    Google gave me what i pasted above with some modifications but i was unable to make it work


    Would there be a better way to update those records on that event ??

  4. #4
    chuckdarwin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    15
    I like the Default value for the date i will do that

  5. #5
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    212
    Dovetailing on what June7 said, I would remove the _ and - in any naming conventions. It is easy to mix things up and it is easy mistake to add a small thing like a space that drastically alters the code like - becomes a minus. Instead I personally do things like FeedLogUpdate.

  6. #6
    chuckdarwin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    15
    Am i getting close lol

    Private Sub Feed_Log_Update_Click()
    Me![Protein Fed to Date] = (Me.[Protein Fed Today] + Me.[Protein Fed to Date])
    Me![Produce Fed to Date] = (Me.[Produce Fed Today] + Me.[Produce Fed to Date])
    Me![Last Fed] = Date
    End Sub

  7. #7
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    212
    See changes. Also click on each field and then look in Property Sheet > Other > Name and make sure everything in blue corresponds exactly as written there.

    Code:
    Private Sub Feed_Log_Update_Click()
    
    
    ' What is this => Update [Broods] Me![Protein Fed to Date] = ([Protein Fed Today] + [Protein Fed to Date]) Me![Produce Fed to Date] = ([Produce Fed Today] + [Produce Fed to Date]) Me![Last Fed] = Now()
    End Sub
    Now() is date-time stamp
    Date is the date you created the record depending how your code is setup.

  8. #8
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    212
    Forgot to ask...... are all these fields on one table or are they pulling field values from separate tables?

    If all of these fields cannot be found on one table then list the table name followed by the field name shown so we may program properly.


    Otherwise do the following yourself:
    Me![Field Name You Have] = (tblTheTableName.[Field Name You Have] + tblTheTableName.[Field Name You Have])

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,371
    If you followed the advice to save transaction records and not save aggregate data this code would not be necessary.
    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.

  10. #10
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    212
    I agree June7; however, I did not see where the original poster was saving this aggregate data. Even if the text boxes are unbound the process remains the same and merely presents the data on the form for viewing.

  11. #11
    chuckdarwin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Posts
    15
    Trying to follow all this

    What you are recommending is keeping a serpate table with the feed log and then run reports to show total feed to a specific brood.instead of modifying records every log entry

    is that correct ?

  12. #12
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    212
    So you have your normal data you want to save.

    Then there are fields where you want to calculate those saved values. Those fields need unbound text boxes (i.e. not associated with a table field). No need to save those calculated values.

    HOWEVER, there are very valid circumstances when a calculated field (using VBA! not the table calculated field data type - because it is set to short text for VBA) requires saving, which I have and still do for select circumstances.

    The point about a separate table was for you to specify the field of your calculations. For example, [Protein Fed Today] and [Protein Fed to Date] might not both be in one table. If they are not both in one table (i.e. the table the current form or report is bound to) then specify the table name for each so the DB Engine knows where to look for said fields. Otherwise it ASSUMES those fields should be in the table that is bound to the form or report you are using to view the values. Another simple example would be a street address that might exist in five cities. Well now I need to specify which address by associating the city.

  13. #13
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    212
    Also I would switch your Fed to Date calculations around so the current total adds the Today values BUT see last comment below.

    Can provide better help by seeing a screen shot of the table structure.

    Should look something like this:

    FedTableName
    [ID]
    [ProteinName]
    [ProteinFedToday]
    [ProteinFedToDate]
    [ProduceName]
    [ProduceFedToday]
    [ProduceFedToDate]

    Where [ProduceFedToDate] is a new total for the record based on all previous calculations. Another way is something like:

    FedTableName
    [ID]
    [ProteinName]
    [ProteinFedToday]
    [ProduceName]
    [ProduceFedToday]

    Me![txtUnboundTextBox] = SUM(FedTableName.[ProduceFedToday])

    Where there would be no saved value in table for [ProduceFedToDate] and the same applies to [ProteinFedToDate].

    I surmise you are having an error because as part of your calculation is the current field value, which may be null. Therefore if I tried adding something to a blank field (i.e. itself - [ProteinFedToDate]) I would have either just the addition ([ProteinFedToday]) or nothing at all because the system will be confused.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,371
    Quote Originally Posted by chuckdarwin View Post
    Trying to follow all this

    What you are recommending is keeping a serpate table with the feed log and then run reports to show total feed to a specific brood.instead of modifying records every log entry

    is that correct ?
    Yes, that is what I am suggesting.
    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.

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

Similar Threads

  1. Replies: 12
    Last Post: 08-23-2016, 09:22 AM
  2. Problem with this code
    By Abacus1234 in forum Programming
    Replies: 3
    Last Post: 10-16-2015, 02:25 PM
  3. Problem with code.
    By gstylianou in forum Access
    Replies: 3
    Last Post: 09-10-2014, 05:15 PM
  4. Code Problem
    By Jeddell in forum Programming
    Replies: 2
    Last Post: 09-29-2011, 06:31 PM
  5. Problem with Code
    By cujee75 in forum Programming
    Replies: 0
    Last Post: 03-10-2006, 02:40 AM

Posting Permissions

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