populating fields/controls with sql data

D

Dale

I am successful in reading a sql table and now I need to
populate the fields/controls on a custom form with the
name,address info from the data file/table.
How do I do this? When putting it out like this it works:
Item.UserProperties("CustomerName") = "Dale".
However when putting it out with a field name from the
table I get nothing. Here is the snippet using the table
field name:
Item.UserProperties[CustomerName] = ShipToCustNm

Thanks again in advance.
You have been great through this.
 
S

Sue Mosher [MVP-Outlook]

The syntax for the Outlook side is always the same:
Item.UserProperties("CustomerName")

What code are you using to connect to the database table?
 
D

Dale

Sorry for the sloppyness, but this is my first attemp at
this type of programming. I used msgBox to let me know
when sertain functions were performed. For some
explained reason debug does not work. That is another
day. I did find a thread pertaining to that back on
Mar30 and will address that soon.
Thanks again.



Function Item_Open()

Set cnNorthwind = CreateObject("ADODB.Connection")
MsgBox "1a"

cnNorthwind.ConnectionString
= "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=sa;Initial Catalog=TransactionLayer;Data Source=CRE03"
MsgBox "#1b"

' Open connection
cnNorthwind.Open
MsgBox "#1c"

Set cmNorthwind = CreateObject("ADODB.Command")
MsgBox "#1c1"
'Set command connection
cmNorthwind.ActiveConnection = cnNorthwind
MsgBox "#1d"

End Function



'*********************************************************
*********************
'* Procedure: Item_CustomPropertyChange(byVal Name)
'* Description: You use a custom property change
event to respond to a change
'* in order. You can't use the
control's intrinsic events. These events
'* currently do not fire in the
VBScript of an Outlook form.
'*********************************************************
*********************

' This is good code

Sub Item_CustomPropertyChange(ByVal Name)

Select Case Name
Case "OrderNo"

MsgBox "Property Change to Order"

' Set values
Call cmdSetoutValues
End Select

End Sub



Sub cmdSetoutValues()

'This is good code
MsgBox "Begin data change"

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE OrdNum = 666926"
'strSQL = "SELECT * FROM ord WHERE OrdNum = " '& Chr(39)
& OrderNo & Chr(39)

MsgBox "SQL Order"

rstOrd.Open "ord", cnNorthwind
MsgBox "Select" & strSQL
MsgBox "My Set State is = " & adStateOpen
MsgBox "My File State is = " & rstOrd.State

With rstOrd
.MoveFirst
MsgBox "Record moved"

If .State = adStateOpen Then
MsgBox "Begin Data population"
Item.UserProperties("CustomerName") = "Dale"
' Item.UserProperties("CityState") = "OFallon"
'Item.UserProperties[CustomerName] = ShipToCustNm
MsgBox "Data population #1"

Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

MsgBox "variables Customer is " & CustomerName
MsgBox "variables City is " & CityState
MsgBox "Name & City to be Changed"
MsgBox "Name & City Changed"
Else
MsgBox "rstOrdstate did not work "
Item.UserProperties("CustomerName") =
ord.ShipToCustNm
Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

' Item.UserProperties("CustomerName")
= "Namedidnotwork"
' Item.UserProperties("CityState")
= "Citydidnotwork"
End If
End With

End Sub

-----Original Message-----
The syntax for the Outlook side is always the same:
Item.UserProperties("CustomerName")

What code are you using to connect to the database table?

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



I am successful in reading a sql table and now I need to
populate the fields/controls on a custom form with the
name,address info from the data file/table.
How do I do this? When putting it out like this it works:
Item.UserProperties("CustomerName") = "Dale".
However when putting it out with a field name from the
table I get nothing. Here is the snippet using the table
field name:
Item.UserProperties[CustomerName] = ShipToCustNm

Thanks again in advance.
You have been great through this.
Click to expand...


.
Click to expand...
 
S

Sue Mosher [MVP-Outlook]

Did you Dim cnNorthwind in the declarations section (i.e. the top before any
procedures)? If not, your attempt to use that connection anywhere other than
in Item_Open will fail because the object has gone out of scope.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



Dale said:
Sorry for the sloppyness, but this is my first attemp at
this type of programming. I used msgBox to let me know
when sertain functions were performed. For some
explained reason debug does not work. That is another
day. I did find a thread pertaining to that back on
Mar30 and will address that soon.
Thanks again.



Function Item_Open()

Set cnNorthwind = CreateObject("ADODB.Connection")
MsgBox "1a"

cnNorthwind.ConnectionString
= "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=sa;Initial Catalog=TransactionLayer;Data Source=CRE03"
MsgBox "#1b"

' Open connection
cnNorthwind.Open
MsgBox "#1c"

Set cmNorthwind = CreateObject("ADODB.Command")
MsgBox "#1c1"
'Set command connection
cmNorthwind.ActiveConnection = cnNorthwind
MsgBox "#1d"

End Function



'*********************************************************
*********************
'* Procedure: Item_CustomPropertyChange(byVal Name)
'* Description: You use a custom property change
event to respond to a change
'* in order. You can't use the
control's intrinsic events. These events
'* currently do not fire in the
VBScript of an Outlook form.
'*********************************************************
*********************

' This is good code

Sub Item_CustomPropertyChange(ByVal Name)

Select Case Name
Case "OrderNo"

MsgBox "Property Change to Order"

' Set values
Call cmdSetoutValues
End Select

End Sub



Sub cmdSetoutValues()

'This is good code
MsgBox "Begin data change"

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE OrdNum = 666926"
'strSQL = "SELECT * FROM ord WHERE OrdNum = " '& Chr(39)
& OrderNo & Chr(39)

MsgBox "SQL Order"

rstOrd.Open "ord", cnNorthwind
MsgBox "Select" & strSQL
MsgBox "My Set State is = " & adStateOpen
MsgBox "My File State is = " & rstOrd.State

With rstOrd
.MoveFirst
MsgBox "Record moved"

If .State = adStateOpen Then
MsgBox "Begin Data population"
Item.UserProperties("CustomerName") = "Dale"
' Item.UserProperties("CityState") = "OFallon"
'Item.UserProperties[CustomerName] = ShipToCustNm
MsgBox "Data population #1"

Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

MsgBox "variables Customer is " & CustomerName
MsgBox "variables City is " & CityState
MsgBox "Name & City to be Changed"
MsgBox "Name & City Changed"
Else
MsgBox "rstOrdstate did not work "
Item.UserProperties("CustomerName") =
ord.ShipToCustNm
Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

' Item.UserProperties("CustomerName")
= "Namedidnotwork"
' Item.UserProperties("CityState")
= "Citydidnotwork"
End If
End With

End Sub

-----Original Message-----
The syntax for the Outlook side is always the same:
Item.UserProperties("CustomerName")

What code are you using to connect to the database table?


I am successful in reading a sql table and now I need to
populate the fields/controls on a custom form with the
name,address info from the data file/table.
How do I do this? When putting it out like this it works:
Item.UserProperties("CustomerName") = "Dale".
However when putting it out with a field name from the
table I get nothing. Here is the snippet using the table
field name:
Item.UserProperties[CustomerName] = ShipToCustNm

Thanks again in advance.
You have been great through this.
Click to expand...


.
Click to expand...
Click to expand...
 
D

dale

-----Original Message-----
Did you Dim cnNorthwind in the declarations section (i.e. the top before any
procedures)? If not, your attempt to use that connection anywhere other than
in Item_Open will fail because the object has gone out of scope.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



Sorry for the sloppyness, but this is my first attemp at
this type of programming. I used msgBox to let me know
when sertain functions were performed. For some
explained reason debug does not work. That is another
day. I did find a thread pertaining to that back on
Mar30 and will address that soon.
Thanks again.



Function Item_Open()

Set cnNorthwind = CreateObject("ADODB.Connection")
MsgBox "1a"

cnNorthwind.ConnectionString
= "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=sa;Initial Catalog=TransactionLayer;Data Source=CRE03"
MsgBox "#1b"

' Open connection
cnNorthwind.Open
MsgBox "#1c"

Set cmNorthwind = CreateObject("ADODB.Command")
MsgBox "#1c1"
'Set command connection
cmNorthwind.ActiveConnection = cnNorthwind
MsgBox "#1d"

End Function



'****************************************************** ***
*********************
'* Procedure: Item_CustomPropertyChange(byVal Name)
'* Description: You use a custom property change
event to respond to a change
'* in order. You can't use the
control's intrinsic events. These events
'* currently do not fire in the
VBScript of an Outlook form.
'****************************************************** ***
*********************

' This is good code

Sub Item_CustomPropertyChange(ByVal Name)

Select Case Name
Case "OrderNo"

MsgBox "Property Change to Order"

' Set values
Call cmdSetoutValues
End Select

End Sub



Sub cmdSetoutValues()

'This is good code
MsgBox "Begin data change"

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE OrdNum = 666926"
'strSQL = "SELECT * FROM ord WHERE OrdNum = " '& Chr (39)
& OrderNo & Chr(39)

MsgBox "SQL Order"

rstOrd.Open "ord", cnNorthwind
MsgBox "Select" & strSQL
MsgBox "My Set State is = " & adStateOpen
MsgBox "My File State is = " & rstOrd.State

With rstOrd
.MoveFirst
MsgBox "Record moved"

If .State = adStateOpen Then
MsgBox "Begin Data population"
Item.UserProperties("CustomerName") = "Dale"
' Item.UserProperties("CityState") = "OFallon"
'Item.UserProperties[CustomerName] = ShipToCustNm
MsgBox "Data population #1"

Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

MsgBox "variables Customer is " & CustomerName
MsgBox "variables City is " & CityState
MsgBox "Name & City to be Changed"
MsgBox "Name & City Changed"
Else
MsgBox "rstOrdstate did not work "
Item.UserProperties("CustomerName") =
ord.ShipToCustNm
Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

' Item.UserProperties("CustomerName")
= "Namedidnotwork"
' Item.UserProperties("CityState")
= "Citydidnotwork"
End If
End With

End Sub

-----Original Message-----
The syntax for the Outlook side is always the same:
Item.UserProperties("CustomerName")

What code are you using to connect to the database
Click to expand...
Click to expand...
table?
I am successful in reading a sql table and now I
Click to expand...
need
to
populate the fields/controls on a custom form with the
name,address info from the data file/table.
How do I do this? When putting it out like this it works:
Item.UserProperties("CustomerName") = "Dale".
However when putting it out with a field name from the
table I get nothing. Here is the snippet using the table
field name:
Item.UserProperties[CustomerName] = ShipToCustNm

Thanks again in advance.
You have been great through this.



.
Click to expand...
Click to expand...


.
Yes I did. I had to double check. I am providing the
Click to expand...
entire script for you to review if need be. Thanks again
for your help.
 
S

Sue Mosher [MVP-Outlook]

Yes I did. I had to double check. I am providing the
entire script for you to review if need be. Thanks again
for your help.
Click to expand...

I don't see any script beyond what you posted earlier. You might want to
test whether each object variation is not set to Nothing .

Having read the script below 3 times, I'm now confused about just which
statements are not working. Can you clarify that, please?

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



dale said:
-----Original Message-----
Did you Dim cnNorthwind in the declarations section (i.e. the top before any
procedures)? If not, your attempt to use that connection anywhere other than
in Item_Open will fail because the object has gone out of scope.

Sorry for the sloppyness, but this is my first attemp at
this type of programming. I used msgBox to let me know
when sertain functions were performed.

Function Item_Open()

Set cnNorthwind = CreateObject("ADODB.Connection")
MsgBox "1a"

cnNorthwind.ConnectionString
= "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=sa;Initial Catalog=TransactionLayer;Data Source=CRE03"
MsgBox "#1b"

' Open connection
cnNorthwind.Open
MsgBox "#1c"

Set cmNorthwind = CreateObject("ADODB.Command")
MsgBox "#1c1"
'Set command connection
cmNorthwind.ActiveConnection = cnNorthwind
MsgBox "#1d"

End Function



'****************************************************** ***
*********************
'* Procedure: Item_CustomPropertyChange(byVal Name)
'* Description: You use a custom property change
event to respond to a change
'* in order. You can't use the
control's intrinsic events. These events
'* currently do not fire in the
VBScript of an Outlook form.
'****************************************************** ***
*********************

' This is good code

Sub Item_CustomPropertyChange(ByVal Name)

Select Case Name
Case "OrderNo"

MsgBox "Property Change to Order"

' Set values
Call cmdSetoutValues
End Select

End Sub



Sub cmdSetoutValues()

'This is good code
MsgBox "Begin data change"

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE OrdNum = 666926"
'strSQL = "SELECT * FROM ord WHERE OrdNum = " '& Chr (39)
& OrderNo & Chr(39)

MsgBox "SQL Order"

rstOrd.Open "ord", cnNorthwind
MsgBox "Select" & strSQL
MsgBox "My Set State is = " & adStateOpen
MsgBox "My File State is = " & rstOrd.State

With rstOrd
.MoveFirst
MsgBox "Record moved"

If .State = adStateOpen Then
MsgBox "Begin Data population"
Item.UserProperties("CustomerName") = "Dale"
' Item.UserProperties("CityState") = "OFallon"
'Item.UserProperties[CustomerName] = ShipToCustNm
MsgBox "Data population #1"

Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

MsgBox "variables Customer is " & CustomerName
MsgBox "variables City is " & CityState
MsgBox "Name & City to be Changed"
MsgBox "Name & City Changed"
Else
MsgBox "rstOrdstate did not work "
Item.UserProperties("CustomerName") =
ord.ShipToCustNm
Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

' Item.UserProperties("CustomerName")
= "Namedidnotwork"
' Item.UserProperties("CityState")
= "Citydidnotwork"
End If
End With

End Sub


-----Original Message-----
The syntax for the Outlook side is always the same:
Item.UserProperties("CustomerName")

What code are you using to connect to the database
Click to expand...
table?
message
I am successful in reading a sql table and now I need
to
populate the fields/controls on a custom form with the
name,address info from the data file/table.
How do I do this? When putting it out like this it
works:
Item.UserProperties("CustomerName") = "Dale".
However when putting it out with a field name from the
table I get nothing. Here is the snippet using the
table
field name:
Item.UserProperties[CustomerName] = ShipToCustNm

Thanks again in advance.
You have been great through this.



.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I am not getting the data base fields into my customer
fields. The following statement is true but yet the
database field ShipToCustNm does not load into the custom
field CustomerName. Here are the statements where I am
having trouble. Thanks again

If .State = adStateOpen Then
MsgBox "Begin Data population"
' Item.UserProperties("CustomerName") = "Dale"
' Item.UserProperties("CityState") = "OFallon"
Item.UserProperties[CustomerName] = ShipToCustNm
MsgBox "Data population #1"

Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

MsgBox "variables Customer is " & CustomerName
MsgBox "variables City is " & CityState
MsgBox "Name & City to be Changed"
MsgBox "Name & City Changed"
Else
MsgBox "rstOrdstate did not work "
Item.UserProperties("CustomerName") =
ord.ShipToCustNm
Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

' Item.UserProperties("CustomerName")
= "Namedidnotwork"
' Item.UserProperties("CityState")
= "Citydidnotwork"
End If
-----Original Message-----
entire script for you to review if need be. Thanks again
for your help.
Click to expand...

I don't see any script beyond what you posted earlier. You might want to
test whether each object variation is not set to Nothing .

Having read the script below 3 times, I'm now confused about just which
statements are not working. Can you clarify that, please?

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



-----Original Message-----
Did you Dim cnNorthwind in the declarations section (i.e. the top before any
procedures)? If not, your attempt to use that
Click to expand...
connection
anywhere other than
in Item_Open will fail because the object has gone out of scope.

Sorry for the sloppyness, but this is my first
Click to expand...
attemp
at
this type of programming. I used msgBox to let me know
when sertain functions were performed.

Function Item_Open()

Set cnNorthwind = CreateObject("ADODB.Connection")
MsgBox "1a"

cnNorthwind.ConnectionString
= "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=sa;Initial Catalog=TransactionLayer;Data Source=CRE03"
MsgBox "#1b"

' Open connection
cnNorthwind.Open
MsgBox "#1c"

Set cmNorthwind = CreateObject("ADODB.Command")
MsgBox "#1c1"
'Set command connection
cmNorthwind.ActiveConnection = cnNorthwind
MsgBox "#1d"

End Function



'******************************************************
***
*********************
'* Procedure: Item_CustomPropertyChange(byVal Name)
'* Description: You use a custom property change
event to respond to a change
'* in order. You can't use the
control's intrinsic events. These events
'* currently do not fire in the
VBScript of an Outlook form.
'******************************************************
***
*********************

' This is good code

Sub Item_CustomPropertyChange(ByVal Name)

Select Case Name
Case "OrderNo"

MsgBox "Property Change to Order"

' Set values
Call cmdSetoutValues
End Select

End Sub



Sub cmdSetoutValues()

'This is good code
MsgBox "Begin data change"

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE OrdNum = 666926"
'strSQL = "SELECT * FROM ord WHERE OrdNum = " '& Chr (39)
& OrderNo & Chr(39)

MsgBox "SQL Order"

rstOrd.Open "ord", cnNorthwind
MsgBox "Select" & strSQL
MsgBox "My Set State is = " & adStateOpen
MsgBox "My File State is = " & rstOrd.State

With rstOrd
.MoveFirst
MsgBox "Record moved"

If .State = adStateOpen Then
MsgBox "Begin Data population"
Item.UserProperties("CustomerName") = "Dale"
' Item.UserProperties("CityState") = "OFallon"
'Item.UserProperties[CustomerName] = ShipToCustNm
MsgBox "Data population #1"

Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

MsgBox "variables Customer is " & CustomerName
MsgBox "variables City is " & CityState
MsgBox "Name & City to be Changed"
MsgBox "Name & City Changed"
Else
MsgBox "rstOrdstate did not work "
Item.UserProperties("CustomerName") =
ord.ShipToCustNm
Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

' Item.UserProperties("CustomerName")
= "Namedidnotwork"
' Item.UserProperties("CityState")
= "Citydidnotwork"
End If
End With

End Sub


-----Original Message-----
The syntax for the Outlook side is always the same:
Item.UserProperties("CustomerName")

What code are you using to connect to the database table?



message
I am successful in reading a sql table and now I need
to
populate the fields/controls on a custom form
Click to expand...
with
the
name,address info from the data file/table.
How do I do this? When putting it out like this it
works:
Item.UserProperties("CustomerName") = "Dale".
However when putting it out with a field name
Click to expand...
from
the
table I get nothing. Here is the snippet using the
table
field name:
Item.UserProperties[CustomerName] = ShipToCustNm

Thanks again in advance.
You have been great through this.



.
Click to expand...
Click to expand...


.
Click to expand...
 
S

Sue Mosher [MVP-Outlook]

As I've already said, Item.UserProperties("CustomerName") is the correct
syntax. Item.UserProperties[CustomerName] is not correct.

You need to replace Where you have ord.fieldname with rstOrd.fieldname,
since rstOrd is the Recordset variable that you are working with. You use
the actual table name "ord" only in the SQL statement to return the
Recordset.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



I am not getting the data base fields into my customer
fields. The following statement is true but yet the
database field ShipToCustNm does not load into the custom
field CustomerName. Here are the statements where I am
having trouble. Thanks again

If .State = adStateOpen Then
MsgBox "Begin Data population"
' Item.UserProperties("CustomerName") = "Dale"
' Item.UserProperties("CityState") = "OFallon"
Item.UserProperties[CustomerName] = ShipToCustNm
MsgBox "Data population #1"

Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

MsgBox "variables Customer is " & CustomerName
MsgBox "variables City is " & CityState
MsgBox "Name & City to be Changed"
MsgBox "Name & City Changed"
Else
MsgBox "rstOrdstate did not work "
Item.UserProperties("CustomerName") =
ord.ShipToCustNm
Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

' Item.UserProperties("CustomerName")
= "Namedidnotwork"
' Item.UserProperties("CityState")
= "Citydidnotwork"
End If
-----Original Message-----
Yes I did. I had to double check. I am providing the
entire script for you to review if need be. Thanks again
for your help.
Click to expand...

I don't see any script beyond what you posted earlier. You might want to
test whether each object variation is not set to Nothing .

Having read the script below 3 times, I'm now confused about just which
statements are not working. Can you clarify that, please?

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



-----Original Message-----
Did you Dim cnNorthwind in the declarations section
(i.e. the top before any
procedures)? If not, your attempt to use that connection
anywhere other than
in Item_Open will fail because the object has gone out
of scope.
Click to expand...
message
Sorry for the sloppyness, but this is my first attemp
at
this type of programming. I used msgBox to let me know
when sertain functions were performed.

Function Item_Open()

Set cnNorthwind = CreateObject("ADODB.Connection")
MsgBox "1a"

cnNorthwind.ConnectionString
= "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=sa;Initial Catalog=TransactionLayer;Data
Source=CRE03"
MsgBox "#1b"

' Open connection
cnNorthwind.Open
MsgBox "#1c"

Set cmNorthwind = CreateObject("ADODB.Command")
MsgBox "#1c1"
'Set command connection
cmNorthwind.ActiveConnection = cnNorthwind
MsgBox "#1d"

End Function




'******************************************************
***
*********************
'* Procedure: Item_CustomPropertyChange(byVal
Name)
'* Description: You use a custom property change
event to respond to a change
'* in order. You can't use the
control's intrinsic events. These events
'* currently do not fire in the
VBScript of an Outlook form.

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

' This is good code

Sub Item_CustomPropertyChange(ByVal Name)

Select Case Name
Case "OrderNo"

MsgBox "Property Change to Order"

' Set values
Call cmdSetoutValues
End Select

End Sub



Sub cmdSetoutValues()

'This is good code
MsgBox "Begin data change"

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE OrdNum = 666926"
'strSQL = "SELECT * FROM ord WHERE OrdNum = " '& Chr
(39)
& OrderNo & Chr(39)

MsgBox "SQL Order"

rstOrd.Open "ord", cnNorthwind
MsgBox "Select" & strSQL
MsgBox "My Set State is = " & adStateOpen
MsgBox "My File State is = " & rstOrd.State

With rstOrd
.MoveFirst
MsgBox "Record moved"

If .State = adStateOpen Then
MsgBox "Begin Data population"
Item.UserProperties("CustomerName") = "Dale"
' Item.UserProperties("CityState") = "OFallon"
'Item.UserProperties[CustomerName] = ShipToCustNm
MsgBox "Data population #1"

Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

MsgBox "variables Customer is " & CustomerName
MsgBox "variables City is " & CityState
MsgBox "Name & City to be Changed"
MsgBox "Name & City Changed"
Else
MsgBox "rstOrdstate did not work "
Item.UserProperties("CustomerName") =
ord.ShipToCustNm
Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

' Item.UserProperties("CustomerName")
= "Namedidnotwork"
' Item.UserProperties("CityState")
= "Citydidnotwork"
End If
End With

End Sub


-----Original Message-----
The syntax for the Outlook side is always the same:
Item.UserProperties("CustomerName")

What code are you using to connect to the database
table?



message
I am successful in reading a sql table and now I
need
to
populate the fields/controls on a custom form with
the
name,address info from the data file/table.
How do I do this? When putting it out like this it
works:
Item.UserProperties("CustomerName") = "Dale".
However when putting it out with a field name from
the
table I get nothing. Here is the snippet using the
table
field name:
Item.UserProperties[CustomerName] = ShipToCustNm

Thanks again in advance.
You have been great through this.



.
Click to expand...


.
Click to expand...
Click to expand...
 
D

dale

Thank you. My ignorance shows through. I appreciate
your help
-----Original Message-----
As I've already said, Item.UserProperties
Click to expand...
("CustomerName") is the correct
syntax. Item.UserProperties[CustomerName] is not correct.

You need to replace Where you have ord.fieldname with rstOrd.fieldname,
since rstOrd is the Recordset variable that you are working with. You use
the actual table name "ord" only in the SQL statement to return the
Recordset.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



I am not getting the data base fields into my customer
fields. The following statement is true but yet the
database field ShipToCustNm does not load into the custom
field CustomerName. Here are the statements where I am
having trouble. Thanks again

If .State = adStateOpen Then
MsgBox "Begin Data population"
' Item.UserProperties("CustomerName") = "Dale"
' Item.UserProperties("CityState") = "OFallon"
Item.UserProperties[CustomerName] = ShipToCustNm
MsgBox "Data population #1"

Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

MsgBox "variables Customer is " & CustomerName
MsgBox "variables City is " & CityState
MsgBox "Name & City to be Changed"
MsgBox "Name & City Changed"
Else
MsgBox "rstOrdstate did not work "
Item.UserProperties("CustomerName") =
ord.ShipToCustNm
Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

' Item.UserProperties("CustomerName")
= "Namedidnotwork"
' Item.UserProperties("CityState")
= "Citydidnotwork"
End If
-----Original Message-----
Yes I did. I had to double check. I am providing the
entire script for you to review if need be. Thanks again
for your help.

I don't see any script beyond what you posted earlier. You might want to
test whether each object variation is not set to Nothing .

Having read the script below 3 times, I'm now confused about just which
statements are not working. Can you clarify that, please?

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers




-----Original Message-----
Did you Dim cnNorthwind in the declarations section
(i.e. the top before any
procedures)? If not, your attempt to use that connection
anywhere other than
in Item_Open will fail because the object has gone out
of scope.


message
Sorry for the sloppyness, but this is my first attemp
at
this type of programming. I used msgBox to let
Click to expand...
me
know
when sertain functions were performed.

Function Item_Open()

Set cnNorthwind = CreateObject ("ADODB.Connection")
MsgBox "1a"

cnNorthwind.ConnectionString
= "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=sa;Initial Catalog=TransactionLayer;Data
Source=CRE03"
MsgBox "#1b"

' Open connection
cnNorthwind.Open
MsgBox "#1c"

Set cmNorthwind = CreateObject("ADODB.Command")
MsgBox "#1c1"
'Set command connection
cmNorthwind.ActiveConnection = cnNorthwind
MsgBox "#1d"

End Function




'******************************************************
***
*********************
'* Procedure: Item_CustomPropertyChange (byVal
Name)
'* Description: You use a custom property change
event to respond to a change
'* in order. You can't use the
control's intrinsic events. These events
'* currently do not fire in the
VBScript of an Outlook form.

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

' This is good code

Sub Item_CustomPropertyChange(ByVal Name)

Select Case Name
Case "OrderNo"

MsgBox "Property Change to Order"

' Set values
Call cmdSetoutValues
End Select

End Sub



Sub cmdSetoutValues()

'This is good code
MsgBox "Begin data change"

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE OrdNum = 666926"
'strSQL = "SELECT * FROM ord WHERE OrdNum = " '& Chr
(39)
& OrderNo & Chr(39)

MsgBox "SQL Order"

rstOrd.Open "ord", cnNorthwind
MsgBox "Select" & strSQL
MsgBox "My Set State is = " & adStateOpen
MsgBox "My File State is = " & rstOrd.State

With rstOrd
.MoveFirst
MsgBox "Record moved"

If .State = adStateOpen Then
MsgBox "Begin Data population"
Item.UserProperties("CustomerName") = "Dale"
' Item.UserProperties("CityState") = "OFallon"
'Item.UserProperties[CustomerName] = ShipToCustNm
MsgBox "Data population #1"

Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

MsgBox "variables Customer is " & CustomerName
MsgBox "variables City is " & CityState
MsgBox "Name & City to be Changed"
MsgBox "Name & City Changed"
Else
MsgBox "rstOrdstate did not work "
Item.UserProperties("CustomerName") =
ord.ShipToCustNm
Item.UserProperties("CityState") =
ord.ShipToAddrTxt
Item.UserProperties("pono") = ord.PONum
Item.UserProperties("CUSTOMER") =
ord.ShipToCustNum

' Item.UserProperties("CustomerName")
= "Namedidnotwork"
' Item.UserProperties("CityState")
= "Citydidnotwork"
End If
End With

End Sub


-----Original Message-----
The syntax for the Outlook side is always the same:
Item.UserProperties("CustomerName")

What code are you using to connect to the database
table?



"Dale" <[email protected]>
Click to expand...
wrote
in
message
I am successful in reading a sql table and now I
need
to
populate the fields/controls on a custom form with
the
name,address info from the data file/table.
How do I do this? When putting it out like
Click to expand...
this
it
works:
Item.UserProperties("CustomerName") = "Dale".
However when putting it out with a field name from
the
table I get nothing. Here is the snippet
Click to expand...
using
the
table
field name:
Item.UserProperties[CustomerName] = ShipToCustNm

Thanks again in advance.
You have been great through this.



.





.
Click to expand...
Click to expand...


.
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

Copy previous record into form field 0
Outlook 2003 Saving into a textbox when form closes 3
Access 2013: Form problem in reading data tables; relationship one-to-one 0
Import Excel values into a form 0
Combo Box on form - how to control what value saved in table 1
Populate Message field based on custom field 8
How to populate other fields when a dropdown was changed 13
Accessing Address Book with Redemption Object Model 0

Top