Solved

Help working in access 2007 vba environment

Posted on 2008-06-19
10
693 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

910 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

21 Experts available now in Live!

Get 1:1 Help Now