Too Many DLookups

[Kn0wn1]

Hi Everyone,

I have a form that has about 50 dlookups to a query that narrows all records of my main table, down to one record.

Needless to say, this form takes 10-15 seconds to load. I've used dlookups because i want to load the data in the numerous text box fields, to allow the user to make any changes, and press a "save" button, which saves a copy of the record with any new changes, back into the main table as a new entry.

I think doing a DOA.recordset on form's open and copying the records values into my textboxs via vba may be a faster alternative? Not sure, whats best or where to begin.

Thanks

 

[Simon_MT]

You can also get the same type of issue with combi-boxes. At least these you can populate combi-boxes on demand reducing the overhead. Without seeing you project, DLookups, for me, are a last resort and are used very sparingly.

Simon

 

[Kn0wn1]

You can also get the same type of issue with combi-boxes. At least these you can populate combi-boxes on demand reducing the overhead. Without seeing you project, DLookups, for me, are a last resort and are used very sparingly.

Simon

Thanks for your reply. We agree, dlookups should be used sparingly, I just dont know what else to use.

Perhaps you can clarify your combo-box suggestion. Aren't combo boxes used for drop down selections? I'm not sure where this fits in with my question.

 

[Simon_MT]

Few of my combi-boxes have any Record Source and use Functions

On Entry =LookupWebExhibitions()

Function LookupWebExhibitions()
    With Screen.ActiveControl
        .RowSource = "SELECT WebStatuses.[Web Status], WebStatuses.[Web Status Desc], WebStatuses.[Web Status Type] FROM WebStatuses WHERE WebStatuses.[Web Status Type]<>'S';"
        Call ListDisplay 'Exposes the list if the control is Empty
    End With
End Function

On Exit = LookupExit

Function LookupExit()
'   Set to null when exiting control
    With Screen.ActiveControl
        .RowSource = ""
    End With
End Function

Simon

 

[Kn0wn1]

Thanks again, but im failing to either communicate my objective, or comprehend your answer, so I'll rephrase:

The objective is to load a selected employee's most recent profile, into textbox's on a form, then allow user to update these textboxes, and click the "Save" button to make a copy of the new data into the main table (copy is for historic purposes), without comprimising the pre-existing data.

I imagine the only way to achieve this is with unbound text-boxes. I have already achieved my objective by using dlookups as the default value in each of the 50 text boxes, but it is terribly slow.

I believe i can speed this up by loading the unbound data from my query into my textboxes. Perhaps there's even a better way?

I would consider myself a self-taught basic/intermediate access user, so there may be a very simple solution im just not seeing here because i havent come across it in the past. If your "combi-box" solution is still valid, perhaps i just need a breakdown in lamens terms. I haven't heard of a "combi-box", and apparently google hasn't either. Thanks again for your time.

 

[pbaldy]

Is there some reason you can't simply have the query be the form's Record Source, and have each text box have a field as a Control Source? In other words, bind the form to the query.

Simon meant combo box.

 

[Kn0wn1]

Is there some reason you can't simply have the query be the form's Record Source, and have each text box have a field as a Control Source? In other words, bind the form to the query.

Simon meant combo box.

Yes, i've tried and i have several problems with using the query as the recordsource:

1. Its a 4-layer sql query preventing any edits in the textboxes.
2. Even if i could edit, it would have to be bound (im assuming), eliminating my copying of the record without messing up the original record.

 

[pbaldy]

I doubt I would save a copy, I'd probably have an audit trail function going. That said, if you need it like it is, it should be much more efficient to open a recordset on the query and copy the values to the textboxes from the recordset. That would be one call to the data instead of 50.

 

[Kn0wn1]

I doubt I would save a copy, I'd probably have an audit trail function going. That said, if you need it like it is, it should be much more efficient to open a recordset on the query and copy the values to the textboxes from the recordset. That would be one call to the data instead of 50.

This is exactly what i was leaning towards. I used this code to accomplish it (in form open event):

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("EmpEditQ")
rs.MoveFirst
Me.FirstName = rs!FirstName
'etc, etc 50x
rs.Close

I'll post back when complete with some times to see the difference. Thanks all for your help.

 

[pbaldy]

You've probably already done the work, but if all of the fields in the recordset would update a textbox with the same name, like your example, you could use a loop of the fields. It would be much less code, but probably no noticeable difference in execution speed.

I'll be interested to hear the speed difference.

 

[Kn0wn1]

Ok so the end result! 44 dlookups removed, and values copied from recordset in place...time to load....INSTANT. Pretty damn good, i was not expecting these results. Previous time, between 10-12 seconds.

 

[pbaldy]

Great! I expected a pretty dramatic improvement. Welcome to the site by the way.