By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

Insert from excel to access works, update I cannot figure out the proper code

P: 24
I have been working hard to clean up my code but I am still wondering why all incoming records go to the "AddNew" part of the IF statement and never to the Edit alternative. I believe that it must be because my rs.index never really receives the value from the cell in the spreadsheet that it should use to compare to the corresponding field in the Access DB. I have named the field "rtitle" in Access and made it an index called riskIndex. I am an amateur at using debug and add watch but I tried to add watches for the variables and it appears that rs.Index is never getting the value of riskIndex but rather, "riskIndex" the string. I'm not sure about this though.

Is my syntax wrong. Please help, I have a lot of pressure on me to get this working and I'm just a newbie VB person.

Thanks!!

Here's the code:
Expand|Select|Wrap|Line Numbers
  1. Sub TestProc()
  2. Dim lCount As Long
  3. Dim wbResults As Workbook
  4. Dim wbCodeBook As Workbook
  5.  
  6. Application.ScreenUpdating = False
  7. Application.DisplayAlerts = False
  8. Application.EnableEvents = False
  9.  
  10. 'On Error Resume Next
  11. Set wbCodeBook = ThisWorkbook
  12.  
  13.     With Application.FileSearch
  14.         .NewSearch
  15.          'Change path to local conditions of risk group
  16.         .LookIn = "C:\Documents and Settings\basbergb\Desktop\usethisone_risk\pristine  \access\IncomingRiskCandidateFiles"
  17.         .FileType = msoFileTypeExcelWorkbooks
  18.         .Filename = "*.xls"
  19.  
  20.             If .Execute > 0 Then 'Workbooks in folder
  21.             For lCount = 1 To .FoundFiles.Count 'Loop through all.
  22.             'Open Workbook x and Set a Workbook variable to it
  23.             Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
  24.  
  25. 'I put some code I want to perform on each workbook between these lines
  26. ''''''''''''''''''''''''''''''''''''''''''''''''''  '''''''''''''''''''''
  27. 'Sub DAOFromExcelToAccess()
  28. 'exports data from the active worksheet to a table in an Access database
  29. Dim db As DAO.Database, rs As DAO.Recordset, r As Integer, Ans As Integer, myWS As Worksheet, crit As String
  30.     Set db = OpenDatabase("C:\Documents and Settings\basbergb\desktop\usethisone_risk\pristine  \access\risk.mdb")
  31.     Set rs = db.OpenRecordset("CandidateRisk", dbOpenTable)
  32.     Set myWS = Sheets("Candidate Risk Worksheet")
  33.  
  34.     Dim STRrtitle As String
  35.     Dim vrtIDby As Variant
  36.  
  37. 'rs.Index = "riskIndex"
  38.  
  39.  
  40. 'Get rtitle in Excel
  41. STRrtitle = Range("B" & 7).Value
  42.  
  43. 'Get IDby in Excel
  44. 'vrtIDby = Range("B" & 11).Value
  45.  
  46. With rs
  47. .Index = "riskIndex"
  48. 'Find record in database
  49. .Seek "=", STRrtitle
  50. End With
  51.  
  52. 'if record not found in database, add it
  53.  
  54. If rs.NoMatch = True Then
  55. 'Record not found so create new record:
  56.  With rs
  57.             .AddNew 'create a new record
  58.             .Fields("rtitle") = myWS.Range("B7").Value
  59.             .Fields("status") = myWS.Range("K7").Value
  60.             .Fields("IDby") = myWS.Range("B11").Value
  61.             .Fields("IPT_WGID") = myWS.Range("G11").Value
  62.             .Fields("dateID") = myWS.Range("K11").Value
  63.             .Fields("riskOwner") = myWS.Range("B14").Value
  64.             .Fields("IPT_WGRO") = myWS.Range("G14").Value
  65.             .Fields("dateAssigned") = myWS.Range("K14").Value
  66.             .Fields("dateFirstPresented") = myWS.Range("K17").Value
  67.             .Fields("ifThenPerf") = myWS.Range("C19").Value
  68.             .Fields("sitPerf") = myWS.Range("C20").Value
  69.             .Fields("LH_Perf") = myWS.Range("E21").Value
  70.             .Fields("CQ_Perf") = myWS.Range("E22").Value
  71.             .Fields("RHA_Perf") = myWS.Range("F23").Value
  72.             .Fields("ifThenCost") = myWS.Range("C19").Value
  73.             .Fields("sitCost") = myWS.Range("C20").Value
  74.             .Fields("LH_Cost") = myWS.Range("E21").Value
  75.             .Fields("CQ_Cost") = myWS.Range("E22").Value
  76.             .Fields("RHA_Cost") = myWS.Range("F23").Value
  77.             .Fields("ifThenSched") = myWS.Range("C19").Value
  78.             .Fields("sitSched") = myWS.Range("C20").Value
  79.             .Fields("LH_Sched") = myWS.Range("E21").Value
  80.             .Fields("CQ_Sched") = myWS.Range("E22").Value
  81.             .Fields("RHA_Sched") = myWS.Range("F23").Value
  82.             .Fields("DAESriskFactor") = myWS.Range("B40").Value
  83.             .Fields("reqRiskBasedOn") = myWS.Range("J40").Value
  84.  
  85.             .Update 'stores the new record
  86.         End With
  87.         Ans = MsgBox("Candidate Risk written to Access database", vbInformation, "Transferred Data")
  88.  
  89. Else
  90.  
  91. 'Record is found so edit it
  92. With rs
  93.             .Edit
  94.             .Fields("rtitle") = myWS.Range("B7").Value
  95.             .Fields("status") = myWS.Range("K7").Value
  96.             .Fields("IDby") = myWS.Range("B11").Value
  97.             .Fields("IPT_WGID") = myWS.Range("G11").Value
  98.             .Fields("dateID") = myWS.Range("K11").Value
  99.             .Fields("riskOwner") = myWS.Range("B14").Value
  100.             .Fields("IPT_WGRO") = myWS.Range("G14").Value
  101.             .Fields("dateAssigned") = myWS.Range("K14").Value
  102.             .Fields("dateFirstPresented") = myWS.Range("K17").Value
  103.             .Fields("ifThenPerf") = myWS.Range("C19").Value
  104.             .Fields("sitPerf") = myWS.Range("C20").Value
  105.             .Fields("LH_Perf") = myWS.Range("E21").Value
  106.             .Fields("CQ_Perf") = myWS.Range("E22").Value
  107.             .Fields("RHA_Perf") = myWS.Range("F23").Value
  108.             .Fields("ifThenCost") = myWS.Range("C19").Value
  109.             .Fields("sitCost") = myWS.Range("C20").Value
  110.             .Fields("LH_Cost") = myWS.Range("E21").Value
  111.             .Fields("CQ_Cost") = myWS.Range("E22").Value
  112.             .Fields("RHA_Cost") = myWS.Range("F23").Value
  113.             .Fields("ifThenSched") = myWS.Range("C19").Value
  114.             .Fields("sitSched") = myWS.Range("C20").Value
  115.             .Fields("LH_Sched") = myWS.Range("E21").Value
  116.             .Fields("CQ_Sched") = myWS.Range("E22").Value
  117.             .Fields("RHA_Sched") = myWS.Range("F23").Value
  118.             .Fields("DAESriskFactor") = myWS.Range("B40").Value
  119.             .Fields("reqRiskBasedOn") = myWS.Range("J40").Value
  120.     .Update
  121. End With
  122.  
  123.  
  124. Ans = MsgBox("Successfully edited the record", vbInformation, "Updated values")
  125. End If
  126.                  ''''''''''''''''''''''''''''''''''''''''''''''''''  ''''''''''''''
  127.  
  128.                  wbResults.Close SaveChanges:=True
  129.  
  130.                  Next lCount
  131.             End If
  132.     End With
  133.  
  134.     On Error GoTo 0
  135.     Application.ScreenUpdating = True
  136.     Application.DisplayAlerts = True
  137.     Application.EnableEvents = True
  138. End Sub
  139.  
  140.  
  141.  
If anyone can help I cannot tell you how relieved and grateful I will be- I have been struggling with this for so long > over a week pretty much non-stop. Please let me know if I should clarify anything though I think I've been too detailed which may make it too much work for anyone to help.

Thank you in advance for your time and talent
Jan 6 '07 #1
Share this Question
Share on Google+
2 Replies


P: 24
Thanks anyway. I got it working. It turns out everything worked great once I saw that I was looking in the excel workbook the program was running from rather than the individual workbooks that I was processing. So the value in the cell of the incoming workbook was never being compared to the riskIndex in the Access database.
Jan 6 '07 #2

100+
P: 1,646
Thanks anyway. I got it working. It turns out everything worked great once I saw that I was looking in the excel workbook the program was running from rather than the individual workbooks that I was processing. So the value in the cell of the incoming workbook was never being compared to the riskIndex in the Access database.
You're welcome :)
Jan 7 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.