Hi there,

I am in the process of migrating an application written in Access2002/03
format. As I understand it these two file versions are of the same format and
will run under both versions of Access. The migration at this stage is purely
running this application on Access 2003 as it currently runs on Access 2002.
So there is no file conversion required. So I have installed Acess2003 and
run the application and have found one issue where it does not seem to be
compatible.

We have some combo boxes that have their source set to a Stored Procedure
with parameters. As far as I can see the parameters are declared in VBA as
global variables of the same name as the stored procedure parameters.
Access2002 seems to Automatically pass these global variables to the
procedure when a combo box requery occurs. After running this ade in
Access2003 this functionality no longer seems to work and Access throws up a
parameter box asking for the user to input the parameters.

So my question is: Is the way this parameter passing is done correct or not?
and if not what would be the correct way to pass parameters in this example.
I was hoping that we wouldn't have to find every example of this and correct
the code.

Any thoughts would be appreciated.

Thanks

--
Devron Blatchford

Hi Devron,
To let me better understand your issue, could you please answer me the
following questions:
1. Did you use an Access Database Project (.adp)?

2. Could you please elaborate how you set your combo boxes' sources to a
stored procedure?

3. What is the result if you set the parameters dynamically like that in
this KB article?
How to dynamically set the input parameters of a report at run time in
an ADP
http://support.microsoft.com/kb/300693/en-us

Look forward to your response. If you have any other questions ro concerns,
please feel free to let me know.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (E-Mail Removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

On Sun, 29 Jun 2008 16:53:10 -0700, Devron Blatchford
<(E-Mail Removed)> wrote:

Show us the code of the RowSource, and where the global (shudder)
variables are set.

-Tom.


>Hi there,
>
>I am in the process of migrating an application written in Access2002/03
>format. As I understand it these two file versions are of the same format and
>will run under both versions of Access. The migration at this stage is purely
>running this application on Access 2003 as it currently runs on Access 2002.
>So there is no file conversion required. So I have installed Acess2003 and
>run the application and have found one issue where it does not seem to be
>compatible.
>
>We have some combo boxes that have their source set to a Stored Procedure
>with parameters. As far as I can see the parameters are declared in VBA as
>global variables of the same name as the stored procedure parameters.
>Access2002 seems to Automatically pass these global variables to the
>procedure when a combo box requery occurs. After running this ade in
>Access2003 this functionality no longer seems to work and Access throws up a
>parameter box asking for the user to input the parameters.
>
>So my question is: Is the way this parameter passing is done correct or not?
>and if not what would be the correct way to pass parameters in this example.
>I was hoping that we wouldn't have to find every example of this and correct
>the code.
>
>Any thoughts would be appreciated.
>
>Thanks

Hi there,

It's an adp connected to SQL2000 Database.

The combo box's rowsource is set to the name of the stored procedure,
nothing fancy there just a string = "qPeriodForTransactionLookup"

The following sub is called to requery the combo box when required. The
variables that are set are also parameters to the procedure with the same
names minus the @ symbol. These variables are decalred as public at the
header of the forms module.

I am fully aware that the naming conventions and using these public
variables in not good practice. We will be looking to address these issues in
the future but for now my questions is: Does Access2002 allow this by design
and if so was it removed in ACC2003 for some reason. Or could it be something
that the developers stumbled accross and used even though it was unsupported
maybe?

Thanks

Public Sub RequeryPeriodCombo()
On Error GoTo ProcErr
Const strProcedureName = "RequeryPeriodCombo"

TransactionDate = Format(Nz(txtOrderDate, Date), "d mmm yyyy")
TransactionTypeID = 7
AllowRetro = True
EntityID = Nz(cboLocationID.Column(3), 0)

cboPeriodID.Requery


ProcExit:
Exit Sub

ProcErr:
If basError.GetResumeStatus(strProcedureName) Then
Resume
Else
Resume ProcExit
End If
End Sub

--
Devron Blatchford


""Charles Wang [MSFT]"" wrote:

> Hi Devron,
> To let me better understand your issue, could you please answer me the
> following questions:
> 1. Did you use an Access Database Project (.adp)?
>
> 2. Could you please elaborate how you set your combo boxes' sources to a
> stored procedure?
>
> 3. What is the result if you set the parameters dynamically like that in
> this KB article?
> How to dynamically set the input parameters of a report at run time in
> an ADP
> http://support.microsoft.com/kb/300693/en-us
>
> Look forward to your response. If you have any other questions ro concerns,
> please feel free to let me know.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ===========================================================
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: (E-Mail Removed).
> ===========================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications.
>
> Note: The MSDN Managed Newsgroup support offering is for
> non-urgent issues where an initial response from the community
> or a Microsoft Support Engineer within 1 business day is acceptable.
> Please note that each follow up response may take approximately
> 2 business days as the support professional working with you may
> need further investigation to reach the most efficient resolution.
> The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by
> contacting Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscripti...t/default.aspx.
> ============================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>

On Mon, 30 Jun 2008 15:45:37 -0700, Devron Blatchford
<(E-Mail Removed)> wrote:

You're saying that the stored procedure that is the rowsource of
cboPeriodID (perhaps qPeriodForTransactionLookup) takes four arguments
and that they are @TransactionDate etc, and that up to A2002 these
argument values were somehow magically passed into the sproc?
And you confirmed this with SQL Server Profiler that indeed these
values were passed in?

That seems really hard to believe.

One way to implement this is to put an EXEC statement as the RowSource
in your Form_Load event:
cboPeriodID.RowSource = GetRowSource("cboPeriodID")

And then in a private function GetRowSource:
Private Function GetRowSource(byval strCombo as string) as String
dim strResult as string
select case strCombo
case "cboPeriodID"
strResult = "EXEC qPeriodForTransactionLookup '" & TransactionDate
& "'," & TransactionTypeID & "," & AllowRetro & "," & EntityID
'case AnotherCbo
end select
GetRowSource = strResult
end function

-Tom.


>Hi there,
>
>It's an adp connected to SQL2000 Database.
>
>The combo box's rowsource is set to the name of the stored procedure,
>nothing fancy there just a string = "qPeriodForTransactionLookup"
>
>The following sub is called to requery the combo box when required. The
>variables that are set are also parameters to the procedure with the same
>names minus the @ symbol. These variables are decalred as public at the
>header of the forms module.
>
>I am fully aware that the naming conventions and using these public
>variables in not good practice. We will be looking to address these issues in
>the future but for now my questions is: Does Access2002 allow this by design
>and if so was it removed in ACC2003 for some reason. Or could it be something
>that the developers stumbled accross and used even though it was unsupported
>maybe?
>
>Thanks
>
>Public Sub RequeryPeriodCombo()
> On Error GoTo ProcErr
> Const strProcedureName = "RequeryPeriodCombo"
>
> TransactionDate = Format(Nz(txtOrderDate, Date), "d mmm yyyy")
> TransactionTypeID = 7
> AllowRetro = True
> EntityID = Nz(cboLocationID.Column(3), 0)
>
> cboPeriodID.Requery
>
>
>ProcExit:
> Exit Sub
>
>ProcErr:
> If basError.GetResumeStatus(strProcedureName) Then
> Resume
> Else
> Resume ProcExit
> End If
>End Sub

Hi Tom,

Thanks for your response. Your assumptions are correct. As hard as it may be
for you to believe that is what actually happens hence the post as I found it
unusual myself! I am aware of your suggested solution so thanks for
clarifying this for me. I was really wondering if this was a feature that was
removed in ACC2003 or if it was an "undocumented feature" that someone has
come across and used in ACC2002.

Might just have to stick with ACC2002 until we get time to address the code.

Thanks again for your help
--
Devron Blatchford


"Tom van Stiphout" wrote:

> On Mon, 30 Jun 2008 15:45:37 -0700, Devron Blatchford
> <(E-Mail Removed)> wrote:
>
> You're saying that the stored procedure that is the rowsource of
> cboPeriodID (perhaps qPeriodForTransactionLookup) takes four arguments
> and that they are @TransactionDate etc, and that up to A2002 these
> argument values were somehow magically passed into the sproc?
> And you confirmed this with SQL Server Profiler that indeed these
> values were passed in?
>
> That seems really hard to believe.
>
> One way to implement this is to put an EXEC statement as the RowSource
> in your Form_Load event:
> cboPeriodID.RowSource = GetRowSource("cboPeriodID")
>
> And then in a private function GetRowSource:
> Private Function GetRowSource(byval strCombo as string) as String
> dim strResult as string
> select case strCombo
> case "cboPeriodID"
> strResult = "EXEC qPeriodForTransactionLookup '" & TransactionDate
> & "'," & TransactionTypeID & "," & AllowRetro & "," & EntityID
> 'case AnotherCbo
> end select
> GetRowSource = strResult
> end function
>
> -Tom.
>
>
> >Hi there,
> >
> >It's an adp connected to SQL2000 Database.
> >
> >The combo box's rowsource is set to the name of the stored procedure,
> >nothing fancy there just a string = "qPeriodForTransactionLookup"
> >
> >The following sub is called to requery the combo box when required. The
> >variables that are set are also parameters to the procedure with the same
> >names minus the @ symbol. These variables are decalred as public at the
> >header of the forms module.
> >
> >I am fully aware that the naming conventions and using these public
> >variables in not good practice. We will be looking to address these issues in
> >the future but for now my questions is: Does Access2002 allow this by design
> >and if so was it removed in ACC2003 for some reason. Or could it be something
> >that the developers stumbled accross and used even though it was unsupported
> >maybe?
> >
> >Thanks
> >
> >Public Sub RequeryPeriodCombo()
> > On Error GoTo ProcErr
> > Const strProcedureName = "RequeryPeriodCombo"
> >
> > TransactionDate = Format(Nz(txtOrderDate, Date), "d mmm yyyy")
> > TransactionTypeID = 7
> > AllowRetro = True
> > EntityID = Nz(cboLocationID.Column(3), 0)
> >
> > cboPeriodID.Requery
> >
> >
> >ProcExit:
> > Exit Sub
> >
> >ProcErr:
> > If basError.GetResumeStatus(strProcedureName) Then
> > Resume
> > Else
> > Resume ProcExit
> > End If
> >End Sub
>

Hi Devron,
Thank you for your response.

Since Office XP and the earlier versions are not supported now, it is hard
for us to further check how your original code worked in ACC2002. In
addition to Tom's suggestion, generally for using stored procedure in
Access, I recommend that you refer to this article:
Using Stored Procedures in ADPs
http://msdn.microsoft.com/en-us/library/aa160569(office.11).aspx

You may check if the methods worked for you. If you want to further check
and compare the differences for this issue between ACC2002 and ACC2003,
could you please send me (changliw_at_microsoft_dot_com) a test ACC2002 adp
project with a test database backup so that I can understand your issue
more clearly and correctly reproduce this issue? After that I can perform
further research or effectively try to consult our product team.

If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (E-Mail Removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

The temporary work-around is to create a control for
each form/report public property that you had in an
earlier version.

This is a generic difference between Access 2003-2007
and Access 2002-2000-97-2.0. I've never seen it
reported for an ADP before, but the problem is a well
known problem with Access 2003-2007 MDBs.

There was no documented explanation for why this feature
was removed.

Public Variables are created as Properties in A2002-,
and are available to controls. It was a way of declaring a
public property of a form/report. In A2003+, Public
Variables are not created as Properties, and are not
available to controls. Now that people are transferring
from A2002 to A2007, a whole new set of people are
seeing this problem. It may be worse, or it may just have
visibility again.

The creation of Public Variables as Form/Report Properties
existed by design. The ability to use Form/Report Properties
in queries and in control sources existed by design.

Since A2003 was released 5 years ago, I do not expect
that this feature will ever be restored.

The suggestion that this is an undocumented problem is
ludicrous. Either it's ludicrous that they can't find the
documentation, or it's ludicrous that they chose not to
document the problem.

(david)



"Devron Blatchford" <(E-Mail Removed)> wrote in message
news:A58E3A26-5AE6-49EF-A4F3-(E-Mail Removed)...
> Hi there,
>
> It's an adp connected to SQL2000 Database.
>
> The combo box's rowsource is set to the name of the stored procedure,
> nothing fancy there just a string = "qPeriodForTransactionLookup"
>
> The following sub is called to requery the combo box when required. The
> variables that are set are also parameters to the procedure with the same
> names minus the @ symbol. These variables are decalred as public at the
> header of the forms module.
>
> I am fully aware that the naming conventions and using these public
> variables in not good practice. We will be looking to address these issues
in
> the future but for now my questions is: Does Access2002 allow this by
design
> and if so was it removed in ACC2003 for some reason. Or could it be
something
> that the developers stumbled accross and used even though it was
unsupported
> maybe?
>
> Thanks
>
> Public Sub RequeryPeriodCombo()
> On Error GoTo ProcErr
> Const strProcedureName = "RequeryPeriodCombo"
>
> TransactionDate = Format(Nz(txtOrderDate, Date), "d mmm yyyy")
> TransactionTypeID = 7
> AllowRetro = True
> EntityID = Nz(cboLocationID.Column(3), 0)
>
> cboPeriodID.Requery
>
>
> ProcExit:
> Exit Sub
>
> ProcErr:
> If basError.GetResumeStatus(strProcedureName) Then
> Resume
> Else
> Resume ProcExit
> End If
> End Sub
>
> --
> Devron Blatchford
>
>
> ""Charles Wang [MSFT]"" wrote:
>
> > Hi Devron,
> > To let me better understand your issue, could you please answer me the
> > following questions:
> > 1. Did you use an Access Database Project (.adp)?
> >
> > 2. Could you please elaborate how you set your combo boxes' sources to a
> > stored procedure?
> >
> > 3. What is the result if you set the parameters dynamically like that in
> > this KB article?
> > How to dynamically set the input parameters of a report at run time
in
> > an ADP
> > http://support.microsoft.com/kb/300693/en-us
> >
> > Look forward to your response. If you have any other questions ro
concerns,
> > please feel free to let me know.
> >
> > Best regards,
> > Charles Wang
> > Microsoft Online Community Support
> > ===========================================================
> > Delighting our customers is our #1 priority. We welcome your
> > comments and suggestions about how we can improve the
> > support we provide to you. Please feel free to let my manager
> > know what you think of the level of service provided. You can
> > send feedback directly to my manager at: (E-Mail Removed).
> > ===========================================================
> > Get notification to my posts through email? Please refer to
> >
http://msdn.microsoft.com/subscripti...ult.aspx#notif
> > ications.
> >
> > Note: The MSDN Managed Newsgroup support offering is for
> > non-urgent issues where an initial response from the community
> > or a Microsoft Support Engineer within 1 business day is acceptable.
> > Please note that each follow up response may take approximately
> > 2 business days as the support professional working with you may
> > need further investigation to reach the most efficient resolution.
> > The offering is not appropriate for situations
> > that require urgent, real-time or phone-based interactions or complex
> > project analysis and dump analysis issues. Issues of this nature are
best
> > handled working with a dedicated Microsoft Support Engineer by
> > contacting Microsoft Customer Support Services (CSS) at
> > http://msdn.microsoft.com/subscripti...t/default.aspx.
> > ============================================================
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > =========================================================
> >
> >

Hi David,

Thank you for this post, is has clarified the problem and been very helpful
to me.

Cheers
--
Devron Blatchford


"david@epsomdotcomdotau" wrote:

> The temporary work-around is to create a control for
> each form/report public property that you had in an
> earlier version.
>
> This is a generic difference between Access 2003-2007
> and Access 2002-2000-97-2.0. I've never seen it
> reported for an ADP before, but the problem is a well
> known problem with Access 2003-2007 MDBs.
>
> There was no documented explanation for why this feature
> was removed.
>
> Public Variables are created as Properties in A2002-,
> and are available to controls. It was a way of declaring a
> public property of a form/report. In A2003+, Public
> Variables are not created as Properties, and are not
> available to controls. Now that people are transferring
> from A2002 to A2007, a whole new set of people are
> seeing this problem. It may be worse, or it may just have
> visibility again.
>
> The creation of Public Variables as Form/Report Properties
> existed by design. The ability to use Form/Report Properties
> in queries and in control sources existed by design.
>
> Since A2003 was released 5 years ago, I do not expect
> that this feature will ever be restored.
>
> The suggestion that this is an undocumented problem is
> ludicrous. Either it's ludicrous that they can't find the
> documentation, or it's ludicrous that they chose not to
> document the problem.
>
> (david)
>
>
>
> "Devron Blatchford" <(E-Mail Removed)> wrote in message
> news:A58E3A26-5AE6-49EF-A4F3-(E-Mail Removed)...
> > Hi there,
> >
> > It's an adp connected to SQL2000 Database.
> >
> > The combo box's rowsource is set to the name of the stored procedure,
> > nothing fancy there just a string = "qPeriodForTransactionLookup"
> >
> > The following sub is called to requery the combo box when required. The
> > variables that are set are also parameters to the procedure with the same
> > names minus the @ symbol. These variables are decalred as public at the
> > header of the forms module.
> >
> > I am fully aware that the naming conventions and using these public
> > variables in not good practice. We will be looking to address these issues
> in
> > the future but for now my questions is: Does Access2002 allow this by
> design
> > and if so was it removed in ACC2003 for some reason. Or could it be
> something
> > that the developers stumbled accross and used even though it was
> unsupported
> > maybe?
> >
> > Thanks
> >
> > Public Sub RequeryPeriodCombo()
> > On Error GoTo ProcErr
> > Const strProcedureName = "RequeryPeriodCombo"
> >
> > TransactionDate = Format(Nz(txtOrderDate, Date), "d mmm yyyy")
> > TransactionTypeID = 7
> > AllowRetro = True
> > EntityID = Nz(cboLocationID.Column(3), 0)
> >
> > cboPeriodID.Requery
> >
> >
> > ProcExit:
> > Exit Sub
> >
> > ProcErr:
> > If basError.GetResumeStatus(strProcedureName) Then
> > Resume
> > Else
> > Resume ProcExit
> > End If
> > End Sub
> >
> > --
> > Devron Blatchford
> >
> >
> > ""Charles Wang [MSFT]"" wrote:
> >
> > > Hi Devron,
> > > To let me better understand your issue, could you please answer me the
> > > following questions:
> > > 1. Did you use an Access Database Project (.adp)?
> > >
> > > 2. Could you please elaborate how you set your combo boxes' sources to a
> > > stored procedure?
> > >
> > > 3. What is the result if you set the parameters dynamically like that in
> > > this KB article?
> > > How to dynamically set the input parameters of a report at run time
> in
> > > an ADP
> > > http://support.microsoft.com/kb/300693/en-us
> > >
> > > Look forward to your response. If you have any other questions ro
> concerns,
> > > please feel free to let me know.
> > >
> > > Best regards,
> > > Charles Wang
> > > Microsoft Online Community Support
> > > ===========================================================
> > > Delighting our customers is our #1 priority. We welcome your
> > > comments and suggestions about how we can improve the
> > > support we provide to you. Please feel free to let my manager
> > > know what you think of the level of service provided. You can
> > > send feedback directly to my manager at: (E-Mail Removed).
> > > ===========================================================
> > > Get notification to my posts through email? Please refer to
> > >
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> > > ications.
> > >
> > > Note: The MSDN Managed Newsgroup support offering is for
> > > non-urgent issues where an initial response from the community
> > > or a Microsoft Support Engineer within 1 business day is acceptable.
> > > Please note that each follow up response may take approximately
> > > 2 business days as the support professional working with you may
> > > need further investigation to reach the most efficient resolution.
> > > The offering is not appropriate for situations
> > > that require urgent, real-time or phone-based interactions or complex
> > > project analysis and dump analysis issues. Issues of this nature are
> best
> > > handled working with a dedicated Microsoft Support Engineer by
> > > contacting Microsoft Customer Support Services (CSS) at
> > > http://msdn.microsoft.com/subscripti...t/default.aspx.
> > > ============================================================
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > =========================================================
> > >
> > >
>
>
>