Results 1 to 8 of 8
  1. #1
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151

    Question DLookup sometimes works, sometimes doesn't.

    Hi all

    Yet another DLookup question. I've researched as many threads as I could find, but still can't resolve the issue.

    I have a sub form with a combo box where users can select a customer to whom they can transfer a number of service records for a machine to a new customer. (who has taken possession of the equipment).

    Before the records transfer proceeds, I want to check that the customer to whom the record/s is/are being transfer actually owns the equipment to which the service records belong.

    Aapologies for the spaces in the table name, it's a legacy issue.

    So if CustomerID does not have a record in the table [Customer Machine Components] for the MachineNo in question, then DLookup should resolve Null.

    If I run the code as is, sometimes it will work and throw up the Null error message box if applicable, and other times it won't work. Even if I am testing on the same customer record.

    I'm really stumped with this and can't understand why it would work sometimes, and not others, even on the same dataset, and would appreciate some insight.


    The Combobox has this code attached to the OnChange event;



    Code:
    Private Sub cmbNewOwner_Change()
        FilterComboAsYouType Me.cmbNewOwner, "SELECT * FROM Customers", "CompanyName"
    End Sub

    This is the code that I have attached to the AfterUpdate Event of the combo box.

    Code:
    Private Sub cmbNewOwner_AfterUpdate()
    Me.txtNewOwnerID = Me.cmbNewOwner
    
    If IsNull(DLookup("MachineNo", "[Customer Machine Components]", "[CustomerID] = '" & Me.cmbNewOwner & "'")) Then
        MsgBox Me.cmbNewOwner.Column(1) & " does not own Machine No " & txtMachineNo & ".", vbExclamation, "Error"
        Me.cmbNewOwner.SetFocus
        SendKeys "{BKSP}", True
    Else
        'continue processing the request
        MsgBox "OK to transfer"
    End If
    
    End Sub
    The combo box is unbound.
    MachineNo in the table [Customer Machine Components] is a a short Text field without lookup.
    CustomerID in the table [Customer Machine Components] is a short text field with lookup to a Customers table.

    Click image for larger version. 

Name:	Transfer Screen.jpg 
Views:	19 
Size:	85.3 KB 
ID:	46034

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Exactly how do you 'transfer' service records? Service records belong to machine. Just change the customer that is associated with machine. Or to retain history of ownership, create a new record in a junction table with a date ownership is assumed.

    I advise not to build lookups in table.

    Have you step debugged?

    To provide db for analysis, follow instructions at bottom of my post.
    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
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi June7

    Thanks for the quick reply.

    Just change the customer that is associated with machine
    Is where I started, but there are some major design changes that need to be made. I figured that my approach would be a good solution.

    The transfer button on the form has this action associated to it. Basically it runs an update query on the xServices table and replaces the old owner with the new owner.

    I have step debugged but the immediate window throws up the expected results for the existing CustomerID and the new owner. I'm not actually sure how to step debug the DLookup function itself.

    Code:
            Dim MachineID As String
            MachineID = [Forms]![Training & Services]![Machine No]
            
            Dim Msg As String, Style As Integer, Title As String, DL As String
            Msg = "Do you really want to transfer records for machine no. " & MachineID & " to " & Me.cmbNewOwner.Column(1) & "?"
            Style = vbQuestion + vbYesNo
            Title = "Confirm Record Transfer"
    
            If MsgBox(Msg, Style, Title) = vbYes Then
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "qryTransferServiceRecords"
                DoCmd.SetWarnings True
                MsgBox txtNoOfRecords & " successfully transferred", vbInformation, "Transfer Complete"
                DoCmd.Close acForm, Me.Name, acSaveNo
                
            Else
                Exit Sub
            End If

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The DLookup expression seems wrong to me. If customer is associated with any machine(s), it will return a machine number even if it is not the machine of interest. Consider:

    If Nz(DLookup("CustomerID", "[Customer Machine Components]", "[MachineNo] = '" & Me.txtMachineNo & "'"),"") <> Me.cmbNewOwner Then
    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
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you tried June's suggestion?
    Or to retain history of ownership, create a new record in a junction table with a date ownership is assumed.

  6. #6
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Quote Originally Posted by June7 View Post
    The DLookup expression seems wrong to me. If customer is associated with any machine(s), it will return a machine number even if it is not the machine of interest. Consider:

    If Nz(DLookup("CustomerID", "[Customer Machine Components]", "[MachineNo] = '" & Me.txtMachineNo & "'"),"") <> Me.cmbNewOwner Then
    Hi June7

    Apologies for the slow response. I've tried your suggestion and it works just fine.

    I'm trying to get my head around the difference in approach and why mine sometimes worked.

    Or to retain history of ownership, create a new record in a junction table with a date ownership is assumed.
    Whilst I was mulling over your reply overnight, I decided that I would implement this suggestion. I don't know how it will benefit future users since changes in ownership usually occur as a result of a cessation of trading, but it can't hurt.


    Just change the customer that is associated with machine.
    I've also looked at how I approached this modification to the DB and have come up with a more elegant and streamlined approach.

    I want to thank you again for your help and insight. What I really like about this forum is everyone's willingness to help and that suggestions made often lead to greater learning.

    With no formal training in Access of VB, all that I have learnt over the last 20yrs or so is self taught and with online tutelage by persons such as yourself.

    Cheers.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Having a table that documents when machine ownership was assumed means with date of ownership and date of maintenance can determine under whose ownership maintenance was performed, if that has value.
    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.

  8. #8
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Quote Originally Posted by June7 View Post
    Having a table that documents when machine ownership was assumed means with date of ownership and date of maintenance can determine under whose ownership maintenance was performed, if that has value.
    At the moment I can't see how it has value, but I did program a lot of stuff years ago that is now starting to show its worth. Good design or good luck, I don't know. However I will implement your suggestion because it takes little effort and could pay off in the future.

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

Similar Threads

  1. Dlookup Works in Controlsource - but not in VBA
    By crimedog in forum Programming
    Replies: 13
    Last Post: 11-20-2015, 10:55 AM
  2. Replies: 1
    Last Post: 11-14-2014, 05:12 PM
  3. similar code, one works, the other doesn't
    By johnseito in forum Access
    Replies: 15
    Last Post: 11-03-2013, 07:59 PM
  4. Replies: 2
    Last Post: 07-23-2011, 08:16 PM
  5. One works, the other doesn't. Why?
    By oleBucky in forum Queries
    Replies: 6
    Last Post: 05-09-2011, 01:52 PM

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