Script to show/hide records in FORM Drop Down

[travismp]

tbl_COMPANY (lists all of my company information per client)

[CompanyName] is the name of the field which lists the company name.

[Active] is a Yes/No field which will be checked if a company is currently active.

qry_COMPANY_ALL will show all companies and if they are active.
frm_COMPANY_ALL master form which will show all information on one form and several subforms based on a drop down (unbound drop down field) tied to [CompanyName] field from qry_COMPANY_ALL.

So I open my form and see no data, select any company from the drop down and then it will load the data for that company.

I want to add a checkbox to my form and call it [Check_Active]. It would be blank when I open the form and IF I check that box any [CompanyName] which are NOT [Active] will NOT be available in the drop down. When I take the check mark out of the check box then all companies would be available once again.

So basically I want to filter out some pre-populated data from a drop down if a box is selected.

Does anyone have a sample of something similar that I can start with?

 

[PNGBill]

You should be able to do this at least two ways.

1. a criteria on qry_COMPANY_ALL with IIf linked to the object in your form with DLookup.

2. Use a Command Button on your form that has an on click event to filter your records for a field. This should be the easiest.
Rather then a tick box just a command button and it can be setup to read "Filter ???" and when clicked it a. filters the records and b. changes the caption to "Show all records" and when cliked a 2nd time, all records will appear.

 

[travismp]

You should be able to do this at least two ways.

1. a criteria on qry_COMPANY_ALL with IIf linked to the object in your form with DLookup.

2. Use a Command Button on your form that has an on click event to filter your records for a field. This should be the easiest.
Rather then a tick box just a command button and it can be setup to read "Filter ???" and when clicked it a. filters the records and b. changes the caption to "Show all records" and when cliked a 2nd time, all records will appear.

OK, so option 2 could be down with information in a drop down field too?

 

[PNGBill]

Here is the code to filter or not filter on one command button

Private Sub CmdFilterLateFeesOnlyUnpaid_Click()
On Error GoTo Err_CmdFilterLateFeesOnlyUnpaid_Click

If Me.CmdFilterLateFeesOnlyUnpaid.Caption = "Click to Remove Late Fees Only Loans and Resigned Employees" Then
Me.Filter = "EmpFinished =No AND NetLoanBalance >0"
Me.FilterOn = True
Me.CmdFilterLateFeesOnlyUnpaid.Caption = "Click to Include Late Fees Only Loans but Exclude Resigned Employees"
Else
Me.Filter = "EmpFinished =No"
Me.FilterOn = True
Me.CmdFilterLateFeesOnlyUnpaid.Caption = "Click to Remove Late Fees Only Loans and Resigned Employees"
End If

Exit_CmdFilterLateFeesOnlyUnpaid_Click:
Exit Sub

Err_CmdFilterLateFeesOnlyUnpaid_Click:
MsgBox Err.Description
Resume Exit_CmdFilterLateFeesOnlyUnpaid_Click

End Sub

 

[PNGBill]

Create a Command button on your form with the wizard - say make it a filter.

Then go to properties, event and see the code. Remark out the one line the wizard put in and add something like the above to suit your names.

 

[travismp]

Great Thank you Bill.

I will spend the evening doing BOTH and trying to get each one to work. It will be a good learning tool. Thank you again, I may have questions but I will see how far I can get with the info you have provided.

THANK YOU SIR!

 

[PNGBill]

In my example I actually check for two fields
Me.Filter = "EmpFinished =No AND NetLoanBalance >0"

you may use Me.Filter = "yourtextbox = ??"