Solved

Form Filter Running Out of Room, Need Scroll Bar

Posted on 2013-01-24
21
407 Views
Last Modified: 2013-01-25
Hello,

This is a follow up to what capricorn1 so precisely helped me with (so if you see this capricorn1, I need to follow up with you please--or anyone who would know)

So I need the form to be able to do 2 more things:

1. Hit Enter key, do the same thing as the OnClick Event Procedure for the Filter button
2. Put the Results (bottom section) into a scroll box so I can scroll through it because there are a lot of different fields to be seen on the form

As of now, it seems like I am maxed out at 22 inches horizontally to the right (can't move labels and text boxes anymore to right, it won't let me (in the Results bottom section)??

Thank you in advance
0
Comment
Question by:IEHP1
  • 16
  • 5
21 Comments
 

Author Comment

by:IEHP1
Comment Utility
In my little bit of research on the scroll bar issue, I found a property called Scroll bars for the form itself which said "Both" meaning both horizontal and vertical which it is actually doing now so please disregard #2 request.
0
 

Author Comment

by:IEHP1
Comment Utility
But just out of curiousity and I may decide to implement this into the form if it can be done: Can I create an inner scroll bar inside of the default scroll bar or bars?
0
 

Author Comment

by:IEHP1
Comment Utility
Sorry for all of the posts, but basically I would like to make it a scroll bar for just the results section (the bottom section) without the top section moving along with it?
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
You need to use a subform inside your form so that the scrollbars are independent.
0
 

Author Comment

by:IEHP1
Comment Utility
Yes, that was what I was thinking too. I guess that is the only way to do it and link the Master and Child fields together on the Key ID (although actually not a true unique ID, the other fields brought in the result set differentiate each record)?


So do you or anyone know how I can get the Enter key to take the same action as the Form Filter OnClick Event Procedure (which filters the records based on all of the criteria search fields)? That one, I am not sure about.........??
0
 

Author Comment

by:IEHP1
Comment Utility
So I researched it out a little and saw an article that said to set the button's DEFAULT property to YES so that whenever a user presses the Enter key, no matter where they are on the form, it will be just like clicking the button.

And also, by setting a different button's CANCEL property to YES, if you hit the ESC key, it will run the click event of that button.

So I guess either way you do it (CANCEL to YES or DEFAULT to YES), it will work depending on what key you want users to press......?

What do you guys make out of this? Do you guys agree?
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
How about adding an AfterUpdate event to each textbox used for the criteria. Each one would just call the button's OnClick routine.
0
 

Author Comment

by:IEHP1
Comment Utility
So I don't know VBA (still learning as I go). I saw On Enter Event Procedure as well and thought that sounds like the right one, but now you said AfterUpdate will work just the same as the command button's click event.

But is there a way I can create an event procedure to call out the command button event procedure? I don't really know if that sounds dumb or what because, like I said, just learning as I go.

What would I put in the AfterUpdate event procedure?

Why AfterUpdate (does it happen by default when pressing Enter that the AfterUpdate click event is automatically activated?)?
0
 

Author Comment

by:IEHP1
Comment Utility
I looked at a sample database and didn't see any CANCEL property nor DEFAULT property so I am not sure about that article I read just to let you know.

Not sure if you know what the article is referring to as well?
0
 

Author Comment

by:IEHP1
Comment Utility
It has an AfterUpdate event procedure for the first combo box as follows already:

Private Sub cboFilterIsCorporate_AfterUpdate()
Debug.Print Me.cboFilterIsCorporate
End Sub

I am not sure what that is doing (since the combo box is suppose to show the values if you click the arrow anyway which it is doing now)??
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:IEHP1
Comment Utility
That AfterUpdate event is only for the first combo box, and not for any other search box in the form.

Maybe it would be easier if I just sent you the sample database and then you to play with it and tell me what works??
Form-Building-Using-FiltersRev-2.accdb
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
The OnEnter event fires when a control receives focus either by tabbing to it, clicking on it with the mouse, or by setting focus to it via code.  The AfterUpdate event fires when you make a change in a control and then leave the control either by pressing [Enter], [Tab], or clicking on another control.
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
Comment Utility
Your OnClick procedure should look something like this:
Private Sub NameOfButton_Click()
You can call this procedure just like you would call any custom procedures you create.  So in the AfterUpdate event of each textbox used for criteria, you would add:
Call NameOfButton_Click
0
 

Author Comment

by:IEHP1
Comment Utility
I see IrogSinta, thank you for clarifying that with me.

So do you know how I would go about making the Enter key do the same event as the command button (Filter button) does?
0
 

Author Comment

by:IEHP1
Comment Utility
disregard the last post, it worked!! I was trying to do something like that but didn't know to use Call in front of the button's event name..... I will apply that to all of my textboxes and combo boxes in the form's header section.

Also I will make the Results section (bottom section) a subform.

I will let you know how it goes, it should go well I believe but will post back if I run into any problems.

Thank you so much IrogSinta. I will award you the points as soon as I can set it up.
0
 

Author Comment

by:IEHP1
Comment Utility
OK so I got all of the text boxes and combo boxes to work with the AfterUpdate event,

but when I tab over to the next field tab order when deleting the field I had previously input one of the values in there, it comes up with a messagebox saying Nothing to do in the title and No criteria inside the messagebox.

The following is the section of the code where I believe this is coming from. My question is how can I make this not happen so that when users tab over or press Enter or click in a new field, this message doesn't come up???


'***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
       
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub
0
 

Author Comment

by:IEHP1
Comment Utility
I created a subform inside of the main form and can't find the Link Master Fields, Link Child fields properties??

When I dragged and dropped the subform into the main form it prompted a message saying something about it is going to automatically change the form to Single Form (instead of Continuous Form)

I tried changing it in the Property sheet to Split Form, but don't know why I can't find the Link fields to link them together??
0
 

Author Comment

by:IEHP1
Comment Utility
Nevermind that, I found the Link Properties. The problem I am having with the subform is that when I enter one of the combo box fields or any of the text box fields, too, it will only bring up 1 result in the subform.

Before, as 1 form, it would bring up as many results as there should be????

I am thinking about copying all of the code for the OnClick event of the CmdFilter button and pasting it into one of the events of the subform so that it will give correct results?????

I don't know which event to insert it into though????

Please help me with this???

Capricorn1, I think IrogSinta is out of the office (not responding)???
0
 

Author Comment

by:IEHP1
Comment Utility
oh, fixed it sorry for so many posts again......

So I don't know how I would insert a scroll bar besides doing it in a subform.
The reason I am thinking of a subform is that I want to be able to scroll the Results section without the top section moving, but here is the issue with using a subform:

Since the subform actually makes me have less space since it is a new form with the normal top left border it has for forms (and reports), a subform actually makes it worse.

I would like to be able to have a scroll bar span the whole distance of the horizontal results section. As I tried it, it only went so far, then I had to use the main form's scroll bar to see the rest??

Is there a way around using a subform (or perhaps getting it to span the whole distance within the subform?)??

Please let me know......
0
 

Author Comment

by:IEHP1
Comment Utility
Ok I worked on it and figured this question out. Thank you very much guys
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Hey, I'm just now checking my email but it looks like you figured out all your questions. If you have any more problems, just ask away.  Next time though, if you upload a copy of your database, it would make it easier for us to help you out.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now