Results 1 to 6 of 6
  1. #1
    Julez is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2018
    Posts
    2

    Form with DLookup Control Source field calculates correctly, doesn't show value in Table Field


    I have a Form where one field pulls the following Control Source =DLookUp("Points","SalesCalls","CallType = '" & nz([cbxCallType], "") & "'") The form is a call log that the user can use a combo box to select the type of call and the Control Source field will automatically populate the points for that particular call type. Everything works fine, but when I open the Table that the form feeds into the Points field is empty. The points are showing on the Form, but not on the table. How or what do I do to make the Table show the points? I need the Table to show the Points so I can run a Report to get a total of those points. I'm not really sure what other information would be needed. I've reviewed other forums and videos, etc on how to try and fix this and nothing seems to work. Please help. thank you

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You need to run an update query to set the table value equal to that calculated on the form.
    However do you really need to save it?
    Calculated values are normally determined when required using a query.
    In this case using the DLookup formula
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Julez is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2018
    Posts
    2
    How do I do an update query?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Easy to do. Much harder to explain step by step.
    Don't be put off thinking it's hard...but make sure you read the NOTE at the end

    Open the query designer selecting Update.
    Chose the table and field in the usual way.
    In the update line enter '" & Forms!YourFormName.YourControlName.& "'
    using the name of your form and form control in place of what I've written
    Now change that to SQL VIEW and copy it to the clipboard

    Now go to the form and create an after update event procedure for your control
    Enter the code below

    Code:
    Dim strSQL As String
    StrSQL =""
    CurrentDb.Execute strSQL, dbFailOnError
    Finally paste the SQL string that you copied to the clipboard between the "" marks on the second line

    NOTE
    When the control is updated on the form, ALL values in that table field will be updated.
    Are you SURE that is what you want?

    If not, the update query needs to be filtered so only selected records need updating.
    If in doubt, create a SELECT query and modify to get just the records you want.
    Then change it to an UPDATE query and follow the instructions as above.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Set the DLookup in the text box VBA code under Got Focus [Event Procedure]. This way when you click the combo box it executes the code. In addition, it might work better to program out everything in there rather than using the DLookup. Issue with setting it in the Property Sheet sometimes. If that does not update your table then program in an AfterUpdate or LostFocus [Event Procedure] for the same text box with a If Me.Dirty Then Me.Dirty = False.

    Example programming out DLookup:

    Code:
    Private Sub txtPoints_GotFocus()
    On Error Resume Next
        Dim strSQL As String
        strSQL = "SELECT Points FROM SalesCalls WHERE CallType = '" & Me!cbxCallType & "' "
        CurrentDb.OpenRecordset strSQL
        Me!txtPoints.RowSource = strSQL
        Me!txtPoints.Requery
    End Sub

    Code:
    Private Sub txtPoints_AfterUpdate()
    On Error Resume Next
        If Me.Dirty Then
            Me.Dirty = False
        End If
    End Sub
    
    
    or
    
    
    Private Sub txtPoints_LostFocus()
    On Error Resume Next
        If Me.Dirty Then
            Me.Dirty = False
        End If
    End Sub
    Just change the red stuff to whatever the field name is on your form. Once you select the text box you can find the field name as the form sees it under Property Sheet > Other > Name

    Something else is to ensure the [Event Procedure] shows under Property Sheet > Event for which ever ones you program in VBA.

    If that does not work then a screen shot would help. Just search "Snipping Tool" on Windows start menu if you are unfamiliar.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As already described, code is required to save calculated data. Saving calculated data is usually unnecessary and may even be detrimental to data integrity.

    Why are you duplicating the point value into data table? Build a query that joins tables to retrieve the related info.

    Instead of DLookup() on form - a last resort approach when nothing else works - set up combobox as multi-column. Reference combobox column index to display the points. Column index begins with 0 so if points are in column 2 the index is 1.

    =[comboboxname].Column(1)
    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: 14
    Last Post: 08-20-2015, 03:33 PM
  2. Replies: 2
    Last Post: 05-28-2015, 12:12 PM
  3. Replies: 5
    Last Post: 10-24-2012, 12:35 PM
  4. Dlookup as control source on continuous form.
    By Ramun_Flame in forum Programming
    Replies: 4
    Last Post: 10-24-2012, 10:26 AM
  5. Replies: 6
    Last Post: 10-20-2011, 11:27 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