Trying to improve performance by removing DLookUp - Access 2007

Question

could anybody possibly give me some pointers as to the appropriate method to use here.

-Edit

I have fields in a form that relate to a users 'risk address', and I have a checkbox that updates the values in these fields that if selected loads the 'contact' details (from the table tblInsPersDet) into these fields. If the checkbox isnt selected the user can enter new details.

I run the below sub routine on celection of the checkbox. To get the field values i'm using Dlookup, but i believe this to be inefficient? As it queries the database/table each time.

Public Sub sameAsContact(frm As Form)

frm.riskAddress1 = DLookup("[add1]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskAddress2 = DLookup("[add2]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskAddress3 = DLookup("[add3]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskAddress4 = DLookup("[add4]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskAddress5 = DLookup("[add5]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.cmbRiskCountry = DLookup("[country]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskDstToProp = DLookup("[distToProp]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskInsCompany = DLookup("[insCompany]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskPolNo = DLookup("[polNo]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskBldSi = DLookup("[bldSi]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskContSi = DLookup("[contSi]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskExcess = DLookup("[excess]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskOgLinkMort = DLookup("[linkMort]", "tblInsPersDet", "[ID] =" & frm.insuredId)
frm.riskOgAddOn = DLookup("[addOn]", "tblInsPersDet", "[ID] =" & frm.insuredId)

End Sub

Is there a way to query the database once? Could anybody please give me a pointer as to a more appropriate method to get the data from the required record.

Thanks in advance for any help Cheers Noel

As a matter of curiosity, why do you set all your questions to community wiki? — Fionnuala
aren't you suppossed to?! Doesn't this alow people to edit the question? — noelmcg
It is not the usual thing, and it means that you do not benefit from votes for a good question, but it is up to you. — Fionnuala
If the result is a better question, with possibly better answers, it's worth the trade off. Cheers for the advice though. — noelmcg
BTW anyone with a reputation of >=2000 can edit other people's posts, whether or not the post is a community wiki. — Fionnuala

Remou Remou · Accepted Answer · 2010-07-22T10:54:45

You should use a subform that has insuredId as the Link Master field and ID as the Link Child field.

EDIT re note

I still believe you can use a subform. If the checkbox is ticked, either select the relevant record for the subform:

Me.MySubformControlName.Form.RecordSource="Select * From tblInsPersDet Where ID=" _
 & Me.txtinsuredId

Or fill a value into a hidden or visible field called, say, txtinsuredId.

If the checkbox is not checked, you can move to a new record in the subform.

EDIT re note #2

If you must use something akin to DlookUp, use a recordset:

 Set rs=CurrentDB.OpenRecordset("Select * From tblInsPersDet Where ID=" _
 & Me.txtinsuredId)

 For each fld in rs.Fields
    frm("txt" & fld.name)=fld
 Next

or there abouts.

Trying to improve performance by removing DLookUp - Access 2007

1 Answers