473,221 Members | 2,321 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 software developers and data experts.

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

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
2 2308
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
willakawill
1,646 1GB
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

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

Similar topics

6
by: pk | last post by:
Sorry for the piece-by-piece nature of this post, I moved it from a dormant group to this one and it was 3 separate posts in the other group. Anyway... I'm trying to bulk insert a text file of...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
3
by: Shapper | last post by:
Hello, I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine. The update is not. I checked and my database has all...
4
by: alexandre.brisebois | last post by:
Hi, I am using access 2003, I would like to know if there is an option to reorganize the tables in a maner that is readable, as we can do in sql sever 2000 or 2005. I have been given a database...
1
by: baling | last post by:
Hi.... Hi everybody, i have a code that i make in VBA and know I want to use this code in to VB6. But i don't know how to use that code in to VB 6.0 Please correct this code so i can use it in VB...
4
by: =?Utf-8?B?UmljaA==?= | last post by:
On a form - I have a datagridview which is docked to the entire form. The datagridview allows users to Delete and/or Add Rows. On the Form_Load event I Fill the datagridview source table with a...
2
by: cbadchris | last post by:
I am writing an import function with asp, and excel that writes imported excel sheets to to several SQL tables and everything works fine until it reaches the "add data for search" section or query 2....
22
by: robertgregson | last post by:
Using C#, .NET3.5, Visual Studio 2008 and WCF on Windows VISTA SP1, I have written a service, service host (as a C# console application) and a client. The service uses...
3
by: GR82BBlondie | last post by:
Hello, Fairly new to VB Access. Here's the issue: I have an expense field that is on the main form and an allocation amount on a subform. Code from buttons to update the allocation amount works...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.