VBA, I desperately need help. (1 Viewer)

Dangerous

Registered User.
Local time
Today, 15:00
Joined
Oct 18, 2018
Messages
73
I have built an Assess database with various forms and in the process of expanding it to cover everything I need to do with it.

I'm at the point where before I proceed I need to be able to select a group of record.

There are about 30 fields in each record, some of which contain data from lookup table, another holds attached photos but the majority hold data from the table itself.

One from displays all the fields to allow data entry.

I can, by using the Filter option of ADVANCED, CLEAR ALL FILTERS then ADVANCED, FILTER BY FORM, than after entering one or more items of data in the form by typing in a data field - use drop the down box or click the radio button in the option group and clicking TOGGLE FILTER, have selected all the matching records.

This works perfectly for me but as there may/will be other users the ribbon etc. will be hidden preventing alterations to layout etc. I need a FIND MATCHING RECORDS button to switch from the menu to the search form then something to toggle records so all are selected (toggle filter) and another button to clear selected and start another search if required.

As the only bit of VBA I've managed to get to work is DoCmd.OpenForm "permissions", acNormal (found on internet lol) I don't hold out much hope of finding out how to do this myself.

Anyone help a COMPLETE BEGINNER?
 

Ranman256

Well-known member
Local time
Today, 11:00
Joined
Apr 9, 2015
Messages
4,339
do you want boxes to use to filter like:
cboState , user enters a state
txtLast, user enters a persons last name
etc...
btnFilter, click to filter the records on these values?
It CAN be done with vb code...but

rather than build all that, cant the user just right click on State field , filter, entery code,
then right click on LastName , then filter on the name?

zero code, same results.
 

Dangerous

Registered User.
Local time
Today, 15:00
Joined
Oct 18, 2018
Messages
73
I don't know how best to do what I want.

I can do what I need to by using the sort & filter commands on the ribbon but as the ribbon will be hidden (plus I won't want people to have to read or remember the steps every time) I need it to be automated.

I don't know exactly what you mean by STATE FIELD.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:00
Joined
Jan 23, 2006
Messages
15,364
I recommend you tell us about the application in simple plain English. You could also post a copy of your database in a ZIP file.
Good luck.
 

Dangerous

Registered User.
Local time
Today, 15:00
Joined
Oct 18, 2018
Messages
73
Assume the database is a list of photos.

It could contain the following (and more)

Title
Location
Photographers Name
Camera used
Lens used
Thumbnail of photo
Option Box, select radio buttons for landscape, cityscape, portrait, nightscape (one option only)
Option Box, select radio buttons for trees, rivers, cars, wildlife, buildings, flowers (multiple options

Then I may need to find & display all records (one per page) that were taken near Falkirk - taken by fred - using his Canon XXX camera - and showed both trees & rivers.

Then repeat the filter for all photos that matched other criteria (after clearing previous filter)
 

Dangerous

Registered User.
Local time
Today, 15:00
Joined
Oct 18, 2018
Messages
73
I might be able to figure it out if I could convert the built in macros to VBA, that's the ones on the ribbon
especially those groups in the Sort & filter section.
 

Dangerous

Registered User.
Local time
Today, 15:00
Joined
Oct 18, 2018
Messages
73
I have found that the "Filter by button" option shows as Filter By Form (FilterAdvancedByForm) when hovered over in the Customize the Quick Access Toolbar but I've not been able to run that no matter what I've tried. If I could figure out the correct way to do that then the other commands could be added and it may work for me.

Can anyone help?
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:00
Joined
Jan 23, 2006
Messages
15,364
I recall a photo with attributes question
https://www.access-programmers.co.uk/forums/showthread.php?t=276886

A lot of what you need seems to focus on the kind of question/searches you want to be able to do. This involves assigning several meaningful attributes from some ultimate list. Then your search/filter can be assembled in some form and the results obtained.

If you zip your database, I think you should be able to attach it. You may get more focused responses this way.
 

isladogs

MVP / VIP
Local time
Today, 15:00
Joined
Jan 14, 2017
Messages
18,186

Dangerous

Registered User.
Local time
Today, 15:00
Joined
Oct 18, 2018
Messages
73
That was an example rather than the actual data names. All data will be entered in to fields in a table and displayed on a form. In total there are 20 fields with a few more required.

Object of the filter is to select only the records that match all of the search criteria which could be data in one or more than one field.

In the example it could just be all photos taken by fred or all by fred that are landscapes.

Edited to add: In the actual database the photo is just a piece of date and may be omitted from the record if not required rather than what the record is about.
 

isladogs

MVP / VIP
Local time
Today, 15:00
Joined
Jan 14, 2017
Messages
18,186
Ok there are two things here.

1. Entering the data for each photo into Access for which using the extended properties should be a significant help.

2. Filtering the data using the selections made in multiple combo boxes.
To do that you will need to build the WHERE part of your query SQL concatenating the output from each.

As we don't know your field names and datatypes, I suggest you start by looking at this article by Allen Browne on creating a search form http://allenbrowne.com/ser-62.html
 

Dangerous

Registered User.
Local time
Today, 15:00
Joined
Oct 18, 2018
Messages
73
Had a look at that info, To me it's all double dutch (translated to Martian by a Klingon lol).

Up to now the only thing I have managed to do is the reset filter and have changed it so it selects all records rather than none.

I'll keep looking but first impressions it won't give the results I need as it says one record per ling & I need 1 per form/screen.

Thanks for trying , Is there any basic VBA tutorials for a total beginner?
 

Dangerous

Registered User.
Local time
Today, 15:00
Joined
Oct 18, 2018
Messages
73
It wouldn't help me giving field names as there will be over 20 and filtering could be done on from any 1 to 12 fields out of 20 or more.

Fictitious field names of F1 to F25 in a routine would do and I'd adapt to suit.

To be perfectly the build in filter commands work perfectly but as I say the ribbon will be hidden so calling them, one seems to be called FilterAdvancedByForm, by clicking a button (text or icon) placed on the form would be perfect.
 

Dangerous

Registered User.
Local time
Today, 15:00
Joined
Oct 18, 2018
Messages
73
Isladogs.

I've found how to call filterByForm by clicking text with assigned event but once the records are found I can't do anything other than entering a search term in a field box. I can of course click the ApplyFilter button on the ribbon at which time any buttons or clickable text becomes active again, until then they are greyed out.

Tried Esc., Enter, etc. but they do nothing till I click a ribbon button to apply the filter.

Any Ideas?
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:00
Joined
Jan 23, 2006
Messages
15,364
Post a copy of the database to get more focused responses.
 

isladogs

MVP / VIP
Local time
Today, 15:00
Joined
Jan 14, 2017
Messages
18,186
Jdraw beat me to it ....

In the meantime:
Attached are 3 screenshots from a fairly complex example where a list of students (from a fictitious school) can be filtered by any / all of 12 combo boxes.
Firstly unfiltered, then 3 filters added & finally 6 filters added reducing the list each time.
Is this something like you need ... or irrelevant?

It also has some additional features which you may not need such as sorting by any column and colour formatting for filtered columns

As for tutorials, I often recommend the complete courses of video tutorials by either Steve Bishop or Richard Rost. Both freely available on You Tube & cover everything from beginners through to highly advanced. Its personal preference which ones are better
 

Attachments

  • Unfiltered.jpg
    Unfiltered.jpg
    105.4 KB · Views: 204
  • 3 filters added.jpg
    3 filters added.jpg
    104.7 KB · Views: 213
  • 6 filters added.PNG
    6 filters added.PNG
    54.4 KB · Views: 220

Dangerous

Registered User.
Local time
Today, 15:00
Joined
Oct 18, 2018
Messages
73
It's all become irrelevant, after much searching and trying I almost got it working then I found out it is very difficult to lock a user out of having access to the ribbon etc.

It seems that while you can hide things, add passwords, encrypt etc. some things are still accessible to a user with average knowledge unless you really go to town on it and it isn't worth it for me.

Other databases I've set up in the past made it easy to lock the user out of design mode etc. yet easy for me to unlock & make required changes.

I've also found a better way to deal with the problem I had trying to automate the process of copying data from one field to another on data entry.

Thanks all who have tried to help, No doubt I will need some in the not to distant future and to me VBA syntax/commands don't make sense.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:00
Joined
Jan 23, 2006
Messages
15,364
I've also found a better way to deal with the problem I had trying to automate the process of copying data from one field to another on data entry.

Perhaps you would like to share your "better way" with the rest of us. That's sort of what the forum is about/encourages. We can all learn.
 

Dangerous

Registered User.
Local time
Today, 15:00
Joined
Oct 18, 2018
Messages
73
Thanks Isladogs. That looks good but for now I wouldn't be able to use it, I need all the data from only one record on the screen and would move to the next record as required although things may evolve as the database grows.

I have 48 records in at the moment and each time I add more I come up with improvements to make & different Ideas of how to display them. It's still very much 'work in progress' I do like the 'photo button (I'm using a fixed sized attachment display), How do you assign to a button?
The 'click column to sort may also be handy if I need data in rows in the future.
 

Users who are viewing this thread

Top Bottom