Solved Filtering On A Continuous Form (1 Viewer)

LGDGlen

Member
Local time
Today, 13:04
Joined
Jun 29, 2021
Messages
229
Hopefully this will be a simple fix but as i'm an utter novice in MS Access its currently outfoxing me

Background: I am migrating an excel spreadsheet which is being used to run the business into an Access DB and as part of that I have a continuous form which shows data like a datasheet but with a nice header and column headings etc. I want to add buttons for the user to do things etc in the header and wanted to give my forms a standard look and feel throughout the database design. So rather than have a bare metal datasheet for I have created a nice looking continuous form which functions as a datasheet but fits into the aesthetic i am going for.

Problem: The users are used to filtering data using Excels standard filtering which is much better than what Access has available without creating queries. That said the filtering that is there "begins with" or "contains" as examples will almost certainly suffice for most day to day functions. On normal datasheets (which i have a few on sub forms) when you select "Begins with..." as a filter the resulting dialog box shows the Label Name for the field you are filtering on which is nice and useful. On the continuous form we get the field name, which is useful from a programmatic stand point as i can identify table and other information that groups data together in tables easily with my naming convention, but it makes it difficult to use for the users.

1626879502908.png

The above shows the difference

What i want to do is (somehow i hope) use the Header label that is at the top of the column of data as the name, so when i do the following:

1626879879236.png

"Supplier Ref/Albaran" is in the dialog and not cons_albaran. I just can't figure out how to do this

Hoping this both makes sense as a description of the problem and is possible to resolve

Kind regards

Glen
 

LGDGlen

Member
Local time
Today, 13:04
Joined
Jun 29, 2021
Messages
229
i will spend sometime reviewing the example code and how it works and determine if it achieves what i want thank you, i tried searching (obviously not very well) for something that would describe how to resolve the issue i was seeing i appreciate more knowledgeable people directing me to a post that hopefully will resolve things
 

Minty

AWF VIP
Local time
Today, 13:04
Joined
Jul 26, 2013
Messages
10,354
i will spend sometime reviewing the example code and how it works and determine if it achieves what i want thank you, i tried searching (obviously not very well) for something that would describe how to resolve the issue i was seeing i appreciate more knowledgeable people directing me to a post that hopefully will resolve things
To be fair - it took me a few minutes to dig that out, and I only found it because I know who had posted it in the first place.
Googling can be an art form.
 

LGDGlen

Member
Local time
Today, 13:04
Joined
Jun 29, 2021
Messages
229
ok so looking at this, whilst impressive and i will look at lovingly stealing the functionality, it doesn't solve the issue i'm having. The filter in the example you provide still shows the field name, its just in the example the field names are Human Readable and as such look nice when you do the filter "begins with" for example. Its not the same as when you have a datasheet form vs a continuous form.

it would appear that when you filter on a datasheet form the Label associated with a field is used in the dialog that comes up, on a continuous form that doesn't appear to be the case and i can't find a way to override what is showing in the dialog box to show something nicer than the underlying field name i have in my table

hope that makes sense and also more so i hope there is a solution rather than renaming my table fields to something human readable just because of this particular issue
 

Minty

AWF VIP
Local time
Today, 13:04
Joined
Jul 26, 2013
Messages
10,354
Have you investigated the caption property in the table design, I believe that overrides the field name on a datasheet form?
 

LGDGlen

Member
Local time
Today, 13:04
Joined
Jun 29, 2021
Messages
229
i have tried:

Datasheet Caption
ControlTip Text
Status Bar Text
Shortcut Menu Bar
Tag

Nothing seems to influence whats shown in the dialog unless there is an "in conjunction with this" setting i need to do as well
 

Minty

AWF VIP
Local time
Today, 13:04
Joined
Jul 26, 2013
Messages
10,354
In the Table designer - there is a caption property try that one.
 

LGDGlen

Member
Local time
Today, 13:04
Joined
Jun 29, 2021
Messages
229
ah, yeah that was another thing i'd already tried but i've tried so many things now i completely forgot
 

LGDGlen

Member
Local time
Today, 13:04
Joined
Jun 29, 2021
Messages
229
i think i understand the issue, the problem is that because the headers for the columns are in the "header" of the form and are not linked to the field thats in the continuous part underneath there is nothing for the form to display, if i try to link the label in the header to the field in the continuous bit it move its from the header to the form and then the form goes completely awry
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:04
Joined
May 7, 2009
Messages
19,169
this is actually really simple to Solve.

the reason why the "label caption" is not showing
on the Filter is that the labels are not associated with
the textbox.

to solve this bring your form in design view.
select all labels and textboxes and on menu, Arrange->Remove Layout.
next click on each label and on a paper write down the
"control name" (we will use this names later).

click on the first label and copy/paste
you need to overlay the copied label exactly
on same spot as the original.
to do this click on the original label (copy it's Top property).
click on the cloned label and paste to Top property.

do this with the rest of labels.

adjust the Detail section so it touches with the labels above.

add code to the Load event of the form to associate the labels
you put on the paper.

private sub form_load()
with me
.textbox1.properties("Labelname") = "theFirstLabelNameHere"
.textbox2.properties("Labelname") = "anotherLabelnamehere"
'etc...
end with
end sub

done.

see this demo. custom filter the form.

check the field name in design view.
check the form in design view.
 

Attachments

  • continuousFieldname.accdb
    480 KB · Views: 376

LGDGlen

Member
Local time
Today, 13:04
Joined
Jun 29, 2021
Messages
229
OMG thank you, thats excellent i will take the example and implement. (edit i tested it quickly with your test DB and perfect)

@Minty i appreciate your patience and suggestions
@arnelgp thank you
 

LGDGlen

Member
Local time
Today, 13:04
Joined
Jun 29, 2021
Messages
229
just to let you know, implemented your suggestion and it works perfectly, thank you, i have many other larger problems to deal with but this bugged me more than all of them combined and as such took my focus............ ah well now i have to tackle the big jobs hahaha
 

Users who are viewing this thread

Top Bottom