Creating a custom Filter Button (1 Viewer)

[kevnaff]

I have a field within a table called GroupID. Currently it has the values TEST01, TEST02 and TEST 03.

I want to be able to filter the form/records by the GroupID that is within the box on that given record.

So in the above example, if I was to click the button, I am hoping it will filter the records and bring up all records with TEST01 in the GroupID field.

What would be the easiest way to go about doing this?

Thanks

Adam

 

[arnelgp]

Add code to AfterUpdate event of the combobox
tò filter your form:

Private sub combo_afterupdate()
Me.filter ="groupid='" & [combo] & "'"
Me.filteron=-1
end sub

 

[bob fitz]

View attachment 90402

I have a field within a table called GroupID. Currently it has the values TEST01, TEST02 and TEST 03.

I want to be able to filter the form/records by the GroupID that is within the box on that given record.

So in the above example, if I was to click the button, I am hoping it will filter the records and bring up all records with TEST01 in the GroupID field.

What would be the easiest way to go about doing this?

Thanks

Adam

Rather than using a textbox and a button, it would be better to use a comobox, as already suggested by arnelgp.
If you use the wizard to create it, it will also write the code for you.

 

[kevnaff]

Rather than using a textbox and a button, it would be better to use a comobox, as already suggested by arnelgp.
If you use the wizard to create it, it will also write the code for you.

Thanks for your reply.

The reason I went for a text box was because I want users to be able to edit the GroupID and create new GroupID values just simply by typing in to the text box. I then created the button to the right of it to run the query below. When I run this query it brings up all records that are not blank/null in the GroupID field.

I was hoping to click the button and for it to filter by the GroupID value on the current record.

I had a go at creating a ComboBox, and the AfterUpdate event that arnelgp suggested, is described as an update after data is changed. I don't believe this fits in with I'm looking for, maybe I wasn't clear in what I wanted.

Hope this helps. If you have any suggestions on how to create a filter like this, that would be great. Thanks.

 

[mike60smart]

View attachment 90402

I have a field within a table called GroupID. Currently it has the values TEST01, TEST02 and TEST 03.

I want to be able to filter the form/records by the GroupID that is within the box on that given record.

So in the above example, if I was to click the button, I am hoping it will filter the records and bring up all records with TEST01 in the GroupID field.

What would be the easiest way to go about doing this?

Thanks

Adam

Hi

Are TEST01, TEST02 and TEST 03 stored as a Lookup in the table fieldname groupID ?

If you are doing this then stop. Lookup Fields in tables are not good practice.

 

[kevnaff]

Hi

Are TEST01, TEST02 and TEST 03 stored as a Lookup in the table fieldname groupID ?

If you are doing this then stop. Lookup Fields in tables are not good practice.

Hi Mike,

The database is used in a hospital. So in this case we have medical equipment assigned a 5 digit Asset Number. Some medical systems, will have several assets on the one system. I want to be able to assign the medical system a GroupID so that the individual assets can be easily found.

Could you advise on the best way to go about this?

I have recently taken over the database from a retired member of staff, so it's a possibility that there may be Lookup Fields within the tables already.

What defines a Lookup Field?

Thanks

 

[arnelgp]

I was hoping to click the button and for it to filter by the GroupID value on the current record

you can't use Me, in your query.
use [Forms]![theformName]!GroupID

 

[kevnaff]

you can't use Me, in your query.
use [Forms]![theformName]!GroupID

Fantastic, that works perfectly.

Thanks.