filtering bound data based on custom field

[Mike Abrahamson]

I have found a way to bind data from an Access database using the
Spreadsheet Control. What I need to do now is only show the records where a
certain database field matches a custom field in my custom Outlook contact
form. I was thinking the simplest way to do this would be to include this
in the SQL statement for the Spreadsheet Control's Data Source option, but I
don't know how to reference the custom field in the SQL query, or even if
this is possible.

Is this the correct way to do what I want? I'm only looking for read-only
ability here so that should simplify things.

Thanks for any help,
Mike

 

[Mike Abrahamson]

OK, so far I'm using this code in the custom form:

Dim strAgencyWorksID
Sub cmdLinkProduction

strAgencyWorksID = Item.UserProperties("Agency Works ID")

End Sub

Then in my SQL statement on the Spreadsheet control I am specifying the
following (I already have the ADO connection string defined elsewhere and
can pull in data from the desired database query if I don't use the WHERE
statement below):

Select * FROM Total Query WHERE ((([Total Query].FSTAGT) =
strAgencyWorksID))

All I get using this code in the spreadsheet control is "No value given for
one or more required parameters." I've also tried specifying
"strAgencyWorksID.Value" as the last argument but got the same result. Full
disclosure - the custom field in question is a Number field... would that
make a difference?

I'm really not a programmer so this is all Greek to me (well, I sort of
understand some of it). Can anyone tell me if I'm way off base here? I'd
appreciate any help. I still can't help but think that what I want to do
isn't all that difficult for someone who knows what they're doing, which
obviously I don't.

Thanks for ANY help,
Mike

 

[Ken Slovak - [MVP - Outlook]]

If you are querying a numeric field in a database you can't use a string
value for the SQL query. Cast your string value to a Long:
Dim lngValue
lngValue = Val(strAgencyWorksID)

then use that in your SQL string. Make sure the Long value is not enclosed
in quotes or it will end up as the name of the variable rather than its
value.

 

[Mike Abrahamson]

I tried your suggestion, which gave me the following code in the custom
form:
Dim strAgencyWorksID

Dim lngValue

Sub cmdLinkProduction

strAgencyWorksID = Item.UserProperties("Agency Works
ID")

lngValue = Val(strAgencyWorksID)

End Sub

.... and the following SQL statement in the spreadsheet control:

Select * FROM Total Query WHERE (([Total Query].FSTAGT) = lngValue)

I still get the same message in the spreadsheet control, however ("No value
given for one or more required parameters."). The contact record I am using
to test does have a legitimate ID # that matches records in the database, so
I know that's not the problem.

Is it possibly because the SQL statement inside the spreadsheet control is
not explicitly aware of the form's code and therefore doesn't know what
"lngValue" refers to in the form? Do I need to explicitly define the
current Outlook session somehow in the SQL statement? I'm grabbing at
straws here out of complete ignorance. I really appreciate your help.
Getting this to work would be a major coup for my company.

Thanks for any further assistance!
Mike

If you are querying a numeric field in a database you can't use a string
value for the SQL query. Cast your string value to a Long:
Dim lngValue
lngValue = Val(strAgencyWorksID)

then use that in your SQL string. Make sure the Long value is not enclosed
in quotes or it will end up as the name of the variable rather than its
value.

OK, so far I'm using this code in the custom form:

Dim strAgencyWorksID
Sub cmdLinkProduction

strAgencyWorksID = Item.UserProperties("Agency Works ID")

End Sub

Then in my SQL statement on the Spreadsheet control I am specifying the
following (I already have the ADO connection string defined elsewhere and
can pull in data from the desired database query if I don't use the WHERE
statement below):

Select * FROM Total Query WHERE ((([Total Query].FSTAGT) =
strAgencyWorksID))

All I get using this code in the spreadsheet control is "No value given for
one or more required parameters." I've also tried specifying
"strAgencyWorksID.Value" as the last argument but got the same result. Full
disclosure - the custom field in question is a Number field... would that
make a difference?

I'm really not a programmer so this is all Greek to me (well, I sort of
understand some of it). Can anyone tell me if I'm way off base here? I'd
appreciate any help. I still can't help but think that what I want to do
isn't all that difficult for someone who knows what they're doing, which
obviously I don't.

Thanks for ANY help,
Mike

 

[Ken Slovak - [MVP - Outlook]]

Well, I don't think that the data control would know anything about
lngValue. I never use bound controls like that, I just write raw SQL
statements when I have to do something like that so I don't know how you
would set a filter based on a specific non-constant value.

What version of Outlook are you using and what spreadsheet control and how
are you binding it to a data source and so on? I'll try to play with the
control and see if I can figure out how to do what you want.

Personally, I'd just use a grid control unbound to anything and use SQL in
my form code to get into the Access database using an ADO connection and
populate the grid that way. I know I can use any SQL statement possible that
way to filter my recordset and then iterate the recordset to populate the
grid control.

 

[Mike Abrahamson]

I am using Outlook 2003 with the Microsoft Office 11.0 Spreadsheet Control.
In the "Commands and Options" menu of the control, under the "Data Source"
tab, I am specifying the following connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\\server\data\AgencyWorks\AWproduction.mdb;Persist Security Info=True

Then in the "Command text or SQL" box I am using the following:

Select * FROM Total Query WHERE (([Total Query].FSTAGT) = lngValue)

I agree that it would probably be easier to just include the SQL statement
in the form's code. One of my concerns with any scenario is performance.
The external database I am referencing has several thousand records in the
specified query. I can't have users trying to open the custom contact form
and having to wait several seconds or minutes for the code to perform the
SQL query against the DB. I'd like to have the grid control on page 2 or 3
of the form and either cache the info or run in the background after the
form is opened so the user can at least see the info on the other pages of
the form. Is this a problem?

Thanks so much for your help thus far!

Mike

Well, I don't think that the data control would know anything about
lngValue. I never use bound controls like that, I just write raw SQL
statements when I have to do something like that so I don't know how you
would set a filter based on a specific non-constant value.

What version of Outlook are you using and what spreadsheet control and how
are you binding it to a data source and so on? I'll try to play with the
control and see if I can figure out how to do what you want.

Personally, I'd just use a grid control unbound to anything and use SQL in
my form code to get into the Access database using an ADO connection and
populate the grid that way. I know I can use any SQL statement possible that
way to filter my recordset and then iterate the recordset to populate the
grid control.

I tried your suggestion, which gave me the following code in the custom
form:
Dim strAgencyWorksID

Dim lngValue

Sub cmdLinkProduction

strAgencyWorksID = Item.UserProperties("Agency Works
ID")

lngValue = Val(strAgencyWorksID)

End Sub

... and the following SQL statement in the spreadsheet control:

Select * FROM Total Query WHERE (([Total Query].FSTAGT) = lngValue)

I still get the same message in the spreadsheet control, however ("No value
given for one or more required parameters."). The contact record I am using
to test does have a legitimate ID # that matches records in the

database,

so
I know that's not the problem.

Is it possibly because the SQL statement inside the spreadsheet control is
not explicitly aware of the form's code and therefore doesn't know what
"lngValue" refers to in the form? Do I need to explicitly define the
current Outlook session somehow in the SQL statement? I'm grabbing at
straws here out of complete ignorance. I really appreciate your help.
Getting this to work would be a major coup for my company.

Thanks for any further assistance!
Mike

 

[Ken Slovak - [MVP - Outlook]]

If what you want to do can be done at all then you'd somehow have to use a
fully qualified reference to the field in the Outlook item rather than to a
variable value. I don't think it can be done.

I haven't noticed any particularly long response times when accessing a
database using ADO and SQL from form code in VBScript. I do it all the time.
If you want things to be even faster you could use COM addin compiled code
to get the recordset and then fill in a grid control when the form opens by
handling the NewInspector event and if the item in the Inspector is one of
interest you can get the field value, get your filtered recordset and then
populate the grid. You might even be able to access the properties of the
spreadsheet control on the form and populate that control from the filtered
recordset that way.