Hello:

    I am quite new to this coding thing and am trying to use Beginning 

Access97 VBA; however Chapter 4 is blowing my mind away. I understand what 

variables are for and how to declare them. I am struggling with how do you 

know what variables you will need when you begin to create a procedure or 

fucntion? The authors seem to have forgotten to explain why they choose 

the variables they did at the beginning of their codes. Thanks for any 

help.

Dave,



You don't always know what you need when you start BUT you should have 

an

idea of what you are trying to achieve.



Start with a printout of the properties of the table  you are working o

n,

add the properties of the table you might use to produce results and ad

d to

that certain variables you will need to monitor how the procedure runs.





I start with assuming that I will wish to display a status / progress b

ar

and immediately I need certain variables



Dim RETVAL As Variant, SEQ As Long, COUNTRECS As Long, MSGTXT As String





Investigate the HELP in access and look at SYSCMD to see the help about



status bar / progress bar



You probably need to have a line like



Dim DB As DAO.Database, RS As DAO.Recordset



or the equivalent in ADO, if you are using recordsets in a procedure.



The rest is down to experience and thinking about what you are trying t

o

achieve. Best advice is to get hold of free samples of how other people

 do

it - there is a lot out there on the web - generally Access people and

programmers in general are incredibly generous in making their experien

ce

available to others. We all keep learning from others.



One of the best bits of advice I can offer to 'rookies' (if this doesn'

t

upset people) is to not get over-ambitious - I have seen many people tr

ying

to write incredibly complex reports and I advise them to consider split

ting

the problem into 2 parts:



   collect the data (and prove its accuracy) in a temporary table  -

   temporary in that you empty the table and recalculate the results ev

ery

   time you run the procedure.

   once you are sure of the data collected, handle the formatting eleme

nts

   in the report itself.



if you attempt all of this in the report, you will find it very difficu

lt

to prove the data before you can concentrate on the look of the report.



Coming back to your question of what variables do you need to define,

splitting a complex process into 2 parts (collection and presentation),



clarifies your mind as to what you need in each part.



By the way, splitting the job into 2 and eventually running the whole

process with a bit of code (or even a macro) does not generally add tha

t

much overhead. You benefit by isolating various issues into separate ar

eas.



I hope this is useful - there is no magic involved -  but it is my

(hopefully) common-sense approach to a development problem.



I subscribe to several Access publications (such as Smart Access) and g

et

useful code for a very low cost - better still I get good explanations 

of

why the code is written as it is - and I buy (or get my firm to buy!) g

ood

books (try Access 2000 Developer's Handbook). Courses are generally wel

l

worth the money but a course usually costs about =A3150 upwards per day



($225 in US?) and my annual subscription to Smart Access plus my purcha

se

of the 2 volume set of Access 20000 Developer's Handbook costs about th

e

same as one day's course fees.



What I am trying to say is that there is no immediate answer to your

question but  there is a lot of help out there for FREE or for very lit

tle

cost. Go for it!



Joe Dunn









                                                                       

                 

                    "Dave Cool"                                        

                 

                    <dac@l...        To:     "Access" <access@p...

rox.com>         

                    .com>                cc:                           

                 

                                         Subject:     [access] Declarin

g Variables      

                    18/12/2001                                         

                 

                    14:46                                              

                 

                    Please                                             

                 

                    respond to                                         

                 

                    "Access"                                           

                 

                                                                       

                 

                                                                       

                 









Hello:

    I am quite new to this coding thing and am trying to use Beginning

Access97 VBA; however Chapter 4 is blowing my mind away. I understand w

hat

variables are for and how to declare them. I am struggling with how do 

you

know what variables you will need when you begin to create a procedure 

or

fucntion? The authors seem to have forgotten to explain why they choose



the variables they did at the beginning of their codes. Thanks for any

help.














*************************************************************************



This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.



The CIS marketing group, which is regulated for Investment Business by the Personal Investment Authority, includes:

Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions

CIS Unit Managers Limited Registered in England and Wales number 2369965 (also regulated by IMRO) - for unit trusts and PEPs

CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name

Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...



CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.



CIS is a member of the General Insurance Standards Council



CIS & the CIS logo (R) Co-operative Insurance Society Limited



********************************************************************************

Dear Dave Cool,



<< I am quite new to this coding thing and am trying to use Beginning 

Access97 VBA; however Chapter 4 is blowing my mind away. I understand what 

variables are for and how to declare them. I am struggling with how do you 

know what variables you will need when you begin to create a procedure or 

fucntion? The authors seem to have forgotten to explain why they choose 

the variables they did at the beginning of their codes. Thanks for any 

help. >>



Many times, variable selection within a procedure really depends on 

whether or not you need a temporary variable to hold data so you can 

perform an operation on it, and then display the result.



For example, you have a table that holds one field:  Mass.  In your user 

form you want to tell the user what energy a mass has for record no. 27, 

for example.  You know that E = MC^2, or energy is mass times the square 

of the speed of light.



You're NOT going to store the value for "Energy" nor for the speed of 

light for all 25,000 records.  That takes up space.  So you create a 

constant called LightSpeed and a variable called Energy.



Then you can set LightSpeed once as the speed of light.  Then on the form, 

you can tell the user that for record 27 or whatever...



Engergy = Me.Mass * LightSpeed * LightSpeed



(I'm not sure if "squared" in Access is denoted by double asterisk ** or a 

single carat ^).



In this case you need to create two variables to hold numbers temporarily 

because storing them permanently in a table would be a waste of computer 

byte space.  Numbers that can be calculated easily normally are not stored 

for that reason.



In the example above, when you close the database, the memory held by 

Energy and LightSpeed are freed.  Again, you only store Mass, saving 

space... er, mass.  Heh, heh...  



Greg



P.S.:  Are you any relation to Joe Cool?   :)

Thanks Gary

	What I gather from you answer:

		I should probably work out the procedure on paper - that way

I can see what values I will need. Then determine from where those values

are derived. If they are in a table, refer to the table (which is another

question - how do you accomplish that?). If they are not stored in a table,

that is a good clue a variable is required.



I do have a grand nephew named Joe



But I like to think I AM Joe Cool!!!





Dave



The Lubrizol Corporation

Fluid Technologies For a Better World *

Dave Cool (DAC) 

Phone:     (xxx) xxx-xxxx  

Fax:        (xxx) xxx-xxxx

E-mail:    dac@l... <mailto:dac@l...> 





	-----Original Message-----

	From:	Gregory Serrano [SMTP:SerranoG@m...]

	Sent:	Tuesday, December 18, 2001 11:14 AM

	To:	Access

	Subject:	[access] Re: Declaring Variables



	Dear Dave Cool,



	<< I am quite new to this coding thing and am trying to use

Beginning 

	Access97 VBA; however Chapter 4 is blowing my mind away. I

understand what 

	variables are for and how to declare them. I am struggling with how

do you 

	know what variables you will need when you begin to create a

procedure or 

	fucntion? The authors seem to have forgotten to explain why they

choose 

	the variables they did at the beginning of their codes. Thanks for

any 

	help. >>



	Many times, variable selection within a procedure really depends on 

	whether or not you need a temporary variable to hold data so you can



	perform an operation on it, and then display the result.



	For example, you have a table that holds one field:  Mass.  In your

user 

	form you want to tell the user what energy a mass has for record no.

27, 

	for example.  You know that E = MC^2, or energy is mass times the

square 

	of the speed of light.



	You're NOT going to store the value for "Energy" nor for the speed

of 

	light for all 25,000 records.  That takes up space.  So you create a



	constant called LightSpeed and a variable called Energy.



	Then you can set LightSpeed once as the speed of light.  Then on the

form, 

	you can tell the user that for record 27 or whatever...



	Engergy = Me.Mass * LightSpeed * LightSpeed



	(I'm not sure if "squared" in Access is denoted by double asterisk

** or a 

	single carat ^).



	In this case you need to create two variables to hold numbers

temporarily 

	because storing them permanently in a table would be a waste of

computer 

	byte space.  Numbers that can be calculated easily normally are not

stored 

	for that reason.



	In the example above, when you close the database, the memory held

by 

	Energy and LightSpeed are freed.  Again, you only store Mass, saving



	space... er, mass.  Heh, heh...  



	Greg



	P.S.:  Are you any relation to Joe Cool?   :)






$subst('Email.Unsub')..











Both the individual sending this e-mail and The Lubrizol Corporation

intend that this electronic message be used exclusively by the 

individual or entity to which it is intended to be addressed.  This 

message may contain information that is privileged, confidential and

thereby exempt and protected from unauthorized disclosure under

applicable law.  If the reader of this message is not the intended

recipient, or an employee or agent responsible for delivering the

message to the intended recipient, be aware that any disclosure,

dissemination, distribution or copying of this communication, or the

use of its contents, is not authorized and is strictly prohibited.  If 

you have received this communication and are not the intended

recipient, please notify the sender immediately and permanently

delete the original message from your e-mail system.      

I appreciate your help; however, your answer was a little over my head.

 I

will try and catch up to where I need to be so I can understand answers

 from

those who are gracious enough to help me.



Thanks



Dave



The Lubrizol Corporation

Fluid Technologies For a Better World *

Dave Cool (DAC) 

Phone:     (xxx) xxx-xxxx  

Fax:        (xxx) xxx-xxxx

E-mail:    dac@l... <mailto:dac@l...> 





	-----Original Message-----

	From:	joe.dunn@c... [SMTP:joe.dunn@c...]

	Sent:	Tuesday, December 18, 2001 10:45 AM

	To:	Access

	Subject:	[access] Re: Declaring Variables





	Dave,



	You don't always know what you need when you start BUT you should

have an

	idea of what you are trying to achieve.



	Start with a printout of the properties of the table  you are

working on,

	add the properties of the table you might use to produce results and

add to

	that certain variables you will need to monitor how the procedure

runs.



	I start with assuming that I will wish to display a status /

progress bar

	and immediately I need certain variables



	Dim RETVAL As Variant, SEQ As Long, COUNTRECS As Long, MSGTXT As

String



	Investigate the HELP in access and look at SYSCMD to see the help

about

	status bar / progress bar



	You probably need to have a line like



	Dim DB As DAO.Database, RS As DAO.Recordset



	or the equivalent in ADO, if you are using recordsets in a

procedure.



	The rest is down to experience and thinking about what you are

trying to

	achieve. Best advice is to get hold of free samples of how other

people do

	it - there is a lot out there on the web - generally Access people

and

	programmers in general are incredibly generous in making their

experience

	available to others. We all keep learning from others.



	One of the best bits of advice I can offer to 'rookies' (if this

doesn't

	upset people) is to not get over-ambitious - I have seen many people

trying

	to write incredibly complex reports and I advise them to consider

splitting

	the problem into 2 parts:



	   collect the data (and prove its accuracy) in a temporary table  -

	   temporary in that you empty the table and recalculate the results

every

	   time you run the procedure.

	   once you are sure of the data collected, handle the formatting

elements

	   in the report itself.



	if you attempt all of this in the report, you will find it very

difficult

	to prove the data before you can concentrate on the look of the

report.

	Coming back to your question of what variables do you need to

define,

	splitting a complex process into 2 parts (collection and

presentation),

	clarifies your mind as to what you need in each part.



	By the way, splitting the job into 2 and eventually running the

whole

	process with a bit of code (or even a macro) does not generally add

that

	much overhead. You benefit by isolating various issues into separate

areas.



	I hope this is useful - there is no magic involved -  but it is my

	(hopefully) common-sense approach to a development problem.



	I subscribe to several Access publications (such as Smart Access)

and get

	useful code for a very low cost - better still I get good

explanations of

	why the code is written as it is - and I buy (or get my firm to

buy!) good

	books (try Access 2000 Developer's Handbook). Courses are generally

well

	worth the money but a course usually costs about =A3150 upwards per

day

	($225 in US?) and my annual subscription to Smart Access plus my

purchase

	of the 2 volume set of Access 20000 Developer's Handbook costs about

the

	same as one day's course fees.



	What I am trying to say is that there is no immediate answer to your

	question but  there is a lot of help out there for FREE or for very

little

	cost. Go for it!



	Joe Dunn









	



	                    "Dave Cool"



	                    <dac@l...        To:     "Access"

<access@p...>          

	                    .com>                cc:



	                                         Subject:     [access]

Declaring Variables       

	                    18/12/2001



	                    14:46



	                    Please



	                    respond to



	                    "Access"



	



	











	Hello:

	    I am quite new to this coding thing and am trying to use

Beginning

	Access97 VBA; however Chapter 4 is blowing my mind away. I

understand what

	variables are for and how to declare them. I am struggling with how

do you

	know what variables you will need when you begin to create a

procedure or

	fucntion? The authors seem to have forgotten to explain why they

choose

	the variables they did at the beginning of their codes. Thanks for

any

	help.




$subst('Email.Unsub').







	

***********************************************************************

**



	This e-mail may contain confidential information or be privileged.

It is intended to be read and used only by the named recipient(s). If y

ou

are not the intended recipient(s) please notify us immediately so that 

we

can make arrangements for its return: you should not disclose the conte

nts

of this e-mail to any other person, or take any copies. Unless stated

otherwise by an authorised individual, nothing contained in this e-mail

 is

intended to create binding legal obligations between us and opinions

expressed are those of the individual author.



	The CIS marketing group, which is regulated for Investment Business

by the Personal Investment Authority, includes:

	Co-operative Insurance Society Limited Registered in England number

3615R - for life assurance and pensions

	CIS Unit Managers Limited Registered in England and Wales number

2369965 (also regulated by IMRO) - for unit trusts and PEPs

	CIS Policyholder Services Limited Registered in England and Wales

number 3390839 - for ISAs and investment products bearing the CIS name

	Registered offices: Miller Street, Manchester M60 0AL   Telephone

0161-832-8686   Internet  http://www.cis.co.uk   E-mail cis@c...



	CIS Deposit and Instant Access Savings Accounts are held with The

Co-operative Bank p.l.c., registered in England and Wales number 990937

,

P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by

 CIS

Policyholder Services Limited as agent of the Bank.



	CIS is a member of the General Insurance Standards Council



	CIS & the CIS logo (R) Co-operative Insurance Society Limited



	

***********************************************************************

*****

****






$subst('Email.Unsub')..













Both the individual sending this e-mail and The Lubrizol Corporation

intend that this electronic message be used exclusively by the 

individual or entity to which it is intended to be addressed.  This 

message may contain information that is privileged, confidential and

thereby exempt and protected from unauthorized disclosure under

applicable law.  If the reader of this message is not the intended

recipient, or an employee or agent responsible for delivering the

message to the intended recipient, be aware that any disclosure,

dissemination, distribution or copying of this communication, or the

use of its contents, is not authorized and is strictly prohibited.  If 



you have received this communication and are not the intended

recipient, please notify the sender immediately and permanently

delete the original message from your e-mail system.      

Dave,



<< What I gather from you answer:



I should probably work out the procedure on paper - that way I can see 

what values I will need. Then determine from where those values are 

derived. If they are in a table, refer to the table (which is another 

question - how do you accomplish that?). If they are not stored in a 

table, that is a good clue a variable is required. >>



That's a pretty good assessment.



As for referring to values in tables, it depends...



If varible is in a table that is the open form's (say Form #1) own record 

source, then to refer to the current value, you refer to Me.{name of 

field}.  The "Me." is shorthand for "Forms![Form 1]."



If the variable is in a table that is the record source for ANOTHER OPEN 

FORM, say Form #2 (Form #1 has the focus and Form #2 is a hidden window) 

then you can't use the shorthand.  Form #1 is on top, so you write Forms!

[Form 2].variable.  If Form #2 is not open, this will give you an error 

message.



If you want to refer to a variable that is not in open Form #1 then there 

are a couple of ways depending on what you want.  Suppose Form #1 has 

variables A, B, and C; but you want to add A, B, C, and D based on an 

Employee ID number.  Form #1 accesses Table #1 and A, B, and C.  However, 

D is in Table #2.



You can do this:



SumNo = Me.A + Me.B + Me.C + DLOOKUP("[D]","Table 2","[Employee ID] = '" & 

Me.[Employee ID] & "'")



DLOOKUP is great for reading values and using them.  Now if you want to 

WRITE to Table #2 from Form #1 (whose record source is Table #1).  That's 

a whole other issue and programming.



Greg