ACCESS FEED: Re: [MS_AccessPros] RE:

Ryan

Filtering a report is a snap. You just include the WhereCondition in the OpenReport arguments. That can be built from the form's selections. Without knowing how your form is set up I can't give you an exact method, but we can work on that as we go.

As far as sorting, there is a little trick I use. First I create group headers with these names (be sure to include the equal sign):

="A0"

="A1"

="A2"

Add as many as you will need for sorting. With three, I can sort on one field then the second then the 3rd such as Division then department then employee.

Next you will need a global array to hold the groups in the order you want to sort. I dim a global array with no size in a standard module:

Option Compare Database

Option Explicit

'Used for report sorting hierarchy.

Public garySortOrder() As String

Then you can use combo boxes on the form so the user can pick the groups in the order he wants the sort. cbo1 would be the top sort, then cbo2 and cbo3.

Then in the form I use a button called cmdOpenReport

Private Sub cmdOpenReport()

ReDim garySortOrder(2)

garySortOrder(0) = cbo1

garySortOrder(1) = cbo2

garySortOrder(2) = cbo3

DoCmd.OpenReport "myReport"

End

In the report's Open event:

Private Sub Report_Open(Cancel As Integer)

'A0 group

Me.GroupLevel(0).ControlSource = garySortOrder(0)

'A1 group

Me.GroupLevel(1).ControlSource = garySortOrder(1)

'A2 group

Me.GroupLevel(2).ControlSource = garySortOrder(2)

End Sub

This is a simplified example. Mine is much more complex, but I hope it is understandable to you.

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <ryan.paschal@gmail.com> wrote:

Hi,

It's Ryan again.

I have different problem now.

How to manipulate a report's order (sort) when opened from a form using 'docmd.openreport' method? How to pass the sorting argument?
I have successfully passing a filtering argument to a report using 'hard code'. However, how to do it using several options in a form? For example, I want user can pick which department's employees he/she want and which ranks to view by checking check boxes. Do I have to (1)write a code to determine which check boxes are checked & associate the check boxes with certain values that is acceptable as filters? Or, (2) is there any other simpler way? Because I have tried option (1) and successful, but the codes became very long & complicated.

Any help is very much appreciated.

Thank you.

Ryan

On Tue, Feb 25, 2014 at 8:39 PM, Ryan <ryan.paschal@gmail.com> wrote:
Dear John,

After pausing my work on this small project, I have just gone back to work on it.
And, in fact, you're correct. The intellisense still doesn't work, but the syntax is correct. Auto capitalization shows that it is a recognized method.

I've put the code in, tried it, and 'voila'! Thanks

To Bill, Graham & Darell: Thank you for your comments!

Ryan

On Thu, Feb 20, 2014 at 1:17 AM, Graham Mandeno <graham@mandeno.com> wrote:

Hi Ryan

The Recordset (and RecordsetClone) properties of a form can be either DAO.Recordset or ADODB.Recordset, so in the object library they are simply declared "As Object". This is why the intellisense does not work.

As Darrell points out, if you declare an object variable of the required type and then Set it to the Recordset property, then intellisense will work:

Dim rs as DAO.Recordset
Set rs = Me.Recordset
rs. <<< intellisense dropdown options appear here

Of course, the methods and properties still work without the object declaration, because the object is effectively late-bound.

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Embrey, Darrell
Sent: Thursday, 20 February 2014 06:38

To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] RE:

Bill,
It seems the Intellisense only works so deep. If you were to create a DAO.Recordset object variable and then assign Me.Recordset to that variable, then the Intellisense works.

Darrell

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of wrmosca@comcast.net
Sent: Wednesday, February 19, 2014 12:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] RE:

The same thing happens to me in Acc 2003 and 2010. It's always been that way. Something wrong with Intellisense is my guess. FindFirst still works. It's just that there appears to be nothing tied to Me.Recordset followed by a dot.

Bill

---In MS_Access_Professionals@yahoogroups.com, <DEmbrey@bcbsm.com> wrote:
John,
He's referring to the Intellisense not working after typing Me.Recordset.

Darrell

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, February 19, 2014 11:46 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] RE:

Ryan-

What version of Access are you using, and what libraries do you see checked when you choose Tools / References in the VB Editor?

John Viescas

Sent from my iPad

On Feb 19, 2014, at 7:13, "易健福" <ryan.paschal@gmail.com> wrote:
Sorry for the late reply.
I mean, usually, the VBA shows a list of on object's available methods right after I type its name. But, this time, there was no list pop up after I typed me.Recordset.

Ryan

On Wed, Feb 12, 2014 at 7:49 PM, John Viescas <JohnV@msn.com> wrote:

Ryan-

No, it's correct. Please explain what you mean by "it couldn't who's the findfirst method."

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 12, 2014, at 1:22 PM, 易健福 <ryan.paschal@gmail.com> wrote:

Dear Bill & John,

Thank you for your advice.

John V. has given me an advice on how to jump to certain record via jump combo box using the me.Recordset.Findfirst method in afterupdate event. However, it couldn't show the findfirst method. So, I presumed either John's wrong or I missed something.

Your advices are appreciated.

Regards,
Ryan

On Wed, Feb 12, 2014 at 3:35 AM, <wrmosca@comcast.net> wrote:

Ryan

Nothing is too simple for posting here. We help members with all levels of Access knowledge.
About the Save button...Access is built to save data straight into the underlying table. That is one of its most powerful features. If you want to stop an auto save, make whatever fields you want to be required in the table. That way if the user moves off the record and the required fields do not have entries Access will give the user a warning and halt the record insert. That might be all you need.

The Cancel button would need to have just a simple Click event. Create the button and select it. In the Properties sheet, Select the Click event. You will see a builder button (...) to the right of the line. Click it and pick Code. That will open the code window to the button's Click event.

It should look like this:
Private Sub YourButtonName_Click()

End Sub

Put a line inside the Sub so it looks like this:
Private Sub YourButtonName_Click()
Me.Undo
End Sub

That should remove all entered data provided the record has not been saved yet.

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <ryan.paschal@gmail.com> wrote:
Hi,

I am MS Access 2007's user. I hope experts here can help me with my problems. My problems perhaps too simple for experts here.

Here is my case:

I need a form to add/edit employee data.
I have made the basic form using form wizard, but it saves everything into table anyway without confirmation because it's connected to employee table directly via RecordSource.

I want the form to:
Have a button for saving the record; confirms whether or not user want to save the record
Have cancel button to cancel all data entry & clean the form to start over
In view mode, be uneditable unless clicked the edit button
Please help me.
Thank you in advance.

Best regards,
Ryan

The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.

The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.