Re: Loop through names of controls

From: MikeB (m.byerleyATVerizonDottieNettie)
Date: 10/17/04 

Date: Sun, 17 Oct 2004 15:47:36 -0500

oops. Looked too quick. Didn't scroll down to the string of the sql. I
thought he was having trouble getting an object in his loop.

"Dan Artuso" <dartuso@NSpagepearls.com> wrote in message
news:ubBJhjItEHA.2072@tk2msftngp13.phx.gbl...
> Hi,
> This is how you would refer to a control in your loop:
> stEstCode = Me("cboEstCode" & intEstCode)
>
> AND
>
> stCands = Me("txtCands" & intCands)
>
>
>
> --
> HTH
> Dan Artuso, Access MVP
>
>
> "Baby Face Lee" <BabyFaceLee@discussions.microsoft.com> wrote in message
news:BB6D7288-FB04-4D9F-9A6A-709AA548E95F@microsoft.com...
> > Hello (again)
> > Sorry to keep bothering you guys but I'm having probs trying to do a loop.
> > I've got a control (well two infact) that each have the same name except for
> > a number at the end (eg cboEstCode1 to cboEstCode12) - there are 12 of them.
> > I'm running some SQL to insert the value in each control into a table. I
> > thought I'd do a For/Next loop that goes round 12 times adding the digit to
> > the end of the control name but the SQL is taking the string values
literally
> > (not surprisingly) rather than finding the value that's in the name of the
> > control.
> > Here's my code:
> >
> > Set db = DBEngine(0)(0)
> >
> > For intEstCode = 1 To 12
> > stEstCode = "cboEstCode" & intEstCode
> > For intCands = 1 To 12
> > stCands = "txtCands" & intCands
> >
> > strSQL = "INSERT INTO tbl_Estimates (CentreNo, EstCode, Cands)" _
> > & " VALUES (" & Me!cboCentreNo & ", " & "'" & stEstCode & "'" & ", "
> > & stCands & ");"
> > db.Execute strSQL
> > Exit For
> > Next
> > Next
> >
> > I know this is not the way a form should be constructed but I'm trying to
> > 'push' the data into a table to improve speed and a continuous form doesn't
> > work because being Unbound it doesn't hold more than one record at a time.
> > Sorry for all the waffle but can you help with this? Have I got the loop
> > structure right too?
> > Thanks guys!
> > Lee
>
>

Relevant Pages

  • Re: Simple Insert Into...
    ... You can store unbound control data in a variable, ... in the sql. ... Dim strSQL As String ... In your form's Sub, you'll wind up with something like the following: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Simple Insert Into...
    ... The form is based on an SQL in the form's Record Source property, ... control on the form with the resulting value as a bound control. ... Sometimes the unbound controls do have a value (such as when they are set ... Dim strVar as String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Sql code in access form in adp
    ... control after the name of the form got me distracted. ... quote ' because they are probably string characters from their name. ... Sylvain Lafontaine, ing. ... Dim SQL As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: passing NUll value in SQL statement
    ... indicate that the word NULL should be entered in the INSERT INTO string. ... "value," which can't be placed in the SQL string you are building. ... So I was supposing that since my code referes to values of form's controls and since eptime control as been left blank it will pass Null to my code, so its like I have wrote ... check the Required property of the column EPTIME. ...
    (microsoft.public.access.queries)
  • Re: Loop through names of controls
    ... Dan Artuso, Access MVP ... Didn't scroll down to the string of the sql. ... > thought he was having trouble getting an object in his loop. ... >>> I'm running some SQL to insert the value in each control into a table. ...
    (microsoft.public.access.formscoding)