Solved: Setting a field value in a table

[andrew93]

Hello

Introduction
I have an Access (2003) form where the user has the option to e-mail a pdf version of the invoice to the customer. This is a multi-entity database (i.e. many entities can create many invoices for many clients) and each entity has a 'show logo' option of 1 (never), 2 (always) or 3 (e-mails only) which sets whether the logo is shown on the invoice or not. This works ok to a point (i.e. options 1 and 2 are fine thanks to this forum).

My Question
If the user e-mails the invoice and the entity has a 'show logo' option of 3, how can I reset the 'logo option' in the underlying table to 2, and after the invoice has been e-mailed reset the option back to 3?

Other Information
There is a VBA routine to e-mail a pdf version of the invoice and to cut a long story short, I think this is the only way I can get around this issue because I believe I have to set the visible property of the logo before I open the report when using the pdf writer. I have tried playing around with the 'SetValue' function but am unsure as to how to get this to work given the logo option is a bound control and I can't work out the VBA syntax.

Any suggestions would be appreciated.

TIA, Andrew

[matthewspatrick]

Andrew,

Please post the code that generates the email. Include any code that gets triggered by its execution, such as an OnFormat event sub.

Patrick

[andrew93]

Hi Patrick and thanks for replying

Here is the code within the report :

Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)

Dim tmp As Integer

tmp = Me.Entity_Invoice_Logo_Option

Select Case tmp
Case 1
Me.Entity_Logo.Visible = False
Me.Trade_Name.Visible = True
Case 2
Me.Entity_Logo.Visible = True
Me.Trade_Name.Visible = False
Case 3
Me.Entity_Logo.Visible = False
Me.Trade_Name.Visible = True
End Select

End Sub

and here is the part of the code that sends the e-mail in its entirety (apologies for the long code but this has been a collaborative effort from a number of contributors on this and other forums given I am relatively novice with VBA but I have added comments along the way to explain what is happening):


Option Compare Database

'Declare Windows' API functions (next 3 private functions are for the ExpressClickYes)
Private Declare Function RegisterWindowMessage _
Lib "user32" Alias "RegisterWindowMessageA" _
(ByVal lpString As String) As Long

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As Any, _
ByVal lpWindowName As Any) As Long

Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" (ByVal hwnd As Long, _
ByVal wMsg As Long, ByVal wParam As Long, _
lParam As Any) As Long

Private Sub Command96_Click() 'E-mail the invoice

'File handling variables
Dim strSave As String, strReport As String, strPathOld As String, _
strPathNew As String, Counter As Integer, KillFile As String, _
RunFile As String, fso As Object, PID As Long, mWnd As Long
'e-mail variables
Dim strRecip As String, strBCC As String, strMailText As String
'Access variables
Dim UniqID As Integer, InvNum As Integer, EntityID As Integer, _
LogoOption As Integer
'Variables required for the ExpressClickYes
Dim wnd As Long, uClickYes As Long, Res As Long

On Error GoTo ErrHandler

'Get the unique ID
UniqID = Nz(Me.Unique_ID)

'Saves calculated data using another subroutine
Command65_Click

'Exit if there is no invoice, otherwise get the actual invoice number
If UniqID = 0 Then
MsgBox "Please select an invoice to e-mail", vbCritical, "Error"
Exit Sub
Else
InvNum = DLookup("Invoice", "T_Invoices", "Unique_ID = " & UniqID)
End If

'Get the e-mail address, if there is none entered then exit
If IsNull(Me.E_mail) Or Me.E_mail = "" Then
MsgBox "There must be an e-mail address entered before you can e-mail an invoice.", _
vbCritical, "Error"
Exit Sub
Else
strRecip = Me.E_mail
End If

DoCmd.Hourglass (True)

'Get the entity ID
EntityID = Nz(Me.Entity_ID_Link)

If EntityID = 0 Then
MsgBox "Please select an entity", vbCritical, "Error"
Exit Sub
End If

'Get the logo option of the entity
LogoOption = DLookup("[Entity_Invoice_Logo_Option]", "T_Entities", _
"[Entity_ID] = " & [EntityID])


'***************************************
'This was my attempt at changing the option by opening another form

'If LogoOption = 3 Then
' DoCmd.OpenForm "frmResetLogoOption", , , _
' "[Entity_ID] = " & [EntityID], acFormEdit
' ????SetValue???? Forms!frmResetLogoOption!Entity_Invoice_Logo_Option, 2
'End If

'***************************************

'Hard coded the report name and the save name convention - to alter later
strReport = "rptInvoice"
strSave = "Invoice_" & Format(InvNum, "0") & ".pdf"
strPathOld = "C:\Program Files\Adobe\Acrobat 6.0\Distillr\"

'Create the pdf file
If RunReportAsPDF(strReport, strSave, UniqID) = False Then
MsgBox "The pdf version of the invoice could not be created", _
vbCritical, "Error - Invoice Not Saved"
End If

'Set the new save path to a sub-directory
strPathNew = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\", , vbTextCompare))
strPathNew = strPathNew & "Invoices\"

'If the sub-directory does not exist then create it
If Dir(strPathNew, vbDirectory) = "" Then
MkDir strPathNew
End If

'Set the name of the old file to delete from the Adobe distiller directory
KillFile = strPathOld & strSave

'Check the file exists
Counter = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Do While Counter < 20
If Not fso.FileExists(KillFile) Then
Counter = Counter + 1
If Counter = 15 Then
'Stop after 30 seconds of trying
MsgBox "There is a problem creating the invoice." _
& vbCrLf & vbCrLf & "This process will be terminated", _
vbCritical, "Error."
DoCmd.Hourglass (False)
Exit Sub
Else
'Wait another 2 seconds
DoEvents
Sleep 2000
End If
Else
'File exists so copy the file to the correct location
'This is a workaround given Adobe tries to open the document
FileCopy strPathOld & strSave, strPathNew & strSave
'Exit the loop
Counter = 20
End If
Loop

'Pause
DoEvents
Sleep 500

'Check that file exists, remove read only attribute (if set), then delete the file
If Len(Dir$(KillFile)) > 0 Then
SetAttr KillFile, vbNormal
Kill KillFile
End If

'Start ClickYes - but check it exists first
'This stops Outlook popping up a message warning about VBA sending an-email
PID = 0
Set fso = CreateObject("Scripting.FileSystemObject")
RunFile = "C:\Program Files\ExpressClickYes\ClickYes.exe"
If fso.FileExists(RunFile) Then
PID = Shell(RunFile, vbMinimizedNoFocus)
Sleep 500
Else
RunFile = "C:\Program Files\Express ClickYes\ClickYes.exe"
If fso.FileExists(RunFile) Then
PID = Shell(RunFile, vbMinimizedNoFocus)
Sleep 500
End If
End If

If PID > 0 Then
'Turn off the automated e-mail warning in Outlook
'Register a message to send
uClickYes = RegisterWindowMessage("CLICKYES_SUSPEND_RESUME")
'Find ClickYes Window by classname
wnd = FindWindow("EXCLICKYES_WND", 0&)
'Send the message to Resume ClickYes
Res = SendMessage(wnd, uClickYes, 1, 0)
End If

'Send the e-mail
Call SendEMessage(True, strRecip, InvNum, EntityID, strPathNew & strSave)

If PID > 0 Then
'Pause
DoEvents
Sleep 300
'Turn on the automated e-mail warning in Outlook
'Send the message to Suspend ClickYes
Res = SendMessage(wnd, uClickYes, 0, 0)
DoEvents
Sleep 300
CloseProcess ("ClickYes.exe")
Sleep 300
'This step was added given Adobe leaves this process running
CloseProcess ("acrotray.exe")
End If

'Reset the printed status for this invoice
DoCmd.SetWarnings (False)
DoCmd.OpenQuery "qryResetPrintedStatusOne"
DoCmd.SetWarnings (True)

'Pause
DoEvents
Sleep 500

'Make sure the invoice is no longer visible now that it has been printed
DoCmd.SelectObject acForm, "frmInvoices"
DoCmd.ApplyFilter , "Printed = No"

ExitHere:
DoCmd.Hourglass (False)
Exit Sub

ErrHandler:
MsgBox Err.Number & vbCrLf & Err.Description
'Send the message to Suspend ClickYes
If StartClickYes = True Then
Res = SendMessage(wnd, uClickYes, 0, 0)
End If
Resume ExitHere

End Sub

There are a few other modules the above code calls but I think they pretty much speak for themselves and don't impact opening the report and e-mailing it.

Thanks for looking
Andrew

[andrew93]

Bump.

I think the crucial part of the code is where I added the asterisks half way down. If I can reset the value in the table (or via a temporary form) then the problem is solved. Is there a VBA command to set a value of a field in a table, or on a form that doesn't have the focus? Like SetValue or something like that?

Thanks, Andrew

[XLGibbs]

how can I reset the 'logo option' in the underlying table to 2, and after the invoice has been e-mailed reset the option back to 3?


If this is an actual TABLE, you would need an UPDATE statement

UPDATE {Table Name}
Set {Field} = {Argument|can be select From statement...}

I don't see anything jumping out inside the code itself, other than the SetValue option won;t really work for this.

Where is this Logo Option actually contained? Is it on a form? is it reading from a table?

If linked to a form, you may need to refresh the form after sending the email....or requery the data after getting the mail sent to replace the value...

[andrew93]

Hi
That is exactly what I needed! I got it to work by inserting the following piece of code:


'Set the logo option to 2 (always show the logo) given the invoice is being e-mailed
'and option 3 requires the logo to be on the invoice
If LogoOption = 3 Then
DoCmd.RunSQL "Update T_Entities Set T_Entities.Entity_Invoice_Logo_Option = 2 WHERE ((T_Entities.Entity_ID) = " & [EntityID] & ")"
End If

I then set the logo option back to 3 after the invoice was created using the same logic.

In answer to your questions : the logo option is contained in a table but changes how the report behaves (see my first section of code in my 2nd post), and rather than show the user anything, this all occurs behind the scenes and is one detail they don't need to know....

Thanks a bunch for the SQL pointer - I will remember that for later use! {*Thinks to self : 'Why didn't I learn this technique earlier? It would have made life so much easier before!'*}

Much appreciated
Thanks again
Andrew
:thumb