Sample code required

A

Anita

Hello

I am an IT Trainer and know a fair bit about access but am doing a course
this week where I have to demonstrate creating a module. I know v. little
about VB. The version of Access is 2003.

Could anyone give me a really simple example of some code I could use to
demonstrate in class - something they can type in and see a result from?

Thanks so much - I'm desperate.

Anita
 
A

Arvin Meyer [MVP]

Really simple eh? How about:

Function DoBeep()
DoCmd.Beep
End Function

Then in the Immediate Window:

? DoBeep()

and hit the Enter key

Or only slightly more complex:

Sub Foo()
MsgBox "Hello World", "vbOKOnly, "Hi there!"
End Sub

Then in the Immediate Window:

Foo

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
S

Stephen Haley

Methinks one too many "s just before vbOkOnly
Sub Foo()
MsgBox "Hello World", "vbOKOnly, "Hi there!"
End Sub

should be

Sub Foo()
MsgBox "Hello World", vbOKOnly, "Hi there!"
End Sub

rgds
Stephen
 
A

Anita

Thanks for that - you're right it is easy but would like something to
demonstrate that's useful too! Something that the delegates will think 'oh
yeah' I could use that.

Cheers for your help
 
A

Albert D.Kallal

Well, it turns out that virtually any ms-access application I made has code,
and quite a bit of it...


Some simple, but useful examples:

Display a person age on a form.

You can have a birthdate field (text box control) on a form where you enter
the persons birthday. Beside that text box, you also want
to display the age of the person. So, you put some code in the module to
calculate the age.

The code:

Function Age(BirthDate) As Integer

Dim DateToday As Date

DateToday = Date ' "date is a built in function that
' returns the current date.
'
' We could have used this "date"
' function in the code below, but
' by placing the "date" into a
' variable called DateToday, the code
' is easy to read and understand.



' Returns the Age in years

Age = Year(DateToday) - Year(BirthDate)

' we still need to check if the month of the year
' has past..if not...birthday has not yet occureed!!

If Month(DateToday) < Month(BirthDate) Then

Age = Age - 1

End If

' perhaps it is the "same month", but the day
' of birth has not yet ocucred

If Month(DateToday) = Month(BirthDate) Then

' ok...same month as today..but check for the
' date in the month...

If Day(DateToday) < Day(BirthDate) Then
' ok..same month..but the day
' has not yet happened...so
Age = Age - 1
End If
End If

End Function

now, place a text box on the form, and we put the persons birthday, and also
today's date...

=Age([PersonBirthDay])

Of course, you change the above age expression "personbirthday" to whatever
the name of the field is that you have that is the actual birth day.

The advantage of using this function is now that when we build a report, we
also can place a text box on the report, and use the above function also.
 
G

George Nicholson

You might open up Northwind and take a look at it's General modules
(including the sole "Utility Function" IsLoaded)

HTH,
 
A

Anita

Well it looked perfect til I tried it out.

I don't know where I should be putting the code. I did Insert Procedure and
pasted it into there.

On my form I have a bound field called date of birth, then I created a text
box like you said for the age, but how do I get the age to display in the
text box - I'm not sure I understand how the new age field will link to the
existing date of birth field.

Sorry if I'm being dim !
 
A

Arvin Meyer [MVP]

To use Albert's example with your text box:

Add another text box to your form and set its controlsource in the property
sheet to:

=Function Age([Forms]![Your Form Name]![date of birth])

assuming that "date of birth" is your textbox and "Your Form Name" is the
name of your form.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Anita said:
Well it looked perfect til I tried it out.

I don't know where I should be putting the code. I did Insert Procedure and
pasted it into there.

On my form I have a bound field called date of birth, then I created a text
box like you said for the age, but how do I get the age to display in the
text box - I'm not sure I understand how the new age field will link to the
existing date of birth field.

Sorry if I'm being dim !

Albert D.Kallal said:
Well, it turns out that virtually any ms-access application I made has code,
and quite a bit of it...


Some simple, but useful examples:

Display a person age on a form.

You can have a birthdate field (text box control) on a form where you enter
the persons birthday. Beside that text box, you also want
to display the age of the person. So, you put some code in the module to
calculate the age.

The code:

Function Age(BirthDate) As Integer

Dim DateToday As Date

DateToday = Date ' "date is a built in function that
' returns the current date.
'
' We could have used this "date"
' function in the code below, but
' by placing the "date" into a
' variable called DateToday, the code
' is easy to read and understand.



' Returns the Age in years

Age = Year(DateToday) - Year(BirthDate)

' we still need to check if the month of the year
' has past..if not...birthday has not yet occureed!!

If Month(DateToday) < Month(BirthDate) Then

Age = Age - 1

End If

' perhaps it is the "same month", but the day
' of birth has not yet ocucred

If Month(DateToday) = Month(BirthDate) Then

' ok...same month as today..but check for the
' date in the month...

If Day(DateToday) < Day(BirthDate) Then
' ok..same month..but the day
' has not yet happened...so
Age = Age - 1
End If
End If

End Function

now, place a text box on the form, and we put the persons birthday, and also
today's date...

=Age([PersonBirthDay])

Of course, you change the above age expression "personbirthday" to whatever
the name of the field is that you have that is the actual birth day.

The advantage of using this function is now that when we build a report, we
also can place a text box on the report, and use the above function also.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Slight typo on Arvin's part. The keyword Function shouldn't have been there
in what he gave you. It should simply be:

=Age([Forms]![Your Form Name]![date of birth])

Make sure that you didn't name the module in which you put the function code
"Age": modules cannot be named the same as any subs or functions in the
application.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Arvin Meyer said:
To use Albert's example with your text box:

Add another text box to your form and set its controlsource in the
property
sheet to:

=Function Age([Forms]![Your Form Name]![date of birth])

assuming that "date of birth" is your textbox and "Your Form Name" is the
name of your form.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Anita said:
Well it looked perfect til I tried it out.

I don't know where I should be putting the code. I did Insert Procedure and
pasted it into there.

On my form I have a bound field called date of birth, then I created a text
box like you said for the age, but how do I get the age to display in the
text box - I'm not sure I understand how the new age field will link to the
existing date of birth field.

Sorry if I'm being dim !

Albert D.Kallal said:
Well, it turns out that virtually any ms-access application I made has code,
and quite a bit of it...


Some simple, but useful examples:

Display a person age on a form.

You can have a birthdate field (text box control) on a form where you enter
the persons birthday. Beside that text box, you also want
to display the age of the person. So, you put some code in the module
to
calculate the age.

The code:

Function Age(BirthDate) As Integer

Dim DateToday As Date

DateToday = Date ' "date is a built in function that
' returns the current date.
'
' We could have used this "date"
' function in the code below, but
' by placing the "date" into a
' variable called DateToday, the code
' is easy to read and understand.



' Returns the Age in years

Age = Year(DateToday) - Year(BirthDate)

' we still need to check if the month of the year
' has past..if not...birthday has not yet occureed!!

If Month(DateToday) < Month(BirthDate) Then

Age = Age - 1

End If

' perhaps it is the "same month", but the day
' of birth has not yet ocucred

If Month(DateToday) = Month(BirthDate) Then

' ok...same month as today..but check for the
' date in the month...

If Day(DateToday) < Day(BirthDate) Then
' ok..same month..but the day
' has not yet happened...so
Age = Age - 1
End If
End If

End Function

now, place a text box on the form, and we put the persons birthday, and also
today's date...

=Age([PersonBirthDay])

Of course, you change the above age expression "personbirthday" to whatever
the name of the field is that you have that is the actual birth day.

The advantage of using this function is now that when we build a
report, we
also can place a text box on the report, and use the above function also.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal
Click to expand...
Click to expand...
Click to expand...
 
Top