Simple Insert Into...

[Robert5833]

Hi Tom,

This is great information, and I appreciate your time and guidance. For all
that I’ve read in the “formal†publications, I seem to get the most, and the
best “real world†context from this discussion group and those who support it.

Thanks again for your input and the help.

Comments in line below:

Hi Robert,


Here is a "gem tip" that I wrote on this subject. It also shows you how to
change the default behavior, so that all new modules you create will
automatically include these two very important words:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Good advice; and making the settings changes (I’ve seen that feature, but
just haven’t done it yet). And I'll be reading the linked material shortly.
Thanks!

While you're at it, you might as well remove the not-so-helpful auto index
"feature":

http://www.access.qbuilt.com/html/gem_tips.html#AutoIndex

Good advice; I’ve already disabled Auto Correct Names, but didn’t think
about this setting or what it meant.

No--the only real benefit is to aid in debugging.

Debugging; or better, the methods and techniques one uses to debug is
something I’m beginning to understand more clearly; and I’ve found the
Debug.Print and other uses of the Immediate Window very helpful tools.

I routinely set a string variable myself, rather than try to include the SQL
within the .execute method directly. And when I need to troubleshoot other's
databases that include db.execute "Some SQL String" (where "db" has
previously been set = CurrentDB), I usually go ahead and separate it out, so
that I can look at the SQL statement that results.

This helps me understand the different approach much better. I did find that
when I changed to this technique, I was able to do a lot more with the
Immediate Window.

I also tend to declare a database variable, and set it, like this:

Dim db as DAO.Database
Set db = CurrentDB()

so that I can use db.execute to run an action query. I generally prefer
doing that versus using CurrentDB.Execute, however, it doesn't really matter
performance wise, unless you have a need to reference CurrentDB more than one
time in the same procedure (for example, you have a procedure that runs
several action queries). In that case, setting the variable once and reusing
it will be faster versus making a call to CurrentDB each time.

I am doing this routinely now in my procedures; and I can see the savings in
time it provides. I understand better now why and how it is done. Thank you!

However, when you set a database variable, you should ensure that you also
set it equal to nothing as a part of the ExitProc part of your procedure.

I am doing this too. Thank you!

As always, I appreciate your help and your patience with me and others like
me; who are interested in learning the “tricks of the trade†even as we
struggle to understand.

Best regards,
Robert

 

[dymondjack]

The field’s record is stored, but in another table, not the one the form is

based on. The form is based on an SQL in the form's Record Source property,
not on a query object (I don't know if this is bad or good, but I've tried to
minimize the number of database objects. Maybe this is one example where a
saved query is better than the alternative?).

I would do the same thing, personally, for the same reasons. The little bit
of extra coding it takes working with a SQL string as a recordsource rather
than a saved query generally balances out nicely with the fact that you won't
be saddled with all sorts of miniscule objects as your db grows. Thats just
my opinion though.

I would have gotten stumped on this one; thank you for pointing that out!

Sometimes the unbound controls do have a value (such as when they are set
via code or I *think* from a drop down), even though it won't actually save
that value. You may still be able to refer to the Value rather than the
Text, but as a safegaurd I generally go with the text, just in case.

I like this technique; there are dozens of other places I can apply its use!

Hundreds of places... a good undrestanding of variable scope and lifetime,
and how to use them, will make your coding a thousand times easier than they
would be otherwise.

Does that mean I need to put a statement like; *Set plngID = Nothing* in the
Exit_Procedure?

Nope. The difference here is plngID is a variable, and a Recordset is an
object. Variables cannot be set to nothing (try it, you'll get an error).
Nothing is similar to a variable being Null, but it is important to remove
the reference with objects.

Variables (that don't need/can't have = Nothing), are as follows:

Long
Byte
Integer
String
Currency
Boolean
Date
Decimal
Single
Double

Examples of objects that you can remove references on:

Recordsets
Forms
Tables
Queries

To name a few... press F2 from the vba window to open the object browser.

Anytime you need to set an object reference with the Set statement, you
should set it to Nothing when you are done with it.

Set objControl = Forms!Formname.Controls!Controlname
Set rstContacts = Currentdb.OpenRecordset(strSQL)

Set objControl = Nothing
Set rstContacts = Nothing

If you try this in your code:

Dim strVar as String
Set strVar = "mystring"

you'll get an error. Therefore, variables cannot be 'Set'

Variables automatically get cleared at the end of their scope, there usually
is no reason to explicitly set them to anything at the end of a procedure.
Object references, however, sometimes stick around to cause problems later,
so we always make sure to un-reference them.

Follow the rule "Close what you open, destroy what you create" when working
with object references.

rstContacts.Close
Set rstContacts = Nothing

Not all objects have a Close method, but every object reference can be set
to Nothing


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Hello Jack,

Thank you for the reply and the detail in your outline! You’ve helped me
tremendously and I appreciate that so much.

Honestly; I’m still struggling with how to reply in line and in sequence on
these posts, but please don’t take that to mean I appreciate or favor one
helpful reply over another, and I do make the attempt to recognize everyone
who has contributed. <humble smile>

I’ve read through this latest reply from you, and I think it’s exactly what
I was looking for! (Responses in line)

Hello again

If the data you are trying to incorporate into your sql is already in a
control on the form, its easy enough to include. There's a few additional
'rules' for dealing with unbound controls vs. bound controls, but its nothing
major.

Yes, it is in a control on the form; and is unbound (it's actually a combo
box that is used as a dropdown list, values taken from a different table; SQL
in Row Source).

You can store unbound control data in a variable, and refer to that variable
in the sql. It is possible to run a function to return the value directly
from the sql, but I believe its one of those "you shouldn't unless its the
only way" type of situations (IMO, but I may be quite wrong here... just
because I prefer not to doesn't really mean anything).

The field’s record is stored, but in another table, not the one the form is
based on. The form is based on an SQL in the form's Record Source property,
not on a query object (I don't know if this is bad or good, but I've tried to
minimize the number of database objects. Maybe this is one example where a
saved query is better than the alternative?).

Also, note that including this in the SELECT sql won't work (at least if I
understand the situation correctly... there is no actual value stored in the
table to SELECT from. I believe you want this value to be part of your
INSERT statement???).

Yes; I need this value to assign a unique ID to the entire recordset being
appended (inserted). And no, the value is not stored in the named table the
SELECT statement is based on (for the INSERT INTO SQL).

Including the value of a variable into an SQL statement is done the same way
you would refer to the a form's control. Here's a very basic example.

Dim strSQL As String
Dim strVar As String
strVar = "mystring"
strSQL = "INSERT INTO... " & strVar & " ........"

therefore, if you were to type ?strSQL in the immediate window while
debugging, you would see:

INSERT INTO... mystring .......

This is cool! I've tried to use variables like this, and for the reasons and
the benefit of using the Immediate Window...something I'm just now learning
the value of.

The problem with unbound controls is that they don't always have an actual
Value (they are unbound, and hence have no place to store it). But, you can
always refer to the control's Text property (Me.ControlName.Text), which
holds the current text in the control, whether it's saved or not.

I would have gotten stumped on this one; thank you for pointing that out!

But, in order to refer to that control, it needs to have the focus. You can
set the focus of the control using the SetFocus method:

Me.ControlName.SetFocus

And, always be careful of null values, easily handled using the Nz function
(see help file for details)

strVar = Nz(Me.ControlName.Text, "")

Glad you pointed this out; use of Nz in the variable, I would have gotten
stuck there for sure! (I use the Nz elsewhere, but wouldn't have guessed it
could be used as above.) The field's value will only be null when the form
opens. If the Insert Into function is called on a Null value, I catch the
event and throw a message and Exit Procedure.

Sometimes though, you don't generally want your user playing around with
unbound controls, so you may have the Enabled property set to False. In this
case, the SetFocus method won't work, because a disabled control is not
allowed to have the focus. And if it can't have the focus, you cant read the
Text property from code. Luckily though, Access provides with a Locked
property for each control, where it can have focus, but cannot be changed by
the user. The Locked property can be found in the properties window of the
control in design view, should you decide to use it.

I've got this control set to *Limit to List* and the control's property
settings don't allow edits.

Now, back to getting that Text value into your sql statement...

In your form's Sub, you'll wind up with something like the following:


Dim strID As String
Me.ControlName.SetFocus
strID = Nz(Me.ControlName.Text, "")
...
...
Currentdb.Execute "INSERT INTO ..." & strID & "......"


At this point, you may be scratching your head, thinking "I thought I said
that value was a Number, not a String, this isn't going to work." And you're
right. The thing is, any value pulled from the Text property of a control is
a String, regardless of what it was when you put it there. Luckily though,
VBA provides us with conversion functions, so that we may convert that String
from the Text property back into a Long for your SQL.

I would have gotten stumped on this one; thank you for pointing that out!

Dim strID As String
Dim lngID As Long
Me.ControlName.SetFocus
strID = Nz(Me.ControlName.Text, "")
lngID = Clng(strID)
...
...
Currentdb.Execute "INSERT INTO ..." & lngID & "......"

This seems pretty straight forward… What I’ve tried so far has revealed that
the INSERT INTO needs a corresponding FROM statement; i.e., both statements
need to agree in the number of arguments… I’ll give it a try!

________________________________________________


Now that I've typed all that out, here's another way that might be a bit
easier. Lets store that number from your function into a variable thats
visible to all procedures in the form. Then, you can use that variable to
set the control, and later you can use that variable in your SQL without
having to go through conversions and makings sure you have focus of the
control to get the Text value, yadda yadda yadda.

I like this technique; there are dozens of other places I can apply its use!

In the declarations portion of your form's module (After the Option Explicit
but before the first Sub or Function), declare a variable that's private to
this entire module. Like so:

Private plngID As Long

(I always prefix these variables with a 'p' so they don't get confused with
normal ones inside procedures). This variable will now be available to every
function or sub in this module, and will hold it's value until the form
closes (just be sure to reset it with your function on the OnCurrent event of
your form... so every time the record changes the value changes as well).

Does that mean I need to put a statement like; *Set plngID = Nothing* in the
Exit_Procedure?

So then you might end up with a module like this:


'START CODE
Option Compare Database
Option Explicit

Private plngID As Long

Private Sub Form_Current()
plngID = YourFunction()
Me.UnboundControlName = plngID
End Sub

Private Sub ButtonRunSQL_Click()
Currentdb.Execute "INSERT INTO... " & plngID & " ....."
End Sub
'END CODE


At this point, you might find that you don't even need that unbound control...

Yes, I'm going to give that some more thought. Because I'm using (getting)
the value from a dropdown list; I'm not sure if there are better options for
me.

Hopefully this helps. Sometimes I'm not sure if my posts are more confusing
than helpful as its difficult to know exactly what the situation is (no
matter how well someone explains it), but maybe you can use some of the info
to get your task done.

All of your posts have been helpful, and I’ve never struggled to understand
them. <big smile> On the other hand; sometimes I don’t know enough about what
I’m doing to even formulate a coherent question…but fortunately for me you
and others seem to be able to muddle through my rambling enough to offer up a
helpful response.

Feel free to ask any more questions, as long as you don't mind trying to
work your way through my posts

Thank you so much for the help! I’m sure I’ll have more questions, but I
always make a diligent effort on my own so as not to bother you all
unnecessarily.

Best regards,

Robert

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Hello Jack, et al;

Thank you Jack and everyone else for the good advice and suggestions on
methods and technique; and although I’m quite new to most of this, I’d like
to think I’m smart enough, and humble enough adopt such input and will do
just that; even if it remains beyond my understanding at present. <smile>

With all of the good advice and suggestions I've found here, I’ve created
several functions now that are doing their jobs very nicely. I have a form
that filters records, I have a button to click to append the form’s recordset
to another table, and balance and serenity have returned to the universe once
again!

Thank you all so much for the help!

On a related note (it seems that for each fix there’s yet another problem);
I still have one bit of data that I need to manage:

Where the table I’m pulling data from is generic; call it an Inventory list
that can be ordered against, the table I’m appending the recordset to uses a
unique identifier to relate those records to, call it an Order ID. And while
I can append the recordset to the applicable table the unique identifier is
not part of that recordset, but I would like to be able to *add* or *assign*
the unique ID during the append process.

The *form* that provides the filtered records to append to the other table
does hold the unique ID, but it is an unbound control with a lookup SQL in
its Row Source property and not part of the form’s underlying table.

The problem is I can’t figure out how to include that bit of data in the
SELECT statement?

Here’s the code I’m using (shortened by removing some of the referenced
fields) for appending the recordset from the form:

CurrentDb.Execute "INSERT INTO tblTracked([fldTrackedID], " _
& "[fldTrackedMake], [fldTrackedModel], [fldTrackedPN]) " & _
"SELECT fldTrackedID, fldTrackedMake, fldTrackedModel, " & _
"fldTrackedPN " & _
"FROM tblTrackReqd " & _
"WHERE tblTrackReqd.fldTrackedID = " &
[Forms]![frmBuildTracked]![cboSelTrackedModel], dbFailOnError

I have created a function to get the unique ID (a Number data type) from the
calling form that I want to add to the data appended from the SQL returned
recordset, but I can’t figure out how to include it in the SQL string so it
*inserts* with the other data? Should, or could the ID just be a variable
declared in the form’s Sub, or is it better to call it from a Function?

At any rate, I can’t get the syntax right to include either the called
Function or its returned value into the SQL or UPDATE Function, and this last
little detail will round out what is otherwise a functional procedure.

Any suggestions or help you may be able to offer would be most appreciated!

Best regards,
Robert


"dymondjack" wrote:

 

[Robert5833]

Hello Jack,
And thank you for the reply; and the guidance and explanations. I’m getting
smarter by the second, and as you’ve intimated here; each technique and
method has a resounding effect on other procedures. So that you know, I apply
my “new found†knowledge to other existing procedures and my whole Db gets
better each day.

Comments in line below:

I would do the same thing, personally, for the same reasons. The little bit
of extra coding it takes working with a SQL string as a recordsource rather
than a saved query generally balances out nicely with the fact that you won't
be saddled with all sorts of miniscule objects as your db grows. Thats just
my opinion though.

I’m learning too, that I can customize the Record Source level SQL, which
makes it that much more valuable. In another of your posts in a different
thread you provided instructions on how to create a calculated field. That
worked very well, and when I was done (creating such in a query object), I
simply pasted the SQL version into my form’s Record Source. Now I have a
control on the form with the resulting value as a bound control.

After reading some of the replies to another question I posted regarding the
use of lookup fields in tables, there was mention about what one can do with
a saved query that has advantages over “unsaved†queries, which I presume to
be reference to the Record Source type. Because saved queries have query
properties and definitions that can be set at the design level, it stands to
reason that some instances may require one over the other. I’m going to
continue to read up on that point.

Sometimes the unbound controls do have a value (such as when they are set
via code or I *think* from a drop down), even though it won't actually save
that value. You may still be able to refer to the Value rather than the
Text, but as a safegaurd I generally go with the text, just in case.



Hundreds of places... a good undrestanding of variable scope and lifetime,
and how to use them, will make your coding a thousand times easier than they
would be otherwise.

Agreed; clearly making full use of variables has many advantages, and I’m
anxious to learn more with respect to their use. I’ll continue to read up on
them, and will pay close attention to how others are using them.

Nope. The difference here is plngID is a variable, and a Recordset is an
object. Variables cannot be set to nothing (try it, you'll get an error).
Nothing is similar to a variable being Null, but it is important to remove
the reference with objects.

I think I understand now; if the value of a variable is declared at the
procedure level it would hold that value until the form is closed. If that
same variable and value statement is set in the form’s On Current event, it
would reset with each record cycled through on the form. (I may not have
explained this right, but I think I get the concept.)

Variables (that don't need/can't have = Nothing), are as follows:

Long
Byte
Integer
String
Currency
Boolean
Date
Decimal
Single
Double

Examples of objects that you can remove references on:

Recordsets
Forms
Tables
Queries

To name a few... press F2 from the vba window to open the object browser.

Anytime you need to set an object reference with the Set statement, you
should set it to Nothing when you are done with it.

Set objControl = Forms!Formname.Controls!Controlname
Set rstContacts = Currentdb.OpenRecordset(strSQL)

Set objControl = Nothing
Set rstContacts = Nothing

If you try this in your code:

Dim strVar as String
Set strVar = "mystring"

you'll get an error. Therefore, variables cannot be 'Set'

Got it; thank you!

Variables automatically get cleared at the end of their scope, there usually
is no reason to explicitly set them to anything at the end of a procedure.
Object references, however, sometimes stick around to cause problems later,
so we always make sure to un-reference them.

Follow the rule "Close what you open, destroy what you create" when working
with object references.

rstContacts.Close
Set rstContacts = Nothing

Not all objects have a Close method, but every object reference can be set
to Nothing

Very good information to have; thank you!

As always, thank you for taking the time to help me learn and grow. I’m
simply overwhelmed by the support you’ve offered me, and you can be sure that
I put it all to good use.

Best regards,
Robert

 

[Tom Wickerath]

Hi Robert,

Thanks again for your input and the help.

You're certainly welcome. Thanks for letting those of us who try to answer
questions how much our help means.

Debugging; or better, the methods and techniques one uses to debug is
something I’m beginning to understand more clearly; and I’ve found the
Debug.Print and other uses of the Immediate Window very helpful tools.

Sprinking Debug.Print statements throughout a complicated procedure,
especially one that calls other procedures, can certainly help pinpoint where
an error first crops up. You might also want to experiment with setting
Watches in the VBE (Visual Basic Editor), to, for example, suspend code
execution when a variable has a certain value. At that point, you might want
to single step through the code (F8 key), or perhaps use the F5 key to
advance to the next break point (or to the end of the procedure, if there are
no more break points). By the way, have you seen how to set a break point?
This is done by clicking your mouse in the grey margin to the left of the
code, although you cannot set a break point on any Dim statements. You should
see a maroon-colored dot when a break point is set.

As always, I appreciate your help and your patience with me and others like
me; who are interested in learning the “tricks of the trade†even as we
struggle to understand.

Here are some additional resources for you:

1.) Bookmark the http://www.mvps.org/access/ web site. Make sure to read the
Ten Commandments of Access.

2.) Bookmark Access MVP Allen Browne's web site, and read as much as you
can. Allen has lots of wonderful information here:
http://allenbrowne.com/tips.html

Check out the Utilities section for "Database Issue Checker".

3.) Bookmark Access Alumni Jeff Conrad's sites:
http://www.accessmvp.com/JConrad/accessjunkie.html
and
http://accessjunkie.com/default.aspx (for Access 2007)

Grab a copy of his free add-in known as "CSD Tools" from the first site
listed above. Disregard the warning that it does not work with Access 2007;
it actually does work, but has not been fully regression tested with this
newest version of Access.

4.) Perhaps check out a presentation that I gave to the Seattle Access
User's Group during Jan./Feb., 2007, "DAO - Back to Basics":
http://www.seattleaccess.org/downloads.htm

Also check out a Word document that I make available, which I call "Access
Links". This document is filled with lots of hyperlinks and other useful
information. The first four pages includes information that anyone working
with Access should be familiar with. The rest of the document is worth
skimming, to get a general familiarity of it's contents. You can download a
zipped copy from my web site:

http://www.accessmvp.com/TWickerath/


5.) Check out Access MVP Crystal's tutorials, here:

http://www.accessmvp.com/Strive4Peace/Index.htm


Okay, that should keep you busy for a while <smile>.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Robert5833]

Hello Tom,

At the risk of stringing this thread beyond what may be proper etiquette, I
thought your posted reply warranted some feedback on the generous resources
you and others have made available (DymondJack this includes you <smile>). It
isn’t lost on me.

So here goes; comments in line:

Hi Robert,


You're certainly welcome. Thanks for letting those of us who try to answer
questions how much our help means.

It means a lot! Yours and everyone else’s, and the least I can do in return
is to be clear about it! (And hope in the mean time I don’t wear out my

Sprinking Debug.Print statements throughout a complicated procedure,
especially one that calls other procedures, can certainly help pinpoint where
an error first crops up. You might also want to experiment with setting
Watches in the VBE (Visual Basic Editor), to, for example, suspend code
execution when a variable has a certain value. At that point, you might want
to single step through the code (F8 key), or perhaps use the F5 key to
advance to the next break point (or to the end of the procedure, if there are
no more break points). By the way, have you seen how to set a break point?
This is done by clicking your mouse in the grey margin to the left of the
code, although you cannot set a break point on any Dim statements. You should
see a maroon-colored dot when a break point is set.

Thank you, and yes I do set break points and that has proven invaluable in
troubleshooting. I haven’t used the Watch function yet, but I’m thinking that
will be my very next progression in learning and applying good technique.

Here are some additional resources for you:

1.) Bookmark the http://www.mvps.org/access/ web site. Make sure to read the
Ten Commandments of Access.

Did that; which is when I learned among other things that lookup fields in
tables weren’t the best idea (possibly the worst, depending on who you’re

2.) Bookmark Access MVP Allen Browne's web site, and read as much as you
can. Allen has lots of wonderful information here:
http://allenbrowne.com/tips.html

Got that; refer to his site frequently.

Check out the Utilities section for "Database Issue Checker".

Downloaded and used several times (I actually got a pretty clean report!) I
have to admit that I hadn’t EVER compiled my project db until just a week
ago, and to my astonishment I didn’t have that many compile errors? And I
fully expected a puff of smoke followed by a complete meltdown of my hard
drive…

3.) Bookmark Access Alumni Jeff Conrad's sites:
http://www.accessmvp.com/JConrad/accessjunkie.html
and
http://accessjunkie.com/default.aspx (for Access 2007)

Got it; been to his site several times (there’s so much information out
there if you know where to look!). Thanks!

Grab a copy of his free add-in known as "CSD Tools" from the first site
listed above. Disregard the warning that it does not work with Access 2007;
it actually does work, but has not been fully regression tested with this
newest version of Access.

Got the download; this looks like an awesome tool! I can’t wait to use it.
I’ve been keeping track of some object definitions and such, long hand in a
table. Ugh!

4.) Perhaps check out a presentation that I gave to the Seattle Access
User's Group during Jan./Feb., 2007, "DAO - Back to Basics":
http://www.seattleaccess.org/downloads.htm

Will do!

Also check out a Word document that I make available, which I call "Access
Links". This document is filled with lots of hyperlinks and other useful
information. The first four pages includes information that anyone working
with Access should be familiar with. The rest of the document is worth
skimming, to get a general familiarity of it's contents. You can download a
zipped copy from my web site:

http://www.accessmvp.com/TWickerath/

Got it already; and have used it and the resources and links several times!

5.) Check out Access MVP Crystal's tutorials, here:

http://www.accessmvp.com/Strive4Peace/Index.htm

Will do! Thanks!

Okay, that should keep you busy for a while <smile>.

Yep; it will, but it’s all good!

Thanks again for your help and your consideration!

Best regards,
Robert

 

[dymondjack]

If you haven't come across it yet check out www.mztools.com as well. It's
not necessarily a learning resource, but I would be lost without it.

www.cpearson.com is another excellent site for detailed and complete
information on VBA (this site is written for excel users, but the majority of
it can be applied to Access). I've picked up tons of stuff from this site
where I've had trouble finding out about certain subjects elsewhere.
Everything form what a variable is to how to programmatically insert lines
into the VBA code window via code from another project. I would definately
say its worth a quick peruse, and probably a bookmark.

This has turned into quite the thread from a simple insert into... <g>


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Robert5833]

Hello Jack,

Thank you for the information; and no I hadn't come accross that site
before. Good stuff posted there; and the first thing that caught my eye was
"Hints, tips, and guidelines for using the USENET newsgroups. Who knew?
<sheepish grin>

Anyway, thanks again for the link and all of your help!

Best regards,
Robert

 

[Tom Wickerath]

Hi Robert,

At the risk of stringing this thread beyond what may be proper etiquette,...

There is no limit that I'm aware of. I've seen threads go 60, 80, even 150
or more replies, although that tends to happen more in the CDMA
(Comp.Databases-MsAccess) more so than this group
(MPA=Microsoft.Public.Access).

Did that; which is when I learned among other things that lookup fields in
tables weren’t the best idea (possibly the worst, depending on who you’re
talking to… <smile>).

Personally, I consider lookup fields defined at the Table or Query level as
absolute evil. I regularly remove these when asked to work on databases
created by other people. However, be careful about not removing lookup fields
that are based on Value Lists, unless you have an easy way of capturing this
data (especially Value Lists with 2 or more columns, where you are storing
the bound column [usually the first column]--you don't want to leave yourself
without a way to know what a value pertained to). Of course, you can always
restore from the latest back-up that you dutifully maintain.

I have to admit that I hadn’t EVER compiled my project db until just a week
ago, and to my astonishment I didn’t have that many compile errors? And I
fully expected a puff of smoke followed by a complete meltdown of my hard
drive…

Having Option Explicit as the second line of code at the top of every module
allows one to help catch potential compile errors before they can build up,
since you won't be able to use undeclared variables. Here's another trick
that I recommend doing: On the General Tab of the Visual Basic Editor
options, remove the two default selections for Background Compile and Compile
On Demand. The Compile On Demand option allows Access to do a partial
compilation only--it compiles on demand all of the code in a given module, if
any code in that module is executed. However, what this also means is that
you might have several form or report class modules, or other stand-alone
modules, that have compile errors waiting to bite you at any moment. Okay, as
long as you remember to do a Debug | Compile {ProjectName} you will catch any
of these hidden compile errors that are lurking. But what if you forget to do
a Debug | Compile *before* distributing your work to a co-worker, customer,
boss, etc? At that point, the only thing that you can be assured of is that
any code present in modules where you specifically caused some code to be
executed will be free of compile errors. You won't have the same assurance
concerning any modules with code that was not "exercised" in this manner,
unless you remove the check mark from the Compile On Demand option, which is
set by default for all new installations of Office (at least Office 2003 and
earlier--I'm not sure about Office 2007).

With Compile On Demand disabled (unchecked) *ALL* code must be compileable
before *ANY* code can be run. To me, that's just good insurance. Also, there
has been past talk about the Compile On Demand feature causing code to seem
to run that you had already deleted. This is a quote that I've personally
heard from Michael Kaplan, who is a former member of the Access Development
Team at Microsoft.

Sounds to me like you have achieved the honored status of being a truly
bonified "Access Junkie"!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Robert5833]

Good-day Tom,

Thank you for the reply and your continued support and patience as slowly
but surely the many gaps in my knowledge and skill with Access and VBA are
filled in.

In the case of my original post, what I thought was simple question turned
out not to be so simple; but as I’ve mentioned before, oftentimes I don’t
know what I don’t know, and such was the case here.

Also, it’s worth mentioning that I am cognizant of the time questions such
as mine demand from you and others who respond. That being said, I do a lot
of homework and research on my own both before I ask a question, and after I
have received a reply. I feel it’s important that we who benefit from the
support of this discussion group are disciplined and diligent in our own
efforts so as not to monopolize or unnecessarily burden the resources offered
here.

Moving on then to your reply; please find my comments inline below:

There is no limit that I'm aware of. I've seen threads go 60, 80, even 150
or more replies, although that tends to happen more in the CDMA
(Comp.Databases-MsAccess) more so than this group
(MPA=Microsoft.Public.Access).

Good to know! (And while I’m not known for being brief…I’ll try at least to
be concise!)

Personally, I consider lookup fields defined at the Table or Query level as
absolute evil. I regularly remove these when asked to work on databases
created by other people. However, be careful about not removing lookup fields
that are based on Value Lists, unless you have an easy way of capturing this
data (especially Value Lists with 2 or more columns, where you are storing
the bound column [usually the first column]--you don't want to leave yourself
without a way to know what a value pertained to). Of course, you can always
restore from the latest back-up that you dutifully maintain.

I’ve begun the painful, but necessary process of removing nearly all lookup
fields in my tables. (I do have some field and value lists that will remain,
but very few.) As I look back now on some of the problems I’ve encountered
(sorting and appending data among them), I’m convinced that most were a
direct result of my table constructs. A lesson learned—albeit the hard way,
and not without pain.

And I absolutely do keep backup copies; in fact I create a backup anytime I
make quantifiable changes to procedures and queries.

Having Option Explicit as the second line of code at the top of every module
allows one to help catch potential compile errors before they can build up,
since you won't be able to use undeclared variables. Here's another trick
that I recommend doing: On the General Tab of the Visual Basic Editor
options, remove the two default selections for Background Compile and Compile
On Demand. The Compile On Demand option allows Access to do a partial
compilation only--it compiles on demand all of the code in a given module, if
any code in that module is executed. However, what this also means is that
you might have several form or report class modules, or other stand-alone
modules, that have compile errors waiting to bite you at any moment. Okay, as
long as you remember to do a Debug | Compile {ProjectName} you will catch any
of these hidden compile errors that are lurking. But what if you forget to do
a Debug | Compile *before* distributing your work to a co-worker, customer,
boss, etc? At that point, the only thing that you can be assured of is that
any code present in modules where you specifically caused some code to be
executed will be free of compile errors. You won't have the same assurance
concerning any modules with code that was not "exercised" in this manner,
unless you remove the check mark from the Compile On Demand option, which is
set by default for all new installations of Office (at least Office 2003 and
earlier--I'm not sure about Office 2007).

I’ve got all of those settings configured now, and I compile frequently now
and have since converted a copy of my db project into an ACCDE version just
to see what the process would be.

With Compile On Demand disabled (unchecked) *ALL* code must be compileable
before *ANY* code can be run. To me, that's just good insurance. Also, there
has been past talk about the Compile On Demand feature causing code to seem
to run that you had already deleted. This is a quote that I've personally
heard from Michael Kaplan, who is a former member of the Access Development
Team at Microsoft.

I did at one time have Compile On Demand as a default setting but have since
changed it. Also, after researching the effects of that setting and the
phenomena or potential for deprecated and/or significant re-writes of code to
*remain in memory* I’m going to do a de-compile and re-compile just in case.

Sounds to me like you have achieved the honored status of being a truly
bonified "Access Junkie"!

If we take the term “junkie†to mean a person addicted to a thing and never
reaching a point of satiation, then I certainly fit that persona. Add to that
an insatiable want for knowledge, voracities for research and study, coupled
with an interminable curiosity; then yes, I am indeed an Access Junkie!

My first experience with Access (Access 97) was in 1998, and I knew the very
first time I peered into the developers window of the application that the
possibilities were endless. In fact I had the revelation then that this
(Access) could be the panacea for so much of the work that I do, and indeed
the very reason I owned a computer—to make it do work, and to help me do my
work.

Until recently though I’ve only been a casual user of Access (and if
measured by knowledge; I still am); never having delved too deeply into code
but that’s changing now. The first relational database I created circa 1998
wasn’t much more than an elaborate flat file, and while I have created quite
a few databases over the years, most of them were minor considerations with
respect to my current project.

As always; I thank you for sharing your knowledge, your time, and your
perspective with me and with this community!

Best regards,

Robert

Hi Robert,

At the risk of stringing this thread beyond what may be proper etiquette,...

There is no limit that I'm aware of. I've seen threads go 60, 80, even 150
or more replies, although that tends to happen more in the CDMA
(Comp.Databases-MsAccess) more so than this group
(MPA=Microsoft.Public.Access).

Did that; which is when I learned among other things that lookup fields in
tables weren’t the best idea (possibly the worst, depending on who you’re
talking to… <smile>).

Personally, I consider lookup fields defined at the Table or Query level as
absolute evil. I regularly remove these when asked to work on databases
created by other people. However, be careful about not removing lookup fields
that are based on Value Lists, unless you have an easy way of capturing this
data (especially Value Lists with 2 or more columns, where you are storing
the bound column [usually the first column]--you don't want to leave yourself
without a way to know what a value pertained to). Of course, you can always
restore from the latest back-up that you dutifully maintain.

I have to admit that I hadn’t EVER compiled my project db until just a week
ago, and to my astonishment I didn’t have that many compile errors? And I
fully expected a puff of smoke followed by a complete meltdown of my hard
drive…

Having Option Explicit as the second line of code at the top of every module
allows one to help catch potential compile errors before they can build up,
since you won't be able to use undeclared variables. Here's another trick
that I recommend doing: On the General Tab of the Visual Basic Editor
options, remove the two default selections for Background Compile and Compile
On Demand. The Compile On Demand option allows Access to do a partial
compilation only--it compiles on demand all of the code in a given module, if
any code in that module is executed. However, what this also means is that
you might have several form or report class modules, or other stand-alone
modules, that have compile errors waiting to bite you at any moment. Okay, as
long as you remember to do a Debug | Compile {ProjectName} you will catch any
of these hidden compile errors that are lurking. But what if you forget to do
a Debug | Compile *before* distributing your work to a co-worker, customer,
boss, etc? At that point, the only thing that you can be assured of is that
any code present in modules where you specifically caused some code to be
executed will be free of compile errors. You won't have the same assurance
concerning any modules with code that was not "exercised" in this manner,
unless you remove the check mark from the Compile On Demand option, which is
set by default for all new installations of Office (at least Office 2003 and
earlier--I'm not sure about Office 2007).

With Compile On Demand disabled (unchecked) *ALL* code must be compileable
before *ANY* code can be run. To me, that's just good insurance. Also, there
has been past talk about the Compile On Demand feature causing code to seem
to run that you had already deleted. This is a quote that I've personally
heard from Michael Kaplan, who is a former member of the Access Development
Team at Microsoft.

Sounds to me like you have achieved the honored status of being a truly
bonified "Access Junkie"!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hello Tom,

At the risk of stringing this thread beyond what may be proper etiquette, I
thought your posted reply warranted some feedback on the generous resources
you and others have made available (DymondJack this includes you <smile>). It
isn’t lost on me.

So here goes; comments in line:




It means a lot! Yours and everyone else’s, and the least I can do in return
is to be clear about it! (And hope in the mean time I don’t wear out my


Thank you, and yes I do set break points and that has proven invaluable in
troubleshooting. I haven’t used the Watch function yet, but I’m thinking that
will be my very next progression in learning and applying good technique.


Did that; which is when I learned among other things that lookup fields in
tables weren’t the best idea (possibly the worst, depending on who you’re


Got that; refer to his site frequently.


Downloaded and used several times (I actually got a pretty clean report!) I
have to admit that I hadn’t EVER compiled my project db until just a week
ago, and to my astonishment I didn’t have that many compile errors? And I
fully expected a puff of smoke followed by a complete meltdown of my hard
drive…


Got it; been to his site several times (there’s so much information out
there if you know where to look!). Thanks!


Got the download; this looks like an awesome tool! I can’t wait to use it.
I’ve been keeping track of some object definitions and such, long hand in a
table. Ugh!


Will do!


Got it already; and have used it and the resources and links several times!


Will do! Thanks!


Yep; it will, but it’s all good!

Thanks again for your help and your consideration!

Best regards,
Robert