Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    doinitbig804 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    8

    Need Help Having A Form Pull Data From An Existing Table


    Hello,

    I'm new here and pretty new to using access. I have a pretty basic Access document setup. I have 1 table and 1 form in my database. I need a field in my form to update from the table each time I use the form to add a new entry to the table. The form needs to pull the value from a column in the most recent entry made in the table and use it in a calculation for a new entry in the table. I can attach or send my document if that would help things. I'd appreciate any help I can get. I hope all is well!

    All the Best

  2. #2
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Need examples of your table/fields and values, what field is calculated, etc.

  3. #3
    doinitbig804 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    8
    I need the "Pump #1 Initial Reading" field to update for each entry by reading the value from the "Gallons Used #1" column from the previous day. I need that value to be pulled in for the next form entry so the calculations can continue and not just use the initial default value I set. The "Gallons Used" field is being calculated. The form needs to pull the gallons used from the previous entry to have the correct initial reading for the entries following. Otherwise it just subtracts from my default value which is wrong.
    Click image for larger version. 

Name:	access.jpg 
Views:	26 
Size:	122.7 KB 
ID:	25482

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This is not a normalized data structure and it's going to make things very hard on you as you progress with your database. You should really look at data normalization articles.

    A more appropriate data structure would be something like

    Code:
    tblPump
    P_ID  P_Desc  P_Initial
    1     Pump 1  100000
    2     Pump 2  50000
    3     Pump 3  60000
    
    tblPumpLog
    PL_ID  P_ID  PL_Amount  PL_Date
    1      1     99500      1/1/2016
    2      1     90150      1/2/2016
    3      1     87240      1/3/2016
    I do not know what the remaining fields on your table are meant to represent but you should be calculating a lot of the values you're actually recording rather than storing them.

    For instance on a report you could have a running total of usage without needing to store the 'used' value each day.

  5. #5
    doinitbig804 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    8
    Quote Originally Posted by rpeare View Post
    This is not a normalized data structure and it's going to make things very hard on you as you progress with your database. You should really look at data normalization articles.

    A more appropriate data structure would be something like

    Code:
    tblPump
    P_ID  P_Desc  P_Initial
    1     Pump 1  100000
    2     Pump 2  50000
    3     Pump 3  60000
    
    tblPumpLog
    PL_ID  P_ID  PL_Amount  PL_Date
    1      1     99500      1/1/2016
    2      1     90150      1/2/2016
    3      1     87240      1/3/2016
    I do not know what the remaining fields on your table are meant to represent but you should be calculating a lot of the values you're actually recording rather than storing them.

    For instance on a report you could have a running total of usage without needing to store the 'used' value each day.
    Thank you so much for the response! I'll definitely read in to that now that you suggested it. I had no knowledge of Access previous to attempting this. I did construct a few reports but that won't let me see the totals without running the report, correct? I wanted to have the form able to pull the appropriate data value for "Gallons remaining" from the last entry and use it as the "Initial Reading" for the new form entry.

  6. #6
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    If you are trying to get a value from the previous day, you could use DLoopUp function to check for that value where Date = Date()-1 kind of logic. Do this on the OnClick on the Add button of your form.

    Something like:
    me.Pump #1 Initial Reading = DLookup("Gallons Used #1" , "Bulk Fuel" , "Date = #Date()-1#")

    As rpeare said, it probably should be reworked.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I would avoid domain functions if you can, they can be time consuming, especially if buried in queries. They are better used as a single reference point in code or something like that. In this case you have to also consider that you want total gallons available across pumps and for each pump individually, you also have to have some way to 'refill' pumps similar to a debit/credit system, except this would be for volume rather than money. So you might want to add a field to the suggested 'tblPumpLog' to store a transaction type and have a transaction type table like

    Code:
    tblTransType
    TT_ID  TT_Desc
    1      Daily Measurement
    2      Refill
    3      Adjustment
    So you could handle things like evaporation or samples taken from the tank that would, over time, add up to make your volume recorded vs actual volume disagree.

  8. #8
    doinitbig804 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    8
    I appreciate you both for helping me out! I will look at more data normalization material so that I actually have an understanding. Where would I put this line of code in the OnClick though? I found the OnClick Properties Menu, and got it to show the code for going to a new record, but I don't know how/where to add that code. Any help would appreciated.

  9. #9
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    If you created the button with the wizard, go to design mode of form, they top left in ribbon bar in Macro section, click Convert Form's macro to VB and check the 2 boxes that pop up. Then in that code window after it creates the new record, add the code to set the value.

  10. #10
    doinitbig804 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    8
    Ok, I did all that, but now when I add the code for it I get a Compile Error: Expected end of statement error on the "#1" portion. I tried deleting it and it gives me the same error on the word "initial" and " reading". Any ideas? Thank you so much for helping me figure this out!

  11. #11
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Try putting it in paren or brackets:
    me(Pump #1 Initial Reading) =
    or
    me.[Pump #1 Initial Reading] =

    By the way, never use special characters or spaces in naming things, can cause alot of havoc.

  12. #12
    doinitbig804 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    8
    Hmmm. yeah that's what I was thinking too. Thanks for that tip, I won't do that anymore. It still won't put thevalue in. Here's my code:

    Code:
    '------------------------------------------------------------
    ' NEW_Click
    '
    '------------------------------------------------------------
    Private Sub NEW_Click()
    On Error GoTo NEW_Click_Err
    
        ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
        ' <UserInterfaceMacro For="DELETE" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="OnError"/><Action Name="GoToControl"><Argument Name="
        ' _AXL:ControlName">=[Screen].[PreviousControl].[Name]</Argument></Action><Action Name="ClearMacroError"/><ConditionalBlock><If><Condition>Not [Form].[NewRecord]</Condition><Statements><Action Name="DeleteRecord"/></Statements></If></ConditionalBlock><Con
        ' _AXL:ditionalBlock><If><Condition>[Form].[NewRecord] And Not [Form].[Dirty]</Condition><Statements><Action Name="Beep"/></Statements></If></ConditionalBlock><ConditionalBlock><If><Condition>[Form].[NewRecord] And [Form].[Dirty]</Condition><Statements><A
        ' _AXL:ction Name="UndoRecord"/></Statements></If></ConditionalBlock><ConditionalBlock><If><Condition>[MacroError]&lt;&gt;0</Condition><Statements><Action Name="MessageBox"><Argument Name="Message">=[MacroError].[Description]</Argument></Action></Statemen
        ' _AXL:ts></If></ConditionalBlock></Statements></UserInterfaceMacro>
        On Error Resume Next
        DoCmd.GoToRecord , "", acNewRec
        If (MacroError <> 0) Then
            Beep
            MsgBox MacroError.Description, vbOKOnly, ""
        End If
    Me.[Pump #1 Initial Reading] = DLookup("Gallons Used #1", "Bulk Fuel", "Date = #Date()-1#")
    
    NEW_Click_Exit:
        Exit Sub
    
    NEW_Click_Err:
        MsgBox Error$
        Resume NEW_Click_Exit
    
    End Sub

  13. #13
    doinitbig804 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    8
    If anyone can figure out how to do what I was attempting, I'll PayPal you some money. I've included access to my access file via Google Drive.

    https://drive.google.com/open?id=0B3...zJBQThkV1Zvbmc

    Any help is appreciated! Thank you so much.

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I think you will find that any database designer would need some discussion time to understand the scope of your business. Issuing a blanket 'do this for me' is not likely to yield the results you want. The person designing your database needs to know all the details of how you want the database to operate and what type of reporting you need. I am sure there are a ton of people here that do consulting on the side (I do) but nobody worth their salt is going to build you a database without an in depth conversation about your needs.

  15. #15
    doinitbig804 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    8
    Thank you for the response. And if you were willing, I'd be more than happy to consult with you about this matter as I already have the database built, and just need it to perform one specific function I cannot figure out as I have discussed above. Thank you.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-05-2015, 10:17 PM
  2. Replies: 3
    Last Post: 02-06-2013, 10:00 PM
  3. Replies: 3
    Last Post: 08-02-2012, 10:27 AM
  4. Replies: 11
    Last Post: 07-24-2012, 07:50 PM
  5. pull up data from a table into a form
    By MattD00 in forum Forms
    Replies: 1
    Last Post: 03-30-2011, 08:15 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
  •  
Other Forums: Microsoft Office Forums