Using Check Boxes to run a query 1

[isthisthingon]

I am hoping there is someone who can help me with this.

I am using MS Access 2000 and I have a table of employees and each employee has a status. There are about 8 different status's such as active, terminated, resigned, inactive, probation, etc. I wish to run a query based on an end user checking off which status's they would like to include in the results. I would like to use a form with each status listed and a check box next to it with a command button to execute the query. What is the best (easiest) way to do this? Thanks for any and all help!!!!

 

[snyperx3]

You can build the sql statement on the button press in your VBA code.

this will get you started

Dim strSQL as String
Dim NumOfStatus as Integer

NumOfStatus = 0
strSQL = "SELECT * FROM tblEmployees WHERE Status = '"
If checkbox1 then
     If NumOfStatus > 0 Then
         strSQL = strSQL & " OR Status = '"
     EndIf
     strSQL = strSQL & "Active'"
     NumOfStatus = NumOfStatus + 1
endif
If checkbox2 then
     If NumOfStatus > 0 Then
         strSQL = strSQL & " OR Status = '"
     EndIf
     strSQL = strSQL & "Terminated'"
     NumOfStatus = NumOfStatus + 1
endif

'And so on...till the end then

strSQL = strSQL & ";"

Then you can execute that SQL statement or set a query definition to that SQL statement...or whatever you wanted to do. =]

Make sure you not the single quote after each status - "Active'"

Also, if the captions of the checkboxes are only the exact status representation, this can be formed into a loop.

-Pete

 

[Remou]

Or perhaps add the employee status code to the tag property of the control:

Private Sub cmdPrint_Click()
'ctl tag = Employee status code ie 1,2,3 etc
Dim ctl As Control
Dim strInc
For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl Then
            strInc = strInc & ctl.Tag & ","
        End If
    End If
Next
strInc = Left(strInc, Len(strInc) - 1)
DoCmd.OpenReport "rptReport", acViewPreview, , "Status In (" & strInc & ")"
End Sub

Private Sub Form_Load()
'Default value = false
For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        ctl.Value = False
    End If
Next
End Sub

 

[isthisthingon]

Perfect Pete, now how do I execute that SQL statement and turn it into a report? Thanks!!!!!!

 

[PHV]

Have a look at the 4th argument of the DoCmd.OpenReport method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snyperx3]

You can create a query with the information you need, and name it whatever youd like...lets say qryStatus. and create your report based on it...lets say rptStatus. We will change the definition of the query when we press the button.

this code will go immediately after the last statement i had in my code.

Dim dbs as DAO.Database
Dim qdf as DAO.QueryDef

Set dbs = CurrentDB()
Set qdf = dbs.QueryDefs("qryStatus")

qdf.SQL = strSQL
DoCmd.OpenReport "rptStatus",acViewPreview

You will need the Microsoft DAO Object Library added in your references.

-Pete

 

[isthisthingon]

Sorry to be such a PIA!!! Seems the only thing left for me to do is figure out how to replace tblemployees with the correct table name of "Personnel Information" in the following string strSQL = "SELECT * FROM tblEmployees WHERE Status = '" I put the name in and it tries to read from a table called information. I tried to put it in single quotes to no avail. Thanks SOOO much!!!

 

[PHV]

strSQL = "SELECT * FROM [!][[/!]Personnel Information[!]][/!] WHERE Status = '"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[isthisthingon]

I learn something every day!! Thanks for making this painless. Thanks Snyperx3!!!

 

[snyperx3]

No problem. Glad to help. =]

-Pete

 

[isthisthingon]

Thanks for your previous help. I have one more follow up question. I want to create a form to log meeting attendance. I'd like a listing of employees, a list of meeting types and a date. Is there a way to create a form that will allow a combobox listing the employees and allow users to select multiple employees using the ctrl button and then click submit and have entries made to the table logging the meeting type, employee name and date specified? Thanks again!!!

 

[snyperx3]

Maybe a listbox instead of a combo box.

-Pete

 

[isthisthingon]

Well, I know how to implement the list box with the multiple selections, but not transfer all selected names to a table. How do I create code to create a row in the new table for each of the names selected?

 

[snyperx3]

sorry i cannot test this right now as i am at home, but something like this should work. variable names are blatant guesses obviously.

Dim dbs as DAO.Database
Dim rst as DAO.Recordset
Dim i as Integer

Set dbs = CurrentDB()
Set rst = dbs.OpenRecordset("tblMeeting")

For i=0 to lstEmployees.ListCount-1
    If lstEmployees.Selected(i) Then
        rst.AddNew
        rst![Employee] = lstEmployees.ItemData(i)
        rst![MeetingType] = lstMeetingType.Value
        rst![Date] = txtDate.Value
        rst.Update
    End If
Next i

-Pete