ansaurus

Question

Trying to improve performance by removing DLookUp - Access 2007

Answer 1

+2 A:

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.

Remou 2010-07-22 10:54:45

Hi Remou, I've edited my question as I do not believe I was completely clear as to what the check box is achieving. I don't think I can use a subform in this instance?

glinch 2010-07-22 11:45:17

I still think that a subform is the solution you need.

Remou 2010-07-22 11:52:51

I see now how this solution would work. Time restraints may make it tricky to implement, as as far as i see it, i would need to restructure the database from the way I have it organised at present. But I certainly see how this would be effective.

glinch 2010-07-22 12:29:18

I have added another note.

Remou 2010-07-22 12:48:03

The recordset solution could be more effective for me in this instance, will research this as it looks the way to go. Cheers

glinch 2010-07-22 14:01:37

For what it's worth, you seem to be really fighting against all the natural methods of doing things in Access, but it could be that you're in this boat because your table design is completely wrong -- not enough information.

David-W-Fenton 2010-07-22 18:29:07

Yes, I think i will have to address this problem in my table design now rather than suffer later.I think i can see the error of my ways, thanks for the advice chaps

glinch 2010-07-23 10:37:33

ansaurus

tags:

views:

answers:

Trying to improve performance by removing DLookUp - Access 2007

related questions