D
doyle60
Woops! I thought you were 12 hours ahead. But you are actually six
hours back and 24 ahead. So I am sorry to have awakened you.
Matt
hours back and 24 ahead. So I am sorry to have awakened you.
Matt
G
Graham Mandeno
Hi Matt
Don't worry - there wasn't much that was going to awaken me last night!
Time zones are a curse in this game, aren't they?
Overflow error? What are the values in the offending variables at the time?
In the immediate window, type:
?i
?iRemaining
?aProj(m+i)
What do you want MOH to be if OnHand is negative?
Don't worry - there wasn't much that was going to awaken me last night!
Time zones are a curse in this game, aren't they?
Overflow error? What are the values in the offending variables at the time?
In the immediate window, type:
?i
?iRemaining
?aProj(m+i)
What do you want MOH to be if OnHand is negative?
D
doyle60
It happens when the On Hand is 0. (I do not allow for nulls in the
query.) The code works just fine when my filter does not contain a 0
on hand.
When the current on hand is 0 (or when it gets reduced to 0), the code
should show 0 months inventory for those months it is figuring.
The immediate window shows:
aOnHand: [blank]
M: 12
i: 0
MOH: -1.#IND
iRemaining: 0
iDaysInMonth: 30
and some other things that I do not believe matter.
Thanks,
Matt
query.) The code works just fine when my filter does not contain a 0
on hand.
When the current on hand is 0 (or when it gets reduced to 0), the code
should show 0 months inventory for those months it is figuring.
The immediate window shows:
aOnHand: [blank]
M: 12
i: 0
MOH: -1.#IND
iRemaining: 0
iDaysInMonth: 30
and some other things that I do not believe matter.
Thanks,
Matt
D
doyle60
The error also pops up when there is an initial On Hand quantity but
where it gets reduced down to less than zero through the process.
Matt
where it gets reduced down to less than zero through the process.
Matt
G
Graham Mandeno
Hi Matt
That's strange! I've set the OnHand in your sample data to -50,000,000 and
it still didn't break (although it did give some rather large negative
numbers for MOH!)
Anyway, first, if you can have negative stock on-hand, you must delete this
line from the code:
If aOnHand(m) < 0 Then aOnHand(m) = 0
Then, modify your Do Until Done loop as follows:
Do Until Done
If iRemaining < 0 then
Done = True
MOH = i
ElseIf iRemaining > aProj(m + i) Then
iRemaining = iRemaining - aProj(m + i)
i = i + 1
If m + i > 12 Then
Done = True ' we can't go past 12
MOH = i
End If
Else
Done = True
MOH = i + iRemaining / aProj(m + i)
End If
Loop
(add lines 2-5 and change the next line to ElseIf)
I'm sure we'll nail this blighter some day
That's strange! I've set the OnHand in your sample data to -50,000,000 and
it still didn't break (although it did give some rather large negative
numbers for MOH!)
Anyway, first, if you can have negative stock on-hand, you must delete this
line from the code:
If aOnHand(m) < 0 Then aOnHand(m) = 0
Then, modify your Do Until Done loop as follows:
Do Until Done
If iRemaining < 0 then
Done = True
MOH = i
ElseIf iRemaining > aProj(m + i) Then
iRemaining = iRemaining - aProj(m + i)
i = i + 1
If m + i > 12 Then
Done = True ' we can't go past 12
MOH = i
End If
Else
Done = True
MOH = i + iRemaining / aProj(m + i)
End If
Loop
(add lines 2-5 and change the next line to ElseIf)
I'm sure we'll nail this blighter some day
D
doyle60
You nailed it! The only problem is what to do in the case where the
user does not fill out a projection all the way to the 12th month. If
they only fill out 3 months, the report gives strange results. Also,
the last months quite often just show a 4.0, 3.0, 2.0, and 1.0.
Logically, these months cannot go higher than those values. So they
are a bit meaningless. I think I may just make the last four months
invisible.
Since it takes 5 to 6 months to order and receive a product on hand,
the meet of the matter is in those first 6 or so months. That's when
we have the ability to make changes based on the figures reported.
If users are not going to fill out their projections, the report
falsely states an overloaded on hand per month figure. So it will pop
up as a problem when they filter when it truly isn't. But that is
their fault and the error for us at least in a safe one.
In other words, let's leave the code here.
But can we add a filter? I have a form where I have put combo boxes
as filters. Basically they fill out a month range and a (not these)
values range.
Form: MapUpOutfrm
MonthLowChosen
MonthHighChosen
LowBOMOHChosen
HighBOMOHChosen
The MonthLowChosen and the MonthHighChosen are bound on a hidden
second column that have 01 to 12 in them. (I can change these values
if you want.) The form says:
"From |______| to |______|"
The LowBOMOHChosen (the BOMOH stands for Beginning of Month On Hand
and refers to the values the code returns) and the HighBOMOHChosen are
combos where the user can enter a double number from 0.0 to 100.0. It
is a range of numbers that they do NOT want to see.
This is a bit tricky. The form says:
"Where at least one of the months has an 'Estimated Beginning On Hand'
not between |____| and |____| months."
"Not" between, you see. So if they enter a 2 and a 5, and the values
for the months they chose are all between 2 and 5, it should not
return the record. But if they enter a 2 and a 5 and just one value is
outside that range, it should return the record.
Management excepts a range from 2 to 5 as good. Anything over 5 is
holding too much inventory, anything lower than 2 is holding too
little.
If you want to take a break from all this and move on to something
else and get back to it later, that is fine. I understand.
Thanks again for the code,
Matt
user does not fill out a projection all the way to the 12th month. If
they only fill out 3 months, the report gives strange results. Also,
the last months quite often just show a 4.0, 3.0, 2.0, and 1.0.
Logically, these months cannot go higher than those values. So they
are a bit meaningless. I think I may just make the last four months
invisible.
Since it takes 5 to 6 months to order and receive a product on hand,
the meet of the matter is in those first 6 or so months. That's when
we have the ability to make changes based on the figures reported.
If users are not going to fill out their projections, the report
falsely states an overloaded on hand per month figure. So it will pop
up as a problem when they filter when it truly isn't. But that is
their fault and the error for us at least in a safe one.
In other words, let's leave the code here.
But can we add a filter? I have a form where I have put combo boxes
as filters. Basically they fill out a month range and a (not these)
values range.
Form: MapUpOutfrm
MonthLowChosen
MonthHighChosen
LowBOMOHChosen
HighBOMOHChosen
The MonthLowChosen and the MonthHighChosen are bound on a hidden
second column that have 01 to 12 in them. (I can change these values
if you want.) The form says:
"From |______| to |______|"
The LowBOMOHChosen (the BOMOH stands for Beginning of Month On Hand
and refers to the values the code returns) and the HighBOMOHChosen are
combos where the user can enter a double number from 0.0 to 100.0. It
is a range of numbers that they do NOT want to see.
This is a bit tricky. The form says:
"Where at least one of the months has an 'Estimated Beginning On Hand'
not between |____| and |____| months."
"Not" between, you see. So if they enter a 2 and a 5, and the values
for the months they chose are all between 2 and 5, it should not
return the record. But if they enter a 2 and a 5 and just one value is
outside that range, it should return the record.
Management excepts a range from 2 to 5 as good. Anything over 5 is
holding too much inventory, anything lower than 2 is holding too
little.
If you want to take a break from all this and move on to something
else and get back to it later, that is fine. I understand.
Thanks again for the code,
Matt
G
Graham Mandeno
Hey, we're getting there at last
The first problem (MOH counting down by 1) will occur, I think, if the total
aProj(m+i) for the remaining months in the year is greater than aOnHand(m).
In other words, iRemaining never makes it to zero.
In this case, the code gets to the If m + i > 12 part, so we can easily
check for that condition and do something different. I suggest this:
If m + i > 12 Then
Done = True ' we can't go past 12
MOH = -1 ' invalid value
End If
Then, further down:
If MOH < 0 Then
Me("MOH" & Format(m, "00")) = Null
' or "n/a" or "-" if you prefer
Else
Me("MOH" & Format(m, "00")) = MOH
If MOH >= 0 Then
ShowRecord = True
End If
End If
For the filtering, declare two variables in the "Declarations Section" at
the top of your report module:
Private LowMOH as Single
Private HighMOH as Single
Now, in your Report_Open event procedure, load these values from the form:
With Forms("Name of your form")
LowMOH = !LowBOMOHChosen
HighMOH = !HighBOMOHChosen
End With
Then, in place of the dummy test If MOH >= 0, put in the real test for
values outside your bounds:
If MOH < LowMOH Or MOH > HighMOH Then
ShowRecord = True
End If
You might want to consider setting default values in Report_Open if the
textboxes on the form are empty.
The first problem (MOH counting down by 1) will occur, I think, if the total
aProj(m+i) for the remaining months in the year is greater than aOnHand(m).
In other words, iRemaining never makes it to zero.
In this case, the code gets to the If m + i > 12 part, so we can easily
check for that condition and do something different. I suggest this:
If m + i > 12 Then
Done = True ' we can't go past 12
MOH = -1 ' invalid value
End If
Then, further down:
If MOH < 0 Then
Me("MOH" & Format(m, "00")) = Null
' or "n/a" or "-" if you prefer
Else
Me("MOH" & Format(m, "00")) = MOH
If MOH >= 0 Then
ShowRecord = True
End If
End If
For the filtering, declare two variables in the "Declarations Section" at
the top of your report module:
Private LowMOH as Single
Private HighMOH as Single
Now, in your Report_Open event procedure, load these values from the form:
With Forms("Name of your form")
LowMOH = !LowBOMOHChosen
HighMOH = !HighBOMOHChosen
End With
Then, in place of the dummy test If MOH >= 0, put in the real test for
values outside your bounds:
If MOH < LowMOH Or MOH > HighMOH Then
ShowRecord = True
End If
You might want to consider setting default values in Report_Open if the
textboxes on the form are empty.
D
doyle60
Thanks. But there are two issues with the filter:
1) I need the filter to search only in selected months. A manager may
want a report seeing only SKUs that have a current BOM greater than
6. He may want to do some yelling. "Why we carrying all these
goods!" That is, he only wants to direct his search on the first
month, not caring what the other months will have.
So in my form, he would enter:
MonthLowChosen: 01
Month HighChosen: 01
LowBOMOHChosen: 0
HighBOMOHChosen: 6
The filter does not use the MonthLowChosen and the MonthHighChosen.
(Presently, these two controls on the form (MapUpOutfrm) are bound to
a hidden column with values of 01, 02, 03 to 12. But I can make them
M01, M02, M03 to M12, or anything else.)
To give another example, a user may want to see if they have to order
product for next April, or even sooner. They would want to put this
in the filters:
MonthLowChosen: 01
Month HighChosen: 06
LowBOMOHChosen: 2
HighBOMOHChosen: 100
That is, they want to see what is lower than 2, for 6 months out.
But if they continually use and react to this report, most of the
time, I believe, they will be entering a search for months 03 to say
07 and filtering out the good of 2 through 5, and getting back a
report that just shows them the "baddies" they can react to. That is,
they can delay production or order production.
2) As the filter is now, some headers mistakenly show up when the
detail is filtered out.
My headers are:
GroupHeader1: Division Header
GroupHeader2: SalesRepLastName Header
GroupHeader3: StoreName Header
GroupHeader4: Group_NameAdjA
GroupHeader5: Group_NameAdjB
GroupHeader0: Bucket Header
Presently, these do not have corresponding footers.
I have done plenty of simple code for making headers invisible when
such and such happens but I'm not sure how to do it when one detail
says print the headers and another may say do not. Thanks again,
Matt
1) I need the filter to search only in selected months. A manager may
want a report seeing only SKUs that have a current BOM greater than
6. He may want to do some yelling. "Why we carrying all these
goods!" That is, he only wants to direct his search on the first
month, not caring what the other months will have.
So in my form, he would enter:
MonthLowChosen: 01
Month HighChosen: 01
LowBOMOHChosen: 0
HighBOMOHChosen: 6
The filter does not use the MonthLowChosen and the MonthHighChosen.
(Presently, these two controls on the form (MapUpOutfrm) are bound to
a hidden column with values of 01, 02, 03 to 12. But I can make them
M01, M02, M03 to M12, or anything else.)
To give another example, a user may want to see if they have to order
product for next April, or even sooner. They would want to put this
in the filters:
MonthLowChosen: 01
Month HighChosen: 06
LowBOMOHChosen: 2
HighBOMOHChosen: 100
That is, they want to see what is lower than 2, for 6 months out.
But if they continually use and react to this report, most of the
time, I believe, they will be entering a search for months 03 to say
07 and filtering out the good of 2 through 5, and getting back a
report that just shows them the "baddies" they can react to. That is,
they can delay production or order production.
2) As the filter is now, some headers mistakenly show up when the
detail is filtered out.
My headers are:
GroupHeader1: Division Header
GroupHeader2: SalesRepLastName Header
GroupHeader3: StoreName Header
GroupHeader4: Group_NameAdjA
GroupHeader5: Group_NameAdjB
GroupHeader0: Bucket Header
Presently, these do not have corresponding footers.
I have done plenty of simple code for making headers invisible when
such and such happens but I'm not sure how to do it when one detail
says print the headers and another may say do not. Thanks again,
Matt
G
Graham Mandeno
Hi Matt
The filter on a range of months is very easy to implement. Simply load the
low and high months into module variables in the Report_Open procedure, in
same way you have for low/high MOH.
Then modify the test which decides whether or not to set ShowRecord=True to
test that M is in the required range *and* MOH is outside the range.
The problem of suppressing headers if *all* the detail records are cancelled
is a trickier question. I'll have a think about it.
The filter on a range of months is very easy to implement. Simply load the
low and high months into module variables in the Report_Open procedure, in
same way you have for low/high MOH.
Then modify the test which decides whether or not to set ShowRecord=True to
test that M is in the required range *and* MOH is outside the range.
The problem of suppressing headers if *all* the detail records are cancelled
is a trickier question. I'll have a think about it.
D
doyle60
1) I tried and have failed. The code reacts to what I have done and
does not give me errors but the results are random and I can't figure
out how to manipulate the code to get it right. I used my head and
then I used trial an error but to no avail.
I did the following:
a) I added the last two in the declarations (this was not part of your
directions but I mention it in case I did something wrong):
Private LowMOH As Single
Private HighMOH As Single
Private MonthLow As Long
Private MonthHigh As Long
(The control on the field is now bound to a 1, 2, 3 to 12 (Not as a
text like I told you before of 01, 02, 03 to 12.)
b) I added lines four and five to the On Open of the report as you
said:
With Forms("MapUpOutfrm")
LowMOH = !LowBOMOHChosen
HighMOH = !HighBOMOHChosen
MonthLow = !MonthLowChosen
MonthHigh = !MonthHighChosen
End With
c) Now the tricky part. I have the code reading the below now, where
I added the third line and one of the End Ifs. I tried using just one
If and End If line but failed and this is just my last trial-and-error
trial:
Loop
Me("MOH" & Format(M, "00")) = MOH
If MonthLow <= M And MonthHigh >= M Then
If MOH < LowMOH Or MOH > HighMOH Then
ShowRecord = True
End If
End If
Next M
If Not ShowRecord Then Cancel = True
End Sub
I can't make heads or tails out of why some records do show and some
do not when I run it. It definitely filters but not correctly. I
don't understand how this code knows to show it if one of the filters
is true instead of just the last one it ran through. You see if I am
filter three months only, and it runs the first one and says "Print"
and runs the second one which answers "Print" and then runs the third
and it says "Don't print" how does it know yes, print it because at
least one says to print. I sort of thought we would be having some
counter system here that counts the instances that say print and if
they are 1 and greater, print, if not, don't.
I don't know how to do that myself but just thought that would be the
process.
How was your swim? Is this exercise? I do laps at the gym. If I
average one minute a lap, I'm doing pretty good.
Thanks,
Matt
does not give me errors but the results are random and I can't figure
out how to manipulate the code to get it right. I used my head and
then I used trial an error but to no avail.
I did the following:
a) I added the last two in the declarations (this was not part of your
directions but I mention it in case I did something wrong):
Private LowMOH As Single
Private HighMOH As Single
Private MonthLow As Long
Private MonthHigh As Long
(The control on the field is now bound to a 1, 2, 3 to 12 (Not as a
text like I told you before of 01, 02, 03 to 12.)
b) I added lines four and five to the On Open of the report as you
said:
With Forms("MapUpOutfrm")
LowMOH = !LowBOMOHChosen
HighMOH = !HighBOMOHChosen
MonthLow = !MonthLowChosen
MonthHigh = !MonthHighChosen
End With
c) Now the tricky part. I have the code reading the below now, where
I added the third line and one of the End Ifs. I tried using just one
If and End If line but failed and this is just my last trial-and-error
trial:
Loop
Me("MOH" & Format(M, "00")) = MOH
If MonthLow <= M And MonthHigh >= M Then
If MOH < LowMOH Or MOH > HighMOH Then
ShowRecord = True
End If
End If
Next M
If Not ShowRecord Then Cancel = True
End Sub
I can't make heads or tails out of why some records do show and some
do not when I run it. It definitely filters but not correctly. I
don't understand how this code knows to show it if one of the filters
is true instead of just the last one it ran through. You see if I am
filter three months only, and it runs the first one and says "Print"
and runs the second one which answers "Print" and then runs the third
and it says "Don't print" how does it know yes, print it because at
least one says to print. I sort of thought we would be having some
counter system here that counts the instances that say print and if
they are 1 and greater, print, if not, don't.
I don't know how to do that myself but just thought that would be the
process.
How was your swim? Is this exercise? I do laps at the gym. If I
average one minute a lap, I'm doing pretty good.
Thanks,
Matt
G
Graham Mandeno
Hi Matt
I can't tell you why it's not working, but you might be able to spot the
problem by stepping through the code.
Identify a SKU that is behaving in an undesirable way (either showing when
it shouldn't or vice-versa) and put in a like to stop the code when you get
to that SKU:
If Me![SKU] = "st00234" then stop
I suggest you put this just before:
For m = 1 To 12
iRemaining = aOnHand(m)
Now, using the F8 key, you can step through the code one line at a time and
examine the contents of all the variables as they change. Pay particular
attention to the code around :
If MonthLow <= M ...
Especially check the values of MonthLow and all your other module-level
variables.
I can't tell you why it's not working, but you might be able to spot the
problem by stepping through the code.
Identify a SKU that is behaving in an undesirable way (either showing when
it shouldn't or vice-versa) and put in a like to stop the code when you get
to that SKU:
If Me![SKU] = "st00234" then stop
I suggest you put this just before:
For m = 1 To 12
iRemaining = aOnHand(m)
Now, using the F8 key, you can step through the code one line at a time and
examine the contents of all the variables as they change. Pay particular
attention to the code around :
If MonthLow <= M ...
Especially check the values of MonthLow and all your other module-level
variables.
D
doyle60
Thanks. Actually, the filter does work. I don't know why I thought
it didn't. Well, actually I do. It is a bit tricky in its negative
thinking and I just got confused and once or twice probably didn't
leave the filter cell in the form to make sure what was entered was
actually entered.
When you figure out how to get rid of the headings, please let me
know. Absolutely no rush. But if you do reply to this after about
two weeks, please write me an email to come look here for it. My
email is (e-mail address removed).
Thanks again for staying with me here, I greatly appreciate it,
Matt
it didn't. Well, actually I do. It is a bit tricky in its negative
thinking and I just got confused and once or twice probably didn't
leave the filter cell in the form to make sure what was entered was
actually entered.
When you figure out how to get rid of the headings, please let me
know. Absolutely no rush. But if you do reply to this after about
two weeks, please write me an email to come look here for it. My
email is (e-mail address removed).
Thanks again for staying with me here, I greatly appreciate it,
Matt
G
Graham Mandeno
Hi Matt
I'm glad you got it all (mostly!) working OK. It's been a good challenge
and I've learned some things too.
I'm sorry I don't yet have a solution to the problem of hiding the header
sections. I'm sure we'll nail that one too though. We have some of the
finest minds in the Access world working on it as we speak
All the best!
I'm glad you got it all (mostly!) working OK. It's been a good challenge
and I've learned some things too.
I'm sorry I don't yet have a solution to the problem of hiding the header
sections. I'm sure we'll nail that one too though. We have some of the
finest minds in the Access world working on it as we speak
All the best!
D
doyle60
Thanks. One more little tiddly problem I can't solve, but tried. I
only want the Print command button to light up if all four of our
filters are null or if all four are filled in. If there is just one
filled in, or two, or three, the print button should not light up.
I put this in the after update of the four controls (but it doesn't
work):
'If IsNull(MonthLowChosen) And IsNull(MonthHighChosen) And IsNull
(LowBOMOHChosen) And IsNull(HighBOMOHChosen) Then 'Command7.Enabled =
True
'Command7.Enabled = True
'Else
'Command7.Enabled = False
'End If
'If Not IsNull(MonthLowChosen) And Not IsNull(MonthHighChosen) And Not
IsNull(LowBOMOHChosen) And Not IsNull(HighBOMOHChosen) Then
'Command7.Enabled = True
'Else
'Command7.Enabled = False
'End If
I'm not sure if it is one of those things where the box is null when I
open (IsNull) but when I clear it out after putting something in, it
is not the same null (it's "").
Thanks,
Matt
only want the Print command button to light up if all four of our
filters are null or if all four are filled in. If there is just one
filled in, or two, or three, the print button should not light up.
I put this in the after update of the four controls (but it doesn't
work):
'If IsNull(MonthLowChosen) And IsNull(MonthHighChosen) And IsNull
(LowBOMOHChosen) And IsNull(HighBOMOHChosen) Then 'Command7.Enabled =
True
'Command7.Enabled = True
'Else
'Command7.Enabled = False
'End If
'If Not IsNull(MonthLowChosen) And Not IsNull(MonthHighChosen) And Not
IsNull(LowBOMOHChosen) And Not IsNull(HighBOMOHChosen) Then
'Command7.Enabled = True
'Else
'Command7.Enabled = False
'End If
I'm not sure if it is one of those things where the box is null when I
open (IsNull) but when I clear it out after putting something in, it
is not the same null (it's "").
Thanks,
Matt
G
Graham Mandeno
Hi Matt
Reminds me of a song from "Oklahoma!" - "With me it's all or nuthin'"
You can use a trick here - the "real" value of the boolean True is -1, and
False is 0. So, if you add all four IsNull()s together, you want to get
either 0 or -4.
Private Function SetButtonStatus()
Dim iTotal as Integer
iTotal = IsNull(MonthLowChosen) + IsNull(MonthHighChosen) _
+ IsNull(LowBOMOHChosen) + IsNull(HighBOMOHChosen)
Command7.Enabled = (iTotal = 0) or (iTotal = -4)
End Function
Then, instead of individual AfterUpdate events for each of the textboxes,
you can just set their AfterUpdate properties to:
=SetButtonStatus()
Also, I recommend you rename the command button. "Command7" means
absolutely nothing, while "cmdPrintReport" is much more meaningful and will
help you to understand what its purpose is when you revisit this form in a
year's time.
I think we're close to a solution for the report headers... watch this
space.
Reminds me of a song from "Oklahoma!" - "With me it's all or nuthin'"
You can use a trick here - the "real" value of the boolean True is -1, and
False is 0. So, if you add all four IsNull()s together, you want to get
either 0 or -4.
Private Function SetButtonStatus()
Dim iTotal as Integer
iTotal = IsNull(MonthLowChosen) + IsNull(MonthHighChosen) _
+ IsNull(LowBOMOHChosen) + IsNull(HighBOMOHChosen)
Command7.Enabled = (iTotal = 0) or (iTotal = -4)
End Function
Then, instead of individual AfterUpdate events for each of the textboxes,
you can just set their AfterUpdate properties to:
=SetButtonStatus()
Also, I recommend you rename the command button. "Command7" means
absolutely nothing, while "cmdPrintReport" is much more meaningful and will
help you to understand what its purpose is when you revisit this form in a
year's time.
I think we're close to a solution for the report headers... watch this
space.