MS Access script not working. Please help with script.

[daniell]

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:

Private Sub CmdCopyPaste_Click()
 
Dim iResponce As Integer
 
iResponce = MsgBox("Are you sure you want to copy THESE results to ALL departments on THIS worksheet? ", vbYesNo, "COPY Results")
 
If iResponce = vbYes Then ' They Clicked YES
 
 
 
Dim vInterviewee
 
Dim vInterviewDate As String
 
Dim vExpWContent
 
Dim vExpWSystems
 
Dim vMaxOffFloor
 
Dim vImpactedProjects
 
Dim vLocation
 
Dim vGoodTimes
 
Dim vBadTimes
 
Dim vAccessibilityIssues
 
Dim vAccessibilityDesc
 
Dim vConcurrentTraining
 
Dim vConTrainDetail
 
Dim vSpecialNeeds
 
Dim vSpecNeedDetail
 
Dim vProcessChange
 
Dim vBusProcessContact
 
Dim vProcessEPOContact
 
Dim vProcessDetail
 
Dim vNotes
 
Dim vID
 
 
 
Dim strSQL
 
 
 
Me.Dirty = False ' save current record
 
 
 
vInterviewee = Me.Interviewee
 
vProjectImpact = Me.ProjectImpact
 
vExpWContent = Me.ExpWContent
 
vExpWSystems = Me.ExpWSystems
 
vMaxOffFloor = Me.MaxOffFloor
 
vImpactedProjects = Me.ImpactedProjects
 
vLocation = Me.Location
 
vGoodTimes = Me.GoodTimes
 
vBadTimes = Me.BadTimes
 
vAccessibilityIssues = Me.AccessibilityIssues
 
vAccessibilityDesc = Me.AccessibilityDesc
 
vConcurrentTraining = Me.ConcurrentTraining
 
vConTrainDetail = Me.ConTrainDetail
 
vSpecialNeeds = Me.SpecialNeeds
 
vSpecNeedDetail = Me.SpecNeedDetail
 
vProcessChange = Me.ProcessChange
 
vBusProcessContact = Me.BusProcessContact
 
vProcessEPOContact = Me.ProcessEPOContact
 
vProcessDetail = Me.ProcessDetail
 
vNotes = Me.Notes
 
vID = Me.ID
 
 
   If IsNull(InterviewDate) Then
 
      vInterviewDate = "NULL"
 
   Else
 
      vInterviewDate = "#" & CStr(InterviewDate) & "#"
 
   End If
 
strSQL = "Update tblAAResults set Interviewee =  '" & vInterviewee & _
 
"', InterviewDate = " & vInterviewDate & " " & " ,  ProjectImpact = '" & vProjectImpact & "' , ExpWContent = '" & vExpWContent & _
 
"' , ExpWSystems = '" & ExpWSystems & "', MaxOffFloor = '" & vMaxOffFloor & _
 
"', ImpactedProjects = '" & vImpactedProjects & "', Location = '" & vLocation & _
 
"', GoodTimes = '" & vGoodTimes & "', BadTimes = '" & vBadTimes & _
 
"' , AccessibilityIssues = '" & vAccessibilityIssues & "' , AccessibilityDesc = '" & vAccessibilityDesc & _
 
"' , ConcurrentTraining = '" & vConcurrentTraining & "', ConTrainDetail = '" & vConTrainDetail & _
 
"', SpecialNeeds = '" & vSpecialNeeds & "', SpecNeedDetail = '" & vSpecNeedDetail & _
 
"' , ProcessChange = '" & vProcessChange & "', BusProcessContact = '" & vBusProcessContact & _
 
"', ProcessEPOContact = '" & vProcessEPOContact & " ', ProcessDetail = '" & vProcessDetail & _
 
"', ID = '" & vID & "',  Notes = '" & vNotes & "' where AAID = " & Me.AAID & " and Department <> ' " & Me.Department & "'"
MsgBox strSQL
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
 
Else
 End If
End Sub

[damonreid]

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.

[NeoPa]

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.