Outlook 2003 Saving into a textbox when form closes

D

Daniel

Ok, when the Post closes it saves everything in the Post to an Access database and its suppose to grab the autonumber from the access database and place it in a textbox on the Post. But when I open the Post the textbox with the ID number is empty, How do I save the Number when the Post closes?



Function OpenAccessDB(strDBPath)
Const adStateOpen = 1
Dim objADOConn
Dim strConn, UID, PWD
UID = ""
PWD = ""
On Error Resume Next
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & strDBPath & "; " & _
"User ID=" & UID & "; " & _
"Password=" & PWD & "; "
Set objADOConn = CreateObject("ADODB.Connection")
objADOConn.Open strConn
If (Err = 0) And (objADOConn.State = adStateOpen) Then
Set OpenAccessDB = objADOConn
Else
Set OpenAccessDB = Nothing
End If
Set objADOConn = Nothing
End Function

'----------------------------------------------------------- ------------------------------------------------------------ -

Function GetNewNumber()
Const adLockOptimistic = 3
Const adOpenDynamic = 2
Dim adoConn 'As ADODB.Connection
Dim rstHelpDesk 'As ADODB.Recordset
Dim strPath 'As String
Dim strSQL 'As String
Dim strName 'As String
Dim FormID
' ### USER OPTION ###
' path to HelpDesk database
strPath = "C:\HelpDesk\HelpDesk.mdb"
Set adoConn = OpenAccessDB(strPath)
strSQL = "SELECT * FROM [HelpDesk];" 'was "SELECT [ID] FROM HelpDesk;"
Set rstHelpDesk = CreateObject("ADODB.Recordset")
rstHelpDesk.Open strSQL, adoConn , adOpenDynamic, adLockOptimistic

If Item.UserProperties("ID") = "" Then
rstHelpDesk.AddNew
rstHelpDesk.Fields("ComputerName") = Item.UserProperties("Computer Name")
rstHelpDesk.Fields("Date") = Item.UserProperties("Date")
rstHelpDesk.Fields("AVG") = Item.UserProperties("AVG")
rstHelpDesk.Fields("Mas200") = Item.UserProperties("Mas200")
rstHelpDesk.Fields("Unexplained Popups") = Item.UserProperties("Unexplained Popups")
rstHelpDesk.Fields("Virus/Trojans") = Item.UserProperties("Virus/Trojans")
rstHelpDesk.Fields("Windows") = Item.UserProperties("Windows")
rstHelpDesk.Fields("Other") = Item.UserProperties("Other")
rstHelpDesk.Fields("Other Text") = Item.UserProperties("Other2")
rstHelpDesk.Fields("Description") = Item.UserProperties("Description")
rstHelpDesk.Fields("Was Anything Installed") = Item.UserProperties("Installed")
Else
FormID = "ID = " & Item.UserProperties("ID")
rstHelpDesk.Find FormID
rstHelpDesk.Fields("how to") = Item.UserProperties("How to")
rstHelpDesk.Fields("did it work?") = Item.UserProperties("Option")
rstHelpDesk.Fields("if no") = Item.UserProperties("If no")
rstHelpDesk.Fields("Is this Issue Resolved?") = Item.UserProperties("Option2")
rstHelpDesk.Fields("Notes") = Item.UserProperties("Note")

End if

rstHelpDesk.Update
Item.UserProperties("ID")= rstHelpDesk.Fields("ID") <-- doesn't save
Msgbox Item.UserProperties("ID") <--the messagebox does give me a number
rstHelpDesk.Close
adoConn.Close
Set adoConn = Nothing
Set rstHelpDesk = Nothing
End Function


'----------------------------------------------------------- ------------------------------------------------------------ -

Function Item_Close()
Dim Save
Save=msgbox("Do you want to Save this Form to the Database or Cancel?",1,"Save?")

If Save = VbOk Then
GetNewNumber()
End If

End Function.
Submitted using http://www.outlookforums.com
 
S

Sue Mosher [MVP]

If the item is closing because no previous changes were made to it, the item
isn't going to be saved. You may need to change the logic and require users
to save the item in order to get a new number, not just close it.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


Daniel said:
Ok, when the Post closes it saves everything in the Post to an Access
database and its suppose to grab the autonumber from the access database
and place it in a textbox on the Post. But when I open the Post the
textbox with the ID number is empty, How do I save the Number when the
Post closes?

Function OpenAccessDB(strDBPath)
Const adStateOpen = 1
Dim objADOConn
Dim strConn, UID, PWD
UID = ""
PWD = ""
On Error Resume Next
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & strDBPath & "; " & _
"User ID=" & UID & "; " & _
"Password=" & PWD & "; "
Set objADOConn = CreateObject("ADODB.Connection")
objADOConn.Open strConn
If (Err = 0) And (objADOConn.State = adStateOpen) Then
Set OpenAccessDB = objADOConn
Else
Set OpenAccessDB = Nothing
End If
Set objADOConn = Nothing
End Function

'----------------------------------------------------------- ------------------------------------------------------------
-

Function GetNewNumber()
Const adLockOptimistic = 3
Const adOpenDynamic = 2
Dim adoConn 'As ADODB.Connection
Dim rstHelpDesk 'As ADODB.Recordset
Dim strPath 'As String
Dim strSQL 'As String
Dim strName 'As String
Dim FormID
' ### USER OPTION ###
' path to HelpDesk database
strPath = "C:\HelpDesk\HelpDesk.mdb"
Set adoConn = OpenAccessDB(strPath)
strSQL = "SELECT * FROM [HelpDesk];" 'was "SELECT [ID] FROM HelpDesk;"
Set rstHelpDesk = CreateObject("ADODB.Recordset")
rstHelpDesk.Open strSQL, adoConn , adOpenDynamic, adLockOptimistic

If Item.UserProperties("ID") = "" Then
rstHelpDesk.AddNew
rstHelpDesk.Fields("ComputerName") = Item.UserProperties("Computer Name")
rstHelpDesk.Fields("Date") = Item.UserProperties("Date")
rstHelpDesk.Fields("AVG") = Item.UserProperties("AVG")
rstHelpDesk.Fields("Mas200") = Item.UserProperties("Mas200")
rstHelpDesk.Fields("Unexplained Popups") =
Item.UserProperties("Unexplained Popups")
rstHelpDesk.Fields("Virus/Trojans") = Item.UserProperties("Virus/Trojans")
rstHelpDesk.Fields("Windows") = Item.UserProperties("Windows")
rstHelpDesk.Fields("Other") = Item.UserProperties("Other")
rstHelpDesk.Fields("Other Text") = Item.UserProperties("Other2")
rstHelpDesk.Fields("Description") = Item.UserProperties("Description")
rstHelpDesk.Fields("Was Anything Installed") =
Item.UserProperties("Installed")
Else
FormID = "ID = " & Item.UserProperties("ID")
rstHelpDesk.Find FormID
rstHelpDesk.Fields("how to") = Item.UserProperties("How to")
rstHelpDesk.Fields("did it work?") = Item.UserProperties("Option")
rstHelpDesk.Fields("if no") = Item.UserProperties("If no")
rstHelpDesk.Fields("Is this Issue Resolved?") =
Item.UserProperties("Option2")
rstHelpDesk.Fields("Notes") = Item.UserProperties("Note")

End if

rstHelpDesk.Update
Item.UserProperties("ID")= rstHelpDesk.Fields("ID") <-- doesn't save
Msgbox Item.UserProperties("ID") <--the messagebox does give me a number
rstHelpDesk.Close
adoConn.Close
Set adoConn = Nothing
Set rstHelpDesk = Nothing
End Function


'----------------------------------------------------------- ------------------------------------------------------------
-

Function Item_Close()
Dim Save
Save=msgbox("Do you want to Save this Form to the Database or
Cancel?",1,"Save?")

If Save = VbOk Then
GetNewNumber()
End If

End Function. Submitted using http://www.outlookforums.com
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Outlook 2003 bound textbox 2
Loading Data into a droplist 3
Access 2007 Recordsets? 1
Password from custom form 0
DB Connection 3
Combo box value? 1
Loading Data into a droplist using Access Database 1
Connecting Forms to Database 0

Top