DLookup Function Alternative

[Engr. Matthew]

this is a Temp table I use in moving items from products table to Purchase Order Details table
Here is the code I use to fine product record:
Private Sub FindProductCode()
On Error GoTo FindProductCodeError
If IsNull(Code) Then
Code.TabStop = False
DoEvents
Else
Code.TabStop = True
'Use Domain lookup function to populate the text boxes
ProductName = DLookup("ProductName", "Products", "Code =" & Code)
ProductID = DLookup("ProductID", "Products", "Code =" & Code)
' QuantityPerUnit = DLookup("QuantityPerUnit", "Products", "Code =" & Code)
PPrice = DLookup("[Products].[PPrice]", "Products", "Code =" & Code)
Extended = Quantity * PPrice
Forms!Orders!txtDes = DLookup("Description", "Products", "Code =" & Code)
DoCmd.RunCommand acCmdSaveRecord
End If
Exit Sub
FindProductCodeError:
' MsgBox "No ID Found", vbOKOnly, "No Record Found"
Exit Sub
Me.Refresh
End Sub
---------------------
This code works fine. I noticed when data is large on a wireless network it takes longer time to find an item and users not been patient, their always try to click one thing on Access form as a result of the interruption Access stop responding. Is there other way I can organize the VBA code to run faster.

 

[dalski]

I'm just a learner but I believe queries greatly outperform dLookup; especially as it scales. I think you'd be better off using an update query. I don't know much about them but I believe an update query will only save/ act on the fields in which it makes changes so be forewarned to save as a separate query then probably run the update query off of the other query.
Might end up with a subquery in there also.
I'm sorry I'm not a great deal of help, but at least it gives you some pointers to research on.

 

[Minty]

If you brought all those fields into a Product combo box, (hidden from view) you could simply display them from the hidden column directly, no need to fill out that data separately at all.

If the product description doesn't ever change then you don't need to store it on the order, you can always look it up, and display it.

 

[arnelgp]

This code works fine. I noticed when data is large on a wireless network it takes longer time to find an item and users not been patient, their

Wireless network is a No no. You may experience corruption on your db, since wireless connection is not reliable and may drop the connection.
also you are using 4 dlookup on same record which is very expensive over the network.
why not use a recordset:

Dim dbs As DAO.Database
set dbs = Currentdb
With dbs.OpenRecordset("SELECT * FROM Products WHERE Code = " & [Code])
    If Not (.BOF And .EOF) Then
        .MoveFirst
        ProductName = !ProductName
        ProductID = !ProductID
        ' QuantityPerUnit = !QuantityPerUnit
        PPrice = !PPrice
        Extended = Quantity * Nz(PPrice, 0)
        Forms!Orders!txtDes = !Description
    End If
    .Close
End With
Set dbs=Nothing

 

[plog]

this is a Temp table I use in moving items from products table to Purchase Order Details table

Why are you moving data? In a referential database, you reference data, not move it.

If you simply stored the Code value in your Purchase Order Details table you could then just link Purchase Order Details to Products and have all that data available. Why must it be moved?

 

[The_Doc_Man]

There is also this question: When you do a lookup, is the field you are using for the criteria argument an indexed field? Having indexes on a lookup or FindFirst or other value-oriented method will CERTAINLY improve performance.

I offered that indexing comment and not any other comments because I won't beat the dead horses.

plog is right about making reference to data rather than moving it. Why move the whole record when you can use a JOIN query later to recall the relevant details when you need them?

arnelgp is right about the perils of wireless networking and Access being together. The protocol used for Access is extremely sensitive to network dropouts. DEADLY sensitive, since it can leave a DB unusable in milliseconds.

minty has a point about using a combo box to bring together data in an easily retrievable way. The 4 x DLookup could be replaced by the single lookup implied by the combo-box activation.

dalski is right about performance of recordsets vs. domain aggregate functions. Among other things, each DLookup activation includes a well-hidden query. So 4 x DLookup is also 4 queries "behind the scenes". If you wonder about why something is slow when you get large amounts of data, you are doing 4 extra queries per relevant record. And a second word of warning from arnelgp's comment. The more data you have, the wider open the time window for a network failure to eat your database and spit out nothing useful.

 

[Gasman]

You could of course get all 4 values with one dlookup as well?

 

[The_Doc_Man]

You could of course get all 4 values with one dlookup as well?

Four returned fields in a single lookup? When I read the detailed description of the DLookup function, that returned set of fields would be in the "expr" argument, which doesn't APPEAR to allow multiple fields. It appears to require a single-valued expression.

DLookup Function - Microsoft Support

Use the DLookup function in Access desktop databases to get the value of a particular field from a specified set of records.

support.microsoft.com

If you've ever done a four-field lookup in a single call, that would be useful. However, the remarks section of the linked article says that "The DLookup function returns a single field value based on the information specified in criteria."

 

[Gasman]

Four returned fields in a single lookup? When I read the detailed description of the DLookup function, that returned set of fields would be in the "expr" argument, which doesn't APPEAR to allow multiple fields. It appears to require a single-valued expression.

DLookup Function - Microsoft Support

Use the DLookup function in Access desktop databases to get the value of a particular field from a specified set of records.

support.microsoft.com

If you've ever done a four-field lookup in a single call, that would be useful. However, the remarks section of the linked article says that "The DLookup function returns a single field value based on the information specified in criteria."

No, @arnelgp showed how to retrieve more than one value from DLookup many posts ago. I will let him say it again, else I will need to go looking for where I saved that code.
His example was for 2, but I see no reason as to why more cannot be used.
Only restriction is criteria must be the same, which in this case it is.

 

[tvanstiphout]

No, @arnelgp showed how to retrieve more than one value from DLookup many posts ago. I will let him say it again, else I will need to go looking for where I saved that code.
His example was for 2, but I see no reason as to why more cannot be used.
Only restriction is criteria must be the same, which in this case it is.

The first argument of DLookup can be an expression that includes multiple field names, concatenated with a separator character in-between. Then you can use Split() on the resulting value.

 

[riktek]

DLookup() is nifty as far as it goes but can be optimized in many ways. I developed a DLookup() clone (see below) to do just this, and designed it to be a drop-in for the OEM function.

This won't address your network latency issues but may speed things a bit. I haven't tried it but I expect it will work with Tom's suggestion concerning many field names in the Expr argument.

No warranties, obviously test first on a throwaway before attempting on live data, and YMMV but this has been in my production library for a long while. Ultimately, it depends on SQL, so it's pretty quick, besides cleaning up nicely after itself.

Function LtLookup(ByRef strExpr As String, ByRef strDomain As String, Optional ByRef strCriteria As Variant = Null) As Variant
  
    Dim varReturn As Variant
    Dim rst As DAO.Recordset2

    Set rst = LookupRecordset(strExpr, strDomain, strCriteria)

    If (rst.EOF) Then
        varReturn = Null
    Else
        varReturn = rst(0)
    End If

    rst.Close
    Set rst = Nothing
  
    LtLookup = varReturn

End Function    'LtLookup()

Function LookupRecordset(ByRef strExpr As String, ByRef strDomain As String, Optional ByRef strCriteria As Variant = Null) As DAO.Recordset

    Dim strSQL As String

    strSQL = "SELECT " & strExpr & " FROM " & strDomain & (" WHERE " + strCriteria)     'Note Null propagation.

    Set LookupRecordset = LookupSQL(strSQL)
  
End Function    'LookupRecordset()

Function LookupSQL(ByRef strSQL As String) As DAO.Recordset

    Set LookupSQL = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

End Function    'LookupSQL()

 

[Gasman]

The first argument of DLookup can be an expression that includes multiple field names, concatenated with a separator character in-between. Then you can use Split() on the resulting value.

You got it Tom.

 

[Engr. Matthew]

The table i am retrieving products record into is not among the tables relationships. It is a local table I use the Table in processing data before inserting the records to the normal table. I use the method in bringing Items into the purchase order Details and I use the same method in order booking.
the DLOOKUP is on the Purchase Orders subform on the Code Exit Event Procedure.

--------------------------
Dim dbs As DAO.Database
set dbs = Currentdb
With dbs.OpenRecordset("SELECT * FROM Products WHERE Code = " &

)  
If Not (.BOF And .EOF) Then        
.MoveFirst        
ProductName = !ProductName        
ProductID = !ProductID
        ' QuantityPerUnit = !QuantityPerUnit    
 PPrice = !PPrice     

Extended = Quantity * Nz(PPrice, 0)        
Forms!Orders!txtDes = !Description
 End If   
.Close
End With
Set dbs=Nothing

This code did not work

 

[cheekybuddha]

This code did not work

What happened?

 

[Gasman]

What you posted appears to have parts missing?

 

[Engr. Matthew]

What happened?

the code did not allow cursor to move out from the field I scanned the product code. The code is on the exit event procedure field called code. It suppose to find the item after exiting the code field. I entered your syntax on the exit procedure and try to execute it by scanning a product into the code field nothing happened and cursor can't get out of the field.

 

[Engr. Matthew]

DLookup() is nifty as far as it goes but can be optimized in many ways. I developed a DLookup() clone (see below) to do just this, and designed it to be a drop-in for the OEM function.

This won't address your network latency issues but may speed things a bit. I haven't tried it but I expect it will work with Tom's suggestion concerning many field names in the Expr argument.

No warranties, obviously test first on a throwaway before attempting on live data, and YMMV but this has been in my production library for a long while. Ultimately, it depends on SQL, so it's pretty quick, besides cleaning up nicely after itself.

Function LtLookup(ByRef strExpr As String, ByRef strDomain As String, Optional ByRef strCriteria As Variant = Null) As Variant
 
    Dim varReturn As Variant
    Dim rst As DAO.Recordset2

    Set rst = LookupRecordset(strExpr, strDomain, strCriteria)

    If (rst.EOF) Then
        varReturn = Null
    Else
        varReturn = rst(0)
    End If

    rst.Close
    Set rst = Nothing
 
    LtLookup = varReturn

End Function    'LtLookup()

Function LookupRecordset(ByRef strExpr As String, ByRef strDomain As String, Optional ByRef strCriteria As Variant = Null) As DAO.Recordset

    Dim strSQL As String

    strSQL = "SELECT " & strExpr & " FROM " & strDomain & (" WHERE " + strCriteria)     'Note Null propagation.

    Set LookupRecordset = LookupSQL(strSQL)
 
End Function    'LookupRecordset()

Function LookupSQL(ByRef strSQL As String) As DAO.Recordset

    Set LookupSQL = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

End Function    'LookupSQL()

Please take a look at attached file and help me construct dlookup i can use to update the VBA code
I place the procedure code on Code Exit event procedure. when a product barcode is scanned on the Code field the Items come out and the cursor sets on quantity for the use the enter quantity

 

[Isaac]

The table i am retrieving products record into is not among the tables relationships. It is a local table I use the Table in processing data before inserting the records to the normal table. I use the method in bringing Items into the purchase order Details and I use the same method in order booking.
the DLOOKUP is on the Purchase Orders subform on the Code Exit Event Procedure.

--------------------------
Dim dbs As DAO.Database
set dbs = Currentdb
With dbs.OpenRecordset("SELECT * FROM Products WHERE Code = " &

) 
If Not (.BOF And .EOF) Then       
.MoveFirst       
ProductName = !ProductName       
ProductID = !ProductID
        ' QuantityPerUnit = !QuantityPerUnit   
 PPrice = !PPrice    

Extended = Quantity * Nz(PPrice, 0)       
Forms!Orders!txtDes = !Description
 End If  
.Close
End With
Set dbs=Nothing

This code did not work

I'm not sure about a recordset being both bof and eof at once, but there is no shame in answering that by simply moving first regardless without the condition.
Format your code so it's easier to read!

Also, I'd recommend at your level to avoid the With clause for the moment, it will make it easier to mentally digest what is going on with the hierarchy of objects, properties, methods, etc combined with intellisense

 

[Gasman]

My opinion which I have mentioned before is Immediately after you open a recordset, if there are any records, then you will always be on the first record, else will be EOF (plus BOF), though I only ever check for EOF.
So moving first is just a waste of a line of code.

 

[cheekybuddha]

but there is no shame in answering that by simply moving first regardless without the condition.

No shame, except for a run-time error when it's an empty recordset! (ie when .BOF and .EOF are both True!)