1 Replies - 681 Views - Last Post: 22 February 2010 - 01:29 AM Rate Topic: -----

#1 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 817
  • Joined: 16-September 09

Odd error in Access when transferring data table2table

Posted 18 February 2010 - 01:19 PM

I'm transferring data from a table that I imported information to, to a table that has a layout that i need to export in. I'm getting error 3020, 'Update or cancelupdate with addnew or edit' ((spelled exactly that way)). The code looks like this

[code]
'STEPS TO CREATING A FILE _
1. CREATE TABLE FOR FILE (WITH BLANK COLS) _
2. CREATE MONARCH TEMPLATE TO READ _
3. USE ACCESS TO RUN FOLLOWING STEPS _
A) OPEN FILE IN MONARCH AND OPEN MODEL THEN EXPORT TO FOLDER _
B) OPEN EXPORTED MONARCH FILE AND IMPORT TO ACCESS _
C) RUN ALL PROGRAMS IN ACCESS _
D) EXPORT TO CT_READY FILE
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Compare Database
Public dbs As Database


Private Sub Check2_Click()
If Check2.Value = True Then Check4.Value = False
If Check4.Value = False Then Check2.Value = True
End Sub

Private Sub Check4_Click()
If Check4.Value = True Then Check2.Value = False
If Check2.Value = False Then Check4.Value = True
End Sub

Private Sub Command1_Click()

Dim sInputFile As String
Dim sInitDir As String
Dim sSelectedFile As String
Dim sMonApptFile As String
Dim intSlashHOLD As Integer
Dim intLastSlash As Integer
Dim sProName As String
Dim MonarchObj As Object
Dim openfile, openmod, Monarch_LogFile As Boolean
Dim sMultiPick As String
Dim rsAppts As Recordset
Dim rsNew As Recordset
Dim sDate As String
Dim sTime As String
Dim sDuration As String
Dim sTypeID As String
Dim sType As String
Dim sProID As String
Dim sPro As String
Dim sLocID As String
Dim sLoc As String
Dim sMRN As String
Dim sFName As String
Dim sLName As String

ProName:
sProName = InputBox("Please enter the name of the provider")
'If 2 Then End 'Cancel
If sProName = "" Then
MsgBox ("Invalid Proivder Name"), vbCritical, "Enter Good Provider Name"
GoTo ProName
End If

With Application.FileDialog(msoFileDialogFilePicker)
If .Show = False Then End
sSelectedFile = .SelectedItems(1)
.AllowMultiSelect = False
End With

'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ _
!!DONT FORGET TO FINISH THIS. IT SELECTS MULTIPLE FILES BUT HAS TO INSERT THEM ALL TO ONE TABLE!!
'Sample Code: 'static sfiles as integer -- for each file in sfiles run monarch and import

If Check2.Value = True Then 'Multiple files
sMultiPick = MsgBox("Do you want to add another file?", vbYesNo, "Multi-file Picker")
Do Until sMultiPick = vbNo
sMultiPick = MsgBox("Do you want to add another file?", vbYesNo, "Multi-file Picker")
If sMultiPick = vbYes Then
With Application.FileDialog(msoFileDialogFilePicker)
If .Show = False Then End
sSelectedFile = .SelectedItems(1)
.AllowMultiSelect = False
End With
End If
Loop
End If
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ _


intLastSlash = InStr(1, sSelectedFile, "\")
Do Until intLastSlash = 0
intLastSlash = InStr((intLastSlash + 1), sSelectedFile, "\")
If intLastSlash <> 0 Then
intSlashHOLD = intLastSlash
End If
Loop

' remember the selected directory for subsequent dialog boxes...
sInitDir = Mid(sSelectedFile, 1, (intSlashHOLD - 1))
sInputFile = Mid(sSelectedFile, (intSlashHOLD + 1))
sMonApptFile = sInitDir & "\" & sProName & ".csv"

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''FILE(S) NOW SELECTED & HAVE TO BE RUN THROUGH MONARCH & IMPORTED TO EMPTY TABLE
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Monarch

'If Monarch is currently active GetObject will use Monarch.If it is not use _
the CreateObject() to open another copy of Monarch.

Set MonarchObj = GetObject("", "Monarch32")
If MonarchObj Is Nothing Then
Set MonarchObj = CreateObject("Monarch32")
End If
Monarch_LogFile = MonarchObj.SetLogFile("C:\MonTemp\MPrg_G5.log", False)
openfile = MonarchObj.SetReportFile(sSelectedFile, False)
If openfile = True Then
openmod = MonarchObj.SetModelFile("V:\Information\Rich\CONV\Jimmys Models\Med.mod")
If openmod = True Then
MonarchObj.ExportTable (sMonApptFile)
End If
End If
MonarchObj.CloseAllDocuments
MonarchObj.Exit

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''NOW THAT THE FILES HAVE BEEN THROUGH MONARCH, RETRIEVE AND FORMAT TO OUTPUT AS CSV
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set dbs = CurrentDb

'Delete Records from table
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * from MM_History"
DoCmd.RunSQL "DELETE * from Main_Appt"
DoCmd.SetWarnings True

'Import the information to table as text (had to create spec...thanks rich for the great notes in the mcp)
DoCmd.TransferText acImportDelim, "MM_Appt_Hist_Spec", "MM_History", sMonApptFile

'Transfer records from import table to appt layout table
Set rsAppts = dbs.OpenRecordset("MM_History")

'Set columns to memory for transfer to final table before export
sDate = rsAppts.Fields("Appt Date")
sTime = rsAppts.Fields("Appt Time")
sDuration = rsAppts.Fields("Durations")
sTypeID = rsAppts.Fields("Type ID")
sType = rsAppts.Fields("appt type")
sProID = rsAppts.Fields("pro id")
sPro = rsAppts.Fields("pro name")
If rsAppts.Fields("Location") <> "" Then
sLoc = rsAppts.Fields("location")
End If
sMRN = rsAppts.Fields("MRN")
sFName = rsAppts.Fields("FName")
sLName = rsAppts.Fields("LName")

Set rsNew = dbs.OpenRecordset("Main_Appt")

'Insert columns in final table (Main_Appt) for final export
rsNew.Fields("Date") = sDate '!!!!!!!!!!!!!!!!!!!!!!!!!THIS IS WHERE I GET MY ERROR!!!!!!!!!!!!!!!
rsNew.Fields("time") = sTime
rsNew.Fields("duration") = sDuration
rsNew.Fields("typeid") = sTypeID
rsNew.Fields("type") = sType
rsNew.Fields("proid") = sProID
rsNew.Fields("pro") = sPro
rsNew.Fields("loc") = sLoc
rsNew.Fields("mrn") = sMRN
rsNew.Fields("fname") = sFName
rsNew.Fields("lname") = sLName

End Sub
{/CODE]

Could someone please explain what I'm doing wrong here? Thanks in advance!

Is This A Good Question/Topic? 0
  • +

Replies To: Odd error in Access when transferring data table2table

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Odd error in Access when transferring data table2table

Posted 22 February 2010 - 01:29 AM

simple just add this one line before the error
rsNew.AddNew



and after setting the values to the record use
rsnew.update


to save the record

This post has been edited by thava: 22 February 2010 - 01:31 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1