Outlook 07 Custom Task Form and SQL

[Job]

I've been searching the net finding examples of connecting to a sql db in
order to populate a custom form drop down. I've had my example code in the
vb editor...can I only use the vb script editor? Everytime I reference
Set ctl = Item.GetInspector.ModifiedFormPages("PM
Task").Controls("cmbo_Client")
for example I get the error that the object can't be found.

Cheers,

Job

 

[Sue Mosher [MVP-Outlook]]

Correct. All code behind an Outlook form must be written in VBScript. The error you're getting suggests either a typo on the page name or the control name. You can narrow down the problem by getting just one object per statement.

 

[Job]

Ok,

I a discussion that addresses this issue. Is the best way then from Visual
Studio or something?

 

[Job]

Hi Sue,

I've used your information a lot trying to solve this..can you look at the
following code. I don't use vbscript much so I'm not sure why I'm getting
the following error.
"Expected end of statement" and it is initially pointing to my dim
statment. If I comment that out it also has the same error on the variable
'e'
Here is the code

Sub FillClientData()

Dim rst As New ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open "DSN=db_OCEANS15"

Sql = "Select pj.cl_ClientNum + '_' + cl_Client_Short_Desc From
tbl_prj_Projects pj left join tbl_cl_Clients cl On pj.cl_ClientNum =
cl.cl_ClientNum Where len(pj.cl_ClientNum + '_' + cl_Client_Short_Desc) > 4
Group By pj.cl_ClientNum + '_' + cl_Client_Short_Desc"



Set ctl = Item.GetInspector.ModifiedFormPages("PM
Task").Controls("cmbo_Client")
rst.Open Sql, Conn, adOpenKeyset, adLockOptimistic, Options:=adCmdText


rst.MoveLast
rst.MoveFirst

ReDim MyArray(rst.RecordCount - 1)

For I = 0 To rst.RecordCount - 1

MyArray(I) = rst(0)

rst.MoveNext

Next

For e = 0 To rst.RecordCount - 1

ctl.AddItem MyArray(e)
Next e

'cmbo_Client.List() = MyArray

End Sub

Any help is greatly appreciated

 

[Job]

Ok,

I figured it out. Here is the code that I used.

Dim rst
Dim e
Dim I
Dim ctl

Sub cmd_ClientUpdate_Click()

FillClientData()

End Sub


Function FillClientData()

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "DSN=db_OCEANS15"

conn.CursorLocation = 3
Sql = "Select pj.cl_ClientNum + '_' + cl_Client_Short_Desc From
tbl_prj_Projects pj left join tbl_cl_Clients cl On pj.cl_ClientNum =
cl.cl_ClientNum Where len(pj.cl_ClientNum + '_' + cl_Client_Short_Desc) > 4
Group By pj.cl_ClientNum + '_' + cl_Client_Short_Desc"

Set rst = Conn.Execute(Sql)


Set ctl = Item.GetInspector.ModifiedFormPages("PM
Task").Controls("cmbo_Client")
'rst.Open Sql, Conn, adOpenKeyset, adLockOptimistic', Options:=adCmdText


rst.MoveLast
rst.MoveFirst

ReDim MyArray(rst.RecordCount - 1)

For I = 0 To rst.RecordCount - 1

MyArray(I) = rst(0)

rst.MoveNext

Next

For e = 0 To rst.RecordCount - 1

ctl.AddItem MyArray(e)
Next

'cmbo_Client.List() = MyArray

End Function


Now only one problem. when I open this form the button doesn't work.
However, when I go into developer mode and then choose 'Run This Form' then
the button works fine. Any ideas?

Cheers

 

[Sue Mosher [MVP-Outlook]]

VBScript doesn't support data typing for variable or procedure declarations. You'd need to comment out the As clauses. Also don't forget to declare your ad* constants.

The alternative, BTW, would be to develop an add-in that manages the form's UI, not with code behind the form but through the events that fire when the user interacts with the form.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

 

[Job]

These add-ins give more control over the development of the form? are you
referring to your site http://www.outlookcode.com/article.aspx?ID=36 or is it
a different kind of add-in via vba or something?

 

[Sue Mosher [MVP-Outlook]]

Not more control. The same control, but with a different architecture and your choice of programming language, instead of VBScript. Traditional COM add-ins are one solution. Add-ins built with VSTO are another. Both make the project somewhat more complex, as you get into deployment issues.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

 

[Job]

I sent you an email to your listed account, but can I hire you to help create
this form and functionality?

 

[Sue Mosher [MVP-Outlook]]

If you are seeking paid help, you can send a message to outlook-dev-hire at yahoogroups.com with details of your requirements, including the Outlook version(s) to be supported. Once the moderator approves your message, Outlook developers on the outlook-dev-hire list can respond directly to you if they're interested in your project.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

 

[Job]

Sue, I have everything working now. Thanks for your help. Didn't need an
outside developer as of yet. I'm having an issue. I've read all of the
posts I can find on the code not running due to one-off etc. Let me tell you
the situation from the beginning and hopefully you'll see my error somewhere.

We have decided to start using tasks as our psuedo project management
solution. My first thought was to use the public folder as the repository
for all of the tasks, that way one could look to see the 'universe' of
projects. I quickly realized that this is good in theory, however, you
cannot assign tasks to others from the public folder (tell me if I'm wrong
here).

So I decided that if we created a generic user and then gave permissions to
all the users for the generic persons tasks, this would solve the problem.
This all works fine.

I created the custom form, for which you've seen the code. A few of the
boxes are pulled from an SQL db (though it would be nice to update the list
and save the list, maybe through the Possible Values? and only update when
something in the list is not found..ie project number).

Here is where I'm mixed up. If I publish the custom form to the
Organizational Forms Library it is available to all as would be expected.

Because I'm using the 'People's Tasks' Shared task folder > 'EQC' If I
want to create tasks in the name of EQC I have to select this folder and
create the task. If I use the right-click>Properties>When Posting to this
folder use> Organizational Forms Library>%Published Custom Form Name% Then
when I create a new task with as EQC from the shared task folder, the code
does not work. If I choose New>Choose Form and choose the OFL published form
then the code works, but now it is under my name not the EQC. I read about
the Allow script check boxes in Outlook 2003 post you've mentioned, but I
don't have that option in 2007 and I don't see the reference to this in the
registry.

All of your help is greatly appreciated.

 

[Job]

http://support.microsoft.com/kb/929590

This did the trick. Just had to know where to look in 2007 > Trust Center.
Any thoughts on this part
"update the list
and save the list, maybe through the Possible Values? and only update when
something in the list is not found..ie project number"

Thanks

 

[Sue Mosher [MVP-Outlook]]

Never, ever use the PossibleValues property of a combo or list box control to set rows programmatically. Doing so will one-off the form. Always use regular methods to load the list.

If you want to save the list, you could put it in a StorageItem object in a mail/post public folder. Or wait until SP1 comes out, which hopefully will fix the current bug with creating StorageItem objects in non-mail/post folders.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54