efficient method?!?! requery...

[eocheng]

Hi,

I'm trying to make the code a bit better.

Do While (exit condition that works)
ProgNo = rst![ProgramNo] 'from another table, this changes on each iteration thru the loop
'inefficient!!!!! there should be a method to requery without opening and closing each time
rst2.Open "SELECT ProgramName FROM tblProgName WHERE ProgramNo =" & ProgNo, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
ProgName = rst2!ProgramName
rst2.Close
Loop

Obviously, it is ridiculus to open and close the table each time, I was wondering if anyone knew the function I should be using. Note that the query is based on the ProgNo variable that changes on each iteration.

Thanks in advance,
Eric

 

[RoyVidar]

One step, could be trying an update query:

[tt]sSgl= "Update tblProgName Set ProgramName '" & rst2!ProgramName & "' where ProgramNo = " & rst![ProgramNo]
currentprojcect.connection.execute sSql[/tt]

Not knowing the basis of the recordset, it's a bit hard to evaluate, but it might be possible to use an update query for the whole process.

Roy-Vidar

 

[batteam]

Could you not just open both databases then do something like:

rst![ProgramNo].MoveFirst
Do Until rst![ProgramNo].EOF
ProgNo = rst![ProgramNo]
If rst2!ProgramNo = ProgNo then
ProgName = rst2!ProgramName
Else
End if
rst!ProgramNo.MoveNext
Loop

Maybe this example isn't quite what will work for you; maybe you need to iterate thru rst2 instead, but there should be a way, once both db's are open, to just loop thru with an IF test stmt and getting the ProgName you need.

Hope this gives you some rough ideas.

 

[batteam]

Regarding my last response, I meant to say " ...open both TABLES....", not databases. Sorry for any confusion this may have caused.