Re: Simple Insert Into...

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:

Tina has some excellent points here as well. I thought about getting into it
with my original post but I didn't know if it would be a little too confusing
of not.

One last thing:

It might make it a little bit easier for you to decide on functions or subs
if you know that a lot of programmers will never really write a sub. In
fact, in many programming languages, theres no such thing as subs, its all
functions.

Personally, the only subs in my db's are the ones that Access supplies (the
ones in form modules).

Consider this: even if you want to tell access to do something (subs), you
might want to know how it went (sub... turn yourself into a function and tell
me how it went). This is especially true in key functions (the one's where
you want to make absolutely positive everything went right before moving on).
Set your datatype of the function return to a boolean or Long Integer, and
you can actually moniter how the function performs from the 'other side'.

Consider a Login function... one would think it might be a sub, but really,
it would be nice if you could know if it worked or not.

If Login(Username, Pass) = False Then
MsgBox "Login Failed"
Goto Exit_Line
End If

Anyway... if you were to never write your own sub, and stick to strictly
functions, you would do fine (I even recommending doing just that, even if it
doesn't return a value... it won't hurt and may very well help down the road).

glad to help!

--
Jack Leach
www.tristatemachine.com

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


"tina" wrote:

one additional note: functions are a bit more versatile than subs - you can
call them directly from the property line in a form (or report), as

=MyFunctionName()

with or without parameters, as needed. and you can call them from custom
toolbars and custom menus, same syntax as above, from within queries, from
macros using the RunCode action - in short, from anywhere that you can call
a built-in function, such as Date(). AFAIK, subs can only be called from a
form's event property line as [Event Procedure] or from within another sub
or a function in a module.

also, you can write a function without declaring a return value data type
(no "string" after the closing parens), if you don't need to use it. so you
might ask "why write a function when you don't need a return value, instead
of just writing a sub?" i do that when i need to run a procedure without
having to call it from within VBA, but rather from somewhere else in my
application.

hth


"Robert5833" <Robert5833@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:85835447-CA14-45F1-988F-7EA61892E2AC@xxxxxxxxxxxxxxxx
Hello Jack,

This is an awesome explanation! I've always been one of those folks who
needed real-world examples to evaluate situations and those you provided
have
really helped me understand. It's frustrating not to know these things,
but
thanks to you I'm a little smarter and a lot less frustrated now. <big
smile>

Thanks again for your time and the help!

Best regards,
Robert


"dymondjack" wrote:

I know there are differences between procedures and functions; I'm
.