RE: Simple Insert Into...

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: 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)
  • RE: Simple Insert Into...
    ... The stuff inside the ahave the same meaning for either a sub or function. ... that the have a datatype, and are more or less declared at the time the ... As Currency) is saying that it needs an initial value passed to it so it can ... Private Sub Form_Open ...
    (microsoft.public.access.modulesdaovba)
  • Re: Simple Insert Into...
    ... if you know that a lot of programmers will never really write a sub. ... Public Function WinUserName() As String ... in the datatype of currency. ... Private Sub Form_Open ...
    (microsoft.public.access.modulesdaovba)
  • Re: adding record to sql db
    ... What is the datatype of the field in SqlServer? ... type is specified as Bit but by the Exception message, ... > im trying to add a record to an sql db on ms sql server 2000, ... > Public Sub Register_User ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Simple Insert Into...
    ... Public Function WinUserName() As String ... in the datatype of currency. ... Private Sub cmdLoadNew_Click ... Private Sub Form_Open ...
    (microsoft.public.access.modulesdaovba)
Loading