[AccessD] A2K: DlookUp in a For Next Loop

Darren,

First of all, I'd use a query instead of a DLookup.

But that aside, try this:

y  =  DLookup("[MyCriteria"  & x & "]", "tblOptions", "[OptionID] = 1")


Since it appears that all the data is from one table, why not try something
like:

*** WARNING:  This is untested air code.  But it should get you going on the
right track ***

dim db     as database
dim rs     as recordset
dim strSQL as string
dim x      as long

set db=currentdb()

strSQL="SELECT
MyCriteria1,MyCriteria2,MyCriteria3,MyCriteria4,MyCriteria5,MyCriteria6 " &
_
	"FROM tblOptions " & _
	"WHERE OptionID=1;"

'if you are returning all of the fields, you could use SELECT * FROM...
instead

set rs=db.openrecordset(strSQL,dbopensnapshot)

with rs
   if not .eof then
	for x=1 to 6
	   me("MyControl" & x) =  nz(rs("MyCriteria" & i))
	   next x
      endif
   .close
   end with

set rs=nothing
db.close
set db=nothing


Note that there is no error handling here.  I think that you will find the
perfomance much better not using the DLookup.

HTH,
Bobby



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK
Sent: Thursday, October 23, 2003 7:47 AM
To: AccessD List
Subject: [AccessD] A2K: DlookUp in a For Next Loop


Hello all
I have a table called tblOptions Int it are 6 fields each sequentially
numbered 
MyCriteria1,MyCriteria2,MyCriteria3,MyCriteria4,MyCriteria5,MyCriteria6

I have a form with 6 controls on it called MyControl1, MyControl2,MyControl3
etc. The form they are on is not bound in any way to the table tblOptions.

So what I wanted to do was a "for next" loop using DlookUp, but I can't get
it right. :-( In psuedo code I want something like...

Dim x, y
For x = 1 To 6
        y  =  DLookup("[MyCriteria & x]", "tblOptions", "[OptionID] = 1")
        Me.MyControl" & x =  y
Next

Of course this doesn't work and I know I could do soemthing like

Me.MyControl1 = DLookup("[MyCriteria1]", "tblOptions", "[OptionID] = 1")

Me.MyControl2 = DLookup("[MyCriteria2]", "tblOptions", "[OptionID] = 1")
Me.MyControl3 = DLookup("[MyCriteria3]", "tblOptions", "[OptionID] = 1")

etc but I wanted something neater

Many thanks in advance

Darren


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com