Can Checkbox values be text?

[junglej815]

Hello,

I have a form in Access that has 4 checkboxes:

Awaiting Action
Awaiting Material
Additional Work Required
Completed

Within my table I have a field name titled STATUS. When the checkbox for "Completed" is ticked/clicked, that particular record then has a value of "3" in the STATUS Field column.

Can this value for checkboxes only be numbers? Or is there a way to change it from "3" to "Complete"?

Thanks,
Joe

[SamT]

CheckBox Values are only True or False


Private Sub chbxComplete_Change()
If chbxComplete Then Record = "Complete"
Else: Record = ""
End Sub

[junglej815]

Thanks for responding SamT. for some reason it doesn't seem to be working. When I tick the completed checkbox it's still displaying as Option Value 3 instead of displaying as Complete on the table and query/report. I guess I have the option to just on in manually and change the 0 to Awaiting Action or 1 to Additional Work Required or 2 to Awaiting Material or 3 to Completed. I was just hoping to have this done for me by clicking on the checkbox.

[SamT]

Thanks for responding SamT. for some reason it doesn't seem to be working. When I tick the completed checkbox it's still displaying as Option Value 3 instead of displaying as Complete on the table and query/report. I guess I have the option to just on in manually and change the 0 to Awaiting Action or 1 to Additional Work Required or 2 to Awaiting Material or 3 to Completed. I was just hoping to have this done for me by clicking on the checkbox.
Well there are still some details missing.

Where are the Option buttons located?
What toolbar did you find them them on.?
What code are you using now?

In the VBA editor, Click inside the code, then press Ctrl+A , then Ctrl+C, then in your message to us, Click below your message, then click on the Pound sign on the Post editor menu and press Ctrl+V.

These actions will copy the code in VBA, insert CODE Tags into your message and paste the code between the CODE Tags so it will be nicely formatted when you post the message.

[jonh]

Add the values to a lookup table. Join the two tables in a query. It's basic database design.

Checkboxes are true/false until you put them in a frame. Then they have an 'option value'. (numeric)

[junglej815]

Thanks guys, sorry for not having enough information first time around. Let me just say that I am not the original designer of this form and database so just trying to figure out what was done by the person who did design it. Upon inspecting the form it looks like the 4 checkboxes on the form are within a frame and the control source for the frame is the STATUS field. Like I mentioned before the check boxes are as follows:

1. Awaiting Action - Option Value: 0 ( Named Check313 )
2. Additional Work Required - Option Value: 1 ( Named Check315 )
3. Awaiting Material - Option Value: 2 ( Named Check317 )
4. Completed - Option Value: 3 ( Named Check319 )

In the STATUS field column on the main table is where these numbers show that I would like to have the words as opposed to the numbers if possible. Below is all of the coding...I did omit some information such as email addresses and names...

Any help would be greatly appreciated.



Private Sub ADDCHECK_AfterUpdate()
Me.ADDID = fOSUserName()
Me.ADDDATE = Date
Me.ADDTIME = Time
End Sub
Private Sub AWAITINGCHECK_AfterUpdate()
End Sub

Private Sub Command_13181_Click()
' Tech Create Email
Set o = CreateObject("Outlook.Application")
Set m = o.CreateItem(0)
m.SentOnBehalfOfName = "Name"
m.BCC = "name@email.com; "
m.Subject = "# " & Me.WORKORDER.Value & " - Work Request has been created"
m.Body = "Work Request # " & Me.WORKORDER.Value & Chr(13) & "Date of Request: " & Me.DATEREQUESTED.Value & Chr(13) & "Requested By: " & Me.REQUESTOR.Value & Chr(13) & "Phone Number:" & Me.PHONE.Value & Chr(13) & "Location: " & Me.LOCATION.Value & Chr(13) & "Description: " & Me.DESCRIPTION.Value & Chr(13) & Chr(13) & "Technician: " & Chr(13) & "Time to Charge: " & Chr(13) & "Comments: " & Chr(13) & Chr(13) & "ID: " & Me.STATUSID.Value & Chr(13) & Chr(13) & "FSC" & Chr(13) & Chr(13) & "Please visit the website for more resources and information." & Chr(13) & Chr(13) & "Please check to make sure your information in the system is correct and if changes are needed, contact your department UAA requestor."

' There can be multiple of the above
m.Display ' display shows email and the user has to press send button
Set m = Nothing
Set o = Nothing
End Sub
Private Sub Command_13182_Click()
' Tech Complete Email
Set o = CreateObject("Outlook.Application")
Set m = o.CreateItem(0)
m.SentOnBehalfOfName = "name"
m.BCC = "name@email.com; "
m.Subject = "# " & Me.WORKORDER.Value & " - Work Request has been completed"
m.Body = "Completed on: " & Me.ENDDATE.Value & Chr(13) & "Techician: " & Me.TECHNICIAN.Value & Chr(13) & "Technician Comments: " & Me.TECHNICIAN_COMMENTS.Value & Chr(13) & Chr(13) & "Original work request: " & Chr(13) & Chr(13) & "Work Request # " & Me.WORKORDER.Value & Chr(13) & "Date of Request: " & Me.DATEREQUESTED.Value & Chr(13) & "Requested By: " & Me.REQUESTOR.Value & Chr(13) & "Phone Number:" & Me.PHONE.Value & Chr(13) & "Location: " & Me.LOCATION.Value & Chr(13) & "Description: " & Me.DESCRIPTION.Value & Chr(13) & Chr(13) & "ID: " & Me.STATUSID.Value & Chr(13) & "FSC" & Chr(13) & Chr(13) & "Please visit the website for more resources and information." & Chr(13) & Chr(13) & "Please check to make sure your information in the system is correct and if changes are needed, contact your department UAA requestor."

' There can be multiple of the above
m.Display ' display shows email and the user has to press send button
Set m = Nothing
Set o = Nothing


End Sub
Private Sub Command26635_Click()

Dim objWord As Object

' Open Microsoft Word using automation
Set objWord = New Word.Application

objWord.Documents.ADD "G:\location.dot"
objWord.Visible = True

' The following code is generated from your Access form while it is
' manipulating an instance of Word.
' So, while it looks somewhat like VBA, it's really Word VBA.

If objWord.ActiveDocument.Bookmarks.Exists("WorkOrder") = True Then
objWord.ActiveDocument.Bookmarks("WorkOrder").Range.Text = Me!WORKORDER
End If
If objWord.ActiveDocument.Bookmarks.Exists("DATEREQUESTED") = True Then
objWord.ActiveDocument.Bookmarks("DATEREQUESTED").Range.Text = Me!DATEREQUESTED
End If

If objWord.ActiveDocument.Bookmarks.Exists("NAME") = True Then
objWord.ActiveDocument.Bookmarks("NAME").Range.Text = Me!REQUESTOR
End If
If objWord.ActiveDocument.Bookmarks.Exists("PHONE") = True Then
objWord.ActiveDocument.Bookmarks("PHONE").Range.Text = Me!PHONE
End If
If objWord.ActiveDocument.Bookmarks.Exists("LOCATION") = True Then
objWord.ActiveDocument.Bookmarks("LOCATION").Range.Text = Me!LOCATION
End If
If objWord.ActiveDocument.Bookmarks.Exists("DESCRIPTION") = True Then
objWord.ActiveDocument.Bookmarks("DESCRIPTION").Range.Text = Me!DESCRIPTION
End If

If objWord.ActiveDocument.Bookmarks.Exists("CRAFT") = True Then
objWord.ActiveDocument.Bookmarks("CRAFT").Range.Text = Me!CRAFT
End If
If objWord.ActiveDocument.Bookmarks.Exists("NOTES") = True Then
objWord.ActiveDocument.Bookmarks("NOTES").Range.Text = Me!NOTES
End If
If objWord.ActiveDocument.Bookmarks.Exists("PRIORITY") = True Then
objWord.ActiveDocument.Bookmarks("PRIORITY").Range.Text = Me!PRIORITY
End If
' If objWord.ActiveDocument.Bookmarks.Exists("NOTES") = True Then
' objWord.ActiveDocument.Bookmarks("NOTES").Range.Text = Me!NOTES
' End If
' If objWord.ActiveDocument.Bookmarks.Exists("TECHNICIAN") = True Then
' objWord.ActiveDocument.Bookmarks("TECHNICIAN").Range.Text = Me!TECHNICIAN
' End If

' If objWord.ActiveDocument.Bookmarks.Exists("TECHNICIANCOMMENTS") = True Then
' objWord.ActiveDocument.Bookmarks("TECHNICIANCOMMENTS").Range.Text = Me!TECHNICIANCOMMENTS
' End If
If objWord.ActiveDocument.Bookmarks.Exists("NETWORKID") = True Then
objWord.ActiveDocument.Bookmarks("NETWORKID").Range.Text = Me!NETWORKID
End If
If objWord.ActiveDocument.Bookmarks.Exists("CREATEDDATE") = True Then
objWord.ActiveDocument.Bookmarks("CREATEDDATE").Range.Text = Me!CREATED_DATE
End If

' ... continue reading data from form and inserting into bookmark
End Sub
Private Sub COMPLETECHECK_AfterUpdate()
Me.COMPLETEID = fOSUserName()
Me.COMPLETEDATE = Date
Me.COMPLETETIME = Time
End Sub
Private Sub Command27183_Click()
' Complete Email
Me.FOLLOWUP.Value = 1
Set o = CreateObject("Outlook.Application")
Set m = o.CreateItem(0)
m.SentOnBehalfOfName = "Name"
m.to = Me.REQUESTOR.Value 'The distribution list/name goes here
If LOCATION = "Name" And LOCATIONDETAIL = "Name" Then
m.to = "Name" 'If Security Dept is selected - email Security DL
m.cc = name@email.com 'If Kids Center is selected - email Unit Address


ElseIf LOCATION = "KIDSCENTER" And LOCATIONDETAIL = "Name" Then
m.to = name@email.com 'If CAFE Dept is selected - email Unit Address
m.cc = name@email.com 'If Kids Center is selected - email Name and KC Address

ElseIf LOCATIONDETAIL = "Name" Then
m.to = "Name" 'If Name is selected - email Name DL

ElseIf LOCATION = "KIDSCENTER" Then
m.to = Me.REQUESTOR.Value 'The distribution list/name goes here
m.cc = name@email.com 'If Kids Center is selected - email Unit Address

ElseIf LOCATIONDETAIL = "Name" Then
m.to = name@email.com 'If CAFE Dept is selected - email Unit Address


End If

m.BCC = " "
m.Subject = "# " & Me.WORKORDER.Value & " - Work Request has been completed"
m.Body = "The following work request has been completed: " & Chr(13) & Chr(13) & "Work Request # " & Me.WORKORDER.Value & Chr(13) & "Date of Request: " & Me.DATEREQUESTED.Value & Chr(13) & "Requested By: " & Me.REQUESTOR.Value & Chr(13) & "Phone Number:" & Me.PHONE.Value & Chr(13) & "Location: " & Me.LOCATION.Value & Chr(13) & "Description: " & Me.DESCRIPTION.Value & Chr(13) & Chr(13) & Chr(13) & "Completed on: " & Me.ENDDATE.Value & Chr(13) & "ID: " & Me.STATUSID.Value & Chr(13) & Chr(13) & Chr(13) & "If you have any questions or concerns, please contact us at name@email.com or ext 55555." & Chr(13) & Chr(13) & "FSC" & Chr(13) & "Please visit the website for more resources and information." & Chr(13) & Chr(13) & "Please make sure your information in the system is correct and if changes are needed, contact your department UAA requestor."

' There can be multiple of the above
m.Display ' display shows email and the user has to press send button

Set m = Nothing
Set o = Nothing
End Sub

Private Sub Command27184_Click()
' Create Email
'Check the Create Email Box
Me.EMAILSENT.Value = 1
Set o = CreateObject("Outlook.Application")
Set m = o.CreateItem(0)
m.SentOnBehalfOfName = "FSC (Name)"
m.to = Me.REQUESTOR.Value 'The distribution list/name goes here
If LOCATION = "KIDSCENTER" And LOCATIONDETAIL = "SECURITY" Then
m.to = "Name" 'If Security Dept is selected - email Security DL
m.cc = name@email.com 'If Kids Center is selected - email KC Address

ElseIf LOCATION = "KIDSCENTER" And LOCATIONDETAIL = "Name" Then
m.to = Name@email.com 'If CAFE Dept is selected - email Unit Address
m.cc = Name@email.com 'If Kids Center is selected - email Denise and KC Address
ElseIf LOCATIONDETAIL = "SECURITY" Then
m.to = "Name" 'If Security Dept is selected - email Security DL
ElseIf LOCATION = "KIDSCENTER" Then
m.to = Me.REQUESTOR.Value 'The distribution list/name goes here
m.cc = name@email.com 'If Kids Center is selected - email Unit Address
ElseIf LOCATIONDETAIL = "CAFE/FLIK" Then
m.to = Name@email.com 'If CAFE Dept is selected - email Unit Address

End If


m.BCC = " "
m.Subject = "# " & Me.WORKORDER.Value & " - Work request has been created"
m.Body = "Thank you for contacting the Name." & Chr(13) & Chr(13) & "The following work request has been created: " & Chr(13) & Chr(13) & "Work Request # " & Me.WORKORDER.Value & Chr(13) & "Date of Request: " & Me.DATEREQUESTED.Value & Chr(13) & "Requested By: " & Me.REQUESTOR.Value & Chr(13) & "Phone Number: " & Me.PHONE.Value & Chr(13) & "Location: " & Me.LOCATION.Value & Chr(13) & "Description: " & Me.DESCRIPTION.Value & Chr(13) & "ID: " & Me.NETWORKID.Value & Chr(13) & Chr(13) & "If you have any questions or concerns, please contact us at name@email.com or ext 55555 & Chr(13) & Chr(13) & "FSC" & Chr(13) & "Please visit the website for more resources and information." & Chr(13) & Chr(13) & "Please make sure your information in the system is correct and if changes are needed, contact your department UAA requestor."

' There can be multiple of the above
m.Display ' display shows email and the user has to press send button

Set m = Nothing
Set o = Nothing
End Sub
Private Sub CREATED_DATE_BeforeUpdate(Cancel As Integer)

End Sub
Private Sub CUSTOMERREQUEST_AfterUpdate()
Me![CUSTOMERREQUEST] = UCase([CUSTOMERREQUEST])
End Sub

Private Sub FOLLOWUP_AfterUpdate()
Me.FOLLOWID = fOSUserName()
Me.FOLLOWDATE = Date
Me.FOLLOWTIME = Time
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
'Set the Created_Date field in the Customers table to the current system
'date when a new record is created
CREATED_DATE = Now()
End Sub

Private Sub Frame310_AfterUpdate()
Me.STATUSID = fOSUserName()
Me.STATUSDATE = Date
Me.STATUSTIME = Time
End Sub
Private Sub TECHNICIAN_AfterUpdate()
Me![TECHNICIAN] = UCase([TECHNICIAN])
End Sub

Private Sub Text17008_AfterUpdate()
Me.UserNameTextBoxName = fOSUserName()
End Sub

Private Sub Print_Record_Click()
On Error GoTo Err_Print_Record_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection
Exit_Print_Record_Click:
Exit Sub
Err_Print_Record_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Print_Record_Click

End Sub

[InLaNoche]

Why not remove the checkboxes, and instead use a ComboBox? it seems that you can only select one of the options. The combobox can have the text Options as a list you define when creating the ComboBox (or as stated above, make a ref. table to store these values), then have it's control source be the field you want the value to end up in. I skimmed the code, but did not find reference to the checkbox controls, so I'm not sure they matter to the code...

[HiTechCoach]

The Option Group is working correctly.

I agree a combo box is probably a better choice.

You should have a lookup table where the primary key is the 0,1,2.... along with the description for each

It does not matter if you using a Option Group or a combo box (using the lookup table for the row source), you will still use the lookup table in your queries to get pull in the Description text. You still should store the numeric vale int he table.

I took a look at your code and you are making form reference not using a recordset. In that case a Combo box would make it easier.

[junglej815]

Thank you all for the input. I'll have to take a look at it and see if I can figure anything out.