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

MS Access script not working. Please help with script.

P: 1
Please help with this code. I am unable to figure out what's wrong with it.

The purpose of this code is to copy data from one record into an infinite amount of records. For example if there were 5 department records (from different departments) linked by a common identifier (worksheet#). The (worksheet#) and I wanted to enter information in one record and have that data copied into the remaining 4.

Thanks in Advance for your help!

Regards,

DR

Here is code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdCopyPaste_Click()
  2.  
  3. Dim iResponce As Integer
  4.  
  5. iResponce = MsgBox("Are you sure you want to copy THESE results to ALL departments on THIS worksheet? ", vbYesNo, "COPY Results")
  6.  
  7. If iResponce = vbYes Then ' They Clicked YES
  8.  
  9.  
  10.  
  11. Dim vInterviewee
  12.  
  13. Dim vInterviewDate As String
  14.  
  15. Dim vExpWContent
  16.  
  17. Dim vExpWSystems
  18.  
  19. Dim vMaxOffFloor
  20.  
  21. Dim vImpactedProjects
  22.  
  23. Dim vLocation
  24.  
  25. Dim vGoodTimes
  26.  
  27. Dim vBadTimes
  28.  
  29. Dim vAccessibilityIssues
  30.  
  31. Dim vAccessibilityDesc
  32.  
  33. Dim vConcurrentTraining
  34.  
  35. Dim vConTrainDetail
  36.  
  37. Dim vSpecialNeeds
  38.  
  39. Dim vSpecNeedDetail
  40.  
  41. Dim vProcessChange
  42.  
  43. Dim vBusProcessContact
  44.  
  45. Dim vProcessEPOContact
  46.  
  47. Dim vProcessDetail
  48.  
  49. Dim vNotes
  50.  
  51. Dim vID
  52.  
  53.  
  54.  
  55. Dim strSQL
  56.  
  57.  
  58.  
  59. Me.Dirty = False ' save current record
  60.  
  61.  
  62.  
  63. vInterviewee = Me.Interviewee
  64.  
  65. vProjectImpact = Me.ProjectImpact
  66.  
  67. vExpWContent = Me.ExpWContent
  68.  
  69. vExpWSystems = Me.ExpWSystems
  70.  
  71. vMaxOffFloor = Me.MaxOffFloor
  72.  
  73. vImpactedProjects = Me.ImpactedProjects
  74.  
  75. vLocation = Me.Location
  76.  
  77. vGoodTimes = Me.GoodTimes
  78.  
  79. vBadTimes = Me.BadTimes
  80.  
  81. vAccessibilityIssues = Me.AccessibilityIssues
  82.  
  83. vAccessibilityDesc = Me.AccessibilityDesc
  84.  
  85. vConcurrentTraining = Me.ConcurrentTraining
  86.  
  87. vConTrainDetail = Me.ConTrainDetail
  88.  
  89. vSpecialNeeds = Me.SpecialNeeds
  90.  
  91. vSpecNeedDetail = Me.SpecNeedDetail
  92.  
  93. vProcessChange = Me.ProcessChange
  94.  
  95. vBusProcessContact = Me.BusProcessContact
  96.  
  97. vProcessEPOContact = Me.ProcessEPOContact
  98.  
  99. vProcessDetail = Me.ProcessDetail
  100.  
  101. vNotes = Me.Notes
  102.  
  103. vID = Me.ID
  104.  
  105.  
  106.    If IsNull(InterviewDate) Then
  107.  
  108.       vInterviewDate = "NULL"
  109.  
  110.    Else
  111.  
  112.       vInterviewDate = "#" & CStr(InterviewDate) & "#"
  113.  
  114.    End If
  115.  
  116. strSQL = "Update tblAAResults set Interviewee =  '" & vInterviewee & _
  117.  
  118. "', InterviewDate = " & vInterviewDate & " " & " ,  ProjectImpact = '" & vProjectImpact & "' , ExpWContent = '" & vExpWContent & _
  119.  
  120. "' , ExpWSystems = '" & ExpWSystems & "', MaxOffFloor = '" & vMaxOffFloor & _
  121.  
  122. "', ImpactedProjects = '" & vImpactedProjects & "', Location = '" & vLocation & _
  123.  
  124. "', GoodTimes = '" & vGoodTimes & "', BadTimes = '" & vBadTimes & _
  125.  
  126. "' , AccessibilityIssues = '" & vAccessibilityIssues & "' , AccessibilityDesc = '" & vAccessibilityDesc & _
  127.  
  128. "' , ConcurrentTraining = '" & vConcurrentTraining & "', ConTrainDetail = '" & vConTrainDetail & _
  129.  
  130. "', SpecialNeeds = '" & vSpecialNeeds & "', SpecNeedDetail = '" & vSpecNeedDetail & _
  131.  
  132. "' , ProcessChange = '" & vProcessChange & "', BusProcessContact = '" & vBusProcessContact & _
  133.  
  134. "', ProcessEPOContact = '" & vProcessEPOContact & " ', ProcessDetail = '" & vProcessDetail & _
  135.  
  136. "', ID = '" & vID & "',  Notes = '" & vNotes & "' where AAID = " & Me.AAID & " and Department <> ' " & Me.Department & "'"
  137. MsgBox strSQL
  138. Debug.Print strSQL
  139. CurrentDb.Execute strSQL, dbFailOnError
  140.  
  141. Else
  142.  End If
  143. End Sub
Sep 12 '07 #1
Share this Question
Share on Google+
2 Replies


damonreid
Expert 100+
P: 114
Why not just link the tables using a query if they have the same common key? That way you can keep the information in one location and still use it for reporting.
Sep 12 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
Daniell,

This is too much like a "Please do my job for me" request.
That is not what this forum is about. We answer technical questions when we can and may guide and assist in some small measure while we do so. We are not a free resource to do your job for you.

If you want help on this then you must form this into some sort of question after doing as much as you can to determine your problem for yourself. You would also need to pass on (explain) what you understood of the problem.

MODERATOR.
Sep 12 '07 #3

Post your reply

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