Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to select correct Snapshot report based on current ECN 1

Status
Not open for further replies.
netrusher

netrusher

Technical User
Feb 13, 2005
952
US
I am trying to save a report in snapshot mode and attached
it to an email. The first piece of code works and picks
up the correct snapshot report.

The second piece of code works but the snapshot report it
picks up is always based on the the first ECN in the
database. What I am wanting it to be is the snapshot report
based on the ECN that is currently selected. I need it
to work with the second piece of code because that is
the email code we are using throughout the database. Can
someone tell me what I need to do to get the second piece
of code to pick up the correct snapshot report based on
whatever ECN that is currently selected?


Code: 
On Error GoTo Err_EmailEcn_Click
'Checks for empty field value, it not empty then proceeds
If Not cboECNLookup.Value = "" Then

    Dim stDocName As String

stDocName = "ECNBCNVIPrpt-2"
If InStr(UserGroups(), "admingrp") > 0 Then
DoCmd.RunCommand acCmdSaveRecord
ElseIf InStr(UserGroups(), "entrygrp") > 0 Then
Me.ECN_Analyst.SetFocus
DoCmd.RunCommand acCmdSaveRecord
End If
DoCmd.SendObject acReport, stDocName, "Snapshot Format"


Else: MsgBox "Please select an ECN Number from the drop down list on the left."
End If
Exit_EmailEcn_Click:
    Exit Sub

Err_EmailEcn_Click:
    MsgBox Err.Description
    Resume Exit_EmailEcn_Click
Code: 
If Not Forms!ECNBCNVIPfrm!cboECNLookup.Value = "" Then

Dim stDocName As String

stDocName = "ECNBCNVIPrpt-2"
If InStr(UserGroups(), "admingrp") > 0 Then
DoCmd.RunCommand acCmdSaveRecord
ElseIf InStr(UserGroups(), "entrygrp") > 0 Then
Forms!ECNBCNVIPfrm!ECN_Analyst.SetFocus
DoCmd.RunCommand acCmdSaveRecord
End If

DoCmd.OutputTo acOutputReport, "ECNBCNVIPrpt-2", "Snapshot Format", "ECNBCNVIPrpt.snp", , "C:\my documents\ECNBCNVIPrpt.snp", True

       Dim O As Outlook.Application
       Dim m As Outlook.MailItem
       Dim toEmail1 As String
       Dim toEmail2 As String
       Dim ccEmail1 As String
       Dim ccEmail2 As String
       Dim ccEmail3 As String
       Dim bccEmail1 As String
       Dim SL As String, DL As String
       SL = vbNewLine
       DL = SL & SL

Set O = CreateObject("Outlook.Application")
Set m = Outlook.CreateItem(0)

Dim X
Dim Y
toEmail1 = ""
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![Engineering Distribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormEngMETbl", "EngME='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![MastDistribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormMastEngTbl", "MastEng='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![FabDistribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormFabEngTbl", "FabEng='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![PaintDistribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormPaintEngTbl", "PaintMe='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![Quality Distribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormQAEngTbl", "QAEng='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![MasterSchedulerDistribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormMasterSchedulerTbl", "MasterSchedulers='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
If Forms!ECNBCNVIPfrm!ECNDetailfrm![Materials Distribution] = "Kim Dees" Then
toEmail1 = toEmail1 & "abkdees@nmhg.com;"
End If
If Forms!ECNBCNVIPfrm!ECNDetailfrm![Finance Distribution] = "Shirley Combs" Then
toEmail1 = toEmail1 & "abscombs@nmhg.com;"
End If
If Forms!ECNBCNVIPfrm!ECNDetailfrm![Sped Distribution] = "Jim Taylor" Then
toEmail1 = toEmail1 & "abjtaylo@nmhg.com;"
End If
If Forms!ECNBCNVIPfrm!ECNDetailfrm![Production Scheduling Distribution] = "Kimberly Kulhavy" Then
toEmail1 = toEmail1 & "abkkulhy@nmhg.com;"
End If
If Forms!ECNBCNVIPfrm!ECNDetailfrm![Service Engineering Distribution] = "Mark Hume" Then
toEmail1 = toEmail1 & "abmhume@nmhg.com;"
End If
If Forms!ECNBCNVIPfrm!ECNDetailfrm![ISA Distribution] = "FYI" Then
toEmail1 = toEmail1 & "kelley.johnson@sdi.com;"
End If

ccEmail1 = "abkkulhy@nmhg.com"
ccEmail2 = "abkkulhy@nmhg.com"
ccEmail3 = ""
bccEmail1 = "abajacks@nmhg.com"

m.To = toEmail1
m.CC = ""
m.BCC = bccEmail1
m.Subject = "ECN #  " & Forms!ECNBCNVIPfrm![ECN Number].Value & "; This ECN is ready to be worked"
m.Body = "This ECN is Ready to be worked." & DL & _
       "Form # " & Forms!ECNBCNVIPfrm![ECNBCNVIP ID].Value & DL & _
       "ECN #  " & Forms!ECNBCNVIPfrm![ECN Number].Value & DL & _
       "ECN Description: " & Forms!ECNBCNVIPfrm!ECNDetailfrm![ECN Description].Value & DL & _
       "Comments: " & Forms!ECNBCNVIPfrm!ECNDetailfrm![Comments].Value & DL & _
       "Thank you for your help" & DL & _
       DLookup("ActualName", "ChangeFormUserNameTbl", "LoginName='" & GetCurrentUserName() & "'") & SL & _
       "ECN Analyst"

m.Attachments.Add "c:\my documents\ECNBCNVIPrpt.snp"

m.Display

On Error GoTo Err_CloseForm_Click


    DoCmd.Close acForm, "PopupEcn3"

Exit_CloseForm_Click:
    Exit Sub

Err_CloseForm_Click:
    MsgBox Err.Description
    Resume Exit_CloseForm_Click
End If
 
Sort by date Sort by votes
I would assume that I am not saving the Report based on
which ECN is selected but I do not know how to do that. The
first piece of code will give me the Snapshot report based
on the ECN that is selected. The second will not.
 
Upvote 0 Downvote
Replace this:
DoCmd.RunCommand acCmdSaveRecord

with this:
Forms!ECNBCNVIPfrm.Dirty = False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I changed the first part of the code to below as you
suggested and I am still getting ECN 100001 instead of
ECN 94233 which is the currently selected ECN Number
on the form.

Code: 
If Not Forms!ECNBCNVIPfrm!cboECNLookup.Value = "" Then

Dim stDocName As String

stDocName = "ECNBCNVIPrpt-2"
If InStr(UserGroups(), "admingrp") > 0 Then
Forms!ECNBCNVIPfrm.Dirty = False
ElseIf InStr(UserGroups(), "entrygrp") > 0 Then
Forms!ECNBCNVIPfrm!ECN_Analyst.SetFocus
Forms!ECNBCNVIPfrm.Dirty = False
End If
 
Upvote 0 Downvote
PH or anybody,

Any help out there on this. This is a real thorn in my side.
One email option works but I need the second one to work.
It will pick up a snpshot report but it is not the report
associated to whatever the ECN selected currently is. I
cannot figure out why.
 
Upvote 0 Downvote
It looks to me like your report is not picking up the selected criteria from you combo box. Try inserting a message box prior to the output of the report:

msgbox me.yourlistbox
 
Upvote 0 Downvote
pdldavis,

Do I just create a txtbox or something? I am not sure what
you mean?
 
Upvote 0 Downvote
Hi, what I mean is it looks like your report is supposed be display its results based on a selection from your dropdown list, which is a combobox or list box, and it isn't doing that. If that is the case, a message box is helpful to see what your code is doing.

If the name of your dropdown box is cboECN, then:

....DoCmd.RunCommand acCmdSaveRecord
End If

msgbox cboECN

DoCmd.OutputTo acOutputReport, "ECNBCNVIPrpt-2", "Snapshot Format", "ECNBCNVIPrpt.snp" .....

The code will halt at the msgbox line and show you what is selected.

ECN - Engineering Change Notice?

 
Upvote 0 Downvote
I understand and you are right. There is nothing in the
msgbox. Any suggestions as to how to fix this problem?

ECN = Engineering Change Notice
Click to expand...
 
Upvote 0 Downvote
The report is based on a query. The only criteria in the
query is under the ECN Number field the Criteria is:
Code: 
[Forms]![ECNBCNVIPfrm]![cboECNLookup]
 
Upvote 0 Downvote
The Code is actually triggered on a close form command
button EventClick on a form named EcnPopup3
 
Upvote 0 Downvote
Well, the easy way would probably go back to your report and go to design view. Put some criteria into your ECN field if it's not already there, which looks for your combo box selection:

This assumes the ECN field in your report is the same starting field in your combo box.

=forms!yourForm.yourComboBox

Then to make it a little more bullet proof, fire the code in the AfterUpdate Event of your combobox rather than from another button.

One last thought - how are you clearing out the old report?
I don't know whether or not a new 'output report' will over-write the old one. Might be worth checking...


 
Upvote 0 Downvote
pldavis,

The code below works perfectly. The problem is I need it
work work with my other email code and I need it to
fire when I click the Close Form Button from my form:
PopupEcn3 form. Maybe you can look a the code below and
see how to make it work.

Code: 
Private Sub EmailEcn_Click()
On Error GoTo Err_EmailEcn_Click
'Checks for empty field value, it not empty then proceeds
If Not cboECNLookup.Value = "" Then

    Dim stDocName As String

stDocName = "ECNBCNVIPrpt-2"
If InStr(UserGroups(), "admingrp") > 0 Then
DoCmd.RunCommand acCmdSaveRecord
ElseIf InStr(UserGroups(), "entrygrp") > 0 Then
Me.ECN_Analyst.SetFocus
DoCmd.RunCommand acCmdSaveRecord
End If
DoCmd.SendObject acReport, stDocName, "Snapshot Format"


Else: MsgBox "Please select an ECN Number from the drop down list on the left."
End If
Exit_EmailEcn_Click:
    Exit Sub

Err_EmailEcn_Click:
    MsgBox Err.Description
    Resume Exit_EmailEcn_Click


End Sub
 
Upvote 0 Downvote
Also,

The Report is based on a Query. The ECN Field of the
query has the only Criteria of the Query
Code: 
[Forms]![ECNBCNVIPfrm]![cboECNLookup]
.The
Query has the correct ECN number it is just the Report
Snapshot that has the same ECN number 100001 no matter
what ECN number is selected. The original code above
works just fine.
 
Upvote 0 Downvote
Shoot, looks like I missed something obvious - change your output to command to this:

DoCmd.OutputTo acOutputReport, "ECNBCNVIPrpt-2", acFormatSNP, "C:\my documents\ECNBCNVIPrpt.snp", True
 
Upvote 0 Downvote
pdldavis,

I have been off work for two days. Sorry it has taken me
so long to get back on this.

This is still only picking up the same ECN10001 and not
the ECN that the form is currently on. Again, the very first
code I posted on top works. Somehow the new code will not
pick up the correct ECN Report. The query works though
 
Upvote 0 Downvote
Hi, are you sure your 'output' code for snapshots works? I think as a test I would delete all snapshot files out of your directory and step through the code.

Did you ever try this:?

DoCmd.OutputTo acOutputReport, "ECNBCNVIPrpt-2", acFormatSNP, "C:\my documents\ECNBCNVIPrpt.snp", True
 
Upvote 0 Downvote
pdldavis,

That is what I tried this morning. I am getting the Snapshot
attached to my email. The problem is, it is always the
same snapshot no matter what ECN I have selected.
 
Upvote 0 Downvote
I dunno, without seeing it I am running out of ideas. Perhaps someone else has a suggestion.

I am surprised this works: DoCmd.OutputTo acOutputReport, "ECNBCNVIPrpt-2", "Snapshot Format", "ECNBCNVIPrpt.snp", , "C:\my documents\ECNBCNVIPrpt.snp", True

There is one other thing I can think of:

Button Click:

DoCmd.OpenForm "PopupEcn3", acNormal, , , , acDialog


Then at the end of the email code replace:

DoCmd.Close acForm, "PopupEcn3"

with

me.visible = false


 
Upvote 0 Downvote
Everyone:

Below is the code that I finally got to work.

Code: 
If Not Forms!ECNBCNVIPfrm!cboECNLookup.Value = "" Then
Dim stDocName As String

stDocName = "ECNBCNVIPrpt-2"
If InStr(UserGroups(), "admingrp") > 0 Then
DoCmd.RunCommand acCmdSaveRecord
ElseIf InStr(UserGroups(), "entrygrp") > 0 Then
Forms!ECNBCNVIPfrm!ECN_Analyst.SetFocus
DoCmd.RunCommand acCmdSaveRecord
End If
'MsgBox cboECN

DoCmd.OutputTo acOutputReport, stDocName, acFormatSNP, "C:\my documents\ECNBCNVIPrpt.snp", True

'DoCmd.OutputTo acOutputReport, stDocName, "Snapshot Format", "ECNBCNVIPrpt.snp", , "C:\my documents\ECNBCNVIPrpt.snp", True

       Dim O As Outlook.Application
       Dim m As Outlook.MailItem
       Dim toEmail1 As String
       Dim toEmail2 As String
       Dim ccEmail1 As String
       Dim ccEmail2 As String
       Dim ccEmail3 As String
       Dim bccEmail1 As String
       Dim SL As String, DL As String
       SL = vbNewLine
       DL = SL & SL

Set O = CreateObject("Outlook.Application")
Set m = Outlook.CreateItem(0)

Dim X
Dim Y
toEmail1 = ""
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![Engineering Distribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormEngMETbl", "EngME='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![MastDistribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormMastEngTbl", "MastEng='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![FabDistribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormFabEngTbl", "FabEng='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![PaintDistribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormPaintEngTbl", "PaintMe='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![Quality Distribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormQAEngTbl", "QAEng='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
For Each X In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![MasterSchedulerDistribution] & "", vbCrLf)
  If Trim(X & "") > "" Then
    Y = DLookup("LoginName", "FormMasterSchedulerTbl", "MasterSchedulers='" & X & "'")
    If Trim(Y & "") > "" Then
      toEmail1 = toEmail1 & Y & "@nmhg.com;"
    End If
  End If
Next
If Forms!ECNBCNVIPfrm!ECNDetailfrm![Materials Distribution] = "Kim Dees" Then
toEmail1 = toEmail1 & "abkdees@nmhg.com;"
End If
If Forms!ECNBCNVIPfrm!ECNDetailfrm![Finance Distribution] = "Shirley Combs" Then
toEmail1 = toEmail1 & "abscombs@nmhg.com;"
End If
If Forms!ECNBCNVIPfrm!ECNDetailfrm![Sped Distribution] = "Jim Taylor" Then
toEmail1 = toEmail1 & "abjtaylo@nmhg.com;"
End If
If Forms!ECNBCNVIPfrm!ECNDetailfrm![Production Scheduling Distribution] = "Kimberly Kulhavy" Then
toEmail1 = toEmail1 & "abkkulhy@nmhg.com;"
End If
If Forms!ECNBCNVIPfrm!ECNDetailfrm![Service Engineering Distribution] = "Mark Hume" Then
toEmail1 = toEmail1 & "abmhume@nmhg.com;"
End If
If Forms!ECNBCNVIPfrm!ECNDetailfrm![ISA Distribution] = "FYI" Then
toEmail1 = toEmail1 & "kelley.johnson@sdi.com;"
End If

ccEmail1 = "abkkulhy@nmhg.com"
ccEmail2 = "abkkulhy@nmhg.com"
ccEmail3 = ""
bccEmail1 = "abajacks@nmhg.com"

m.To = toEmail1
m.CC = ""
m.BCC = bccEmail1
m.Subject = "ECN #  " & Forms!ECNBCNVIPfrm![ECN Number].Value & "; This ECN is ready to be worked"
m.Body = "This ECN is Ready to be worked." & DL & _
       "Form # " & Forms!ECNBCNVIPfrm![ECNBCNVIP ID].Value & DL & _
       "ECN #  " & Forms!ECNBCNVIPfrm![ECN Number].Value & DL & _
       "ECN Description: " & Forms!ECNBCNVIPfrm!ECNDetailfrm![ECN Description].Value & DL & _
       "Comments: " & Forms!ECNBCNVIPfrm!ECNDetailfrm![Comments].Value & DL & _
       "Thank you for your help" & DL & _
       DLookup("ActualName", "ChangeFormUserNameTbl", "LoginName='" & GetCurrentUserName() & "'") & SL & _
       "ECN Analyst"

m.Attachments.Add "c:\my documents\ECNBCNVIPrpt.snp"

m.Display

On Error GoTo Err_CloseForm_Click

    DoCmd.Close acForm, "PopupEcn3"

Exit_CloseForm_Click:
    Exit Sub

Err_CloseForm_Click:
    MsgBox Err.Description
    Resume Exit_CloseForm_Click
End If

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

I
  • Locked
  • Question
How to get 2 codes to work together.
Replies
6
Views
449
hermanlaksko
hermanlaksko
ameedoo3000
  • Locked
  • Question
Rename current database
Replies
0
Views
353
ameedoo3000
ameedoo3000
ameedoo3000
  • Locked
  • Question
rename current database 2
2
Replies
31
Views
1K
SkipVought
S
PrimoSteve
  • Locked
  • Question
Cannot add Image to Command Button?
Replies
9
Views
677
PrimoSteve
PrimoSteve
applestoapples
  • Locked
  • Question
Create duplicate rows based on Qty field
Replies
7
Views
249
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top