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:

[CODE=access]Private Sub CmdCopyPaste_Cl ick()

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 vImpactedProjec ts

Dim vLocation

Dim vGoodTimes

Dim vBadTimes

Dim vAccessibilityI ssues

Dim vAccessibilityD esc

Dim vConcurrentTrai ning

Dim vConTrainDetail

Dim vSpecialNeeds

Dim vSpecNeedDetail

Dim vProcessChange

Dim vBusProcessCont act

Dim vProcessEPOCont act

Dim vProcessDetail

Dim vNotes

Dim vID



Dim strSQL



Me.Dirty = False ' save current record



vInterviewee = Me.Interviewee

vProjectImpact = Me.ProjectImpac t

vExpWContent = Me.ExpWContent

vExpWSystems = Me.ExpWSystems

vMaxOffFloor = Me.MaxOffFloor

vImpactedProjec ts = Me.ImpactedProj ects

vLocation = Me.Location

vGoodTimes = Me.GoodTimes

vBadTimes = Me.BadTimes

vAccessibilityI ssues = Me.Accessibilit yIssues

vAccessibilityD esc = Me.Accessibilit yDesc

vConcurrentTrai ning = Me.ConcurrentTr aining

vConTrainDetail = Me.ConTrainDeta il

vSpecialNeeds = Me.SpecialNeeds

vSpecNeedDetail = Me.SpecNeedDeta il

vProcessChange = Me.ProcessChang e

vBusProcessCont act = Me.BusProcessCo ntact

vProcessEPOCont act = Me.ProcessEPOCo ntact

vProcessDetail = Me.ProcessDetai l

vNotes = Me.Notes

vID = Me.ID


If IsNull(Intervie wDate) Then

vInterviewDate = "NULL"

Else

vInterviewDate = "#" & CStr(InterviewD ate) & "#"

End If

strSQL = "Update tblAAResults set Interviewee = '" & vInterviewee & _

"', InterviewDate = " & vInterviewDate & " " & " , ProjectImpact = '" & vProjectImpact & "' , ExpWContent = '" & vExpWContent & _

"' , ExpWSystems = '" & ExpWSystems & "', MaxOffFloor = '" & vMaxOffFloor & _

"', ImpactedProject s = '" & vImpactedProjec ts & "', Location = '" & vLocation & _

"', GoodTimes = '" & vGoodTimes & "', BadTimes = '" & vBadTimes & _

"' , AccessibilityIs sues = '" & vAccessibilityI ssues & "' , AccessibilityDe sc = '" & vAccessibilityD esc & _

"' , ConcurrentTrain ing = '" & vConcurrentTrai ning & "', ConTrainDetail = '" & vConTrainDetail & _

"', SpecialNeeds = '" & vSpecialNeeds & "', SpecNeedDetail = '" & vSpecNeedDetail & _

"' , ProcessChange = '" & vProcessChange & "', BusProcessConta ct = '" & vBusProcessCont act & _

"', ProcessEPOConta ct = '" & vProcessEPOCont act & " ', ProcessDetail = '" & vProcessDetail & _

"', ID = '" & vID & "', Notes = '" & vNotes & "' where AAID = " & Me.AAID & " and Department <> ' " & Me.Department & "'"
MsgBox strSQL
Debug.Print strSQL
CurrentDb.Execu te strSQL, dbFailOnError

Else
End If
End Sub[/CODE]

[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.