Solved

Help working in access 2007 vba environment

Posted on 2008-06-19
10
702 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 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