Advertisement

06.19.2008 at 12:08PM PDT, ID: 23500155
[x]
Attachment Details

Help working in access 2007 vba environment

Asked by prophet001 in Access Coding/Macros, Microsoft Access Database, Miscellaneous Programming

Tags: microsoft, access, 2007, vba

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 itStart Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:

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
[+][-]06.19.2008 at 02:07PM PDT, ID: 21826489

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.19.2008 at 02:22PM PDT, ID: 21826629

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.19.2008 at 02:37PM PDT, ID: 21826744

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.19.2008 at 02:44PM PDT, ID: 21826802

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.19.2008 at 02:52PM PDT, ID: 21826854

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.20.2008 at 06:25AM PDT, ID: 21830925

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.20.2008 at 07:38AM PDT, ID: 21831623

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.20.2008 at 10:36AM PDT, ID: 21833244

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Access Coding/Macros, Microsoft Access Database, Miscellaneous Programming
Tags: microsoft, access, 2007, vba
Sign Up Now!
Solution Provided By: influenz
Participating Experts: 1
Solution Grade: A
 
 
[+][-]06.20.2008 at 11:07AM PDT, ID: 21833463

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.20.2008 at 11:25AM PDT, ID: 21833620

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628