Hide default Add New Record Button ONLY - How? (2 Viewers)

[Pat Hartman]

Parts of the "Access" functionality you loose when you create unbound main forms with bound subforms are the Filter/Where arguments of the OpenForm Action/Method as well as the OpenArgs since those are only captured by the main form. You can pass them along but you are now writing code to replace native Access functionality. At some point, you need to consider using a different platform that works the way you want it to work.

 

[MajP]

Parts of the "Access" functionality you loose when you create unbound main forms with bound subforms are the Filter/Where arguments of the OpenForm Action/Method as well as the OpenArgs since those are only captured by the main form

@Marshall Brooks
Although the above is true, that can easily be overcome 95% of the time. See discussion here.

Solved - Trouble opening multiple instances of a form

Hi, I'm working on my CRM project. I have a problem where the wrong fields are hidden/displayed upon initial opening of the next form after the initial form is opened. Difficult to explain, but open frmMain, open either of the top two options & the correct controls are displayed (that is Nav...

www.access-programmers.co.uk

I am not advocating either way, but if you did want to go this route I doubt you would have many problems.

 

[Marshall Brooks]

I don't use OpenArgs.

I am planning to use SQL Where clauses in the RecordSource of the form and select the recordsource to use and requery the form in the Form_Open event.

Would this not be triggered if I was opening the form as a subform?

 

[MajP]

Would this not be triggered if I was opening the form as a subform

No change there. Would work fine as subform

 

[CJ_London]

can probably get an example out tonight

tried and failed. I had hoped to be able to place the buttons over the navigation bar to hide the buttons, but leave the scrollbar available. But unfortunately this does not work - navigation form is not visible

I was able to place the bar within the form space itself and does not move on scroll (which is what I think the OP is after)

scrolled to left

scrolled to right

But unable to get buttons to work - basically other than mouse move event the other events do not appear to fire, at least not reliably. Another issue - the nav form location is fixed on the form when it is opened, so moving the main form around is not a problem, however resizing the form means you also need code to keep the control within the form window (i.e. at the bottom as the default nav controls/scrollbar does). And although the nav form is now a child of the main form, you cannot reference it as you would other controls - my guess is you would need to refer to the navform object - but not investigated

Pretty sure this could be made to work, but I don't have the time right now to investigate further. If anyone else wants to experiment, I can upload the file but I think the mainform as a nav form with the target form as a subform is probably an easier way to go - or stick with standard access functionality

 

[Marshall Brooks]

@CJ_London - Thank you! I truly appreciate all of the effort.

I wouldn't mind seeing the code - very curious how you keep the subform from moving when the main form is scrolled.

Question - Would putting the nav form in the form footer help, and would Stephan Lebans method help - https://www.lebans.com/recnavbuttons.htm - That is what I am using now - but I removed the counter since it was redundant.

I think the mainform as a nav form with the target form as a subform is probably an easier way to go

I re-considered this. I appreciate the suggestions by everyone and as you said, I am fairly certain it can work, but there are only a few resolutions where we need to scroll the form to view everything and very little needed info on the right side of the screen. (Translation: If our users have to scroll to the left to go to a new record, we can live with that. There are some static tables on the right side, but nothing that changes that you would want to monitor as you scroll through the records.)

If your solution can be made to work, it does almost all of what I was after. If not, I'll likely modify Leban's code (that I am currently using) to add back in the record counter and maybe a Clear Filter Button, hide the built-in navigation bar, and call it good for now.

I was able to place the bar within the form space itself and does not move on scroll (which is what I think the OP is after)

Correct - demos look good!

however resizing the form means you also need code to keep the control within the form window (i.e. at the bottom as the default nav controls/scrollbar does)

Would putting the nav form in the footer solve this, or would that make it scroll off-screen when you scrolled the form. I like the nav form at the extreme bottom left, so that might be an issue. However, usually we just open the database and leave it open. I can see potential issues if we have the database on an external monitor and disconnect it and move it to a laptop, but if necessary, we could live with "Close and re-open the database if you change monitors" - although obviously not desirable.

And although the nav form is now a child of the main form, you cannot reference it as you would other controls - my guess is you would need to refer to the navform object - but not investigated

Makes sense, but I'm not sure when you would need to do this.

Again, thank you very much!

 

[Marshall Brooks]

Figured out how to clear filters: https://www.access-programmers.co.uk/forums/threads/vba-to-apply-remove-filters.175908/ In my case, the button would refer to the parent form. I think I can modify Leban's code to do that. Not sure how to do it with the class object method - it's probably easier, but unfamiliar to me.

 

[Marshall Brooks]

Have a fairly stupid question and the correct answer might be - "you can't" and I can live with that.

If you look back at the initial question, the custom buttons are in the footer, and there is a background image for the form itself (the black area to the right of the custom buttons).

I would like to change the footer background to light gray to simulate the navigation bar.

I thought that would be simple - there is a Back Color option for the footer, but it seems to be ignored if you are using a picture option for the main form.

There is no header.

I thought perhaps I needed to remove the picture from the form and add it to the Detail, but there doesn't seem to be an option for that.

Disregard - I figured out that although the nav buttons form is only .75-inches wide, if I make the space for it equal to the width of the main form (16.0 inches, it does what I was looking for - gray background across the footer.)

 

[CJ_London]

I wouldn't mind seeing the code - very curious how you keep the subform from moving when the main form is scrolled.

See attached. I just used majp's example app and made changes to things like borders. I only played around with the DemoNavigation form, and the navigationControl form. The code you are probably interested in is in the DemoNavigation load event - note also the addition of the setParent API to the module. I also changed access to overlapping windows rather than tabbed forms.

Positioning - not entirely fixed - you will need to drag the bottom of the main form down to see the navigation form, then narrow the form to activate the scrollbar

As I said, still a lot of work to do, and may not actually become a practical solution. If it did, would probably end up as a class module

 

[Marshall Brooks]

@CJ_London - Thank you! I see what you are doing and it is pretty clever. I don't think I'll be the one modifying it, though. I have the buttons working using a subform, but I don't think you can position a subform with DoCmd.Move, but I'm unsure of that.

That said, other than going offscreen when I scroll - I made a LOT of progress with what I wanted to do and more modifications to Stephan Lebans buttons. I'm pretty happy with it!

With Navigation toolbar to show comparison:

Final view without navigation toolbar:

I lose a line of screen real estate if the scroll bar is present, which I did before also. OTOH, I gain one line of screen real estate if the scroll bar isn't required, so overall, it's a plus.

I didn't change the back color of the filter button yet, but that is trivial.

I'm pleased with it.

One thing I did notice (and it might be me working from home) is there is about a 1-second lag between the filtered button caption updating, but I can live with that. (i.e. I click the button, the built-in toolbar changes immediately, mine changes a second or so later).

 

[CJ_London]

but I don't think you can position a subform with DoCmd.Move

you can't, you use the control move method

me.subformname.move ......

but in my example, the 'child' navigation form is not a control.

going offscreen when I scroll

investigate scrollbar events - this link will get you started

SetGetSB

 

[CJ_London]

I lose a line of screen real estate if the scroll bar is present,

investigate using the activeX scrollbar control - could then include the scrollbar as part of your navigation form

 

[Marshall Brooks]

I spoke too soon - apparently you can't set the backcolor of cmdButtons via VBA. I put the correct code in (I think), but the backcolor in design view is #FFFFFF, but the button background doesn't look white to me, and it never changed when I ran my VBA Code.

I'm going to change it to a label with the same name that should work.
@CJ_London - SetGetSB won't open for me - the version of Access is too old.
Do you have a link to the active X scrollbar control information? (I like where this is heading!!!)

 

[CJ_London]

Do you have a link to the active X scrollbar control information?

No - google something like 'access vba active x scrollbar'.

 

[CJ_London]

here is the setgetdb in .accdb format

 

[Marshall Brooks]

Now it opens but needs to be updated for 64-bit. I'm going to see what I can find on ActiveX Scrollbars first ...

 

[Marshall Brooks]

Hmmmn - Google wasn't much help with the ActiveX Scrollbars. Best help was several links that pointed to this thread, that is supposed to be using an Active X scrollbar, but it doesn't say anything about it, and it has to be updated for 64-bit ...
https://www.access-programmers.co.uk/forums/threads/updateable-crosstab-form.243278/

I took a gamble and just pasted an Active X MS Forms 2.0 Scrollbar on my navigation subform. It looks okay, but:

It doesn't actually move anything when you move the slider.

The slider is continually flashing gray to black and back again - not sure why.

Ideally, I only want the scrollbar to show up if the form is larger than the window. There is a visible property that should hide it, but I don't know how to determine the window size. I did not see a Display = "When Needed" like there is for a regular slider.

I tried setting the control source to Me.Parent, but that didn't seem to work.

I can live with the way it is if it works (and if it doesn't eventually slide off the screen also), but ... I set the width of the navigation subform to about 9 inches. Ideally, I would like this to be the same as the width of my main form, but I don't think you can change that via VBA (and I don't want to set it to my widest form and have the slider working just b/c the navigation form is too wide.

I'm going to try putting the slider in the main form's footer and see what happens then.

 

[Marshall Brooks]

No change putting the slider in the footer. Still flashes and doesn't do anything - but the idea is promising.

I made a mistake earlier. I shouldn't have inserted the navigation subform in the footer and made the space for it the width of the form. I should have put it in the form at it's designed width and added a rectangle with the same color fill to make it look like a toolbar - but that's easy to fix ...

 

[Marshall Brooks]

I'm going to live with the two line scroll bar. I did see how the ActiveX scrollbar is supposed to work, but it's somewhat glitchy.

Attached file opens, but might not be correct. I just changed everything that wouldn't open to PtrSafe and commented out anything that gave me a type mismatch.

You have to have an on update event for the scrollbar and then it moves to the record next to the slider - that won't work for me and it is somewhat sluggish, and it flashes until it loses focus. I think it will confuse/annoy our users.

But I'm happy with the other changes!!!

 

[Marshall Brooks]

Forgot the attachment: