On Click OPen a Form Linked and Filtered (field is Checkbox???)

[maggiemuffins]

Hi there - I am fairly new to VBA. I am building an Access DB for work and have hit a brick wall.

On one form (Departmental Approval) there is a Combo Box with a value list of Departments, and below that a button that opens a second Form (Item Entry Form), filtered to show the records pertaining to the chosen Department.

The problem I have is,I want it to show ONLY the records that have not been Approved. To show approval there is a check box that must be "checked" - and I only want to show those that have an "unchecked" box.

Here is the basic code to open the linked forms:


Private Sub open_approval_button_Click()
On Error GoTo Err_open_approval_button_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Item Entry Form"

stLinkCriteria = "[Resp_Dept]=" & "'" & Me![Dept] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_open_approval_button_Click:
Exit Sub

Err_open_approval_button_Click:
MsgBox Err.Description
Resume Exit_open_approval_button_Click

End Sub


I am not sure where I would add code to filter the records further...

I have the feeling I am going about this all the wrong way -
any advice would be appreciated.

Thanks

 

[M8KWR]

In your code do an if statement, something like

If the checkbox = -1 (so is ticked) then

stLinkCriteria = YOUR CODE HERE

else (if the checkbox is not ticked)

stLinkCriteria = YOUR CODE HERE

end if

Hope that helps.... if you let me know the control name of your checkbox etc, i can do the code better for you, but in a rush at the moment.

 

[maggiemuffins]

Thanks -

My Checkbox is called Click_here_to_sign

 

[M8KWR]

Sorry forgot to ask you for your field that contains the Approved or not information, but you should be able to see where i have attempted to put this information in for you

Private Sub open_approval_button_Click()
On Error GoTo Err_open_approval_button_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Item Entry Form"
    
    If Click_here_to_sign = -1 then

    stLinkCriteria = "[Resp_Dept]=" & "'" & Me![Dept] & "'" & " AND [APPROVAL]=APPROVED"

    Else

    stLinkCriteria = "[Resp_Dept]=" & "'" & Me![Dept] & "'" & " AND [APPROVAL]= NOT APPROVED"


    End If

    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
Exit_open_approval_button_Click:
    Exit Sub

Err_open_approval_button_Click:
    MsgBox Err.Description
    Resume Exit_open_approval_button_Click
    
End Sub

Hope this helps