Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

iterate record set

Avatar of Tom Crowfoot
Tom CrowfootFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessSQL
10 Comments1 Solution451 ViewsLast Modified:
Dear Experts

I have a piece of code which edits a query and report when a person is updated or added on my database (see code below). It effectively deletes the old query and report (if there is one) and inserts a new one.

What I would like to do is enable the user of the database to update the report template “Template” and then get vba to iterate through the records found in the form “Frm_Users” and run that piece of code against each

Can anybody help?

Private Sub Update_Click()
On Error Resume Next
DoCmd.DeleteObject acReport, Me.OldReport
DoCmd.DeleteObject acQuery, Me.OldQuery
DoCmd.Save
 Dim SQLstr As String
    SQLstr = "Select * from SinnersInputQuery " & "WHERE [Consultant] Like " & Chr(34) & [Cons] & Chr(34) & " OR [Created by] = " & Chr(34) & [FileFinder Login] & Chr(34)
    Dim db As DAO.Database
    Set db = CurrentDb
    db.CreateQueryDef Me.QryName, SQLstr
    Set db = Nothing

DoCmd.CopyObject , Me.RptName, acReport, "Template"
DoCmd.OpenReport Me.RptName, acViewDesign, , , acHidden
With Reports(Me.RptName)
    .RecordSource = Me.QryName
    .Tag = Me.Email
End With
DoCmd.Close acReport, Me.RptName, acSaveYes

MsgBox (Me.RptName & " is now updated")
DoCmd.Close
On Error GoTo Err_Command14_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Frm_Users"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command14_Click:
    Exit Sub

Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click
End Sub
ASKER CERTIFIED SOLUTION
Avatar of Simon Ball
Simon BallFlag of United Kingdom of Great Britain and Northern Ireland imageChief information Officer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 10 Comments.
See Answers