Change check box values of -1 to "YES" and then fill Word template (1 Viewer)

[Kregg]

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?

 

[michaeljryan78]

dim strEMPLOYEDATREGISTRATION as string
 
if isnull(me.EMPLOYEDATREGISTRATION) then
strEMPLOYEDATREGISTRATION = ""
Else
    if me.strEMPLOYEDATREGISTRATION = -1 then
       strEMPLOYEDATREGISTRATION = "Yes"
   Else
      strEMPLOYEDATREGISTRATION = "No"
  End if
End if

 

[Kregg]

dim strEMPLOYEDATREGISTRATION as string
 
if isnull(me.EMPLOYEDATREGISTRATION) then
strEMPLOYEDATREGISTRATION = ""
Else
    if me.strEMPLOYEDATREGISTRATION = -1 then
       strEMPLOYEDATREGISTRATION = "Yes"
   Else
      strEMPLOYEDATREGISTRATION = "No"
  End if
End if

I am slightly new to Access VBA. Would this be an addition to my code or a modification?

If it needs to be added where is the best location for this to be added in my code? Thanks for the quick reply!!!

 

[michaeljryan78]

I see.
I will start in the middle if I may

[COLOR=black][FONT=Verdana]sAccessCredentialattained = CREDENTIALATTAINED
sAccessEmployedatregistration = EMPLOYEDATREGISTRATION
[/FONT][/COLOR]

Maybe:

sAccessEmployedatregistration = EMPLOYEDATREGISTRATION

if isnull(sAccessEmployedatregistration) then
sAccessEmployedatregistration = ""
Else
    if sAccessEmployedatregistration  = "-1" then
       sAccessEmployedatregistration  = "Yes"
   Else
      sAccessEmployedatregistration = "No"
  End if
End if

i think that the variable of sAccessEmployedatregistration is a string so Access is converting -1 to "-1". We just assign a new value.

Setp through the code and when you get to

[COLOR=black][FONT=Verdana]sAccessEmployedatregistration = EMPLOYEDATREGISTRATION
[/FONT][/COLOR]

type ?sAccessEmployedatregistration into the immediate window to see the value. you can also check the datatype using ?vbText(sAccessEmployedatregistration) to see if the variable is truly string text.

 

[Mihail]

sAccessCredentialattained = IIF(CREDENTIALATTAINED,"Yes","No")

 

[michaeljryan78]

sAccessCredentialattained = IIF(CREDENTIALATTAINED,"Yes","No")

Simple. Duh. I should've thought of that!

 

[Kregg]

You are both great!!! Since you were so helpful what if sAccessCredentialattained is null. I keep getting an error message. Is there any way that if the date is null to return "" and if it is filled in to return the actual date?

 

[Mihail]

As far as I know, a Yes/No field can't be null.
If the check boxes are not bound to a field then set the Triple State property to No. This way the check boxes will never be Null.
If your needs require the check boxes to allow null values, then use this:
=IIF(IsNull(CREDENTIALATTAINED),"",IIF(CREDENTIALATTAINED,"Yes","No"))

 

[Kregg]

As far as I know, a Yes/No field can't be null.
If the check boxes are not bound to a field then set the Triple State property to No. This way the check boxes will never be Null.
If your needs require the check boxes to allow null values, then use this:
=IIF(IsNull(CREDENTIALATTAINED),"",IIF(CREDENTIALATTAINED,"Yes","No"))

CREDENTIALATTAINED is actually a date field not a YES/NO....kind of diverted from the topic. Do I need to start a new thread or is it just a simple tweek?

 

[Mihail]

=IIF(IsNull(CREDENTIALATTAINED),"",CREDENTIALATTAINED)
or take a look to NZ function.

 

[Kregg]

=IIF(IsNull(CREDENTIALATTAINED),"",CREDENTIALATTAINED)
or take a look to NZ function.

You guys are the best!!! Thanks!