Hi there,
I have 2 parameters that I need to group by... by Install SP and by Line item. I want all Line Items that have are linked to an Install SP to be grouped together in the email. I am able to get the data grouped but the Install SP is duplicated in the output.
An example of what I want is:
SP#: 8001194220 SOI 03/24/2007
Line Items
40 1 015-00461A Part A
60 1 015-00366A Part B
SP #8001194221 SOI 03/29/2007
Line Items
30 1 015-00369A Part E
70 1 015-00367A Part C
90 1 015-00368A Part D
Below is the code I've used. I'm pretty sure the problem is where I used the statement "if strCurrentInstall SP <> strPreviousInstallSP, then". Somehow I have to put these into groupings. Any help would be great as I'm at a complete loss as to what to do next! OH! I should mention, I'm new to VBA
Private Sub cmdCOIRequest_Click()
Dim SQL As String
Dim strCriteria As String
Dim intResponse As Integer
'Create criteria to filter the Transaction Number
strCriteria = "tblHeader.[Transaction Number]=" & Forms!frmIP_Planning![Transaction Number]
'Create the select statement to find all records with the specific Transaction Number
'changed statement to take SOI date from Access
SQL = "SELECT tblHeader.[Transaction Number], tblHeader.BP, tblHeader.[Ship-to Party], tblHeader.[CAM: Street]," _
& "tblHeader.[CAM: District], tblHeader.Region, tblDetails.[Installation Order Number]," _
& "tblDetails.[Item Number], tblDetails.[Order Quantity], tblDetails.Material," _
& "tblDetails.[Material Desc from SO line] , tblInstallSummary.SOI" _
& " FROM (tblDetails INNER JOIN tblHeader ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number])" _
& " LEFT JOIN tblInstallSummary ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1" _
& " WHERE " & strCriteria & " ORDER BY tblDetails.[Item Number];"
intResponse = MsgBox("Do you wish to send the COI Request?", vbYesNo, "COI Request")
If intResponse = vbYes Then
Call GetMailMessage(SQL)
End If
End Sub
Private Sub GetMailMessage(SQL As String)
Dim rst As ADODB.Recordset
Dim conn As ADODB.Connection
Dim strMail As String
Dim strTemp As String
Dim blNewInstallSP As Boolean
Dim strCurrentInstallSP As String
Dim strPreviousInstallSP As String
Dim strNextInstallSP As String
Dim strCustomer As String
Dim lngSO As Long
Dim strSOI As String
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
'Open the recordset
rst.Open SQL, conn, adOpenKeyset, adLockOptimistic
'Move to the top of the recordset
rst.MoveFirst
strCustomer = rst![Ship-to Party]
lngSO = rst![Transaction Number]
' strSOI = rst![SOI]
strPreviousInstallSP = ""
strMail = ""
strTemp = ""
'Loop through the recordset and create the Install SP List
Do Until rst.EOF
'Assign variable to current Install SP
strCurrentInstallSP = rst![Installation Order Number]
'Create header for new Install SP
If strCurrentInstallSP <> strPreviousInstallSP Then
strTemp = vbCr & "SP #: " & rst![Installation Order Number] & " " & " SOI " & rst![SOI] & vbCr _
& "LINE ITEMS" & vbCr
End If
'Identify line numbers for each Install SP
strTemp = strTemp & rst![Item Number] & Chr(9) & rst![Order Quantity] & Chr(9) _
& rst![Material] & Chr(9) & rst![Material Desc from SO line] & vbCr
' strTemp = strTemp & rst![Item Number] & Chr(9) & rst![Material] & Chr(9) & rst![Material Desc from SO line] & vbCr
'Create listing
strMail = strMail & strTemp
strTemp = ""
strPreviousInstallSP = strCurrentInstallSP
rst.MoveNext
Loop
'Output the information to an email message
rst.MoveFirst
DoCmd.SendObject , , , , , DLookup("IPPlanner", "tblTempIPPlanner"), "COI Confirmation Request - " & strCustomer & " " & "SO " & lngSO, _
"Sales Order " & rst![Transaction Number] _
& vbCr & "BP #: " & rst!BP _
& vbCr & "Site: " _
& rst![Ship-to Party] & " " & rst![CAM: District] & ", " & rst![Region] _
& vbCr & vbCr _
& "Our records indicate this order is likely completed." _
& " Please confirm back with the DATE OF COMPLETION for the following." _
& " If it is not complete, please provide expected date of completion. " _
& vbCr & vbCr & strMail & vbCr & vbCr _
& "Your confirmation is required to meet our accounting obligations." _
& vbCr & vbCr & "Thank you.", True
'Close the recordset and connection
rst.Close
conn.Close
Set conn = Nothing
Set rst = Nothing
End Sub
Private Sub cmdCOIRequestSpecific_Click()
Dim SQL As String
Dim strCriteria As String
Dim intResponse As Integer
'Create criteria to filter the Transaction Number
strCriteria = "[Installation Order Number]='" & Forms!frmInstallSPList!frmInstallSPListSubform.Form![Installation Order Number] & "'"
'Create the select statement to find all records with the specific Transaction Number
SQL = "SELECT tblHeader.[Transaction Number], tblHeader.BP, tblHeader.[Ship-to Party], tblHeader.[CAM: Street]," _
& "tblHeader.[CAM: District], tblHeader.Region, tblDetails.[Installation Order Number]," _
& "tblDetails.[Item Number], tblDetails.[Order Quantity], tblDetails.Material," _
& "tblDetails.[Material Desc from SO line] , tblInstallSummary.SOI" _
& " FROM (tblDetails INNER JOIN tblHeader ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number])" _
& " LEFT JOIN tblInstallSummary ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1" _
& " WHERE " & strCriteria & " ORDER BY tblDetails.[Item Number];"
' SQL = "SELECT tblHeader.[Transaction Number], tblDetails.[Installation Order Number]," _
' & "tblDetails.[Item Number], tblHeader.BP, tblDetails.[Order Quantity], tblDetails.Material, tblDetails.[Line SOI Date]," _
' & "tblDetails.[Material Desc from SO line], tblHeader.[Ship-to Party]," _
' & "tblHeader.[CAM: Street], tblHeader.[CAM: District], tblHeader.Region" _
' & " FROM tblHeader INNER JOIN tblDetails ON tblHeader.[Transaction Number] = tblDetails.[Transaction Number]" _
' & " AND ON tblInstallSummary.[Transaction Number]" _
' & " WHERE" & strCriteria & " ORDER BY tblDetails.[Item Number]"
intResponse = MsgBox("Do you wish to send the COI Request?", vbYesNo, "COI Request")
If intResponse = vbYes Then
Call GetMailMessage(SQL)
End If
End Sub
I have 2 parameters that I need to group by... by Install SP and by Line item. I want all Line Items that have are linked to an Install SP to be grouped together in the email. I am able to get the data grouped but the Install SP is duplicated in the output.
An example of what I want is:
SP#: 8001194220 SOI 03/24/2007
Line Items
40 1 015-00461A Part A
60 1 015-00366A Part B
SP #8001194221 SOI 03/29/2007
Line Items
30 1 015-00369A Part E
70 1 015-00367A Part C
90 1 015-00368A Part D
Below is the code I've used. I'm pretty sure the problem is where I used the statement "if strCurrentInstall SP <> strPreviousInstallSP, then". Somehow I have to put these into groupings. Any help would be great as I'm at a complete loss as to what to do next! OH! I should mention, I'm new to VBA
Private Sub cmdCOIRequest_Click()
Dim SQL As String
Dim strCriteria As String
Dim intResponse As Integer
'Create criteria to filter the Transaction Number
strCriteria = "tblHeader.[Transaction Number]=" & Forms!frmIP_Planning![Transaction Number]
'Create the select statement to find all records with the specific Transaction Number
'changed statement to take SOI date from Access
SQL = "SELECT tblHeader.[Transaction Number], tblHeader.BP, tblHeader.[Ship-to Party], tblHeader.[CAM: Street]," _
& "tblHeader.[CAM: District], tblHeader.Region, tblDetails.[Installation Order Number]," _
& "tblDetails.[Item Number], tblDetails.[Order Quantity], tblDetails.Material," _
& "tblDetails.[Material Desc from SO line] , tblInstallSummary.SOI" _
& " FROM (tblDetails INNER JOIN tblHeader ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number])" _
& " LEFT JOIN tblInstallSummary ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1" _
& " WHERE " & strCriteria & " ORDER BY tblDetails.[Item Number];"
intResponse = MsgBox("Do you wish to send the COI Request?", vbYesNo, "COI Request")
If intResponse = vbYes Then
Call GetMailMessage(SQL)
End If
End Sub
Private Sub GetMailMessage(SQL As String)
Dim rst As ADODB.Recordset
Dim conn As ADODB.Connection
Dim strMail As String
Dim strTemp As String
Dim blNewInstallSP As Boolean
Dim strCurrentInstallSP As String
Dim strPreviousInstallSP As String
Dim strNextInstallSP As String
Dim strCustomer As String
Dim lngSO As Long
Dim strSOI As String
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
'Open the recordset
rst.Open SQL, conn, adOpenKeyset, adLockOptimistic
'Move to the top of the recordset
rst.MoveFirst
strCustomer = rst![Ship-to Party]
lngSO = rst![Transaction Number]
' strSOI = rst![SOI]
strPreviousInstallSP = ""
strMail = ""
strTemp = ""
'Loop through the recordset and create the Install SP List
Do Until rst.EOF
'Assign variable to current Install SP
strCurrentInstallSP = rst![Installation Order Number]
'Create header for new Install SP
If strCurrentInstallSP <> strPreviousInstallSP Then
strTemp = vbCr & "SP #: " & rst![Installation Order Number] & " " & " SOI " & rst![SOI] & vbCr _
& "LINE ITEMS" & vbCr
End If
'Identify line numbers for each Install SP
strTemp = strTemp & rst![Item Number] & Chr(9) & rst![Order Quantity] & Chr(9) _
& rst![Material] & Chr(9) & rst![Material Desc from SO line] & vbCr
' strTemp = strTemp & rst![Item Number] & Chr(9) & rst![Material] & Chr(9) & rst![Material Desc from SO line] & vbCr
'Create listing
strMail = strMail & strTemp
strTemp = ""
strPreviousInstallSP = strCurrentInstallSP
rst.MoveNext
Loop
'Output the information to an email message
rst.MoveFirst
DoCmd.SendObject , , , , , DLookup("IPPlanner", "tblTempIPPlanner"), "COI Confirmation Request - " & strCustomer & " " & "SO " & lngSO, _
"Sales Order " & rst![Transaction Number] _
& vbCr & "BP #: " & rst!BP _
& vbCr & "Site: " _
& rst![Ship-to Party] & " " & rst![CAM: District] & ", " & rst![Region] _
& vbCr & vbCr _
& "Our records indicate this order is likely completed." _
& " Please confirm back with the DATE OF COMPLETION for the following." _
& " If it is not complete, please provide expected date of completion. " _
& vbCr & vbCr & strMail & vbCr & vbCr _
& "Your confirmation is required to meet our accounting obligations." _
& vbCr & vbCr & "Thank you.", True
'Close the recordset and connection
rst.Close
conn.Close
Set conn = Nothing
Set rst = Nothing
End Sub
Private Sub cmdCOIRequestSpecific_Click()
Dim SQL As String
Dim strCriteria As String
Dim intResponse As Integer
'Create criteria to filter the Transaction Number
strCriteria = "[Installation Order Number]='" & Forms!frmInstallSPList!frmInstallSPListSubform.Form![Installation Order Number] & "'"
'Create the select statement to find all records with the specific Transaction Number
SQL = "SELECT tblHeader.[Transaction Number], tblHeader.BP, tblHeader.[Ship-to Party], tblHeader.[CAM: Street]," _
& "tblHeader.[CAM: District], tblHeader.Region, tblDetails.[Installation Order Number]," _
& "tblDetails.[Item Number], tblDetails.[Order Quantity], tblDetails.Material," _
& "tblDetails.[Material Desc from SO line] , tblInstallSummary.SOI" _
& " FROM (tblDetails INNER JOIN tblHeader ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number])" _
& " LEFT JOIN tblInstallSummary ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1" _
& " WHERE " & strCriteria & " ORDER BY tblDetails.[Item Number];"
' SQL = "SELECT tblHeader.[Transaction Number], tblDetails.[Installation Order Number]," _
' & "tblDetails.[Item Number], tblHeader.BP, tblDetails.[Order Quantity], tblDetails.Material, tblDetails.[Line SOI Date]," _
' & "tblDetails.[Material Desc from SO line], tblHeader.[Ship-to Party]," _
' & "tblHeader.[CAM: Street], tblHeader.[CAM: District], tblHeader.Region" _
' & " FROM tblHeader INNER JOIN tblDetails ON tblHeader.[Transaction Number] = tblDetails.[Transaction Number]" _
' & " AND ON tblInstallSummary.[Transaction Number]" _
' & " WHERE" & strCriteria & " ORDER BY tblDetails.[Item Number]"
intResponse = MsgBox("Do you wish to send the COI Request?", vbYesNo, "COI Request")
If intResponse = vbYes Then
Call GetMailMessage(SQL)
End If
End Sub
