Dlookup Alternative for Performance Improvement?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know if there is an alternative to DLOOKUP that will allow me to
pull information from a table and set control(s) in form(s)? I ran
AccessAnalyzer and the results pointed to the extensive use of Dlookup as a
potential problem with the performance of the database (there is a lot of
latency when running certain features).

For example, if I could set a query to update a control, that would
(theoretically) greatly improve performance (don't think this is an option).

Thanks.

G
 
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("MyQuery")
Rst.FindFirst .................
Me!MyControlName = Rst!MyFieldName
 
It would be better and faster if this recordset were filtered instead of
opening against the entire query:

Set Rst = Db.OpenRecordset("SELECT FieldName From MyQuery WHERE IDField=" &
"SomeValue")

Then no "FindFirst" would be needed as a separate step.

Of course, using a recordset means that the user must test for the presence
of any records in the recordset:


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT FieldName From MyQuery WHERE IDField=" & "SomeValue"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
If rst.BOF = False And rst.EOF = False Then
Me!ControlName.Value = rst.Fields(0).Value
Else
Me!ControlName.Value = Null
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
 
Back
Top