We help IT Professionals succeed at work.

If statements inside record sets

SweetingA
SweetingA asked
on
367 Views
Last Modified: 2012-02-15
Dear Experts,

How can i alter the code below so that when x = 0 addrecord but when x <> 0 move to next record.

When i have tried adding if staements the recordset stops copying multiple records and only copies onerecord.

PS...Off to bed  now so plenty of time to answer, thanks

----------

Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM qry_PDM_Questions WHERE " & Me.Filter)
    rst.MoveFirst
   
    Dim rstADD As DAO.Recordset
    Set rstADD = CurrentDb.OpenRecordset("tbl_PDM_Results")

    Dim x As Long

    Do Until rst.EOF
        strSaveCriteria = Me.txtPeriod & Me.txtSite & Me.txtFunction & Me.txtCell & Me.Question
        x = DCount("*", "tbl_PDM_Results", "[SaveCriteria]=" & Chr$(34) & strSaveCriteria & Chr$(34))
            rstADD.AddNew
            rstADD("Period") = Me.txtPeriod
            rstADD("Site") = Me.txtSite
            rstADD("Function") = rst("Function")
            rstADD("Cell") = Me.txtCell
            rstADD("Question") = rst("Question")
            rstADD("Answer") = rst("Answer")
            rstADD("SaveCriteria") = strSaveCriteria
            rstADD.Update
            rst.MoveNext
    Loop

    rst.Close
    rstADD.Close
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM qry_PDM_Questions WHERE " & Me.Filter)
    rst.MoveFirst
   
    Dim rstADD As DAO.Recordset
    Set rstADD = CurrentDb.OpenRecordset("tbl_PDM_Results")

    Dim x As Long

    Do Until rst.EOF
        strSaveCriteria = Me.txtPeriod & Me.txtSite & Me.txtFunction & Me.txtCell & Me.Question
        x = DCount("*", "tbl_PDM_Results", "[SaveCriteria]=" & Chr$(34) & strSaveCriteria & Chr$(34))

if x=0 then
            rstADD.AddNew
            rstADD("Period") = Me.txtPeriod
            rstADD("Site") = Me.txtSite
            rstADD("Function") = rst("Function")
            rstADD("Cell") = Me.txtCell
            rstADD("Question") = rst("Question")
            rstADD("Answer") = rst("Answer")
            rstADD("SaveCriteria") = strSaveCriteria
            rstADD.Update
end if
            rst.MoveNext
    Loop

    rst.Close
    rstADD.Close

Author

Commented:
Hello Capricorn1,

Thats what i have tried.

When i do that strSaveCriteria sets once and never updates with chnaging records, therefore only 1 record is ever copied to the table.

thanks
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Wondering why you started a new question?  This is virtually identical to the one we were already working on.

Maybe if we stepped back a bit and you decribed what you expect to happen and what the situation is or pointed us to an old question that covered that, we could make more sense of this and give you a little more direction.

As I said in the other question, the code was correct as far as it went, but obviously it's not doing what you think it should be doing.

Jim.
CERTIFIED EXPERT
Top Expert 2016

Commented:
change this

       strSaveCriteria = Me.txtPeriod & Me.txtSite & Me.txtFunction & Me.txtCell & Me.Question

with

        strSaveCriteria = Me.txtPeriod & Me.txtSite & Me.txtFunction & Me.txtCell & rst!Question
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Perfect as usual, thankyou
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.