Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Check box in form

Status
Not open for further replies.
signallt03

signallt03

Technical User
Joined
Mar 14, 2003
Messages
21
Location
US
I have a form with a bunch of different check boxes, each one corresponds with different field in different tables. I would like to be able to generate a report with just the check boxes that are selected. if anyone could help, i would appreciate it.

LT Morrison
 
Sort by date Sort by votes
I have done this just recently. My user has the option of selecting 4 different reports. If the select the custom report (option 4) then they pick the fields they want displayed on the report.

In the On Open event of my report I check to see the options and the check boxes that the user has selected and build my report accordingly. There is also some code in the Detail Section on format event. This report lists the staff for a school. I'll attach the VB code from my report program below. I hope this will help you given that you can't see the report.

-----------------------------------------------------------
Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Forms!frmstaffreports!subStaffReport.Form!Frame7
Case 1 'the phone list report is selected
Me.Text38.Value = Me.StaffLastName & ", " & Me.StaffFirstName

Case 2 ' the summary report is selected
Me.Text38.Value = Me.StaffSalutation & " " & Me.StaffFirstName & " " & Me.StaffLastName
Me.Text69 = Me.SchoolGradeLevel & " " & Me.Description
Case 3 ' the detail report is selected
Me.Text38.Value = Me.StaffSalutation & " " & Me.StaffFirstName & " " & Me.StaffLastName
Me.Text46 = Me.SchoolGradeLevel & " " & Me.Description
Case 4 ' the custom report is selected
If Forms!frmstaffreports!subStaffReport.Form!Check68 = -1 Or _
Forms!frmstaffreports!subStaffReport.Form!Check70 = -1 Or _
Forms!frmstaffreports!subStaffReport.Form!Check72 = -1 Then

'check if all three are checked
If Forms!frmstaffreports!subStaffReport.Form!Check68 = -1 And _
Forms!frmstaffreports!subStaffReport.Form!Check70 = -1 And _
Forms!frmstaffreports!subStaffReport.Form!Check72 = -1 Then
Me.Text38.Value = Me.StaffSalutation & " " & Me.StaffFirstName & " " & Me.StaffLastName
Else
If Forms!frmstaffreports!subStaffReport.Form!Check68 = -1 And _
Forms!frmstaffreports!subStaffReport.Form!Check70 = -1 Then

Me.Text38 = Me.StaffFirstName & " " & Me.StaffLastName

Else
If Forms!frmstaffreports!subStaffReport.Form!Check68 = -1 And _
Forms!frmstaffreports!subStaffReport.Form!Check72 = -1 Then

Me.Text38.Value = Me.StaffSalutation & " " & Me.StaffLastName
Else
If Forms!frmstaffreports!subStaffReport.Form!Check70 = -1 And _
Forms!frmstaffreports!subStaffReport.Form!Check72 = -1 Then

Me.Text38.Value = Me.StaffSalutation & " " & Me.StaffFirstName
Else
If Forms!frmstaffreports!subStaffReport.Form!Check68 = -1 Then

Me.Text38.Value = Me.StaffLastName
Else
If Forms!frmstaffreports!subStaffReport.Form!Check70 = -1 Then

Me.Text38.Value = Me.StaffFirstName
Else
If Forms!frmstaffreports!subStaffReport.Form!Check72 = -1 Then

Me.Text38.Value = Me.StaffSalutation
End If
End If
End If
End If
End If
End If
End If
End If
'check to see if you will base the custom report on the summary report or the
'detailed report. if room or email are checked then need to base on detailed report
If Forms!frmstaffreports!subStaffReport.Form!Check80 = -1 Or _
Forms!frmstaffreports!subStaffReport.Form!Check82 = -1 Then
' base this on the detailed report

' check if function or grade level are checked
If Forms!frmstaffreports!subStaffReport.Form!Check76 = -1 Or _
Forms!frmstaffreports!subStaffReport.Form!Check74 = -1 Then

If Forms!frmstaffreports!subStaffReport.Form!Check76 = -1 And _
Forms!frmstaffreports!subStaffReport.Form!Check74 = -1 Then
Me.Text46.Value = Me.SchoolGradeLevel & " " & Me.Description
Else
If Forms!frmstaffreports!subStaffReport.Form!Check76 = -1 Then
Me.Text46.Value = Me.SchoolGradeLevel
Else
If Forms!frmstaffreports!subStaffReport.Form!Check74 = -1 Then
Me.Text46.Value = Me.Description
End If
End If
End If
End If
Else
'base this on the summary report


If Forms!frmstaffreports!subStaffReport.Form!Check76 = -1 Or _
Forms!frmstaffreports!subStaffReport.Form!Check74 = -1 Then

If Forms!frmstaffreports!subStaffReport.Form!Check76 = -1 And _
Forms!frmstaffreports!subStaffReport.Form!Check74 = -1 Then
Me.Text69.Value = Me.SchoolGradeLevel & " " & Me.Description
Else
If Forms!frmstaffreports!subStaffReport.Form!Check76 = -1 Then
Me.Text69.Value = Me.SchoolGradeLevel
Else
If Forms!frmstaffreports!subStaffReport.Form!Check74 = -1 Then
Me.Text69.Value = Me.Description
End If
End If
End If
End If
End If
End Select
End Sub


Private Sub Report_NoData(Cancel As Integer)
Call InfoMessage(26)
Cancel = True
End Sub

Private Sub Report_Open(Cancel As Integer)
On Error Resume Next

'check if staff codes have been limited

If Forms!fmrstaffreports!subStaffReport.Form!Frame7 = 1 Then
' YOU DON'T NEED TO DO ALL THIS CHECKING - IT'S THE PHONE EXTENSION REPORT
Me.FilterOn = False

Else

If Forms!frmstaffreports!subStaffReport.Form!Frame89 <> 1 Then

Dim varitem As Variant
Dim lst As ListBox
Dim firstbool As Boolean
Dim filterstring As String
Dim firstitemstr As String
Dim otheritemstr As String

firstbool = True

If Forms!frmstaffreports!subStaffReport.Form!Frame89 = 2 Then

'the user selected staff codes to include

firstitemstr = &quot;schoolstaffcodeid = &quot;
otheritemstr = &quot; or &quot; & firstitemstr
Else
'the user selected staff codes to exclude
firstitemstr = &quot;schoolstaffcodeid <> &quot;
otheritemstr = &quot; and &quot; & firstitemstr
End If

'check the list box
Set lst = Forms!frmstaffreports!subStaffReport.Form!List98
If lst.MultiSelect > 0 Then
If lst.ItemsSelected.Count > 0 Then
For Each varitem In lst.ItemsSelected
If firstbool = True Then
filterstring = firstitemstr & lst.ItemData(varitem)
firstbool = False
Else
filterstring = filterstring & otheritemstr & lst.ItemData(varitem)
End If
Next varitem
End If
End If
Me.FilterOn = True
Me.Filter = filterstring
DoCmd.ApplyFilter
Else
Me.FilterOn = False
End If

End If

' set the title for the report

If Forms!frmstaffreports!subStaffReport.Form!Frame7 = 1 Then
'it's the telephone extension report
Me.Label49.Caption = &quot;Telephone Extensions&quot;
Me.OrderBy = &quot;stafflastname, stafffirstname&quot;
Me.OrderByOn = True
Else

' it's not the telephone extensions

If Not IsNull(Forms!frmstaffreports!subStaffReport.Form!Text108) Then
Me.Label49.Caption = Forms!frmstaffreports!subStaffReport.Form!Text108
End If

'evaluate which option button was selected for sorting'

Select Case Forms!frmstaffreports!subStaffReport.Form!Frame49
Case 1
Me.OrderBy = &quot;stafflastname, stafffirstname&quot;
Me.OrderByOn = True
Case 2
Me.OrderByOn = True
Me.OrderBy = &quot;sortpriority, graderank,stafflastname&quot;
End Select

End If

Select Case Forms!frmstaffreports!subStaffReport.Form!Frame7
Case 1 ' the telephone report is selected
Me.Label63.Visible = False ' room number label
Me.Label64.Visible = False ' email label
Me.StaffRoomNumber.Visible = False
Me.Email.Visible = False
Me.Label59.Visible = False
Me.Label62.Visible = False
Me.StaffExtension.Visible = False
Me.Text46.Visible = False
Me.Label65.Visible = False
Me.Label66.Visible = True ' ext label
Me.Text67.Visible = True ' telephone extension
Me.Text69.Visible = False

Case 2 ' the summary report is selected
Me.Label63.Visible = False ' room number label
Me.Label64.Visible = False ' email label
Me.StaffRoomNumber.Visible = False
Me.Email.Visible = False
Me.Label59.Visible = False
Me.Label62.Visible = False
Me.StaffExtension.Visible = False
Me.Text46.Visible = False
Me.Label65.Visible = True
Me.Label66.Visible = True
Me.Text67.Visible = True
Me.Text69.Visible = True

Case 3 ' the detailed report is selected
Me.Label63.Visible = True ' room number label
Me.Label64.Visible = True ' email label
Me.StaffRoomNumber.Visible = True
Me.Email.Visible = True
Me.Label59.Visible = True
Me.Label62.Visible = True
Me.StaffExtension.Visible = True
Me.Text46.Visible = True
Me.Label65.Visible = False
Me.Label66.Visible = False
Me.Text67.Visible = False
Me.Text69.Visible = False

Case 4 ' the custom report is selected

' check if any of the name fields are checked
If Forms!frmstaffreports!subStaffReport.Form!Check68 = -1 Or _
Forms!frmstaffreports!subStaffReport.Form!Check70 = -1 Or _
Forms!frmstaffreports!subStaffReport.Form!Check72 = -1 Then

Me.Label61.Visible = True
Me.Text38.Visible = True

Else

Me.Label61.Visible = False
Me.Text38.Visible = False
End If

'check to see if you will base the custom report on the summary report or the
'detailed report. if room or email are checked then need to base on detailed report
If Forms!frmstaffreports!subStaffReport.Form!Check80 = -1 Or _
Forms!frmstaffreports!subStaffReport.Form!Check82 = -1 Then
' base this on the detailed report
'check if room number is checked
Me.Label65.Visible = False
Me.Label66.Visible = False
Me.Text67.Visible = False
Me.Text69.Visible = False
If Forms!frmstaffreports!subStaffReport.Form!Check80 = -1 Then
Me.StaffRoomNumber.Visible = True
Me.Label63.Visible = True
Else
Me.StaffRoomNumber.Visible = False
Me.Label63.Visible = False
End If
'check if email is checked
If Forms!frmstaffreports!subStaffReport.Form!Check82 = -1 Then
Me.Email.Visible = True
Me.Label64.Visible = True
Else
Me.Email.Visible = False
Me.Label64.Visible = False
End If
'check if extension is checked
If Forms!frmstaffreports!subStaffReport.Form!Check78 = -1 Then
Me.StaffExtension.Visible = True
Me.Label62.Visible = True
Else
Me.StaffExtension.Visible = False
Me.Label62.Visible = False
End If
' check if function or grade level are checked
If Forms!frmstaffreports!subStaffReport.Form!Check76 = -1 Or _
Forms!frmstaffreports!subStaffReport.Form!Check74 = -1 Then
Me.Text46.Visible = True
Me.Label59.Visible = True
Else
Me.Text46.Visible = False
Me.Label59.Visible = False
End If
Else
'base this on the summary report
Me.Text46.Visible = False
Me.Label59.Visible = False
Me.StaffExtension.Visible = False
Me.Label62.Visible = False
Me.Email.Visible = False
Me.Label64.Visible = False
Me.Label63.Visible = False
Me.StaffRoomNumber.Visible = False
If Forms!frmstaffreports!subStaffReport.Form!Check78 = -1 Then
Me.Label66.Visible = True
Me.Text67.Visible = True
Else
Me.Label66.Visible = False
Me.Text67.Visible = False
End If
If Forms!frmstaffreports!subStaffReport.Form!Check76 = -1 Or _
Forms!frmstaffreports!subStaffReport.Form!Check74 = -1 Then
Me.Text69.Visible = True
Me.Label65.Visible = True
Else
Me.Text69.Visible = False
Me.Label65.Visible = False
End If
End If
End Select
End Sub
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
1K
Kennelbloke
Kennelbloke
TrekBiker
  • Locked
  • Question Question
Form Data Not Updating
Replies
6
Views
1K
dhookom
dhookom
monkeysee
  • Locked
  • Question Question
Combo Box - Limit results
Replies
1
Views
669
dhookom
dhookom
TrekBiker
  • Locked
  • Question Question
Forcing Form Updates
Replies
2
Views
810
TrekBiker
TrekBiker
breezett93
  • Locked
  • Question Question
Function that find all controls on a form and puts their tab index in numerical order
Replies
19
Views
2K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top