X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Insert, Access 2016    
post Jan 24 2020, 08:46 AM

UtterAccess VIP
Posts: 13,658
Joined: 6-June 05
From: Dunbar,Scotland

Hi Everyone

EDIT *****************

Please ignore this request as I have goner down a different path

I am trying to use the following Code to insert a number of records from a Subform to another table.

When I try to run the code i get the following error:-

Attached File  error.PNG ( 4.34K )Number of downloads: 0

The Code s as follows:-

Private Sub cmdInsert_Click()
10    On Error GoTo cmdInsert_Click_Error
      Dim rsTemp As DAO.Recordset
      Dim mySQL  As String
      Dim i As Integer
      Dim cIDs As New Collection
      'Create a copy of this forms Recordset
20    Set rsTemp = Me.RecordsetClone
30    rsTemp.MoveFirst

      'Loop through all records and insert records....
40    With rsTemp
50        While Not .EOF
60            cIDs.Add !CustomerPurchaseOrderID.Value, !CustomerPurchaseOrderID.Value & ""
70            .MoveNext
80        Wend
90    End With

100   rsTemp.Close
110   Set rsTemp = Nothing

120   If cIDs.Count > 0 Then
130       mySQL = "INSERT INTO tblG703 (CustomerPurchaseOrderID, ItemNo, DescriptionOfWork, ScheduledValue) " _
                  & "SELECT p0, p1, p2, p3;"
140       With CurrentDb.CreateQueryDef("", mySQL)
150           For i = 1 To cIDs.Count
170               .Parameters("p0") = cIDs(i)
180               .Parameters("p1") = Me.Item
190               .Parameters("p2") = Me.Description
200               .Parameters("p3") = Me.Total_Value

220               .Execute
230           Next
240       End With
250       Me.Requery

260       MsgBox "All Scheduled Values Entered in G703!!", vbInformation

270   End If
275   [Forms]![frmContractAIADetails]![frmG703].[Form].Requery

280       On Error GoTo 0
290       Exit Sub


300       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdInsert_Click, line " & Erl & "."

End Sub

Any help appreciated

Hope this helps?


Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
Doug Steele
post Jan 25 2020, 05:40 PM

UtterAccess VIP
Posts: 22,245
Joined: 8-January 07
From: St. Catharines, ON (Canada)

Just going to add this on so it no longer shows up when I look for unanswered topics!

Glad you got it working, Mike.

Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    21st February 2020 - 11:29 AM