Solved

Help working in access 2007 vba environment

Posted on 2008-06-19
10
690 Views
Last Modified: 2013-11-27
Hi,
  I am designing a database in access 2007 that creates timesheets based on time entered into the database. I have created a form that will allow for a report to be generated for each technician selected on the form. The way i am making this happen is by opening the template report in acviewreport and changing the recordsource property to a query  with the current tech as a WHERE filter. The code loops through all techs showing their timesheet reports in acviewpreview then going back and changing the query for the next tech in acviewreport and re querying. So far, it is going pretty good. However, i have a question. First, is this the best way to handle this problem? Second, I have to control the pace of the code with a msgbox saying "click ok for next". Is there a way to have the code wait for some event within the print preview screen? eg:
Currently the code opens the report in acviewreport with .visible = false and sets the recordsource for tech 1 then requeries. Next it opens that in acviewpreview. After the msgbox is clicked (ok) it goes back to acviewreport, changes the recordsource and requeries and again opens the report in acviewpreview. it continues this logic until the last tech is reached and then sits in acviewpreview. Would it be possible to have the report do essentially the same steps but have the code wait for some event in print preview such as "exit print preview" or "print" and then go to the next tech in acviewpreview again?
the message box seems kind of out of place and disruptive. Thank you for any help you can give. I will attach a code snippet with my sub on it
Private Sub Command8_Click()

  Dim cnn1 As ADODB.Connection

  Dim myRecordSet As New ADODB.Recordset

  Dim mySQLtech As String

  Dim varTechName As String

  Dim datWeekEnd As Date

  

  'create connection

  Set cnn1 = CurrentProject.Connection

  

  'create recordset

  myRecordSet.ActiveConnection = cnn1

  

  'create SQL string to select only desired technicians

  Dim mySQL As String

  mySQL = "SELECT [tblTechs].[techID], [tblTechs].[First Name], [tblTechs].[Last Name] FROM tblTechs"

  mySQL = mySQL & " WHERE tblTechs.CreateTimeSheet = True"

  

  'open recordset with desired technician ids included

  myRecordSet.Open Source:=mySQL, CursorType:=adOpenStatic

  

  'check for error condition and exit sub if error will result

  If myRecordSet.RecordCount = 0 Then

    MsgBox "No technicians are selected", vbOKOnly

    Exit Sub

  End If

  

  'get date from form and decide whether or not to filter

  'on the date in the txtbox

  

  If IsNull([Forms]![form1].[txtWeekEnd]) Then

     strtext41 = "All unpaid time records"

     mySQLtech = "SELECT [tblSvcOrdersDet].[DateWorked], [tblSvcOrdersDet].[SvcOrder], " _

     & "[tblSvcOrders].[ClassID], [tblSvcOrders].[Customer], [tblSvcOrdersDet].[Reg Hours], " _

     & "[tblSvcOrdersDet].[OT Hours], [tblSvcOrders].[JobNumber], [tblSvcOrders].[SODate], [tblSvcOrdersDet].[tech] " _

     & "FROM tblSvcOrders INNER JOIN tblSvcOrdersDet ON tblSvcOrders.SvcOrder=tblSvcOrdersDet.SvcOrder " _

     & "WHERE [tblSvcOrdersDet].[tech] = "

  Else

     datWeekEnd = [Forms]![form1].[txtWeekEnd]

     strtext41 = "Timesheets for the week ending " & Format(datWeekEnd, "dddd mmm d, yyyy")

     mySQLtech = "SELECT [tblSvcOrdersDet].[DateWorked], [tblSvcOrdersDet].[SvcOrder], " _

     & "[tblSvcOrders].[ClassID], [tblSvcOrders].[Customer], [tblSvcOrdersDet].[Reg Hours], " _

     & "[tblSvcOrdersDet].[OT Hours], [tblSvcOrders].[JobNumber], [tblSvcOrders].[SODate], [tblSvcOrdersDet].[tech] " _

     & "FROM tblSvcOrders INNER JOIN tblSvcOrdersDet ON tblSvcOrders.SvcOrder=tblSvcOrdersDet.SvcOrder " _

     & "WHERE [tblSvcOrdersDet].[DateWorked] BETWEEN #" & CDate(DateAdd("d", -6, datWeekEnd)) & "# AND #" _

     & CDate(datWeekEnd) & "# And [tblSvcOrdersDet].[tech] = "

  End If

 

 'MsgBox mySQLtech

  

  

  For i = 0 To myRecordSet.RecordCount - 1

    DoCmd.OpenReport "tblsvcordersdet", acViewReport

    Reports("tblsvcordersdet").Visible = False

    varTechName = myRecordSet.Fields(1) & " " & myRecordSet.Fields(2)

    Reports("tblsvcordersdet").RecordSource = mySQLtech & myRecordSet.Fields(0) & ";"

    Reports("tblsvcordersdet").txtTechname.ControlSource = "=DLookup(""[First Name]"", " _

     & """[tblTechs]"", ""[techid]=" & myRecordSet.Fields(0) & """) & "" "" & DLookup(""[Last Name]"", " _

     & """[tblTechs]"", ""[techid]=" & myRecordSet.Fields(0) & """)"

    Reports("tblsvcordersdet").Requery

    

    DoCmd.OpenReport "tblsvcordersdet", acViewPreview

    

    If myRecordSet.RecordCount - i - 1 <> 0 Then

     msgresult = MsgBox("OK for next technician. Cancel to exit preview.", vbOKCancel)

     If msgresult = 2 Then

       DoCmd.Close acReport, "tblsvcordersdet"

       Exit For

     End If

    myRecordSet.MoveNext

    End If

    

  Next

  

  'remove used objects from memory

  Set myRecordSet = Nothing

  Set cnn1 = Nothing

  'If IsOpen("tblsvcordersdet", acReport) Then DoCmd.Close acReport, "tblsvcordersdet"

End Sub

Open in new window

0
Comment
Question by:prophet001
  • 6
  • 4
10 Comments
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21826489
To replace the If statement / MsgBox check..

Change:
     DoCmd.OpenReport "tblsvcordersdet", acViewPreview
To:
     DoCmd.OpenReport "tblsvcordersdet", acViewPreview, , , acDialog

And:
In the report properties, set "Modal" to "Yes"


This should pause the code until the report is closed manually.
0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21826629
Another way to approach the recordsource/requery bit would be to bind the report to a seperate query once, then change the sql of the query in your code instead of changing the report.  
Something like..

Dim db As Database
Dim qds As QueryDefs
Dim qd As QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qds = db.QueryDefs
Set qd = qds("Query1")
strSQL = "SELECT whatever.."

qd.SQL = strSQL


You would need to do something different to fill txtTechname though, maybe have a subform pull that data from another query.
0
 

Author Comment

by:prophet001
ID: 21826744
hmm i thought that would work but for some reason i am having problems. the report opens but doesn't stop or wait for a close window to print button to be clicked. it just generates all of the reports in sequence (without painting them) and then paints the last one. also, the application stays locked up if i am not able to click the x to close the report.. maybe i have messed something up. all i did was what you said in the first arguement.
also, i uncommented the last line which programmatically closes the report (probably why it's locking up) and haven't seen any other problems. also, tried with and without the popup set to yes
0
 

Author Comment

by:prophet001
ID: 21826802
setting it to visible i can watch it generate a report for every technician and then stop on the last one. for some reason it's not triggering a stop. maybe because we are programmatically opening the report? not sure. if you would like i can email you a copy of the DB that you can play with. it only has one linked table but that linked table is replicated into an access table so you should be able to manipulate it. thank you for your help :)
Preston
For i = 0 To myRecordSet.RecordCount - 1

    

    DoCmd.OpenReport "tblsvcordersdet", acViewReport

    

    'Reports("tblsvcordersdet").Visible = False

    varTechName = myRecordSet.Fields(1) & " " & myRecordSet.Fields(2)

    Reports("tblsvcordersdet").RecordSource = mySQLtech & myRecordSet.Fields(0) & strPay & ";"

    Reports("tblsvcordersdet").txtTechname.ControlSource = "=DLookup(""[First Name]"", " _

     & """[tblTechs]"", ""[techid]=" & myRecordSet.Fields(0) & """) & "" "" & DLookup(""[Last Name]"", " _

     & """[tblTechs]"", ""[techid]=" & myRecordSet.Fields(0) & """)"

    Reports("tblsvcordersdet").Requery

    

    DoCmd.OpenReport "tblsvcordersdet", acViewPreview, windowmode:=acDialog

    

    'If myRecordSet.RecordCount - i - 1 <> 0 Then

     'msgresult = MsgBox("OK for next technician. Cancel to exit preview.", vbOKCancel)

     'If msgresult = 2 Then

       'DoCmd.Close acReport, "tblsvcordersdet"

       'Exit For

     'End If

    myRecordSet.MoveNext

    'End If

    

  Next

Open in new window

0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21826854
I think I see it..

When you first open the report, it does not use windowmode:=acDialog, you don't close it again, it is just set to visible = false so you need to either close it after requerying or the preferred option is to add windowmode:=acDialog to the first DoCmd.OpenReport and replace the second one altogether with..
Reports("tblsvcordersdet").Visible = True
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:prophet001
ID: 21830925
i can't get it to work. there is another problem that i run up against. you cannot change the properties of the report while it is in print preview mode. so i am trying to open it in report mode, change the properties, switch to preview mode, then close it then reopen it in report mode, then change the properties again, then open it in preview mode. i think i am doing this in a dumb also, i haven't seen any behavior yet where the program waits for me in preview or report mode because of it being set to modal and acdialog. it just blows through all of the techs and then stops on the last one like it was doing before. thank you for your help on this but the problem is still open :(
0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21831623
Ok it sounds like this is spiralling a bit.  Shall we go with your suggestion of sending me a copy of the file so i can try it in your environment?  My email is..    dubmofiz [at] hotmail [dot] com
0
 
LVL 5

Accepted Solution

by:
Steve Dubyo earned 500 total points
ID: 21833244
I've tried that out and goty it working at this end.  The For loop now looks like this...

does that work for you ?

  For i = 0 To myRecordSet.RecordCount - 1

  

    DoCmd.OpenReport "tblsvcordersdet", acViewDesign

     

    Reports("tblsvcordersdet").Visible = False

    Reports("tblsvcordersdet").RecordSource = mySQLtech & myRecordSet.Fields(0) & strPay & ";"

    Reports("tblsvcordersdet").txtTechName.ControlSource = "=DLookup(""[First Name]"", " _

     & """[tblTechs]"", ""[techid]=" & myRecordSet.Fields(0) & """) & "" "" & DLookup(""[Last Name]"", " _

     & """[tblTechs]"", ""[techid]=" & myRecordSet.Fields(0) & """)"
 

    DoCmd.Close acReport, "tblsvcordersdet", acSaveYes

   

    DoCmd.OpenReport "tblsvcordersdet", acViewPreview, windowmode:=acDialog

    

    myRecordSet.MoveNext
 

  Next

Open in new window

0
 

Author Comment

by:prophet001
ID: 21833463
thank you very much for your help. it does work. i think the key is that you close and save it before you reopen it in acDialog. this is the step that i was missing. thank you very much for your help. :)
0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21833620
That's right, but had to use acViewDesign which meant removing the Requery stage.

You are welcome.  Good luck ;-]
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This is about my first experience with programming Arduino.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now