Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Loop through a continuous form changing Yes/Know Ctls. to Yes.

Discussion in 'Information Technology' started by Buddy, Nov 8, 2005.

  1. Buddy

    Buddy
    Expand Collapse
    Guest

    I have a continuous form based on a select query. The query selects all
    records based on a yes/no field. I want to put a button that will first print
    out a saved report and then change the value of each record yes/no field to
    yes.

    I have tried the following but it only changes the first record:


    For Each ctlCurr In Me.Controls
    If TypeOf ctlCurr Is CheckBox Then
    ctlCurr = True
    End If
    Next ctlCurr

    DoCmd.PrintOut

    On the DoCmd I want to be able to print out a report I have.
     
  2. Loading...


  3. Ofer

    Ofer
    Expand Collapse
    Guest

    The code you are using will update only the current record, as you have seen.
    To update all the records use an update query

    Docmd.RunSql "UPDATE TableName SET TableName.FieldName = True"

    You probably need to add a criteria to the SQL, so you wont update the all
    table.

    And then open the report using

    Docmd.OpenReport "ReportName"

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Buddy" wrote:

    > I have a continuous form based on a select query. The query selects all
    > records based on a yes/no field. I want to put a button that will first print
    > out a saved report and then change the value of each record yes/no field to
    > yes.
    >
    > I have tried the following but it only changes the first record:
    >
    >
    > For Each ctlCurr In Me.Controls
    > If TypeOf ctlCurr Is CheckBox Then
    > ctlCurr = True
    > End If
    > Next ctlCurr
    >
    > DoCmd.PrintOut
    >
    > On the DoCmd I want to be able to print out a report I have.
     
  4. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    One way is to SAVE the current record,
    use an update query to change the records,
    then REQUERY to show the new values.

    You have to be a bit careful doing this, because
    if you update a record both from the form and
    from a query, Access won't know which value to
    use, and will ask you if you want to save of
    discard the value you have typed, or will fail
    on the update query.

    The other way to do it involves more code and
    is much slower, but does not have any locking
    problems:

    set rs = me.recordsetclone

    if not rs.eof then rs.movefirst
    while not rs.eof
    rs.edit
    rs!yesnofield = true
    rs.update
    rs.movenext
    wend

    (david)


    "Buddy" <Buddy@discussions.microsoft.com> wrote in message
    news:4A54E744-5892-46AA-992F-5D0D586359B8@microsoft.com...
    >I have a continuous form based on a select query. The query selects all
    > records based on a yes/no field. I want to put a button that will first
    > print
    > out a saved report and then change the value of each record yes/no field
    > to
    > yes.
    >
    > I have tried the following but it only changes the first record:
    >
    >
    > For Each ctlCurr In Me.Controls
    > If TypeOf ctlCurr Is CheckBox Then
    > ctlCurr = True
    > End If
    > Next ctlCurr
    >
    > DoCmd.PrintOut
    >
    > On the DoCmd I want to be able to print out a report I have.
     
  5. Buddy

    Buddy
    Expand Collapse
    Guest

    Thanks Ofer, your way works much better. Again thanks.

    "Ofer" wrote:

    > The code you are using will update only the current record, as you have seen.
    > To update all the records use an update query
    >
    > Docmd.RunSql "UPDATE TableName SET TableName.FieldName = True"
    >
    > You probably need to add a criteria to the SQL, so you wont update the all
    > table.
    >
    > And then open the report using
    >
    > Docmd.OpenReport "ReportName"
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "Buddy" wrote:
    >
    > > I have a continuous form based on a select query. The query selects all
    > > records based on a yes/no field. I want to put a button that will first print
    > > out a saved report and then change the value of each record yes/no field to
    > > yes.
    > >
    > > I have tried the following but it only changes the first record:
    > >
    > >
    > > For Each ctlCurr In Me.Controls
    > > If TypeOf ctlCurr Is CheckBox Then
    > > ctlCurr = True
    > > End If
    > > Next ctlCurr
    > >
    > > DoCmd.PrintOut
    > >
    > > On the DoCmd I want to be able to print out a report I have.
     
  6. Buddy

    Buddy
    Expand Collapse
    Guest

    Thanks David,

    With your code it is doing what I intended. Again, Thanks.

    "david epsom dot com dot au" wrote:

    > One way is to SAVE the current record,
    > use an update query to change the records,
    > then REQUERY to show the new values.
    >
    > You have to be a bit careful doing this, because
    > if you update a record both from the form and
    > from a query, Access won't know which value to
    > use, and will ask you if you want to save of
    > discard the value you have typed, or will fail
    > on the update query.
    >
    > The other way to do it involves more code and
    > is much slower, but does not have any locking
    > problems:
    >
    > set rs = me.recordsetclone
    >
    > if not rs.eof then rs.movefirst
    > while not rs.eof
    > rs.edit
    > rs!yesnofield = true
    > rs.update
    > rs.movenext
    > wend
    >
    > (david)
    >
    >
    > "Buddy" <Buddy@discussions.microsoft.com> wrote in message
    > news:4A54E744-5892-46AA-992F-5D0D586359B8@microsoft.com...
    > >I have a continuous form based on a select query. The query selects all
    > > records based on a yes/no field. I want to put a button that will first
    > > print
    > > out a saved report and then change the value of each record yes/no field
    > > to
    > > yes.
    > >
    > > I have tried the following but it only changes the first record:
    > >
    > >
    > > For Each ctlCurr In Me.Controls
    > > If TypeOf ctlCurr Is CheckBox Then
    > > ctlCurr = True
    > > End If
    > > Next ctlCurr
    > >
    > > DoCmd.PrintOut
    > >
    > > On the DoCmd I want to be able to print out a report I have.

    >
    >
    >
     

Share This Page