Passing report values to subroutines

  • Thread starter Thread starter Matt Benvenuti
  • Start date Start date
M

Matt Benvenuti

Hi all!

It's been a while since I've done any VBA, and I'm kind of embarased
to ask this question.

I would like to pass values to a sub as a section is printing. I've
used the code below. Obviously it doesn't work. The error is:
"You entered an expression that has no value."

Any help greatly appreciated.

cheers,
Matt.

Private Sub GroupFooter6_Print(Cancel As Integer, PrintCount As
Integer)
MsgBox ("Field5 " & Field5.Value)
MsgBox ("Field7: " & Field7.Value)
MsgBox ("Field8: " & Field8.Value)
Call CountPart(Field5.Value, Field7.Value, Field8.Value)
End Sub
 
I believe your issue is that you are not pulling values from controls in
your group footer. Try bind some controls to the values and then reference
the control values.
 
Hi Duane!

Thanks for your quick reply.

The field names whose values I would like to pass to the subroutine
are Field5, Field7 and Field8. Field5 is a text field whose Control
Source is a field in the table [Job #]. But Field7 and Field8 control
Source are "=Sum(TotalParts)" and "=Sum(TotalScrap)". I have no idea
how to reference these values in the Footer to pass to the subroutine.

Do you have any other tips?

cheers,
Matt.
 
Do you have text boxes in the Group Footer named Field5, Field7, and Field8?
These are unusual names since text boxes default to either the field's name
or to "text...".

Where and what is CountPart()?

--
Duane Hookom
MS Access MVP
--

Matt Benvenuti said:
Hi Duane!

Thanks for your quick reply.

The field names whose values I would like to pass to the subroutine
are Field5, Field7 and Field8. Field5 is a text field whose Control
Source is a field in the table [Job #]. But Field7 and Field8 control
Source are "=Sum(TotalParts)" and "=Sum(TotalScrap)". I have no idea
how to reference these values in the Footer to pass to the subroutine.

Do you have any other tips?

cheers,
Matt.

I believe your issue is that you are not pulling values from controls in
your group footer. Try bind some controls to the values and then reference
the control values.
Click to expand...
Click to expand...
 
Hi Duane!

I didn't create the report or the database. Those are the text box
names.

CountPart is a sub I wrote to accumulate the values in Field7 and
Field8 and report them in another footer. The code is included below.

Field5 contains an Item we manufacture. Field7 contains the total
parts manufactured for that Item (te database keys on date, shift, and
item, which is why the data is in a footer). Field8 contains the
total items scrapped for the day. Because the operations are
continuous, management wants to see total scrap against just the first
set of operations. The database was never designed for that kind of
query, hence my VBA code. The item data can look like this across
consecutive records: 1294-1A, 1294-1B, 1294-2A, etc. any item where
the number after the hyphen is a 1 is to be included as a first
operation (except for 1371, where the number is 2). Any other records
where the sequence before the hyphen is the smae as the previous
record, we just count the scrap. That's what my code is attempting to
do.

If there's a better way to do this, I'm all ears.

cheers,
Matt.


Public CurrentItem As String
Public CurrentPartSum As Integer
Public CurrentPartScrap As Integer

Option Compare Database
Option Explicit

Public Function FindHyphen(SearchStr As String)

FindHyphen = InStr(1, SearchStr, "-")

End Function

Public Sub CountPart(Item As String, ItemGood As Integer, ItemScrap As
Integer)

Dim Hyp As Integer
Dim LineItem As String

Hyp = FindHyphen(Item)
If IsNull(CurrentItem) Then
If Hyp = 0 Then
CurrentItem = Item
Else
CurrentItem = Mid(Item, 1, Hyp - 1)
End If
End If
If Hyp = 0 Then
LineItem = Item
Else
LineItem = Mid(Item, 1, Hyp - 1)
End If
If LineItem <> CurrentItem Then
CurrentPartSum = 0
CurrentPartScrap = 0
CurrentItem = LineItem
End If
If CurrentItem = "1371" Then
If (Hyp = 0) Or (Mid(CurrentItem, Hyp + 1, 1) = 2) Then
CurrentPartSum = CurrentPartSum + ItemGood
End If
Else
If (Hyp = 0) Or (Mid(CurrentItem, Hyp + 1, 1) = 1) Then
CurrentPartSum = CurrentPartSum + ItemGood
End If
End If
CurrentPartScrap = CurrentPartScrap + ItemScrap

End Sub
 
Which line of code generates the error? Are there possibly nulls in the text
boxes?

Have you tried to set a break point and step through the code?
 
If you go back to the first message, the error is generated by the
first MSGBOX line.

I'll set a Control Point, but I'm not sure how there could be a null
in the text box when the Footer breaks on the contents Field5
(remember Control Source is =[Job #]).

cheers,
Matt.
 
Hi again!

I tried this:
Private Sub GroupFooter6_Print(Cancel As Integer, PrintCount As
Integer)
If (Not IsNull(Field5.Value)) Then
MsgBox ("Field5 " & Field5)
End If
'MsgBox ("Field7: " & Field7)
'MsgBox ("Field8: " & Field8.Value)
Call CountPart("1371-2A", 100, 15)
End Sub

and the error still comes at the MSGBOX about there being an
expression with no value.

I trundle on, still hoping for a light at the end of the tunnel.

cheers,
Matt.
 
Have you attempted to compile your code? Do you use "Option Explicit" in
your general declarations?

When in the code window and you type
Me.Fie...
does Access/VBA display the Field5?
 
Hi Duane!

The code compiles. Option Explicit is declared in the same Module as
the CountPart() sub.

In the Immediate Window typing "?Me.Field5" produces the "You entered
an expression that has no value." error (runtime 2427).

cheers,
Matt.
 
And this errors on the IF statement:

Private Sub GroupFooter6_Print(Cancel As Integer, PrintCount As
Integer)
If (Me.[Cascade - Scrap % Report].Report.Field5.HasData) Then
MsgBox ("Field5 " & Field5)
End If
'MsgBox ("Field7: " & Field7)
'MsgBox ("Field8: " & Field8.Value)
Call CountPart("1371-2A", 100, 15)
End Sub
 
You ignored part of my reply:

When in the code window and you type
Me.Fie...
does Access/VBA display the Field5?


Try change the name of the control from Field5 to "txtFive". Then change
your code to something like:
Private Sub GroupFooter6_Print(Cancel As Integer, PrintCount As Integer)
MsgBox "txtFive: " & Me.txtFive
End Sub

--
Duane Hookom
MS Access MVP


Matt Benvenuti said:
And this errors on the IF statement:

Private Sub GroupFooter6_Print(Cancel As Integer, PrintCount As
Integer)
If (Me.[Cascade - Scrap % Report].Report.Field5.HasData) Then
MsgBox ("Field5 " & Field5)
End If
'MsgBox ("Field7: " & Field7)
'MsgBox ("Field8: " & Field8.Value)
Call CountPart("1371-2A", 100, 15)
End Sub


Have you attempted to compile your code? Do you use "Option Explicit" in
your general declarations?

When in the code window and you type
Me.Fie...
does Access/VBA display the Field5?
Click to expand...
Click to expand...
 
Hi Duane!

My apologies. In the Immediate window, typing Me.Fie.. brings up a
series of objects beginning with the name Fie, and one of them is
Field5.

I still get "you have entered an expression that as no value"

cheers,
Matt.
 
There is an "Object" drop down in the top left of your report design screen.
When you drop this down, do you see "Field5"?
 
Back
Top