Sample code required

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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.
 
You might open up Northwind and take a look at it's General modules
(including the sole "Utility Function" IsLoaded)

HTH,
 
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 !
 
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
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
Click to expand...
Click to expand...
 
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
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
Click to expand...
Click to expand...
Click to expand...
 
Back
Top