Simple Insert Into...

Good day all,

“Back to the well” I come:

Disclaimer; I don’t know what I don’t know about programming…

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

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

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

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

CurrentDb.Execute strSQL, dbFailOnError

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

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

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

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

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

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

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

Any help or suggestions would be greatly appreciated.

Best regards,

RL
.

Relevant Pages

  • Re: Make Table Help
    ... > original query and then change the data type to TRUE/FALSE and then append ... The TRUE/FALSE field has its Required set to No. ... > difference whether I add or don't add the field to the grid of the Append ... > data type being TRUE/FALSE. ...
    (microsoft.public.access.queries)
  • RE: Make Table Help
    ... The TRUE/FALSE field has its Required set to No. ... > difference whether I add or don't add the field to the grid of the Append ... > query or whether I set the default value to FALSE for the append, ... > data type being TRUE/FALSE. ...
    (microsoft.public.access.queries)
  • Re: Date field update
    ... I have a table with 2 fields as data type date. ... Append From theFile.txt Type .... ... Local lcOriginal, lnDay, lcMonth, lnMonth, lnYear, ldDate ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Getting validation rule violation when I dont have any validation rules?
    ... So all I am trying to do is append some data into a table here. ... Check for validation rules on both tables - none exist that I can see ... purposes of the same data type and very similar ... Unfortunately I can not post up any of the information causing ...
    (microsoft.public.access.queries)
  • Re: Message box appear for no records returned in query
    ... Public Function Error_Cd ... On Error GoTo Err_Execute ... MsgBox "There are no changed dates" ... records to append, ...
    (microsoft.public.access.modulesdaovba)
Loading