|
|
|
date: Sun, 29 Jun 2008 16:53:10 -0700,
group: microsoft.public.access.adp.sqlserver
back
RE: Compatibility (2002->2003)
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: msdnmg@microsoft.com.
> ===========================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/default.aspx.
> ============================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>
date: Mon, 30 Jun 2008 15:45:37 -0700
author: Devron Blatchford am
Re: Compatibility (2002->2003)
On Mon, 30 Jun 2008 15:45:37 -0700, Devron Blatchford
<fred@nospam.nospam> 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
date: Mon, 30 Jun 2008 21:45:14 -0700
author: Tom van Stiphout
Re: Compatibility (2002->2003)
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
> <fred@nospam.nospam> 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
>
date: Mon, 30 Jun 2008 23:17:01 -0700
author: Devron Blatchford am
Re: Compatibility (2002->2003)
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" <fred@nospam.nospam> wrote in message
news:A58E3A26-5AE6-49EF-A4F3-9B2D92E5785A@microsoft.com...
> 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: msdnmg@microsoft.com.
> > ===========================================================
> > Get notification to my posts through email? Please refer to
> >
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/default.aspx.
> > ============================================================
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > =========================================================
> >
> >
date: Sun, 6 Jul 2008 22:22:46 +1000
author: david@epsomdotcomdotau
Re: Compatibility (2002->2003)
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" <fred@nospam.nospam> wrote in message
> news:A58E3A26-5AE6-49EF-A4F3-9B2D92E5785A@microsoft.com...
> > 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: msdnmg@microsoft.com.
> > > ===========================================================
> > > Get notification to my posts through email? Please refer to
> > >
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/default.aspx.
> > > ============================================================
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > =========================================================
> > >
> > >
>
>
>
date: Wed, 16 Jul 2008 17:29:00 -0700
author: Devron Blatchford am
|
|
