Simple Insert Into...

[Robert5833]

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

 

[Tom Wickerath]

Hi Robert,

Although you declared a recordset variable, your code was not using it,
other than the With rst .... End With part, which is superfluous. The SQL
statement (strSQL) included some extra commas that were not correct. Also,
the use of the single quotes around each value are valid only for text data
type. Are any of these fields numeric? I replaced the extra call to
CurrentDB (ie. CurrentDB.Execute strSQL) with the variable you had already
declared and set correctly: dbs. I also set this variable to Nothing in the
Exit_Procedure section. Here is a revised version of your code that runs with
the optional dbFailOnError flag in A2003, without producing any errors.
However, this example currently only inserts one record at a time:

Option Compare Database
Option Explicit

Private Sub cmdLoadNew_Click()
On Error GoTo Error_Handler

On Error GoTo Error_Handler
Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb

strSQL = "INSERT INTO tblTracking " _
& "([fldArticleID], " _
& "[fldMake], [fldModelNumber], [fldPartNumber]) " _
& "VALUES " _
& "('" & fldArticleID & "', '" & fldMake & "', '" & fldModelNumber & "',
" _
& "'" & fldAircraftListID & "')"

'Debug.Print strSQL

dbs.Execute strSQL, dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Sub
Error_Handler:
'I didn't have the DisplayUnexpectedError procedure available.
'DisplayUnexpectedError Err.Number, Err.Description
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdLoadNew_Click..."
Resume Exit_Procedure
Resume
End Sub


In order to insert more than one record at a time, you will likely need to
replace the Values clause with a SELECT statement. Something like this:

INSERT INTO [some table] (<list of columns>) SELECT <column names> FROM
[another table] WHERE ...

instead of your current form:
INSERT INTO [some table] (<list of column names>) VALUES (<list of values>);


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

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

 

[Robert5833]

Hi Tom,

Thank you for the reply.

Your points are all valid; and well received. I continue to be amazed at the
enthusiasm this group has for helping others.

Obliviously, I don’t know enough about variables and declarations or their
implications; but I am trying to understand. If you could expound on your
point about the With rst and End With statements/declarations as being
superfluous I will owe you a debt of gratitude.

Yes, some of the fields are Numeric and I’ll review them against the code
you offered and apply the appropriate combinations as best I can. Usually
I’ve found that mismatch errors halt the code and I’m able to resolve them;
given persistence and time. But in this case the code seems to fully execute
(in spite of Error Handling) and posts the data. I wasn’t prepared for that.
(Simple data type mismatch…shouldn’t be a big deal. <humble smile>)

I will try out the code you provided and report back.

Incidentally, my hope here is that the code I end up with will append
(insert) many records into a table, not just one. I have a form with a
dropdown list that fires the control’s After Update event in VBA running a
filter with the returned result displayed on the form.

I’m hoping to append the entire filtered recordset(?) to the table
referenced in the procedure.

I don’t know the syntax for doing that.

If my problem has an answer, I know that it can be found here. Any
suggestions you may have are greatly appreciated.

Thank you again for your time and consideration in helping me with this issue.

Best regards,
Robert

Hi Robert,

Although you declared a recordset variable, your code was not using it,
other than the With rst .... End With part, which is superfluous. The SQL
statement (strSQL) included some extra commas that were not correct. Also,
the use of the single quotes around each value are valid only for text data
type. Are any of these fields numeric? I replaced the extra call to
CurrentDB (ie. CurrentDB.Execute strSQL) with the variable you had already
declared and set correctly: dbs. I also set this variable to Nothing in the
Exit_Procedure section. Here is a revised version of your code that runs with
the optional dbFailOnError flag in A2003, without producing any errors.
However, this example currently only inserts one record at a time:

Option Compare Database
Option Explicit

Private Sub cmdLoadNew_Click()
On Error GoTo Error_Handler

On Error GoTo Error_Handler
Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb

strSQL = "INSERT INTO tblTracking " _
& "([fldArticleID], " _
& "[fldMake], [fldModelNumber], [fldPartNumber]) " _
& "VALUES " _
& "('" & fldArticleID & "', '" & fldMake & "', '" & fldModelNumber & "',
" _
& "'" & fldAircraftListID & "')"

'Debug.Print strSQL

dbs.Execute strSQL, dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Sub
Error_Handler:
'I didn't have the DisplayUnexpectedError procedure available.
'DisplayUnexpectedError Err.Number, Err.Description
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdLoadNew_Click..."
Resume Exit_Procedure
Resume
End Sub


In order to insert more than one record at a time, you will likely need to
replace the Values clause with a SELECT statement. Something like this:

INSERT INTO [some table] (<list of columns>) SELECT <column names> FROM
[another table] WHERE ...

instead of your current form:
INSERT INTO [some table] (<list of column names>) VALUES (<list of values>);


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

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

 

[dymondjack]

As far as the With rst/ End With, the only purpose they have is when
performing actions on/with the rst itself (anything that you would do
rst.<whatever>). In your code snippet, you were using them to encase the
building of your sql statement, which is not something that you need to type
rst. before.

An example of where you would use the With rst would be something like
manually adding a record to the rst:

With rst
.AddNew
.Fields("fieldname") = value
.Fields("fieldname2") = value
.Update
End With

The With statement gets rid of the need to type rst.<whatever> for each
line, and also allows you to perform multiple operations on a recordset at
once (such as adding a record with multiple field values).

So, as your strSQL is not constructed like so:

rst.strSQL = "whatever"

the With statement is not required.


hth

--
Jack Leach
www.tristatemachine.com

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

Hi Tom,

Thank you for the reply.

Your points are all valid; and well received. I continue to be amazed at the
enthusiasm this group has for helping others.

Obliviously, I don’t know enough about variables and declarations or their
implications; but I am trying to understand. If you could expound on your
point about the With rst and End With statements/declarations as being
superfluous I will owe you a debt of gratitude.

Yes, some of the fields are Numeric and I’ll review them against the code
you offered and apply the appropriate combinations as best I can. Usually
I’ve found that mismatch errors halt the code and I’m able to resolve them;
given persistence and time. But in this case the code seems to fully execute
(in spite of Error Handling) and posts the data. I wasn’t prepared for that.
(Simple data type mismatch…shouldn’t be a big deal. <humble smile>)

I will try out the code you provided and report back.

Incidentally, my hope here is that the code I end up with will append
(insert) many records into a table, not just one. I have a form with a
dropdown list that fires the control’s After Update event in VBA running a
filter with the returned result displayed on the form.

I’m hoping to append the entire filtered recordset(?) to the table
referenced in the procedure.

I don’t know the syntax for doing that.

If my problem has an answer, I know that it can be found here. Any
suggestions you may have are greatly appreciated.

Thank you again for your time and consideration in helping me with this issue.

Best regards,
Robert

Hi Robert,

Although you declared a recordset variable, your code was not using it,
other than the With rst .... End With part, which is superfluous. The SQL
statement (strSQL) included some extra commas that were not correct. Also,
the use of the single quotes around each value are valid only for text data
type. Are any of these fields numeric? I replaced the extra call to
CurrentDB (ie. CurrentDB.Execute strSQL) with the variable you had already
declared and set correctly: dbs. I also set this variable to Nothing in the
Exit_Procedure section. Here is a revised version of your code that runs with
the optional dbFailOnError flag in A2003, without producing any errors.
However, this example currently only inserts one record at a time:

Option Compare Database
Option Explicit

Private Sub cmdLoadNew_Click()
On Error GoTo Error_Handler

On Error GoTo Error_Handler
Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb

strSQL = "INSERT INTO tblTracking " _
& "([fldArticleID], " _
& "[fldMake], [fldModelNumber], [fldPartNumber]) " _
& "VALUES " _
& "('" & fldArticleID & "', '" & fldMake & "', '" & fldModelNumber & "',
" _
& "'" & fldAircraftListID & "')"

'Debug.Print strSQL

dbs.Execute strSQL, dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Sub
Error_Handler:
'I didn't have the DisplayUnexpectedError procedure available.
'DisplayUnexpectedError Err.Number, Err.Description
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdLoadNew_Click..."
Resume Exit_Procedure
Resume
End Sub


In order to insert more than one record at a time, you will likely need to
replace the Values clause with a SELECT statement. Something like this:

INSERT INTO [some table] (<list of columns>) SELECT <column names> FROM
[another table] WHERE ...

instead of your current form:
INSERT INTO [some table] (<list of column names>) VALUES (<list of values>);


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

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

 

[Robert5833]

Hello Jack,

Thank you for that explanation; I think I understand now. At this early
stage of my learning I often do a lot of “trial and error†procedure building
and obviously that has mixed results. I do read lots of posts, reviewing
examples and discussion, as well as reading whatever literature I can find;
but there’s nothing like failed code to bear out the truth (read; legal
syntax). <smile>

Little by little I’m gaining knowledge and confidence.

Thanks again for the reply.

Best regards,

Robert

As far as the With rst/ End With, the only purpose they have is when
performing actions on/with the rst itself (anything that you would do
rst.<whatever>). In your code snippet, you were using them to encase the
building of your sql statement, which is not something that you need to type
rst. before.

An example of where you would use the With rst would be something like
manually adding a record to the rst:

With rst
.AddNew
.Fields("fieldname") = value
.Fields("fieldname2") = value
.Update
End With

The With statement gets rid of the need to type rst.<whatever> for each
line, and also allows you to perform multiple operations on a recordset at
once (such as adding a record with multiple field values).

So, as your strSQL is not constructed like so:

rst.strSQL = "whatever"

the With statement is not required.


hth

--
Jack Leach
www.tristatemachine.com

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

Hi Tom,

Thank you for the reply.

Your points are all valid; and well received. I continue to be amazed at the
enthusiasm this group has for helping others.

Obliviously, I don’t know enough about variables and declarations or their
implications; but I am trying to understand. If you could expound on your
point about the With rst and End With statements/declarations as being
superfluous I will owe you a debt of gratitude.

Yes, some of the fields are Numeric and I’ll review them against the code
you offered and apply the appropriate combinations as best I can. Usually
I’ve found that mismatch errors halt the code and I’m able to resolve them;
given persistence and time. But in this case the code seems to fully execute
(in spite of Error Handling) and posts the data. I wasn’t prepared for that.
(Simple data type mismatch…shouldn’t be a big deal. <humble smile>)

I will try out the code you provided and report back.

Incidentally, my hope here is that the code I end up with will append
(insert) many records into a table, not just one. I have a form with a
dropdown list that fires the control’s After Update event in VBA running a
filter with the returned result displayed on the form.

I’m hoping to append the entire filtered recordset(?) to the table
referenced in the procedure.

I don’t know the syntax for doing that.

If my problem has an answer, I know that it can be found here. Any
suggestions you may have are greatly appreciated.

Thank you again for your time and consideration in helping me with this issue.

Best regards,
Robert

Hi Robert,

Although you declared a recordset variable, your code was not using it,
other than the With rst .... End With part, which is superfluous. The SQL
statement (strSQL) included some extra commas that were not correct. Also,
the use of the single quotes around each value are valid only for text data
type. Are any of these fields numeric? I replaced the extra call to
CurrentDB (ie. CurrentDB.Execute strSQL) with the variable you had already
declared and set correctly: dbs. I also set this variable to Nothing in the
Exit_Procedure section. Here is a revised version of your code that runs with
the optional dbFailOnError flag in A2003, without producing any errors.
However, this example currently only inserts one record at a time:

Option Compare Database
Option Explicit

Private Sub cmdLoadNew_Click()
On Error GoTo Error_Handler

On Error GoTo Error_Handler
Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb

strSQL = "INSERT INTO tblTracking " _
& "([fldArticleID], " _
& "[fldMake], [fldModelNumber], [fldPartNumber]) " _
& "VALUES " _
& "('" & fldArticleID & "', '" & fldMake & "', '" & fldModelNumber & "',
" _
& "'" & fldAircraftListID & "')"

'Debug.Print strSQL

dbs.Execute strSQL, dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Sub
Error_Handler:
'I didn't have the DisplayUnexpectedError procedure available.
'DisplayUnexpectedError Err.Number, Err.Description
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdLoadNew_Click..."
Resume Exit_Procedure
Resume
End Sub


In order to insert more than one record at a time, you will likely need to
replace the Values clause with a SELECT statement. Something like this:

INSERT INTO [some table] (<list of columns>) SELECT <column names> FROM
[another table] WHERE ...

instead of your current form:
INSERT INTO [some table] (<list of column names>) VALUES (<list of values>);


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

:

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

 

[Robert5833]

Hi Tom,

I’ve cleaned up the procedure as per your recommendations, and all of the
errors are gone now! <big smile> <lots of cheering> Thanks so much for your
help!

Also, as per your suggestion, I’m in the process of modifying the INSERT
INTO to replace the VALUES clause to SELECT and a WHERE clauses. I’ve had
problems before when trying to establish a valid WHERE in the SQL but I’ll
give my best shot and report back on the results.

Thanks again for the reply!

Best regards,

Robert

Hi Robert,

Although you declared a recordset variable, your code was not using it,
other than the With rst .... End With part, which is superfluous. The SQL
statement (strSQL) included some extra commas that were not correct. Also,
the use of the single quotes around each value are valid only for text data
type. Are any of these fields numeric? I replaced the extra call to
CurrentDB (ie. CurrentDB.Execute strSQL) with the variable you had already
declared and set correctly: dbs. I also set this variable to Nothing in the
Exit_Procedure section. Here is a revised version of your code that runs with
the optional dbFailOnError flag in A2003, without producing any errors.
However, this example currently only inserts one record at a time:

Option Compare Database
Option Explicit

Private Sub cmdLoadNew_Click()
On Error GoTo Error_Handler

On Error GoTo Error_Handler
Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb

strSQL = "INSERT INTO tblTracking " _
& "([fldArticleID], " _
& "[fldMake], [fldModelNumber], [fldPartNumber]) " _
& "VALUES " _
& "('" & fldArticleID & "', '" & fldMake & "', '" & fldModelNumber & "',
" _
& "'" & fldAircraftListID & "')"

'Debug.Print strSQL

dbs.Execute strSQL, dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Sub
Error_Handler:
'I didn't have the DisplayUnexpectedError procedure available.
'DisplayUnexpectedError Err.Number, Err.Description
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdLoadNew_Click..."
Resume Exit_Procedure
Resume
End Sub


In order to insert more than one record at a time, you will likely need to
replace the Values clause with a SELECT statement. Something like this:

INSERT INTO [some table] (<list of columns>) SELECT <column names> FROM
[another table] WHERE ...

instead of your current form:
INSERT INTO [some table] (<list of column names>) VALUES (<list of values>);


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

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

 

[dymondjack]

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

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

 

[Robert5833]

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

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

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

 

[Tom Wickerath]

Hi Jack,

I agree....very good answer.
Thanks for helping out with this thread. (I did not reply last night to
Robert's post made at approx. 8:00pm my time, because I've had an awful cold
and was already in bed at that time).


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

 

[tina]

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

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

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

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

 

[Robert5833]

Hi Tina,

Those are great points; and because I don’t understand subs and functions
very well yet, I’ve struggled to use them, or at least to use them from one
module to another.

Your description of the differences and versatility of either is very helpful.

Thank you so much!

Best regards,
Robert

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

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

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


:

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

 

[tina]

you're welcome

Hi Tina,

Those are great points; and because I don't understand subs and functions
very well yet, I've struggled to use them, or at least to use them from one
module to another.

Your description of the differences and versatility of either is very helpful.

Thank you so much!

Best regards,
Robert

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

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


:

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, time

s
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


:

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

 

[dymondjack]

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

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

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

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


:

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

 

[Robert5833]

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

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

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

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


:

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


:

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

 

[Tom Wickerath]

Hi Robert,

My suggestion is to declare a string variable, strSQL, and build it up as
you currently are, but use the Debug.Print capability to view the result in
the Immediate Window. From there, you can make adjustments, as required.
Sometimes it can be very helpful to copy the SQL statement from the Immediate
Window, and attempt to paste it into the SQL View of a new query. Doing so
will often times help pinpoint errors that may not be so obvious when simply
looking at the SQL statement.

Option Compare Database
Option Explicit '<---Make sure you have this line of code as the 2nd
' line at the top of every module.

Private Sub YourProcedureName()
On Error GoTo ProcError

Dim strSQL As String

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

Debug.Print strSQL '<---Use Immediate Window to view result
Stop '<---Remove this line when you have a working SQL statement

CurrentDb.Execute strSQL, dbFailOnError

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure YourProcedureName..."
Resume ExitProc
End Sub


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

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

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

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


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


:

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


:

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], " _

 

[dymondjack]

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.

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

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???).

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

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.

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, "")

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.

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.


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


________________________________________________


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.

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

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


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.

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



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

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

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


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


:

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


:

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], " _

 

[dymondjack]

Or just listen to Tom and disregard my posts


--
Jack Leach
www.tristatemachine.com

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

 

[Robert5833]

Hi Tom,

Thank you for the reply and the suggestions. I have incorporated them into
my design.

I’ve only recently learned how to use the Debug.Print technique and how
handy it can be. And you’re right, it has helped me so many times to identify
flaws in my code. I’ve also learned to be disciplined in compiling code as I
build procedures, and that has pointed out obvious errors as well.

Also, I’ve made sure that all of my modules are Option Explicit, although
until recently (more reading from writings of yours and others) I didn’t know
the importance of that statement.

The technique you pointed out to declare a string variable in the procedure
is another good one. I’ve done that with others, but without knowing why
(other than for the fact that examples I’ve used or been provided were
written that way).

It does seem that this technique makes it easier to use the Immediate window
tool; but I’m curious…are there benefits beyond that?

If it’s not too much to ask; would you mind commenting further on the use of
one technique over the other?

Thanks again for your time and the help!

Best regards,

Robert

Hi Robert,

My suggestion is to declare a string variable, strSQL, and build it up as
you currently are, but use the Debug.Print capability to view the result in
the Immediate Window. From there, you can make adjustments, as required.
Sometimes it can be very helpful to copy the SQL statement from the Immediate
Window, and attempt to paste it into the SQL View of a new query. Doing so
will often times help pinpoint errors that may not be so obvious when simply
looking at the SQL statement.

Option Compare Database
Option Explicit '<---Make sure you have this line of code as the 2nd
' line at the top of every module.

Private Sub YourProcedureName()
On Error GoTo ProcError

Dim strSQL As String

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

Debug.Print strSQL '<---Use Immediate Window to view result
Stop '<---Remove this line when you have a working SQL statement

CurrentDb.Execute strSQL, dbFailOnError

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure YourProcedureName..."
Resume ExitProc
End Sub


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

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

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


:

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


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


:

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

 

[Tom Wickerath]

Hi Robert,

Also, I’ve made sure that all of my modules are Option Explicit, although
until recently (more reading from writings of yours and others) I didn’t know
the importance of that statement.

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

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

It does seem that this technique makes it easier to use the Immediate window
tool; but I’m curious…are there benefits beyond that?

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

If it’s not too much to ask; would you mind commenting further on the use of
one technique over the other?

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.

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.

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.


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

 

[Robert5833]

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

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


:

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


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


:

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