Dlookup prob

[daninthemix]

I have the following code:

 Asset = DLookup("[Serial Number]", "Serials", "[Serial Number]=" & "'" & Me![Serial Number] & "'")

...but the problem with this is it finds a match even based on partial data. E.G. if I have a serial number CK99901, and someone enters CK999... then the above dlookup finds a match.

How do you force dlookup to only match the entire field only?

 

[RuralGuy]

I don't think your DLookup is working correctly but maybe. How do you have Tools>Options>Edit/Find>[Defaulf find/replace behavior] set? Try Fast Search. I wouldn't think this should affect a DLookup() but let's see.

 

[DB7]

Danitm, I agree with RuralGuy, It's not working correctly.
But, out of curiosity, you wrote...
..."CK99901, and someone enters CK999... "
Are you implying, someone enters "CK999", or "CK999__".

What result do they get, the typed, Serial Number?

 

[daninthemix]

This is odd, because here's the code I have on the field's change event:

Private Sub Serial_Number_Change()

    Dim Asset As Variant
    Dim Host As Variant
    
    Asset = DLookup("[Serial Number]", "Serials", "[Serial Number]=" & "'" & Me![Serial Number] & "'")
    
    If IsNull(Asset) = False Then
    cmdSerial.Enabled = True
    Exit Sub
    End If
    
    Host = DLookup("[Host Name]", "Serials", "[Host name]=" & "'" & Me![Serial Number] & "'")
        
    If IsNull(Host) = False Then
    cmdSerial.Enabled = True
    Exit Sub
    End If
    
End Sub

and I have the same code in the form's current event...only in that event it seems to work!

You can see the objective here - there's a button that lets you jump to the serial number in another form IF it exists there. If not, the button should not be enabled.

 

[RuralGuy]

What disables the cmdSerial button? Are you aware that the Change event occurs *every* time there is a keystroke? Why not the AfterUpdate event?

 

[daninthemix]

RuralGuy - thanks. I'm being a dunce! I just changed the code to this:

    Dim Asset As Variant
    Dim Host As Variant
    
    Asset = DLookup("[Serial Number]", "Serials", "[Serial Number]=" & "'" & Me![Serial Number] & "'")
    
    cmdSerial.Enabled = Not IsNull(Asset)
    
    If IsNull(Asset) = True Then
    
    Host = DLookup("[Host Name]", "Serials", "[Host name]=" & "'" & Me![Serial Number] & "'")
    cmdSerial.Enabled = Not IsNull(Host)
    
    End If

...and now it works fine. The problem is I wanted the user to be able to enter either a serial or a hostname in the same textbox, and if dlookup can't find a match in one then it seaches the other.

Thanks for your help!

 

[RuralGuy]

Hi daninthemix,
The domain aggregate functions like DLookup are notoriously slow and I would think having them in the ChangeEvent would make your application feel sluggish while in that TextBox. I had no problem with using the TextBox for more than one function. Glad to hear you got it working.

 

[daninthemix]

RuralGuy, I took your advice and moved the function to the AfterUpdate even instead.

After what you've said I'm a little worried that speed might become an issue as the database grows, because I have Dlookups going on all over the place, enabling and disabling buttons etc.

 

[RuralGuy]

Hi daninthemix,
Thanks for the update. As long as you get the results you were looking for I think you have made a wise choice. Enjoy the rest of your project.

 

[DB7]

DanInTheMix & Rural Guy, excuse the interjection, I just wanted to add that, the Change event, is only accurate with a textbox's Text.Property.

In other words, you should've been writing this...
"[Serial Number]=" & "'" & Me![Serial Number].Text & "'")

But, like Rural guy said, why keep searching before the criteria is completely typed.

secondly, and I'm curious if you agree Rural Guy, but usually I find it imperative to refresh data, before I use an aggregate function, AfterUpdate or not. It doesn't always save , immediately after the text loses focus.

In other words, your procedure may not be seeing the "new value" in the table.

That's been my experience anyhow,...imperative to Me.refresh or Docmd.SaveRecord..