How to find an Access mentor?

[Rick Willingham]

"But Doc! How are we going to get home!?

----- Jeff Conrad wrote: ----

Hi Fred

Excellent advice for sure. I made all the same common mistakes as well.
wish I had found these newsgroups right when I was starting out with Access
It sure would have made life easier not having to go back and correct all m
past mistakes

At any rate here's a little function I use now frequently to cover pas
stupidity
Place in any standard module and hang on
Use with caution

Public Function FixPastMistakes(

Dim YearsPast As Intege
Dim TargetDate As Dat

YearsPast =

TargetDate = DateSerial(Format(Date, "yyyy") - 2,
(Format(Date, "m")), (Format(Date, "w"))

DoCmd.RunCommand acCmdActivateTimeWarp(TargetDate

End Functio

Oh, make sure you have a reference to the FluxCapacitor.oc

Jeff Conra
Bend, Orego

Dear Rick

Good luck with your application development! Working with Access can be

whole lot of fun! However, I hope you don't mind a small piece of advice
from someone who started from scratch in Access a couple of years ago: tr
not to be too "impatient" as you go about learning. If I had only take th
time to nail down the basics when I started trying to create a applicatio
in Access (way back when!), I would have had to spend *so* much less tim
fixing things up later on! I am *still* fixing problems which stem from m
improperly designed tables and relationships two years later... Also,
made many mistakes in not using a consistent naming convention in m
application, and not understanding and implementing error handling.. and.
well, the list goes on! Of course, I'm not suggesting that *you* have mad
these mistakes, but making sure you get it right at the beginning wil allo
you to fly much faster in the long term..

You might want to use the following as a checklist
http://www.mvps.org/access/tencommandments.ht
HT Fred Boe Hi MacDermott

the
cover the same things. Others don't have the in-depth coverage that I'
looking for. And others are too advanced. I guess I'm too impatient t
wade through beginning VBA and learn about building dice games and no
educated enough to understand a tome like Developer's Handbook having
on one of the websites from the list Jeff Conrad provided. And I hav
gotten 'smart' and used the search feature (doh!) - much better now! S
this forum is proving very beneficial to me. Again, thanks to everyon fo
their support

on mor
advanced Access texts you
area
Or if you're willing to pay, try your local IT staffing agencies They ma
have someone who'd be willing to hire out for an hour or two her

o

 

[Fred Boer]

Dear Rick:

Comments inline...

Fortunately I found out the problem early and was able to go back and clean

them all up. Never again, eh?

Yes, indeed. It took a fair bit of work for the kind souls in these
newsgroups to finally save me from the table lookup wizard!

Thanks for the advice too.

You are more than welcome!

This identifies the type of object and keeps the spaces out of the name.

Do you think that this suits the long-term, or am I missing something?

That looks fine, but I was also thinking of naming conventions for the
controls (comboboxes, textboxes, labels, etc.) on all your forms... Access
suggests names, but if you use a consistent naming convention for controls
it will help as well (i.e. txtFirstName is a textbox control, lblFirstName
is its label... etc.. rather than text0...)

On error handling, I haven't 'written' any code yet. I've only pasted (or

re-typed) a few short (and relatively easy to understand) codes. But I
think I understand the why of using error handling and what it does.
Basically, you write code that instructs (translated to English) "If the
following instructions don't work, then jump to the end of the instructions
and don't bother trying to finish doing the instructions."

Well, I'm not an expert (someone who is, please jump in <g>) , but I would
suggest that error handling does a lot more than just that... I sometimes
think that calling it "Error Handling" is misleading... because very often
the "errors" aren't really mistakes, but are things that you expect will
happen and need to manage...Don't know what I would call it instead,
however...

Have you got any more 'common newbie mistakes' I might try looking for or

avoiding?

Well, the Ten Commandments hits the high points for me, but at the risk of
sounding like a nag... (and my obsessiveness could have a lot to do with my
having spent a week dealing with headaches from having to make a structure
change in my tables..<sigh>)... I would re-visit my tables and
relationships. Maybe post your structure and have people here comment...

HTH
Fred

 

[Rick Willingham]

Fred

I'm lucky you have time for this

The naming conventions for controls and labels is something I didn't think of and I just ran into my first problem. I have a textbox with the name TimeCardDate and I tried to name a command button with the same name. Wrong! If I had been using the naming convention all the way through I wouldn't have had the problem. (D'Oh!) Now to go back and fix all those control and label names - there goes this weekend of being a couch-potato! ;-

I'd be happy to post tables and structure on next message. Does Access have a built-in function or should I type every table and field name here. Oops, even if there was a function, I don't see anyway of attaching files. Guidance

Rick..
----- Fred Boer wrote: ----

Dear Rick

Comments inline..

Fortunately I found out the problem early and was able to go back and clea

them all up. Never again, eh

Yes, indeed. It took a fair bit of work for the kind souls in thes
newsgroups to finally save me from the table lookup wizard! :

Thanks for the advice too

You are more than welcome

This identifies the type of object and keeps the spaces out of the name

Do you think that this suits the long-term, or am I missing something

That looks fine, but I was also thinking of naming conventions for th
controls (comboboxes, textboxes, labels, etc.) on all your forms... Acces
suggests names, but if you use a consistent naming convention for control
it will help as well (i.e. txtFirstName is a textbox control, lblFirstNam
is its label... etc.. rather than text0...

On error handling, I haven't 'written' any code yet. I've only pasted (o

re-typed) a few short (and relatively easy to understand) codes. But
think I understand the why of using error handling and what it does
Basically, you write code that instructs (translated to English) "If th
following instructions don't work, then jump to the end of the instruction
and don't bother trying to finish doing the instructions.

Well, I'm not an expert (someone who is, please jump in <g>) , but I woul
suggest that error handling does a lot more than just that... I sometime
think that calling it "Error Handling" is misleading... because very ofte
the "errors" aren't really mistakes, but are things that you expect wil
happen and need to manage...Don't know what I would call it instead
however... :

Have you got any more 'common newbie mistakes' I might try looking for o

avoiding

Well, the Ten Commandments hits the high points for me, but at the risk o
sounding like a nag... (and my obsessiveness could have a lot to do with m
having spent a week dealing with headaches from having to make a structur
change in my tables..<sigh>)... I would re-visit my tables an
relationships. Maybe post your structure and have people here comment..

HT
Fre

 

[PC Datasheet]

Rick,

Your naming conventions look good but also add these:

In TblInstaller, name the PK InstallerID and you will always know which table
the PK is for.

For forms, also use the prefix PFrm for popup forms and SFrm for subforms. The
name will then tell you the type of form and simolar types of forms will appear
in groups in the database window. For example, Frms will come first, Followed by
PFrms and lastly SFrms. Makes finding a form in the database window easier.

Use the prefix Rpt for main reports and SRpt for subreports. same reason as
forms.

When a query is a recordsource for a form use the prefix QryFrm, QryPFrm or
QrySFrm and when it is a record source for a report use QryRpt or QrySRpt.

Make it a habit to keep object names singular and you will never wonder if there
is an "s" on the end whenyou are writing code.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Fred,

Thanks for the link, the 'rules' are nicely put! I wish I had known NOT to

use Lookup Fields in my tables. Fortunately I found out the problem early and
was able to go back and clean them all up. Never again, eh?

Thanks for the advice too. For naming conventions, I picked up somewhere

(sorry to author) doing something like this:

tblInstaller
frmInstaller
qryActiveInstallers
rptActiveInstallers
This identifies the type of object and keeps the spaces out of the name. Do

you think that this suits the long-term, or am I missing something?

On error handling, I haven't 'written' any code yet. I've only pasted (or

re-typed) a few short (and relatively easy to understand) codes. But I think I
understand the why of using error handling and what it does. Basically, you
write code that instructs (translated to English) "If the following instructions
don't work, then jump to the end of the instructions and don't bother trying to
finish doing the instructions."

After reading the 'Commandments' list, methinks I must learn exact syntax and

go back into the app and add a few lines of code to every sub I have pasted in
there!

Have you got any more 'common newbie mistakes' I might try looking for or avoiding?

Rick...


----- Fred Boer wrote: -----

Dear Rick:

Good luck with your application development! Working with Access can be a
whole lot of fun! However, I hope you don't mind a small piece of advice,
from someone who started from scratch in Access a couple of years ago: try
not to be too "impatient" as you go about learning. If I had only taken the
time to nail down the basics when I started trying to create an application
in Access (way back when!), I would have had to spend *so* much less time
fixing things up later on! I am *still* fixing problems which stem from my
improperly designed tables and relationships two years later... Also, I
made many mistakes in not using a consistent naming convention in my
application, and not understanding and implementing error handling... and..
well, the list goes on! Of course, I'm not suggesting that *you* have made
these mistakes, but making sure you get it right at the beginning will allow
you to fly much faster in the long term...

You might want to use the following as a checklist:

http://www.mvps.org/access/tencommandments.htm

HTH
Fred Boer

Hi MacDermott,

they
cover the same things. Others don't have the in-depth coverage that I'm
looking for. And others are too advanced. I guess I'm too impatient to
wade through beginning VBA and learn about building dice games and not
educated enough to understand a tome like Developer's Handbook. local
user groups. I'm going to try those before I try hiring a consultant. having)
on one of the websites from the list Jeff Conrad provided. And I have
gotten 'smart' and used the search feature (doh!) - much better now! So
this forum is proving very beneficial to me. Again, thanks to everyone for
their support! recommendations
on more

advanced Access texts.

in

your
area.
Or if you're willing to pay, try your local IT staffing agencies. They may
have someone who'd be willing to hire out for an hour or two here

or

there
for the kind of services you're looking for.
if you ask specific questions in newsgroups, you'll usually

get

 

[Rick Willingham]

Steve

Thanks for the conventions. I printed the post and will incorporate the suggestions into my app. (and just when you think you've reached the top of the mountain, look! there's more!

I've posted a new thread that has all of my tables and fields. For the most part, I think I did as you suggest about the Primary key. But maybe another set of eyes??

Rick...
----- PC Datasheet wrote: ----

Rick

Your naming conventions look good but also add these

In TblInstaller, name the PK InstallerID and you will always know which tabl
the PK is for

For forms, also use the prefix PFrm for popup forms and SFrm for subforms. Th
name will then tell you the type of form and simolar types of forms will appea
in groups in the database window. For example, Frms will come first, Followed b
PFrms and lastly SFrms. Makes finding a form in the database window easier

Use the prefix Rpt for main reports and SRpt for subreports. same reason a
forms

When a query is a recordsource for a form use the prefix QryFrm, QryPFrm o
QrySFrm and when it is a record source for a report use QryRpt or QrySRpt

Make it a habit to keep object names singular and you will never wonder if ther
is an "s" on the end whenyou are writing code


-
PC Datashee
Your Resource For Help With Access, Excel And Word Application
(e-mail address removed)
www.pcdatasheet.co

Fred

use Lookup Fields in my tables. Fortunately I found out the problem early an
was able to go back and clean them all up. Never again, eh(sorry to author) doing something like this

tblInstalle
frmInstalle
qryActiveInstaller
rptActiveInstaller
This identifies the type of object and keeps the spaces out of the name. D

you think that this suits the long-term, or am I missing somethingre-typed) a few short (and relatively easy to understand) codes. But I think
understand the why of using error handling and what it does. Basically, yo
write code that instructs (translated to English) "If the following instruction
don't work, then jump to the end of the instructions and don't bother trying t
finish doing the instructions.go back into the app and add a few lines of code to every sub I have pasted i
there

Have you got any more 'common newbie mistakes' I might try looking for o avoiding
Rick..
Dear Rick
Good luck with your application development! Working with Access can be

whole lot of fun! However, I hope you don't mind a small piece of advice
from someone who started from scratch in Access a couple of years ago tr
not to be too "impatient" as you go about learning. If I had only take th
time to nail down the basics when I started trying to create a applicatio
in Access (way back when!), I would have had to spend *so* much less tim
fixing things up later on! I am *still* fixing problems which stem fro m
improperly designed tables and relationships two years later... Also,
made many mistakes in not using a consistent naming convention in m
application, and not understanding and implementing error handling.. and..
well, the list goes on! Of course, I'm not suggesting that *you* have made
these mistakes, but making sure you get it right at the beginning will allow
you to fly much faster in the long term...

You might want to use the following as a checklist:
http://www.mvps.org/access/tencommandments.htm
HTH Fred Boer Hi MacDermott,

they
cover the same things. Others don't have the in-depth coverage that I'm
looking for. And others are too advanced. I guess I'm too impatient to
wade through beginning VBA and learn about building dice games and not
educated enough to understand a tome like Developer's Handbook. local
user groups. I'm going to try those before I try hiring a consultant. having)
on one of the websites from the list Jeff Conrad provided. And I have
gotten 'smart' and used the search feature (doh!) - much better now! So
this forum is proving very beneficial to me. Again, thanks to everyone for
their support! recommendations
on more

advanced Access texts.

in

your
area.
Or if you're willing to pay, try your local IT staffing agencies. They may
have someone who'd be willing to hire out for an hour or two here

or

there
for the kind of services you're looking for.
if you ask specific questions in newsgroups, you'll usually

get

 

[Fred Boer]

Well, I used it, but I think I went too far back!... Now I'm all I want to
do is listen to music by Cat Stevens, and I've got all this hair, and
pimples, and I can't think about anything but girls... <g>

Fred

 

[Jeff Conrad]

Yeah, you do have to use with caution.

Some of my immediate plans for "fixing" my past mistakes:
1. Some past gifts for my wife
2. Not wearing bell-bottom pants
3. Take every spare penny I could find and buy Microsoft stock when it was
dirt cheap
4. Change college major and go to work for Industrial Light and Magic early
on
5. Being on Coke's marketing team and suggesting "New Coke"
6. Not playing with any of my Star Wars toys and instead keeping them sealed
in original packages
.........

Jeff Conrad
Bend, Oregon

 

[John Vinson]

Public Function FixPastMistakes()

Dim YearsPast As Integer
Dim TargetDate As Date

YearsPast = 2

TargetDate = DateSerial(Format(Date, "yyyy") - 2, _
(Format(Date, "m")), (Format(Date, "w")))

DoCmd.RunCommand acCmdActivateTimeWarp(TargetDate)

End Function

Oh, make sure you have a reference to the FluxCapacitor.ocx

Jeff, I think you're now officially Around the Bend... <bg>

 

[Fred Boer]

Jeff, I think you're now officially Around the Bend... <bg>


I agree totally, John...and I think that the situation calls for (thinking
of the sharks in Finding Nemo...) INTERVENTION for our resident Access
Junkie!<g>

Fred

 

[Jan Il]

You know, Jan, there is a major benefit of having this kind of keyboard...

people never ask to use your computer because they can't type properly on
it!

Fred (a.k.a. Mr. "It's MINE computer!! Don't touch!)

Now THERE"S is an idea! That might even work better then the Jolly Roger
screensaver and sign board that flashes "Don't even think about it!" on my
computer as a warning to would-be interlopers. I mean, why split hairs when
you can split a keyboard that only the most agile and dexterous can use
efficiently? But, hey, another good thing is that, when they make the next
Star Trek The Movie, I'll have the Vulcan salute perfected to a V. ;-))

Jan (a.k.a. Ms. "Live long....keep your hands off my Vboard")

 

[Jan Il]

Hi Rick,

I'm new to using Access and I'm building my first application. I have a

really clear Idea of how things should layout, the user interface, etc. and
by following the instructions in 'Access 2003 Inside Out' (and others) I
think that I have built a good foundation for the application. Using the
book as a guide, the designing of the tables, queries and forms was rather
easy. But now, (using VB to automate the functions I want) it feels like
walking into a 'Hall of Mirrors' - I know where I want to go, but I can't
figure out how to get there... And I can't seem to find examples of the
things I want the application to do. Also, I think I may have some problems
in the design of my DB (too many action queries and macros when VB would be
faster/better), in how I'm trying to do things, etc.

Does anyone know of a way I could locate someone to take a look at my DB,

give pointers, explain pros and cons of doing things different ways, etc.?

Welcome aboard, Rick!

Well...as you can see...you have come to the right place. You have not only
found a lot of mentors who are ready and willing to give you all the help
they can, but, a lot of new friends, who know and understand all your
frustrations and stumbles along the way. We have all been there, and many,
like myself, are still there struggling and learning. The folks here, both
posters and those who respond, provide a treasure trove of information and
experience you can't always find in books. New, out of the box ideas spring
up all the time from individual needs that have not yet been fully tried or
delved into before, and they become a whole new adventure to all and
everyone learns something new. There's always someone here 24/7, so when
you need help just fire a signal flare, and someone will throw you a line.

Oh yes..one more thing. Having a good time here is not a requirement, it
just sorta goes with the territory. ;-))

Jan

 

[Jeff Conrad]

Jeff, I think you're now officially Around the Bend... <bg>

ROFLOL!!

I'm NOW officially??!!
Well why the heck have I been in this padded cell the last couple of years
for?
"Just a few more minutes on the computer Ok guys?"
<vbg>

BTW, my apologies for forgetting to put you on the list of possible contract
help for Rick. I just realized I forgot. My humble apologies for the
oversight.

Jeff Conrad
Bend, Oregon

 

[Jeff Conrad]

All I need is for you to distract the guards for a few minutes Fred or to
sign the release form Dr. Boer.
"Really, I'm fine!"
(just like Chief Inspector Dreyfus on the Pink Panther movies; eye
twitching)

;-)

Jeff Conrad
Bend, Oregon

 

[John Vinson]

BTW, my apologies for forgetting to put you on the list of possible contract
help for Rick. I just realized I forgot. My humble apologies for the
oversight.

NOT a problem... over my ears right now anyway!