Re: Simple Insert Into...

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.




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!

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


"Robert5833" wrote:

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)


"dymondjack" wrote:

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 :-p

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


"Robert5833" wrote:

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:
.

Loading