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
Solved

I have an Access Report and would like an ASP page to show the exact same thing

Posted on 2007-11-28
8
205 Views
Last Modified: 2008-02-01
I have an MS Access Project that connects to a SQL backend. In the Access program I have the attached query;

I have created a report that uses this query and I pass a parameter to it to filter the query by Workorder ID. The report works great, it will show the name of the person only once and list the activies on the workorder. This way i see the name once and the list of activities which could be a lot.

When I try to use this query in ASP I get a table that will not group by work order ID. Show the table shows the clients name for each activity ID.

I think the problem is in my JOINS, but am not sure.
SELECT     dbo.tblWorkOrders.ID AS [Work Order ID], dbo.tblCustomer.FirstName AS CFName, dbo.tblCustomer.LastName AS CLName, 
                      dbo.tblCustomer.BusinessName AS CBName, dbo.tblCustomer.DaytimePhone AS CPhone, dbo.tblCustomer.MobilePhone AS CMPhone, 
                      dbo.tblCustomer.Address01 AS CAddress1, dbo.tblCustomer.Address02 AS CAddress2, dbo.tblCustomer.City AS CCity, dbo.tblCustomer.State AS CState, 
                      dbo.tblCustomer.Zip AS CZip, dbo.tblCustomer.EmailAddress AS CEmail, dbo.lstStatus.Status, dbo.lstServiceTypes.ServiceType, 
                      dbo.lstPriority.Priority, dbo.lstComputerManufacturers.ComputerManufacturer, dbo.lstComputerTypes.ComputerType, dbo.tblActivity.ID AS ActID, 
                      dbo.tblTechnician.FirstName AS TFName, dbo.tblActivity.Date AS ActDate, dbo.tblActivity.Time AS ActTime, dbo.tblActivity.Activity, 
                      dbo.tblWorkOrders.DateClosed
FROM         dbo.lstComputerTypes RIGHT OUTER JOIN
                      dbo.tblWorkOrders ON dbo.lstComputerTypes.ID = dbo.tblWorkOrders.ComputerType LEFT OUTER JOIN
                      dbo.tblCustomer ON dbo.tblWorkOrders.CustomerID = dbo.tblCustomer.ID LEFT OUTER JOIN
                      dbo.lstPriority ON dbo.tblWorkOrders.Priority = dbo.lstPriority.ID LEFT OUTER JOIN
                      dbo.lstStatus ON dbo.tblWorkOrders.Status = dbo.lstStatus.ID LEFT OUTER JOIN
                      dbo.lstServiceTypes ON dbo.tblWorkOrders.ServiceType = dbo.lstServiceTypes.ID LEFT OUTER JOIN
                      dbo.lstComputerManufacturers ON dbo.tblWorkOrders.Manufacturer = dbo.lstComputerManufacturers.ID RIGHT OUTER JOIN
                      dbo.tblTechnician RIGHT OUTER JOIN
                      dbo.tblActivity ON dbo.tblTechnician.ID = dbo.tblActivity.TechnicianID ON dbo.tblWorkOrders.ID = dbo.tblActivity.WorkOrderID

Open in new window

0
Comment
Question by:mdefalco
  • 4
  • 3
8 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20367198
spaces   access on the web table, column names must not have spaces
0
 
LVL 15

Expert Comment

by:JimFive
ID: 20385370
To limit to a work order you need to add a WHERE clause to the query.

i.e. WHERE tbl.WorkOrders.ID = '12345'

--
JimFive
0
 
LVL 4

Author Comment

by:mdefalco
ID: 20386275
Yeah, what I do for that is this;

In my access application I have a listbox that the user selects which work order he wants to email. When they click on the work order in the listbox it populates a global parameter I have, G_WOID. Then is uses the Follow.Hyperlink code to go to an asp page I have that will run this query and pull the data.

This works real well, but the problem I have is that if there are more than one activity for the workorder, then two emails will send. I want the email to send once per work order with all activities in the body of the email.

I use ASP because I want the email sent without needing an outlook profile to send the email.

Thanks,

jim
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:JimFive
ID: 20395217
I assume that you are looping through the query results to build the email.

If this is the case, then you will need to save the work order ID, check it on each loop, and only start a new email when the ID changes.

Your loops should look something like this:

while not rs.eof()
  if rs(workorderid) <> savedworkorderid then ' New workorder
      Close and send existing mail message
      savedworkorderid = rs(workorderid)
      create new mail message
      fill in workorder info
  end if
  Add line item info to email
  rs.movenext
wend

--
JimFive
0
 
LVL 15

Expert Comment

by:JimFive
ID: 20395225
Two issues I forgot to mention

1.  Make sure your query is sorted by workorderid (ORDER BY WorkorderID)

2.  The reason the access report is working is because the access report engine is doing all of grouping and looping for you.

--
JimFive
0
 
LVL 4

Author Comment

by:mdefalco
ID: 20416951
Cool, I am  a technician who is always on the road, so I will try this in the next couple days. I think you are correct though, thanks!
0
 
LVL 4

Author Comment

by:mdefalco
ID: 20419636
Here is my asp code,
My query selects only the work order ID I pass through the URL, I looked at your suggestion, but I can not figure out exactly what I should put in for code. So my main goal is to only send one email, but have the all the activities. I guess I don't know how to group the activities,

Thanks in advance.

Here is the code I use in Access to pass the WOID to the ASP PAge;

    Dim strInput As String
    Dim strWOID As String
   
    strWOID = Me.List63.Value
   
    strInput = "http://192.168.142.224/email/sendemail.asp?woid="
    strInput = strInput & strWOID
    Application.FollowHyperlink strInput, , True


<!-- #include virtual="email/conn.asp" -->
 
<% Server.ScriptTimeout =7200%>
 
<%
 
Dim strWoID
 
strWoid = Trim(request("woid"))
 
%>
 
<%
 
Dim strSQL
 
Dim rs
Dim rs_numRows
 
strSQL = "SELECT * FROM qryWFP WHERE [Work Order ID] = " & strWOID
 
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = strCONN
rs.Source = strSQL
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()
rs_numRows = 0
%>
 
<%
Function SndEmail(strSubject,strBody)
 
	Set objCDOSYSMail = Server.CreateObject("CDO.Message")
	Set objCDOSYSCon = Server.CreateObject ("CDO.Configuration")
	
	objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "x.x.x.x"
	objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
	objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/sendusername") = "EMAIL ADDRESS"
	objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "PASSWORD"
	objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
	objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
	objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 1
	objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
	objCDOSYSCon.Fields.Update
	
	Set objCDOSYSMail.Configuration = objCDOSYSCon
	objCDOSYSMail.From = "EMAIL ADDRESS"
	objCDOSYSMail.To = "EMAIL ADDRESS"
	objCDOSYSMail.Subject = strSubject
	objCDOSYSMail.HTMLBody = strBody
	objCDOSYSMail.Send
	Set objCDOSYSMail = Nothing
	Set objCDOSYSCon = Nothing
	
	End Function
 
Dim strSubject
Dim strBody
Dim strFullName
 
 
 
 
 
	
	do while not rs.eof
 
	strFullName = rs("CFName") & " " & rs("CLName")
	
    strSubject = "Work Order ID " & rs("Work Order ID") & " is Waiting for Payment"	
 
	strBody = "Name: " & strFullName & vbcrlf
	strBody = strBody & "Business Name: " & rs("CBName") & vbcrlf
	
 
	Call SndEmail(strSubject,strBody)
	rs.movenext
loop
 
Response.Redirect("success.htm")
 
%>

Open in new window

0
 
LVL 15

Accepted Solution

by:
JimFive earned 500 total points
ID: 20420227
At line 68, you want to move your do while loop to loop around the items that you want to repeat:

Move the do while down to line 73
Change line 74 to be strBody = strBody & "Name: " & strFullName & vbcrlf  <---- this builds the email body
Move line 78 outside of the loop to line 81  <----- This waits to send the email until after all the rows have been processed.
--
JimFive
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

808 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