refeshing/requerying/etc... a single record

[misterhux]

hey...
what I have is 2 subforms (A and B), A's underlying recordset is based off a remote table, while B's recordset is a local temp table. What happens is that when a user dblclicks on a record in A it gets added to B. When this happens I want the record in A to have its color changed. So far all this works, but I'm worried about speed.

To get the color to update in A (using a conditional format that uses DLookup to see if the record is in B) I have to requery or refesh or recalc the entire recordset and that takes too long (seems that recalc takes the least at .5 sec for a recordset of 500, but have the potential to have up to 10,000 records in A). I think I basically get the flow of the program, each individual record in A calls DLookup (so this can be lengthy). So I'm wondering if there is a faster/better way to do this? thanks

 

[Frink]

Hallo,

DLookup is a tad slow. Can you base A on a query which includes a link to table B? Then the conditional format can use information from the underlying query to see if there's a record in B.

If u see what I mean,

- Frink

 

[misterhux]

yeah I get what you mean, its just like most people that post to tek-tips, I'm really new to VB and don't quite know how to do what you suggested. What is happening now, is that I call a storedProc on the server to populate the recordset for A. Do you think that appending another field to A after I've returned from the storedproc that is an included in B state would work (just thought of it reading your post)? But then wouldn't I have to do a Dlookup anyway to see if its in B?

thanks for your help

 

[Frink]

Hallo,

What's the SQL of the query? and what's the field (Key?) which is identical in A and B? If A nd B are the same structure then what's the Primary Key?

- Frink