dlookup anthing better?

[stuartam]

hi me again,

i have created a function that updates the content of a table, but at the moment to get the current values i have to use dlookup and then add what is required to the current value.

is there a better way of doing this?

here is my code:

Call poststatsupd(2, fOSUserName, Date, "test", DLookup("[Pack_posted]", "tbl_personal_stats", "[User_Stats_an] = 2") + 1, DLookup("[Trans_posted]", "tbl_personal_stats", "[User_Stats_an] = 2") + Text39, DLookup("[geo_req]", "tbl_personal_stats", "[User_Stats_an] = 2") + IIf([Geo] = -1, 1, 0), DLookup("[dr4_in_pack]", "tbl_personal_stats", "[User_Stats_an] = 2") + IIf([Dr4inp] = -1, 1, 0))

Public Function poststatsupd(vbarecord As String, VBAUser_ID As String, VBAdate As String, VBArole As String, VBAPack_posted As String, VBATrans_posted As String, VBAgeo_req As String, VBAdr4_in_pack As String)


' open a connection to the connection object
Set Cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "tbl_personal_stats", Cn, adOpenStatic, adLockOptimistic ' open it
.Find "User_Stats_an ='" & vbarecord & "'"

.Fields("User_ID") = VBAUser_ID
.Fields("date") = VBAdate
.Fields("role") = VBArole

.Fields("Pack_posted") = VBAPack_posted
.Fields("Trans_posted") = VBATrans_posted
.Fields("geo_req") = VBAgeo_req
.Fields("dr4_in_pack") = VBAdr4_in_pack

.Update ' update the table
.Close ' close the recordset connection
End With

End Function

best regards

 

[mresann]

Basically, if you are looking at obtaining one unit of data from each single recordset, then the domain function DLookup for each item should suffice. When you start using the domain functions for more than one piece of data from the same record, or when using them when queries would be more efficient, is when you start losing the effieciency. In addition, if you use DLookup on linked tables as opposed to local tables, you can dramatically slow down your performance, especially with tables with more than 3,000 recordsets.

With what you have, it's fine. But I'd probably take a closer look at your table structure, and learn more about normalization.

 

[stuartam]

thanks for the reply, the table is actually local one and will only have 6 records in it, but i did notice it taking some time to calculate ( only a few seconds but).

so i had a mess about and changed my function as follows:

Public Function poststatsupd(vbarecord As String, VBAUser_ID As String, VBAdate As String, VBArole As String, VBAPack_posted As String, VBATrans_posted As String, VBAgeo_req As String, VBAdr4_in_pack As String)


' open a connection to the connection object
Set Cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "tbl_personal_stats", Cn, adOpenStatic, adLockOptimistic ' open it
.Find "User_Stats_an ='" & vbarecord & "'"

.Fields("User_ID") = VBAUser_ID
.Fields("date") = VBAdate
.Fields("role") = VBArole

.Fields("Pack_posted") = .Fields("Pack_posted") + VBAPack_posted
.Fields("Trans_posted") = .Fields("Trans_posted") + VBATrans_posted
.Fields("geo_req") = .Fields("geo_req") + VBAgeo_req
.Fields("dr4_in_pack") = .Fields("dr4_in_pack") + VBAdr4_in_pack

.Update ' update the table
.Close ' close the recordset connection
End With

End Function

and now my call is like this:

Call poststatsupd(2, fOSUserName, Date, "test", 1, Text39, IIf([Geo] = -1, 1, 0), IIf([Dr4inp] = -1, 1, 0))

thanks for the help