[AccessD] A2K: DlookUp in a For Next Loop

Hi

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

Erwin

-----Oorspronkelijk bericht-----
Van: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] Namens Darren DICK
Verzonden: donderdag 23 oktober 2003 13:47
Aan: AccessD List
Onderwerp: [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