Solved

Help working in access 2007 vba environment

Posted on 2008-06-19
10
697 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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
 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to get all the API from website? 11 91
hit enter key to run macro 13 23
Reference Controls on subforms 7 28
backup programme - VBA 3 24
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

777 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