[AccessD] A2K: DlookUp in a For Next Loop

Jim Dettman jimdettman at earthlink.net
Thu Oct 23 07:44:11 CDT 2003


Darren,

It would look like this:

Dim lngLoop
Dim strFieldName as string

For lngLoop = 1 To 6

  strFieldName = "[MyCriteria" & lngLoop & "]"
  Me("MyControl" & lngLoop) =  DLookup(strFieldName, "tblOptions",
"[OptionID] = 1")

Next

  But really any time you pull more then 2 or 3 fields with DLookup from the
same record, it will be faster to open the recordset, seek to the record,
then pull all the fields at one time.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----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




More information about the AccessD mailing list