How not to display records

[rexb]

I've a table with records. some records are active and some are not. What I want to do is when I open the form and I choose to deactivate the record it will not show up the next time I open the form but will not delete the record because I will need it for reporting purposes.

Can somebody show me how to code this. Thanks in advance.

 

[Vassago]

You'll want to base your source for the form on a query that only shows active records instead of the table of all records. Create a query based on that table with only records where your "active" field is true. Then, on the form, instead of choosing the table as your record source, use the query, that already includes all fields from the table.

Hope that makes sense.

 

[WIS]

I've a table with records. some records are active and some are not. What I want to do is when I open the form and I choose to deactivate the record it will not show up the next time I open the form but will not delete the record because I will need it for reporting purposes.

Can somebody show me how to code this. Thanks in advance.

What Vassago has said is spot on.

However, you can go a step further and have an unbound cbo box on the form that shows "Active" as the default, but you can also show "Inactive" and "All". If you need to make an "Inactive" back to "Active" you can do it on the form.

 

[rexb]

What Vassago has said is spot on.

However, you can go a step further and have an unbound cbo box on the form that shows "Active" as the default, but you can also show "Inactive" and "All". If you need to make an "Inactive" back to "Active" you can do it on the form.

Can you help me with the code? thanks

 

[WIS]

Can you help me with the code? thanks

Create an unbound cbo box on the form with the following:
ColumnCount: 1
RowSourceType: Value List
Row Source: "All";"Active";"Inactive"
Default Value: "Active"

Set up a qry with all the reqd flds for the form.

To open so that only Active shows:
OnOpen event or put it in a cmd button to open the form:

strSQL = "(qryMyQuery.Active) = True"
DoCmd.OpenForm "frmMyForm", acNormal, "qryMyQuery", strSQL, acEdit, acNormal


In the AfterUpdate event of the cbo box put:

Me.Refresh
EventFilled

EventFilled is a procedure as follows:

Sub EventFilled()
If Me.cboMyCbo = "All" Then
DoCmd.RunCommand acCmdRemoveFilterSort

ElseIf Me.cboMyCbo = "Active" Then
strSQL = "(qryMyQuery.Active) = True"
DoCmd.OpenForm "frmMyForm", acNormal, "qryMyQuery ", strSQL, acEdit, acNormal

ElseIf Me.cboMyCbo = "Inactive" Then
strSQL = "(qryMyQuery .Active) = False"
DoCmd.OpenForm "frmMyForm", acNormal, "qryMyQuery ", strSQL, acEdit, acNormal
End If
End Sub

Change qryMyQuery, frmMyForm and cboMyCbo to the names you are using.

Don't forget to Dim strSQL

Good Luck