Detail format works in 2000 but not 2003

J

Joe Sutphin

The following code works perfectly in Access 2000. However, it fails with
#Name? being displayed instead of the value for sExtPrice. The code is
contained in the Detail_Format section of the Report module. Does anyone
know why this occurs and how to fix it? TIA.

Joe
--
Public sExtPrice As String

Const QUOTE = """"

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim PartsSQL As String
Dim con As ADODB.Connection
Dim rsParts As New ADODB.Recordset

Set con = Application.CurrentProject.Connection

PartsSQL = "SELECT Quotes.QuoteNumber, BOMs.PartNumber, Sum(BOMs.Count) AS
SumOfCount, Sum(BOMs.Quantity) AS SumOfQuantity, Parts.Description,
Parts.CatalogSectionNumber, Parts.ListPrice, BOMs.AttributeType, Parts.UOM "
& _
"FROM Parts RIGHT JOIN (Quotes LEFT JOIN BOMs ON
Quotes.QuoteNumber = BOMs.QuoteNumber) ON Parts.PartNumber = BOMs.PartNumber
" & _
"GROUP BY Quotes.QuoteNumber, BOMs.PartNumber,
Parts.Description, Parts.CatalogSectionNumber, Parts.ListPrice,
BOMs.AttributeType, Parts.UOM " & _
"HAVING Quotes.QuoteNumber=" & QUOTE &
[Report_BillOfMaterial].PartNumber & QUOTE & " And Sum(BOMs.Count) > 0 And
BOMs.AttributeType=0 Or Sum(BOMs.Quantity) > 0 " & _
"ORDER BY Parts.CatalogSectionNumber;"

'open the parts recordset
rsParts.Open PartsSQL, con, adOpenKeyset

If rsParts.State = adStateOpen Then
Do
If [SumOfCount] > 0 And [SumOfQuantity] = 0 Then
sExtPrice = Format([SumOfCount] * [ListPrice], "$0.00")

Else
If [SumOfQuantity] > 0 Then
If [UOM] <> 1 Then
sExtPrice = Format(([ListPrice] / [UOM]) * [SumOfQuantity],
"$0.00")

Else
sExtPrice = Format([ListPrice] * [SumOfQuantity], "$0.00")
End If
End If
End If

rsParts.MoveNext
Loop Until rsParts.EOF
End If
End Sub
 
D

Douglas J Steele

Does the Format function work at all? Sometimes a problem with the
References collection can arise, affecting the use of functions contained in
any of the Reference files.

Open any code module, then select Tools | References from the menu bar.
Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joe Sutphin said:
The following code works perfectly in Access 2000. However, it fails with
#Name? being displayed instead of the value for sExtPrice. The code is
contained in the Detail_Format section of the Report module. Does anyone
know why this occurs and how to fix it? TIA.

Joe
--
Public sExtPrice As String

Const QUOTE = """"

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim PartsSQL As String
Dim con As ADODB.Connection
Dim rsParts As New ADODB.Recordset

Set con = Application.CurrentProject.Connection

PartsSQL = "SELECT Quotes.QuoteNumber, BOMs.PartNumber, Sum(BOMs.Count) AS
SumOfCount, Sum(BOMs.Quantity) AS SumOfQuantity, Parts.Description,
Parts.CatalogSectionNumber, Parts.ListPrice, BOMs.AttributeType, Parts.UOM "
& _
"FROM Parts RIGHT JOIN (Quotes LEFT JOIN BOMs ON
Quotes.QuoteNumber = BOMs.QuoteNumber) ON Parts.PartNumber = BOMs.PartNumber
" & _
"GROUP BY Quotes.QuoteNumber, BOMs.PartNumber,
Parts.Description, Parts.CatalogSectionNumber, Parts.ListPrice,
BOMs.AttributeType, Parts.UOM " & _
"HAVING Quotes.QuoteNumber=" & QUOTE &
[Report_BillOfMaterial].PartNumber & QUOTE & " And Sum(BOMs.Count) > 0 And
BOMs.AttributeType=0 Or Sum(BOMs.Quantity) > 0 " & _
"ORDER BY Parts.CatalogSectionNumber;"

'open the parts recordset
rsParts.Open PartsSQL, con, adOpenKeyset

If rsParts.State = adStateOpen Then
Do
If [SumOfCount] > 0 And [SumOfQuantity] = 0 Then
sExtPrice = Format([SumOfCount] * [ListPrice], "$0.00")

Else
If [SumOfQuantity] > 0 Then
If [UOM] <> 1 Then
sExtPrice = Format(([ListPrice] / [UOM]) * [SumOfQuantity],
"$0.00")

Else
sExtPrice = Format([ListPrice] * [SumOfQuantity], "$0.00")
End If
End If
End If

rsParts.MoveNext
Loop Until rsParts.EOF
End If
End Sub
Click to expand...
 
J

Joe Sutphin

As I pointed out the code I posted works perfectly in Access 2000 but the
sExtPrice variable (which is calculated from various field values and placed
on the report) comes up #Name? when Access 2003 is the host instead of 2000.

I tried all your suggestions of which none worked.

Joe
--

Douglas J Steele said:
Does the Format function work at all? Sometimes a problem with the
References collection can arise, affecting the use of functions contained
in
any of the Reference files.

Open any code module, then select Tools | References from the menu bar.
Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them,
unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back
out
of the dialog, then go back in and unselect the reference you just added.
If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joe Sutphin said:
The following code works perfectly in Access 2000. However, it fails with
#Name? being displayed instead of the value for sExtPrice. The code is
contained in the Detail_Format section of the Report module. Does anyone
know why this occurs and how to fix it? TIA.

Joe
--
Public sExtPrice As String

Const QUOTE = """"

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim PartsSQL As String
Dim con As ADODB.Connection
Dim rsParts As New ADODB.Recordset

Set con = Application.CurrentProject.Connection

PartsSQL = "SELECT Quotes.QuoteNumber, BOMs.PartNumber, Sum(BOMs.Count) AS
SumOfCount, Sum(BOMs.Quantity) AS SumOfQuantity, Parts.Description,
Parts.CatalogSectionNumber, Parts.ListPrice, BOMs.AttributeType,
Parts.UOM "
& _
"FROM Parts RIGHT JOIN (Quotes LEFT JOIN BOMs ON
Quotes.QuoteNumber = BOMs.QuoteNumber) ON Parts.PartNumber = BOMs.PartNumber
" & _
"GROUP BY Quotes.QuoteNumber, BOMs.PartNumber,
Parts.Description, Parts.CatalogSectionNumber, Parts.ListPrice,
BOMs.AttributeType, Parts.UOM " & _
"HAVING Quotes.QuoteNumber=" & QUOTE &
[Report_BillOfMaterial].PartNumber & QUOTE & " And Sum(BOMs.Count) > 0
And
BOMs.AttributeType=0 Or Sum(BOMs.Quantity) > 0 " & _
"ORDER BY Parts.CatalogSectionNumber;"

'open the parts recordset
rsParts.Open PartsSQL, con, adOpenKeyset

If rsParts.State = adStateOpen Then
Do
If [SumOfCount] > 0 And [SumOfQuantity] = 0 Then
sExtPrice = Format([SumOfCount] * [ListPrice], "$0.00")

Else
If [SumOfQuantity] > 0 Then
If [UOM] <> 1 Then
sExtPrice = Format(([ListPrice] / [UOM]) * [SumOfQuantity],
"$0.00")

Else
sExtPrice = Format([ListPrice] * [SumOfQuantity],
"$0.00")
End If
End If
End If

rsParts.MoveNext
Loop Until rsParts.EOF
End If
End Sub
Click to expand...
Click to expand...
 
D

Douglas J Steele

The fact that it works in Access 2000 but not 2003 is, in fact, one of the
things that lead me to suggest that it may be a problem with References: the
References collection is very sensitive to changes in the environment.

You didn't answer my question, though. Does the Format function work
elsewhere in your application?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joe Sutphin said:
As I pointed out the code I posted works perfectly in Access 2000 but the
sExtPrice variable (which is calculated from various field values and placed
on the report) comes up #Name? when Access 2003 is the host instead of 2000.

I tried all your suggestions of which none worked.

Joe
--

Douglas J Steele said:
Does the Format function work at all? Sometimes a problem with the
References collection can arise, affecting the use of functions contained
in
any of the Reference files.

Open any code module, then select Tools | References from the menu bar.
Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them,
unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back
out
of the dialog, then go back in and unselect the reference you just added.
If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joe Sutphin said:
The following code works perfectly in Access 2000. However, it fails with
#Name? being displayed instead of the value for sExtPrice. The code is
contained in the Detail_Format section of the Report module. Does anyone
know why this occurs and how to fix it? TIA.

Joe
--
Public sExtPrice As String

Const QUOTE = """"

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim PartsSQL As String
Dim con As ADODB.Connection
Dim rsParts As New ADODB.Recordset

Set con = Application.CurrentProject.Connection

PartsSQL = "SELECT Quotes.QuoteNumber, BOMs.PartNumber,
Click to expand...
Sum(BOMs.Count)
AS
SumOfCount, Sum(BOMs.Quantity) AS SumOfQuantity, Parts.Description,
Parts.CatalogSectionNumber, Parts.ListPrice, BOMs.AttributeType,
Parts.UOM "
& _
"FROM Parts RIGHT JOIN (Quotes LEFT JOIN BOMs ON
Quotes.QuoteNumber = BOMs.QuoteNumber) ON Parts.PartNumber = BOMs.PartNumber
" & _
"GROUP BY Quotes.QuoteNumber, BOMs.PartNumber,
Parts.Description, Parts.CatalogSectionNumber, Parts.ListPrice,
BOMs.AttributeType, Parts.UOM " & _
"HAVING Quotes.QuoteNumber=" & QUOTE &
[Report_BillOfMaterial].PartNumber & QUOTE & " And Sum(BOMs.Count) > 0
And
BOMs.AttributeType=0 Or Sum(BOMs.Quantity) > 0 " & _
"ORDER BY Parts.CatalogSectionNumber;"

'open the parts recordset
rsParts.Open PartsSQL, con, adOpenKeyset

If rsParts.State = adStateOpen Then
Do
If [SumOfCount] > 0 And [SumOfQuantity] = 0 Then
sExtPrice = Format([SumOfCount] * [ListPrice], "$0.00")

Else
If [SumOfQuantity] > 0 Then
If [UOM] <> 1 Then
sExtPrice = Format(([ListPrice] / [UOM]) * [SumOfQuantity],
"$0.00")

Else
sExtPrice = Format([ListPrice] * [SumOfQuantity],
"$0.00")
End If
End If
End If

rsParts.MoveNext
Loop Until rsParts.EOF
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

Joe Sutphin

Yes, everything else works. It's just when I calculate the value for the
sExtPrice variable from fields in the database. The value of the sExtPrice
variable is placed in a TextBox on the report.

Joe
--

Douglas J Steele said:
The fact that it works in Access 2000 but not 2003 is, in fact, one of the
things that lead me to suggest that it may be a problem with References:
the
References collection is very sensitive to changes in the environment.

You didn't answer my question, though. Does the Format function work
elsewhere in your application?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joe Sutphin said:
As I pointed out the code I posted works perfectly in Access 2000 but the
sExtPrice variable (which is calculated from various field values and placed
on the report) comes up #Name? when Access 2003 is the host instead of 2000.

I tried all your suggestions of which none worked.

Joe
--

Douglas J Steele said:
Does the Format function work at all? Sometimes a problem with the
References collection can arise, affecting the use of functions contained
in
any of the Reference files.

Open any code module, then select Tools | References from the menu bar.
Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them,
unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back
out
of the dialog, then go back in and unselect the reference you just added.
If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The following code works perfectly in Access 2000. However, it fails with
#Name? being displayed instead of the value for sExtPrice. The code is
contained in the Detail_Format section of the Report module. Does anyone
know why this occurs and how to fix it? TIA.

Joe
--
Public sExtPrice As String

Const QUOTE = """"

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim PartsSQL As String
Dim con As ADODB.Connection
Dim rsParts As New ADODB.Recordset

Set con = Application.CurrentProject.Connection

PartsSQL = "SELECT Quotes.QuoteNumber, BOMs.PartNumber, Sum(BOMs.Count)
AS
SumOfCount, Sum(BOMs.Quantity) AS SumOfQuantity, Parts.Description,
Parts.CatalogSectionNumber, Parts.ListPrice, BOMs.AttributeType,
Parts.UOM
"
& _
"FROM Parts RIGHT JOIN (Quotes LEFT JOIN BOMs ON
Quotes.QuoteNumber = BOMs.QuoteNumber) ON Parts.PartNumber =
BOMs.PartNumber
" & _
"GROUP BY Quotes.QuoteNumber, BOMs.PartNumber,
Parts.Description, Parts.CatalogSectionNumber, Parts.ListPrice,
BOMs.AttributeType, Parts.UOM " & _
"HAVING Quotes.QuoteNumber=" & QUOTE &
[Report_BillOfMaterial].PartNumber & QUOTE & " And Sum(BOMs.Count) > 0
And
BOMs.AttributeType=0 Or Sum(BOMs.Quantity) > 0 " & _
"ORDER BY Parts.CatalogSectionNumber;"

'open the parts recordset
rsParts.Open PartsSQL, con, adOpenKeyset

If rsParts.State = adStateOpen Then
Do
If [SumOfCount] > 0 And [SumOfQuantity] = 0 Then
sExtPrice = Format([SumOfCount] * [ListPrice], "$0.00")

Else
If [SumOfQuantity] > 0 Then
If [UOM] <> 1 Then
sExtPrice = Format(([ListPrice] / [UOM]) * [SumOfQuantity],
"$0.00")

Else
sExtPrice = Format([ListPrice] * [SumOfQuantity],
"$0.00")
End If
End If
End If

rsParts.MoveNext
Loop Until rsParts.EOF
End If
End Sub
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

Detail format differences 7
Detail_Format code works in 2000 but not 2003? 1
Differences between 2000 and 2003 1
Detail_Format works in Print Preview but Not When Printing 0
Exclude records from footer calculations but include in detail sec 1
UPC calculating works in excel but not in access 8
Excel Macro works in 2000 but not in 2003 - HELP! 1
Using VBA to send Word document as body of message works in 2007, but not in 2003 9

Top