Buttons Displaying data and Click to filter

[keiath]

Hi everyone

I have a number of text boxes that displays data, and are arranged in this order "Not Started" - "In Progress" - "Completed"

Above this I have a button that is the lead accountant (Assigned to), and when pressed it filters just that persons records



What I would like to do is to have all the data as buttons so the TextBox displaying "Not Started" becomes a button (that also displays the Number of not started records) and when clicked it filters to all the "Not started" records to that person.

Does that make sense and can it be done?

Thanks in advance

[June7]

Use VBA to construct filter criteria string and set the form Filter and FilterOn properties.

So in the Click event of each button, something like (use your field/control names as appropriate):

Me.Filter = "LeadID = " & Me.LeadID & " AND Status='Not Started'"
Me.FilterOn = True

Wait, not understanding something. You have 3 fields for status info?

Is this form in Single, Continuous, or Datasheet view?

[keiath]

Use VBA to construct filter criteria string and set the form Filter and FilterOn properties.

So in the Click event of each button, something like (use your field/control names as appropriate):

Me.Filter = "LeadID = " & Me.LeadID & " AND Status='Not Started'"
Me.FilterOn = True

Wait, not understanding something. You have 3 fields for status info?

Is this form in Single, Continuous, or Datasheet view?

Its a query based number, each box contains the 'Total number of accounts' - 'Not Started' - 'In Progress' - 'Completed' for each person for the accounts they are responsible for

As I said i want them to be buttons at the moment I have 1 button (Macro) that filters all the records that belong to that person, now i would like to break that down further So instead of all the records if that person clicks on 'Not Started' it will filter just the not started records and the button its self needs to display the number that is currently show in a text box

[keiath]

See Central section of numbers

[June7]

Code can set the Caption property of button. However, this won't work well in Continuous or Datasheet view because property settings are reflected in ALL instances of the control.

[keiath]

Not sure if this helps but each box has this code to fetch the data

=DLookUp("[CountOfAsigned To]","Count Not Started","[Asigned To]='Maryanne'")

Not i just want to convert to a button that filters (this is so the user doesnt have to click though all her records to find only the records 'Not Started')

[June7]

Doesn't change the issue if form is Continuous or Datasheet. If you are talking about the textboxes at bottom of the image, does not look like Continuous or Datasheet.

I presume the value could change as data is edited so maybe code in form Current or AfterUpdate event like:

Me.btnMaryanne.Caption = DLookUp("[CountOfAsigned To]","Count Not Started","[Asigned To]='Maryanne'")

[keiath]

Doesn't change the issue if form is Continuous or Datasheet. If you are talking about the textboxes at bottom of the image, does not look like Continuous or Datasheet.

I presume the value could change as data is edited so maybe code in form Current or AfterUpdate event like:

Me.btnMaryanne.Caption = DLookUp("[CountOfAsigned To]","Count Not Started","[Asigned To]='Maryanne'")

Its the Data in the middle of the screen that is colour coded 'red,amber,green' and yes Data Changes as 'Status' or 'Assigned to' is Changed. So once a 'Not Started' Status is changed to 'In Progress' the numbers change accordingly

The idea here is this apart from telling us whats out standing, the Assigned to allows the Lead accountant to reassign a record to another person (for queries relating to the accounts) once that person has answered he or she then reassigned the record back to the lead accountant.

This is why I want more filtering so if a assistant only has 3 records to deal with they can go directly to that record according to its urgency (as color code dictates)

[June7]

Okay, give my suggestion a try.

[keiath]

Couldnt seem to get that working right, However what I did do was create a macro that works as it filters only the records in the 'Not Started' for Maryanne. That works great,
All I need now is for the button to display the actual number of 'Not Started'

[June7]

I don't use macros and don't know if they can modify control properties.

Why didn't it work? What happened - error message, wrong results, nothing?

[keiath]

Nothing at all when i create the button and I assume you then go to the 'On Click' and enter that code? it only shows 1 record

[June7]

I suggested the form Current and/or AfterUpdate event for code to set the button caption.

Use the button click event to initiate the filter action.

[keiath]

Sorry I cant work out what your saying, I have however done it another way bit messy, but it works, by using a macro on the button, them making is transparant and placing it over the txt box so you can still see the number of record that are not started and when you click on it, it filters all the records for Maryanne that have not been started.

[June7]

Actually, I would say that is nicer than trying to modify button property. Congratulates, wish I'd thought of that. I forget about layering controls.