I have the following VBA code that auto populates a word template:
Private Sub Command24_Click()
On Error GoTo ErrorHandler:
ErrorHandler:
If IsNull(Me.REASONFOREXIT) Then
MsgBox "Reason for Exit needed"
Exit Sub
End If
If Me.TRAININGSTATUS = "COMPLETED" And Me.TRAININGPROVIDER = " " Then
MsgBox "Training Provider needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.JOBTITLE = "" Then
MsgBox "Job Title needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYER = "" Then
MsgBox "Employer needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYERADDRESS = "" Then
MsgBox "Employer Address needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYERCITY = "" Then
MsgBox "Employer City needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYERSTATE = "" Then
MsgBox "Employer State needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYERZIP = "" Then
MsgBox "Employer Zip needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYERCONTACTPHONE = "" Then
MsgBox "Employer Contact Phone needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.HOURS_WEEK = "" Then
MsgBox "Hours/Week needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.HOURLYWAGE = "" Then
MsgBox "Hourly Wage needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.OCCUPATIONATEXIT = " " Then
MsgBox "Occupation at Exit needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.INDUSTRYATEXIT = " " Then
MsgBox "Industry at Exit needed"
Exit Sub
End If
Dim sAccessCustid As String
Dim sAccessFullname As String
Dim sAccessProgram As String
Dim sAccessExitreason As String
Dim sAccessRegistrationdate As String
Dim sAccessWorkkeyscompleted As String
Dim sAccessTrainingstatus As String
Dim sAccessTrainingprovider As String
Dim sAccessCredentialattained As String
Dim sAccessEmployedatregistration As String
Dim sAccessEmployed As String
Dim sAccessJobtitle As String
Dim sAccessEmployer As String
Dim sAccessEmployeraddress As String
Dim sAccessEmployercity As String
Dim sAccessEmployerstate As String
Dim sAccessEmployerzip As String
Dim sAccessEmployercontactphone As String
Dim sAccessHours_week As String
Dim sAccessHourlywage As String
Dim sAccessFringebenefits As String
Dim sAccessOccupationatexit As String
Dim sAccessIndustryatexit As String
sAccessCustid = CUSTID
sAccessFullname = FIRSTNAME & " " & LASTNAME
sAccessProgram = PROGRAM & " " & FUNDINGSOURCE
sAccessExitreason = REASONFOREXIT
sAccessRegistrationdate = REGISTRATIONDATE
sAccessWorkkeyscompleted = WORKKEYSCOMPLETED
sAccessTrainingstatus = TRAININGSTATUS
sAccessTrainingprovider = TRAININGPROVIDER
sAccessCredentialattained = CREDENTIALATTAINED
sAccessEmployedatregistration = EMPLOYEDATREGISTRATION
sAccessEmployed = EMPLOYED
sAccessJobtitle = JOBTITLE
sAccessEmployer = EMPLOYER
sAccessEmployeraddress = EMPLOYERADDRESS
sAccessEmployercity = EMPLOYERCITY
sAccessEmployerstate = EMPLOYERSTATE
sAccessEmployerzip = EMPLOYERZIP
sAccessEmployercontactphone = EMPLOYERCONTACTPHONE
sAccessHours_week = HOURS_WEEK
sAccessHourlywage = HOURLYWAGE
sAccessFringebenefits = FRINGEBENEFITS
sAccessOccupationatexit = OCCUPATIONATEXIT
sAccessIndustryatexit = INDUSTRYATEXIT
Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")
Dim sMergeDoc As String
sMergeDoc = Application.CurrentProject.Path & _
"\Request for Exit.dotx"
Wrd.Documents.Add sMergeDoc
Wrd.Visible = True
With Wrd.ActiveDocument.Bookmarks
.Item("AcceessCustid").Range.Text = sAccessCustid
.Item("AccessFullname").Range.Text = sAccessFullname
.Item("AccessProgram").Range.Text = sAccessProgram
.Item("AccessExitreason").Range.Text = sAccessExitreason
.Item("AccessRegistrationdate").Range.Text = sAccessRegistrationdate
.Item("AccessWorkkeyscompleted").Range.Text = sAccessWorkkeyscompleted
.Item("AccessTrainingstatus").Range.Text = sAccessTrainingstatus
.Item("AccessTrainingprovider").Range.Text = sAccessTrainingprovider
.Item("AccessCredentialattained").Range.Text = sAccessCredentialattained
.Item("AccessEmployedatregistration").Range.Text = sAccessEmployedatregistration
.Item("AccessEmployed").Range.Text = sAccessEmployed
.Item("AccessJobtitle").Range.Text = sAccessJobtitle
.Item("AccessEmployer").Range.Text = sAccessEmployer
.Item("AccessEmployeraddress").Range.Text = sAccessEmployeraddress
.Item("AccessEmployercity").Range.Text = sAccessEmployercity
.Item("AccessEmployerstate").Range.Text = sAccessEmployerstate
.Item("AccessEmployerzip").Range.Text = sAccessEmployerzip
.Item("AccessEmployercontactphone").Range.Text = sAccessEmployercontactphone
.Item("AccessHours_week").Range.Text = sAccessHours_week
.Item("AccessHourlywage").Range.Text = sAccessHourlywage
.Item("AccessFringebenefits").Range.Text = sAccessFringebenefits
.Item("AccessOccupationatexit").Range.Text = sAccessOccupationatexit
.Item("AccessIndustryatexit").Range.Text = sAccessIndustryatexit
End With
Set Wrd = Nothing
End Sub
The code executes flawlessy but there are three values that are based off check boxes set as YES/NO (EMPLOYEDATREGISTRATION, EMPLOYED and FRINGEBENEFITS). These values show up as -1 for YES and 0 for NO.
Is there anyway to add code to mine that would allow me to change these values before they are sent to the Word template?
Private Sub Command24_Click()
On Error GoTo ErrorHandler:
ErrorHandler:
If IsNull(Me.REASONFOREXIT) Then
MsgBox "Reason for Exit needed"
Exit Sub
End If
If Me.TRAININGSTATUS = "COMPLETED" And Me.TRAININGPROVIDER = " " Then
MsgBox "Training Provider needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.JOBTITLE = "" Then
MsgBox "Job Title needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYER = "" Then
MsgBox "Employer needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYERADDRESS = "" Then
MsgBox "Employer Address needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYERCITY = "" Then
MsgBox "Employer City needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYERSTATE = "" Then
MsgBox "Employer State needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYERZIP = "" Then
MsgBox "Employer Zip needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.EMPLOYERCONTACTPHONE = "" Then
MsgBox "Employer Contact Phone needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.HOURS_WEEK = "" Then
MsgBox "Hours/Week needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.HOURLYWAGE = "" Then
MsgBox "Hourly Wage needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.OCCUPATIONATEXIT = " " Then
MsgBox "Occupation at Exit needed"
Exit Sub
End If
If Me.EMPLOYED = "YES" And Me.INDUSTRYATEXIT = " " Then
MsgBox "Industry at Exit needed"
Exit Sub
End If
Dim sAccessCustid As String
Dim sAccessFullname As String
Dim sAccessProgram As String
Dim sAccessExitreason As String
Dim sAccessRegistrationdate As String
Dim sAccessWorkkeyscompleted As String
Dim sAccessTrainingstatus As String
Dim sAccessTrainingprovider As String
Dim sAccessCredentialattained As String
Dim sAccessEmployedatregistration As String
Dim sAccessEmployed As String
Dim sAccessJobtitle As String
Dim sAccessEmployer As String
Dim sAccessEmployeraddress As String
Dim sAccessEmployercity As String
Dim sAccessEmployerstate As String
Dim sAccessEmployerzip As String
Dim sAccessEmployercontactphone As String
Dim sAccessHours_week As String
Dim sAccessHourlywage As String
Dim sAccessFringebenefits As String
Dim sAccessOccupationatexit As String
Dim sAccessIndustryatexit As String
sAccessCustid = CUSTID
sAccessFullname = FIRSTNAME & " " & LASTNAME
sAccessProgram = PROGRAM & " " & FUNDINGSOURCE
sAccessExitreason = REASONFOREXIT
sAccessRegistrationdate = REGISTRATIONDATE
sAccessWorkkeyscompleted = WORKKEYSCOMPLETED
sAccessTrainingstatus = TRAININGSTATUS
sAccessTrainingprovider = TRAININGPROVIDER
sAccessCredentialattained = CREDENTIALATTAINED
sAccessEmployedatregistration = EMPLOYEDATREGISTRATION
sAccessEmployed = EMPLOYED
sAccessJobtitle = JOBTITLE
sAccessEmployer = EMPLOYER
sAccessEmployeraddress = EMPLOYERADDRESS
sAccessEmployercity = EMPLOYERCITY
sAccessEmployerstate = EMPLOYERSTATE
sAccessEmployerzip = EMPLOYERZIP
sAccessEmployercontactphone = EMPLOYERCONTACTPHONE
sAccessHours_week = HOURS_WEEK
sAccessHourlywage = HOURLYWAGE
sAccessFringebenefits = FRINGEBENEFITS
sAccessOccupationatexit = OCCUPATIONATEXIT
sAccessIndustryatexit = INDUSTRYATEXIT
Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")
Dim sMergeDoc As String
sMergeDoc = Application.CurrentProject.Path & _
"\Request for Exit.dotx"
Wrd.Documents.Add sMergeDoc
Wrd.Visible = True
With Wrd.ActiveDocument.Bookmarks
.Item("AcceessCustid").Range.Text = sAccessCustid
.Item("AccessFullname").Range.Text = sAccessFullname
.Item("AccessProgram").Range.Text = sAccessProgram
.Item("AccessExitreason").Range.Text = sAccessExitreason
.Item("AccessRegistrationdate").Range.Text = sAccessRegistrationdate
.Item("AccessWorkkeyscompleted").Range.Text = sAccessWorkkeyscompleted
.Item("AccessTrainingstatus").Range.Text = sAccessTrainingstatus
.Item("AccessTrainingprovider").Range.Text = sAccessTrainingprovider
.Item("AccessCredentialattained").Range.Text = sAccessCredentialattained
.Item("AccessEmployedatregistration").Range.Text = sAccessEmployedatregistration
.Item("AccessEmployed").Range.Text = sAccessEmployed
.Item("AccessJobtitle").Range.Text = sAccessJobtitle
.Item("AccessEmployer").Range.Text = sAccessEmployer
.Item("AccessEmployeraddress").Range.Text = sAccessEmployeraddress
.Item("AccessEmployercity").Range.Text = sAccessEmployercity
.Item("AccessEmployerstate").Range.Text = sAccessEmployerstate
.Item("AccessEmployerzip").Range.Text = sAccessEmployerzip
.Item("AccessEmployercontactphone").Range.Text = sAccessEmployercontactphone
.Item("AccessHours_week").Range.Text = sAccessHours_week
.Item("AccessHourlywage").Range.Text = sAccessHourlywage
.Item("AccessFringebenefits").Range.Text = sAccessFringebenefits
.Item("AccessOccupationatexit").Range.Text = sAccessOccupationatexit
.Item("AccessIndustryatexit").Range.Text = sAccessIndustryatexit
End With
Set Wrd = Nothing
End Sub
The code executes flawlessy but there are three values that are based off check boxes set as YES/NO (EMPLOYEDATREGISTRATION, EMPLOYED and FRINGEBENEFITS). These values show up as -1 for YES and 0 for NO.
Is there anyway to add code to mine that would allow me to change these values before they are sent to the Word template?