set a value to use throughout the database

  • Thread starter Thread starter Lisa Clarke
  • Start date Start date
L

Lisa Clarke

Hi.
Simple question I'm sure......

I want to create a function with an input box to enter in
a year end date, then I want to pass this value SOMEPLACE
to store it. Then I can use it in a variety of other
places - BUT I don't want it to run the input box function
when I use the date.

Thanks
Lisa

Here's what I have so far.

Public Function currentyearend() As Date
End Function

Public Function setyearend() As Date
setyearend = InputBox ("Please enter the current year
end date")

???? Set currentyearend = setyearend
End Function
 
Lisa -

I know there are a lot of people who hate global variables, but I
use a few of them for things just like this. My startup routine
goes to MyOneRecordControlFile and puts the CurrentYearend field in
the global variable. But in case it gets lost along the way, I use
this: In a code module:

public gvCurrentYearend as date
function CurrentYearendIs() as date
if IsADate(gvCurrentYearend) = True then
CurrentYearendIs = gvCurrentYearend
else
gvCurrentYearend =_
dmin("CurrentYearend","MyOneRecordControlFile")
if IsADate(gvCurrentYearend) = False then
gvCurrentYearEnd = _
inputbox("Enter current year end date")
CurrentYearend = gvCurrentYearend
docmd.RunSQL "update MyOneRecordControlFile " _
& "set CurrentYearend = #" & gvCurrentYearend & "#;"
end if
end if
end function

Anywhere I need the current yearend I use
me.CurrentYearEnd = CurrentYearendIs

Unfortunately I found the following function to be necessary
because MS's IsDate function returns True if the date is
12:00:00 AM (or PM) or 12/31/1899, and that occurs entirely too often.

function IsADate (DateIn as Variant) as Boolean
If IsDate(CDate(DateIn)) = False Or CStr(DateIn) & "" = "" _
Or CStr(DateIn) = "12/31/1899" _
Or Right$(CStr(DateIn), 1) = "M" Then
IsADate = False
Else
IsADate = True
End If
Exit Function

So, not so simple a question! (But I'll bet I'll be told I've
overcomplicated it!)

Kevin
 
I want to create a function with an input box to enter in
a year end date, then I want to pass this value SOMEPLACE
to store it. Then I can use it in a variety of other
places - BUT I don't want it to run the input box function
when I use the date.
Click to expand...

What about creating a class? One member to hold the yearnumber, one method
GetValueFromUser() and one read-only property Value. You would declare an
instance of the class in whatever scope is convenient -- global if it has
to be --

Dim yrvalStartYear As New CYearValue

and call

yrvalStartYear.GetValueFromUser

when you want to quiz the user, and then

strSQL = "..." & _
"WHERE YEAR(StartDate) >= " & yrvalStartYear.Value

when you want to use it. Welcome to object oriented programming....


Best wishes


Tim F
 
Hi Tim.

I need a little more guidance.

I want to use the year end date in other formulas - thus I
thought if I stored the value in a function, I could use
it in queries, formulas in forms and reports, etc. - not
just in code.

Thanks
Lisa
 
I want to use the year end date in other formulas - thus I
thought if I stored the value in a function, I could use
it in queries, formulas in forms and reports, etc. - not
just in code.
Click to expand...

Within Access, most of the options we have suggested can still work, but
sometimes you have to massage it a bit...

ControlSource properties for controls, and parameters in queries will see
Public Functions but not global variables or objects; so you can still use
a Global Dim to hold the year number (as a class object or simply as an
Integer), but you need to wrap it up in a Function() call.

So you can still do this in a Module:

Global g_dwYearEnd as Long

...


Public Sub AskTheUser
g_dwYearEnd = InputBox(etc...)
End Sub

and you also need

Public Function GetYearEnd() as Long
GetYearEnd = g_dwYearEnd
End Function

and then you can use =GetYearEnd() in control sources, query parameters
etc. I would be tempted to use a custom class simply because it's easier to
put in error trapping &c, but then again it might simply be over-
complicating things too.

Hope that makes some sense


Tim F
 
Not sure if you're checking this anymore, but thanks for
your help - the whole thing got way to complicated for my
liking so I've got something else working for me.

Thanks anyway
Lisa
 
Not sure if you're checking this anymore, but thanks for
your help - the whole thing got way to complicated for my
liking so I've got something else working for me.
Click to expand...

Fair 'nuff -- and best of luck with the project. It's often easier to sit
through someone else doing it in one go, rather than asking in bits and
pieces here.

All the best


Tim F
 
Back
Top