Need advice on Navigation Control and/or Navigation Where Clause

[noaccessidea]

I am currently using a Navigation Control to switch between a few different forms but I have just learned about the Navigation Where Clause and now realise that I have created a whole load of unnecessary queries and forms.

The issue I have now is that I don't know how to set this up so that I can use Navigation Controls to filter because I would need Nav Controls inside each left hand tab which seems messy if is even possible.

I am thinking about this now because I am adding the Library/Practical tab which will have multiple types of support (with less details needed) so being able to filter on a button/tab press will be very helpful.

I hope I've explained what I'm looking for, it would be great to hear some ideas about how this should be set up. Perhaps a junction table would be better?

 

[vba_php]

if this post doesn't help you, just ignore it. but.....

I am currently using a Navigation Control to switch between a few different forms but I have just learned about the Navigation Where Clause and now realise that I have created a whole load of unnecessary queries and forms.

it doesn't appear to me that adding forms to either the top strip in the nav form or the left-hand strip of the nav form creates any additional objects in the database window.

The issue I have now is that I don't know how to set this up so that I can use Navigation Controls to filter because I would need Nav Controls inside each left hand tab which seems messy if is even possible.

filter on *what*? are you referring to applying filters to the forms that you already have on the nav form that are appearing in datasheet view, as shown in your attached image? it seems to me that forms added to a nav form like this, regardless of what stip (top or side) are retaining their functionality, in terms of being filterable and/or navigation friendly via record searches performed on them.

Library/Practical tab which will have multiple types of support (with less details needed) so being able to filter on a button/tab press will be very helpful.

does the Library/Practical tab include a subform embedded on the main form that you use for filtering records anyway? If it does, then you should already be able to do what you want, per my previous statement. I took at look at a file of my own on this side and tested it on 365 with forms that had embedded subforms on them with search functionality before posting this response to you, to make sure not to waste your time.

https://support.office.com/en-us/ar...ion-form-32e50477-3039-4503-9cd6-210a1a836007

 

[noaccessidea]

if this post doesn't help you, just ignore it. but.....it doesn't appear to me that adding forms to either the top strip in the nav form or the left-hand strip of the nav form creates any additional objects in the database window.filter on *what*? are you referring to applying filters to the forms that you already have on the nav form that are appearing in datasheet view, as shown in your attached image? it seems to me that forms added to a nav form like this, regardless of what stip (top or side) are retaining their functionality, in terms of being filterable and/or navigation friendly via record searches performed on them.does the Library/Practical tab include a subform embedded on the main form that you use for filtering records anyway? If it does, then you should already be able to do what you want, per my previous statement. I took at look at a file of my own on this side and tested it on 365 with forms that had embedded subforms on them with search functionality before posting this response to you, to make sure not to waste your time.

https://support.office.com/en-us/ar...ion-form-32e50477-3039-4503-9cd6-210a1a836007

Thank you, really appreciate your reply. The tables/forms so far are relating to lectures and support workers being assigned to each so Available is all lectures where Support Worker is Null, All Lectures is everything. I have created different queries/forms for this but I realised that this could have been done with the Nav Where Clause. It does all work so I suppose it doesn't matter how many queries etc are used.

I suppose my question is how to best integrate the Library/Practical. There are a few other types of support that I need to keep track of, all of which need less data. I was going to create one table for these other support types so that I can filter on type if needed and keep track of allocated/unallocated ones. But after learning about the Nav Where Clause I am wondering if I should be creating multiple queries/forms for this when it can potentially be done from one form.

With the Nav Where Clause I did notice that it doesn't appear to work particularly well. When changing between Available (support worker is null) and All Lectures (no where clause) then the 'filter' doesn't get applied after switching between them a few times.

 

[theDBguy]

Hi. Perhaps it might be helpful to everyone if you could post a sample copy of your db, so we can easily determine what you mean. Just a thought...

 

[noaccessidea]

Hi. Perhaps it might be helpful to everyone if you could post a sample copy of your db, so we can easily determine what you mean. Just a thought...

That's a good idea, it's hard to describe this stuff well. Should be attached now with data removed.

 

[vba_php]

I suppose my question is how to best integrate the Library/Practical. There are a few other types of support that I need to keep track of, all of which need less data. I was going to create one table for these other support types so that I can filter on type if needed and keep track of allocated/unallocated ones. But after learning about the Nav Where Clause I am wondering if I should be creating multiple queries/forms for this when it can potentially be done from one form.

basically what you have done, after looking at your file, is produced an Oracle-type, form-driven-architectural database, but rather than displaying stacked forms you are using MS's new consolidation feature, the nav form. it doesn't look that bad, to be honest. I didn't look at it that long, but there is one thing i would certainly say:

Some of the buttons on the left side pane open forms in their own windows, some open them in the nav form main window itself, some open in form view, some open in datasheet view. perhaps you should change that and give the user one way only, they don't get confused and you don't pull your own hair out maintaining it? the other thing is that, the ones that open in datasheet view, you have combo boxes in the fields. all the experts say not to do that. I'm guessing because of this: http://access.mvps.org/access/lookupfields.htm.

I don't have time to review it further, but I would suggest initially at least that you streamline the form-oriented process in the nav form and that should get you started. let us know what you think of this post and ask more questions if need be.

 

[theDBguy]

That's a good idea, it's hard to describe this stuff well. Should be attached now with data removed.

Okay, so, if I understand what you wanted correctly, what you need to do is use a single form that shows all the records from your table or query. You can then use this form for all your buttons. The only difference is you would use a specific Navigation Where Clause for each button to apply to that same form, so it will display different sets of records based on whichever button was clicked. Hope that helps...

 

[Dreamweaver]

You could use a main/subform arangement and just change the subform as required

also I hate the tabbed option in the document window option I always use Overlapping but thats me

 

[Dreamweaver]

Just noticed as I was closing it you are using lookup fields in your tables this is a really bad Idear tables are for storing the data not working with it.

mick

 

[noaccessidea]

basically what you have done, after looking at your file, is produced an Oracle-type, form-driven-architectural database, but rather than displaying stacked forms you are using MS's new consolidation feature, the nav form. it doesn't look that bad, to be honest.

I'm not sure what stacked forms are to be honest but I'll look it up to see if it would be better suited. I'm still pretty new to access and jumped into it quite suddenly.

Some of the buttons on the left side pane open forms in their own windows, some open them in the nav form main window itself, some open in form view, some open in datasheet view.

Thank you for mentioning this, it is something I meant to ask about. It is inconsistent and mostly by design/need as it is. I think the All Lectures opening in a new tab is actually unnecessary now that I created the Student and Worker reports but originally the reason for the ones opening in their own windows is that we frequently need to send a support worker or student a list of the lectures to show the student or support worker details. I intended to do this by just using the column filter on student/support worker and then exporting that as Excel etc. I didn't get a chance to test it before it was in production and we noticed that the export didn't work, it just showed a couple field names. The Report tabs work correctly now for the purpose but I'm not sure what the problem is with exporting the currently viewed tab within in the nav form?

the other thing is that, the ones that open in datasheet view, you have combo boxes in the fields. all the experts say not to do that. I'm guessing because of this: http://access.mvps.org/access/lookupfields.htm.

This is an area I am confused about. I was told in another thread here that choosing lookup as a field type in design view is bad but using a lookup in the properties is ok? Or is it the same thing? I'm not in front of access now so I hope you know what I mean.

Okay, so, if I understand what you wanted correctly, what you need to do is use a single form that shows all the records from your table or query. You can then use this form for all your buttons. The only difference is you would use a specific Navigation Where Clause for each button to apply to that same form, so it will display different sets of records based on whichever button was clicked. Hope that helps...

Well I think what I am asking is if that's the best way. I've just discovered that that's possible and it certainly seems like a simpler and cleaner approach. But would it work well from a user perspective? Should I go for a horizontal tab for each support type, with vertical tabs for allocated/unallocated, student/support worker filtered/queried forms for each?

You could use a main/subform arangement and just change the subform as required

also I hate the tabbed option in the document window option I always use Overlapping but thats me

I didn't really think about subforms to be honest. It's important that I can get that drilled down information exported and I don't really want the user to find the information they need and then have to open another window/form and drill down again to export it. Or am I missing something? Please see my response above about the lookup issue, I would be grateful for any clarity on this issue.


Many thanks for all of your replies, I really appreciate the thoughts. I'm very new to this and probably doing lots of things wrong so it's extremely useful to hear.

 

[vba_php]

I'm not sure what stacked forms are to be honest but I'll look it up to see if it would be better suited. I'm still pretty new to access and jumped into it quite suddenly.

"stacked forms" is my term. God only knows what Larry Ellison called it when He invented Oracle! this simply means "form on top of form". I use it in almost everything I do, because it literally forces the user through *your* process and leaves almost 0 room for user error. sometimes I call things like this "simple-stupid". and as I told Tera (a user here) a while back, it's the same thing as Muhammad Ali's "rope-a-dope" technique he used to win so many fights.

I didn't get a chance to test it before it was in production and we noticed that the export didn't work, it just showed a couple field names. The Report tabs work correctly now for the purpose but I'm not sure what the problem is with exporting the currently viewed tab within in the nav form?

I would have to test it on your file and do it myself to see what the issue is. If you can't figure it out, let me or someone else know in your next post and I'm sure someone can figure that one out.

This is an area I am confused about. I was told in another thread here that choosing lookup as a field type in design view is bad but using a lookup in the properties is ok? Or is it the same thing? I'm not in front of access now so I hope you know what I mean.

the concept of a "lookup field" spreads far and wide across access as an application. you can use the idea in FORMS, REPORTS and TABLES. when people implement it in forms and reports it's just fine because the lookup control is pulling from another source, like another box or a table field. it works in forms and reports, when pulling data from a table because the source of the data and display entity for the data are in 2 separate places and therefore won't collide in terms of "interests". The article I posted is talking the situation you can get it when choosing "lookup" as a type of table field. technically you can think of it as a "property" in that regard, but it's really a type of field, even though the "technical" field types are labeled as "data types" like *long*, *text*, *memo*, etc....