Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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.

    >
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page