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