UtterAccess.com
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    
 
   
mike60smart
post Jan 24 2020, 08:46 AM
Post#1


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

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

cmdInsert_Click_Error:

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

End Sub


Any help appreciated

--------------------
Hope this helps?

Mike

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
Post#2


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