Re: Simple Insert Into...

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
particularly interested in what is within the (), or after (), and
why?

The stuff inside the () ahave the same meaning for either a sub or
function.
It is a way to pass information to the procedure so that it may do what
it's
supposed to. Arguments (as they are called), are like variables in the
sense
that the have a datatype, and are more or less declared at the time the
procedure is called.

So, a function like WinUserName() requires no arguments (presumably
because
it will get its information from somewhere globally, rather than having
you
give it infromation it needs). But a function like
RoundCurr(OriginalValue
As Currency) is saying that it needs an initial value passed to it so it
can
round it for you (this is not coming from somewhere global, rather you
need
to supply it). In this case, OriginalValue is declared as a variable
and can
be used anywhere within that function.

As far as the statements on the right side of the (), they are only
applicable to Functions, not Subprocedures (Subs)

First, you should understand the difference between Functions and Subs.
The
difference is that Functions return a value, and Subs do not. What that
means? Consider telling vba in human terms that you need something.
You're
going to say one of two things:

Do this (Subs)
or
Whats This? (Functions)

The functions return a value... presumably what you ask of it. So, to
get
back to the statement on the right side of the (), that datatype is the
datatype that the function is going to give its answer in.

Public Function WinUserName() As String
This returns the Windows User Name (ideally anyway), in the String
datatype.

Public Function RoundCurr(...) As Currency
This returns the rounded value, in the datatype of currency.

Private Sub cmdLoadNew_Click()
This returns no value, and will result in an error if you try and put a
datatype after the ()

The parenthases are added to the procedure declaration even when there
is
nothing in them. This tells vba that it is a procedure of some sort.


So if we take all that stuff and throw it into one example, you might
wind
up with something like below.

Private Sub Form_Open()

'Calculate the value of Me.CalcValue from Me.OrigValue
Me.CalcValue = CalculateVal(Me.OrigValue)

End Sub



Private Function CalculateVal(aValue As Long) As Double
CalculateVal = aValue * 3.14159265
End Function



So, when your form opens, you have a Sub (do this), and inside that sub,
you
ask a question (whats this), and the function CalculateVal returns your
answer.


Private Function CalculateVal(aValue As Long) As Double
This line says 'we need to be given a value to turn this into what you
want,
and it needs to be of the Long datatype'

Me.CalcValue = CalculateVal(Me.OrigValue)
This line says 'Get this information, and the value you need to do it is
in
the control OrigValue'

CalculateVal = aValue * 3.14159265
This line tells the function that the answer to your question (the value
of
the function) is whatever value is passed through the argument, times
Pi. At
this point, the value of the argument aValue is whetever the value of
the
control was when the function got called.

hth

--
Jack Leach
www.tristatemachine.com

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


"Robert5833" wrote:

Good day all,

"Back to the well" I come:

Disclaimer; I don't know what I don't know about programming.

I am trying to use the procedure below to append a group of records to
a
table. I'm using a form to sort one table's records on a unique value,
and
display them in the form and I want that result (is it a recordset?)
to
append to a different table with relevant fields. Call it an order
fill list;
where I've selected all of the items I want, and then append them to
an Order.

Throw the book at me; I'm open to criticism; as one has to be in the
learning process.<humble smile>

The code below does append to the target table; but only one record at
a
time and it does compile without any snags:

Private Sub cmdLoadNew_Click()
On Error GoTo Error_Handler
Dim dbs As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset
Set dbs = CurrentDb
With rst
strSQL = "INSERT INTO tblTracking, " _
& "([fldArticleID], " _
& "[fldMake], [fldModelNumber], [fldPartNumber]), " _
& "VALUES, " _
& "(' " & fldArticleID & " ', ' " & fldMake & " ', ' " &
fldModelNumber
& "', " _
& " ' " & fldAircraftListID & " ')"
End With

CurrentDb.Execute strSQL, dbFailOnError

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume
End Sub

Note: single/double quotes exaggerated for clarity.
Note: the table that data is being appended FROM is
tblArticleSelect...

I do have error handling but interestingly, if I include the
"dbFailOnError"
with the Db.Execute strSQL statement it throws a flag; "Error Number
3464,
Data type mismatch in criteria expression" (which I am sure that has
something to do with data types) but otherwise, without that
expression all
of the data is inserted anyway?

I've read every post on this subject I could find, and have used the
Access
Help files, and my trusty "Access 2007 VBA Programmer's Reference"
guide, but
can't find anything specific to my problem.

Also, I've had problems with calling global and public procedures as
well;
with the errors; "Option not allowed or, Variable not defined." I
would
appreciate any input from the discussion group on that problem:

Example
Private Sub cmdLoadNew_Click()
Vs.
Public Function WinUserName() As String
Vs.
Public Function RoundCurr(OriginalValue As Currency, Optional
NumberOfDecimals As Integer) As Currency

I know there are differences between procedures and functions; I'm
particularly interested in what is within the (), or after (), and
why?

Any help or suggestions would be greatly appreciated.

Best regards,

RL



.

Relevant Pages

  • Re: RichTextBox and multi-level Undo
    ... ' The Undo and Redo descriptions are stored on the same stack. ... Public Sub WatchControl ... Private Sub Class_Initialize ... Public Function CanPaste() As Boolean ...
    (microsoft.public.vb.controls)
  • RE: jpgs not showing on forms
    ... Rather than embed the pictures in the database store the paths to the JPEG ... Private Sub cmdAddImage_Click ... Dim strAdditionalTypes As String, strFileList As String ... Private Sub cmdDeleteImage_Click ...
    (microsoft.public.access.gettingstarted)
  • Re: Vb6 large text file read / arrays performance issues
    ... Private Sub Class_Initialize ... Public Function ReadDelineatedLine() As String ... ' --- Was this the last Field of the Last Buffer ...
    (microsoft.public.vb.general.discussion)
  • VB modal forms so what is best practice ?
    ... Private Sub AmendName() ... ' SetName-called by the client to initialise the name for editing ... Public function IsAccept() as Boolean ...
    (microsoft.public.vb.general.discussion)
  • Re: Controls disabled or Readonly
    ... We "disable" databound controls when the position ... Public Sub New ... Then on your form you would handle the PositionChanged event of the currency ... Private Sub Form1_Load(ByVal sender As System.Object, ...
    (microsoft.public.dotnet.framework.windowsforms)
Loading