CODEWITHSUNDEEP
ms-accessms-access-2007
noelmcg
noelmcg

Reputation: 1067

Trying to improve performance by removing DLookUp - Access 2007

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

Upvotes: 1

Views: 745

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 2

Related Questions