Hi Phil
Given the caveats mentioned by John with regard to the deprecation of ULS,
you *can* find out is a user is a member of a given group with a function
like this:
Public Function CurrentUserInGroup(GroupName As String) As Boolean
Dim grp As DAO.Group
On Error Resume Next
Set grp = DBEngine.Workspaces(0).Users(CurrentUser).Groups(GroupName)
If grp Is Nothing Then
CurrentUserInGroup = False
Err.Clear
Else
CurrentUserInGroup = True
Set grp = Nothing
End If
End Function
Use it like this:
If CurrentUserInGroup("GroupA") Then ...
It makes no sense to have a CurrentGroup() function, because a user can be
(and often is) in multiple groups. For example, *every* user is in the
built-in group "Users", as well as any other group(s) you have placed him
in.
If you have a split back-end/front-end database (the recommended practice)
then you can still convert your front-end to the 2007+ (ACCDB) format to
take advantage of new features, but keep your back-end in the old (MDB)
format, and ULS will continue to work. You really only need to convert the
back-end if you wish to use the new table features such as multi-value
fields and attachments. Personally, I wouldn't touch either of those
"features" with a barge pole!
If you are moving forward to a 2007+ (ACCDB) format for your back-end data,
then you will need to "roll your own" security, and it is probably best to
base this on the Windows username as John has suggested. However, I
recommend you do not use the Environ("Username") function to fetch the
username, as it is much easier to spoof the username returned than it is to
crack the user-level security!
The following function will return the Windows username in a way that cannot
be spoofed:
Private Declare Function GetUserName _
Lib "advapi32.dll" _
Alias "GetUserNameA" ( _
ByVal lpBuffer As String, _
nSize As Long _
) As Long
Public Function WindowsUsername() As String
Static sUsername As String
Dim nLen As Long
If Len(sUsername) = 0 Then
nLen = 100
sUsername = String(nLen, 0)
GetUserName sUsername, nLen
If nLen > 0 Then sUsername = Left(sUsername, nLen - 1)
End If
WindowsUsername = sUsername
End Function
I suggest, rather than hard-coding actual usernames in your code, you create
a table of "Roles" (Admin, Accounts, Sales, OfficeBoy, etc) and another of
"UserRoles", linking each windows username to the role(s) for that person.
It is then each to do a lookup to ascertain if the current user holds a
particular role, in the same way you can ascertain group membership with
ULS.
Hope this helps!
Best wishes,
Graham
==========================================
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Thursday, 1 November 2012 05:17
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] First record in a form
Phil-
Conversion is pretty straightforward. Any custom menus and toolbars you
have move to a "user" tab on the Ribbon, but I suppose you've already
discovered that if you have any. Moving up does let you take advantage of
new features available only in the newer database format. Because ULS
will be going away, you'll have to come up with another way to "secure"
your database. The Environ("UserName") function lets you fairly reliably
fetch the Windows login ID of the current user. You perhaps can use that
instead in your existing code. But you will have to add code to "lock
down" forms and reports yourself. For users who have read-only access,
open the form in read only mode. Hide the Navigation Pane, define and set
the "AllowBypassKey" property, and give your users a .accde file to run
that has all the code stripped out of it.
We still haven't figured out why that one form behaves differently with
different security settingsŠ
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)
-----Original Message-----
From: Phil Knowles <pdk444444@yahoo.co.uk>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, October 31, 2012 4:50 PM
To: "MS_Access_Professionals@yahoogroups.com"
<MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi John
I am in an mdb file along with a wif file - user level security seems to
work ok for what I want and I do get returned the correct user name (ie
the logged on user) when I am comparing currentuser() - (and not "admin")
which enables me to apply logic dependent on user.
I came from a 2003 version originally with this database and so stayed
with the mdb format that i am familiar with.
If it is easy to break and is not very reliable then I suppose that is a
concern.
I have no other experience of building databases in the more advanced
format or converting from an older format. What do you recommend? Is a
conversion from an mdb format trouble free? Are there any aspects of an
old database which don't work in the new format? Is it a steep learning
curve to convert? Any good articles covering what is required?
cheeers
Phil
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, 31 October 2012, 13:15
Subject: Re: [MS_AccessPros] First record in a form
Phil-
No, there is not CurrentGroup function. And if you're using CurrentUser,
that gets you the Access UserID from the old user-level security (ULS)
system. In most cases, it'll return "Admin" - which isn't very useful.
But that tells me you're in an mdb file and have set up user-level
security. Is that true? It's not very reliable and can be easily broken.
As should be obvious to you, ULS was deprecated as of 2007 and only works
in old-style mdb format files. It's not a way to go looking forward.
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Wednesday, October 31, 2012 1:36 PM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi John
We are using 2010.
I need to look more closely into differences but the first obvious
difference is that this particular form is called from (on click of a
button) a parameter entry form - I will provide more details later.
Is there a currentgroup() variable ?
cheers
Phil
________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Wednesday, 31 October 2012, 10:42
Subject: Re: [MS_AccessPros] First record in a form
Phil-
I need to know how these permissions are being enforced. What version of
Access are you using, and if 2003 or earlier, are you using the built-in
Access User-Level security? What is different about this form from the
ones that work regardless of the permission?
I'll just keep asking questions until you stumble on the answer!
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Wednesday, October 31, 2012 11:12 AM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
John
You have asked the key question - you are a true exorcist !!!
In Permissions on this form, the 2 groups were different
the permissions are Open/Run, Read Design, Modify Design and Administer
One group had all of these the other just had Open/Run
The group with Open/Run only was the group which was working properly -
the other group with all the permissions was getting the first record
I took away the 3 permissions and hey presto the current record is now
maintained in moving from datasheet view to form view.
I never imagined that this could be causing this problem - in fact when I
think about it, it must be this in conjunction with something else because
I have other forms with the same permissions setup which work consistently
when moving from datasheet view to form view.
With this in mind could you please explain why having those permissions
could cause the effect I was getting
Thankyou for all your help.
cheers
Phil
________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Wednesday, 31 October 2012, 9:39
Subject: Re: [MS_AccessPros] First record in a form
Phil-
Are there any restrictions applied to this form? If so, where is it done,
and what are the restrictions?
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Wednesday, October 31, 2012 10:30 AM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi John
Thanks for sticking with me on this one!
The reason for the 2 different groups is only to prevent or restrict
access to certain parts of the database.
For example, there are some reports that groupA can view but GroupB can't
and there are some areas of data where GroupA can delete data but GroupB
can't
These restrictions are achieved in the Manage Users and Permissions area
by setting diiferent permissions to the
reports/forms/queries/macros/tables for the 2 groups.
All logic that has been added does what it needs to do by actions
dependent on currentuser()
As I mentioned briefly before, I maybe could achieve some of this by
testing currentgroup() but I don't know and haven't checked whether such a
variable is available - so this is not relevant to my current problem -
but could you alos please tell me if currentgroup() is valid?
cheers
Phil
PS. Hi Liz - haha - I'm hoping John is my exorcist on this one!
________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Tuesday, 30 October 2012, 21:20
Subject: Re: [MS_AccessPros] First record in a form
Phil-
What is different about GroupA and GroupB then? You claim that all
authorization is done by user, but then why have two groups? There must
be something that is different about how your app is handled between the
two groups.
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Tuesday, October 30, 2012 8:29 PM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi John
the embedded macro 'code' is
Openform
Form Name Form of info
View form
Filter Name
Where condition = [id]=[forms]![FormA]![id]
Data Mode read only
window mode normal
but as i said - this works perfectly for all users.
I have just tried moving userA1 from GroupA to GroupB and then userB1 from
groupB to GroupA and the effect for those 2 users changes.
In other words, whatever is causing the 2 different effects is definitely
to do with the groups and not the individual users
This strengthens my view that the problem must somehow lie in the area of
permissions rather than logic - because I don't have ANY logic that is
based on groups ONLY logic based on users.
?????
cheers
Phil
________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Tuesday, 30 October 2012, 16:05
Subject: Re: [MS_AccessPros] First record in a form
Phil-
The only event that should be firing is the Current event when switching
between views. I just tried it with a simple form in 2007, and it
maintains the position when switching, but the Current event fires again.
You've got a Requery somewhere or something else is going on to put the
form back on the first row when switching views.
What, exactly, is the code in the Current Event embedded macro?
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Tuesday, October 30, 2012 4:36 PM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi John (and Glenn)
Glenn - I know something is in there somewhere that is causing this and I
accept that I might be blinded by being so close to the problem.
John- to answer your questions
there is an embedded macro in the 'on current' event of FormA which opens
a form (based on a query) of information relating to the current record.
This works fine for both groups of users and there is nothing in it to
distinguish between the 2 types of users.
All of the users in both groups can see the built in access menus /
toolbars / ribbon and the same thing occurs if they use the drop down view
options on the 'home' tab of the ribbon or use the right mouse button to
select form view.
It is not related to how the user is switching views because I am logging
on myself as the different users and using the same switching methods
cheers
Phil
________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Tuesday, 30 October 2012, 13:50
Subject: Re: [MS_AccessPros] First record in a form
Phil-
Is there any code in "FormA" that runs in the Open, Load, or Current
event? How do users "switch to Form view?" Are users allowed to use the
built-in Access menus / toolbars / Ribbon?
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Tuesday, October 30, 2012 2:22 PM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi Glenn
Thanks for your prompt reply.
There is no logic in the whole system that differentiates between either
GroupA or GroupB or the users in those 2 groups - any logic that there is
is to deal with users of GroupC - users of groupA and groupB always fall
into the 'else' logic of an IF statement and therefore all conditions
should be exactly the same. Or should they?
The only differences between GroupA and GroupB users lie in the
permissions area.
cheers
Phil
________________________________
From: Glenn Lloyd <mailto:argeedblu%40gmail.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Tuesday, 30 October 2012, 11:22
Subject: RE: [MS_AccessPros] First record in a form
Phil,
It is difficult to diagnose this sort of problem without being able to see
the actual form and the code associated with it, or at least the code. My
guess is that there is something in either the Load or Open event handler
for the form that tests either the user name or group and adjusts the
recordset accordingly.
Glenn
From: mailto:MS_Access_Professionals%40yahoogroups.com
[mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of
Phil Knowles
Sent: October-30-12 7:07 AM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] First record in a form
Hi Guys
I got no response on this particular problem and I could really do with
some guidance - I'll try and explain it more fully.
I have 2 groups of user - say GroupA and GroupB
GroupA has 2 users - say UserA1 and User A2
GroupB has 2 users - say UserB1 and UserB2
All the users use FormA with datasheet view as default.
When GroupA users change from datasheet view to form view the record
displayed is whatever record was current in datasheet view but when GroupB
users change views the record displayed is always the first record of the
datasheet view (ie it seems to lose the current record flag)
GroupB has higher privileges than GroupA and I am also in GroupB and in
addition I have ADMINS privileges but it also happens to me. (ie when I
change views I get the first record rather than the current record)
Everything other than permissions is the same for both groups so I have
been searching in there for an answer but am now stuck.
There is some code in a macro to direct a completely different group of
users (GroupC) to a different form based on an If command dependent on
current user along the lines of
if current user()="userC1" then openform FormB else openform FormA
*** [Side Note - I bet I could achieve this by testing current group
rather than current user - is this possible]
Has anyone any ideas about what this could be or how I could track it down?
cheers
Phil
________________________________
From: pdk444444 <mailto:pdk444444%40yahoo.co.uk
<mailto:pdk444444%40yahoo.co.uk> >
To: mailto:MS_Access_Professionals%40yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Tuesday, 23 October 2012, 20:39
Subject: [MS_AccessPros] First record in a form
I have a form which my users display in datasheet view and then switch to
form view.
It behaves differently for 2 groups of users.
For one group it retains the record from datasheet view but for the other
group it reverts to the first record.
The group for which it reverts have higher privileges than the other
group!!
What could be causing it to revert to the first record?
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
------------------------------------
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)
<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
Rabu, 31 Oktober 2012
[MS_AccessPros] Re: Change Visible propery of control on parent form.
Thanks John! That's perfect.
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Doyce-
>
> No need for the If Me.Dirty test - I think that will return False all the
> time because you can Cancel the Dirty event. Just do:
>
> Me.Parent.btnSave.Visible = True
>
> 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)
>
>
>
> -----Original Message-----
> From: dnwinberry <winberry.doyce@...>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Wednesday, October 31, 2012 6:11 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Change Visible propery of control on parent form.
>
> Hello Friends!
> I'm trying to make a button visible on a parent form whenever the sub form
> gets dirty. In the sub form's dirty event I have the following code that
> is not working:
> If Me.Dirty Then
> Forms!frmCitySemiInquiry!btnSave.Visible = True
> End If
>
> The parent form is frmCitySemiInquiry. The sub form is [TagDates Subform]
>
> Seems simple enough but I just can't get this to work.
>
> Doyce
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
.
__,_._,___
Re: [MS_AccessPros] Change Visible propery of control on parent form.
Doyce-
No need for the If Me.Dirty test - I think that will return False all the
time because you can Cancel the Dirty event. Just do:
Me.Parent.btnSave.Visible = True
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)
-----Original Message-----
From: dnwinberry <winberry.doyce@roadsysinc.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, October 31, 2012 6:11 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Change Visible propery of control on parent form.
Hello Friends!
I'm trying to make a button visible on a parent form whenever the sub form
gets dirty. In the sub form's dirty event I have the following code that
is not working:
If Me.Dirty Then
Forms!frmCitySemiInquiry!btnSave.Visible = True
End If
The parent form is frmCitySemiInquiry. The sub form is [TagDates Subform]
Seems simple enough but I just can't get this to work.
Doyce
------------------------------------
Yahoo! Groups Links
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
.
__,_._,___
Re: [MS_AccessPros] Relationship Problems
adding on ...
you can only enforce referential integrity if the relationship is in the same table as the tables. RI is important to ensure data integrity
Tip: Enforce Referential Integrity on Access Relationships (cc) closed-caption
http://www.youtube.com/watch?v=_zxxc9jzWEg
Warm Regards,
Crystal
*
(: have an awesome day :)
*
________________________________
From: Glenn Lloyd <argeedblu@gmail.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, October 31, 2012 8:40 AM
Subject: RE: [MS_AccessPros] Relationship Problems
You maintain relationships in the backend Bill.
Glenn
<mailto:glenn@rgservices.ca> signature block_OTM
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Singer
Sent: October-31-12 10:33 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Relationship Problems
I am having some relationship problems.I need help, I have been spending too
much time with my computer and my relationships are crumbling. (just for
fun)
Here is my relationship problem. I split the database. It appeared to be
working great. This morning I discovered a problem. Sometime in the last
week, since I split the database, one of my tables lost the primary key
designation, which erased a lot of my relationships between my tables. That
has caused problems with some of the queries. I figure out which one. I
corrected the primary key designation. Now my queries run fine. However,
when I go to the relationship screen in the Front end database and try to
reinstate the relationships I am not allowed to enforce Referential
integrity and cascade updates. Those options are greyed out.
Where am I supposed to establish the relationships? In the front end or the
back end?
Thanks,
Bill Singer
[Non-text portions of this message have been removed]
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
.
__,_._,___
Re: [MS_AccessPros] Forms with many combo boxes that look up other records
Just keep "plugging" Connie!
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)
-----Original Message-----
From: mrsgoudge <no_reply@yahoogroups.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, October 31, 2012 6:20 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Forms with many combo boxes that look up
other records
John-- even though I don't like your answer I thank you! ;-)
I'm pondering for now while I work on changing opening of a bunch of forms
to acdialog as you recommended.
Part of the scary part of this is that we use the forms to drill down into
the data, so if they want more info there's often a button to open that
form to that record. I think I could convert that kind of situation to
open a form with the combos removed.
Will have to use the unbound box/button in a few places since all
properties need to be available to be selected for a new listing.
Will have to go through the forms and see what the needs are.
I'll be back! (Unless my boss pulls the plug.)
Connie
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...>
wrote:
>
> Connie-
>
> Are there cases where you might want to limit your lookups to "active"
> properties and addresses only? Do you do anything to eliminate duplicate
> addresses? For example, Access would think "500 W Main Street", "500
>West
> Main Street", and "500 W Main St." are all different addresses.
>
> Having said all that, a combo box with fewer than 1,000 entries in a
>small
> database should be no problem as long as each user's machine has plenty
>of
> memory and the fields are indexed appropriately. 1,143 contacts in a
> combo is pushing the envelope. And surely you don't expect the user to
> have to scroll through all 1000+ entries just to find the contact name
> wanted! I realize Access does help out if you start to type characters,
> but it still seems like an excessive list. As the database gets bigger,
> you'll have to consider using a technique like providing an unbound box
> where the user can type the last name or part of the last name and press
> Enter to pop up a list filtered only by that last name value to do a
>final
> selection.
>
> Just some initial thoughts?
>
> 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)
>
>
>
> -----Original Message-----
> From: mrsgoudge <no_reply@yahoogroups.com>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Tuesday, October 30, 2012 5:00 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Forms with many combo boxes that look up other
> records
>
> Good morning!
>
> I have several forms with combo boxes that look up other records and am
> wondering what sort of issues might be encountered down the road as the
> number of records increases.
>
> We are realtors in a small town/one county area that has no MLS. Few new
> houses are built each year and the population tends to be declining. Our
> population in the county excluding students is around 8500 with 4000
> housing units according to the Census and 19% of those are in apartments.
>
> The first example is the Listings Form:
> 1. 3 combo boxes with Address, ParcelID, HomeID each currently having
>669
> and less than 200/year added to each at the current rate.
> 2. A subform which is a continuous form with contacts--currently have
> 1143 and about 625 will be added during 2012. I would anticipate that in
> following years it will be the same amount or less that will be added
>each
> year since it's increasingly likely that many will be repeat buyers.
>
> The second example would be the Tracking Form with the following combos:
> 1. Property Name--This looks up all listings. Currently we have 669.
> Less than 200 will be added each year.
> 2. Address--Currently we have 669. Less than 200 will be added each
>year
> at the current rate. This is the same as Property Name except a different
> way to approach it. We generally refer/remember by Property Name but
> there are a few listings that only have the address so we need both.
> 3. Buyers -- Approx 100 will be added each year at our current rate.
> 4. Unbound boxes in the header that are part of creating the filter.
> There are two which duplicate #1 and #2 above.
>
> Thanks!
> Connie
>
>
>
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
.
__,_._,___
Re: [MS_AccessPros] Forms with many combo boxes that look up other records
John-- even though I don't like your answer I thank you! ;-)
I'm pondering for now while I work on changing opening of a bunch of forms to acdialog as you recommended.
Part of the scary part of this is that we use the forms to drill down into the data, so if they want more info there's often a button to open that form to that record. I think I could convert that kind of situation to open a form with the combos removed.
Will have to use the unbound box/button in a few places since all properties need to be available to be selected for a new listing.
Will have to go through the forms and see what the needs are.
I'll be back! (Unless my boss pulls the plug.)
Connie
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
> Are there cases where you might want to limit your lookups to "active"
> properties and addresses only? Do you do anything to eliminate duplicate
> addresses? For example, Access would think "500 W Main Street", "500 West
> Main Street", and "500 W Main St." are all different addresses.
>
> Having said all that, a combo box with fewer than 1,000 entries in a small
> database should be no problem as long as each user's machine has plenty of
> memory and the fields are indexed appropriately. 1,143 contacts in a
> combo is pushing the envelope. And surely you don't expect the user to
> have to scroll through all 1000+ entries just to find the contact name
> wanted! I realize Access does help out if you start to type characters,
> but it still seems like an excessive list. As the database gets bigger,
> you'll have to consider using a technique like providing an unbound box
> where the user can type the last name or part of the last name and press
> Enter to pop up a list filtered only by that last name value to do a final
> selection.
>
> Just some initial thoughtsŠ
>
> 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)
>
>
>
> -----Original Message-----
> From: mrsgoudge <no_reply@yahoogroups.com>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Tuesday, October 30, 2012 5:00 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Forms with many combo boxes that look up other
> records
>
> Good morning!
>
> I have several forms with combo boxes that look up other records and am
> wondering what sort of issues might be encountered down the road as the
> number of records increases.
>
> We are realtors in a small town/one county area that has no MLS. Few new
> houses are built each year and the population tends to be declining. Our
> population in the county excluding students is around 8500 with 4000
> housing units according to the Census and 19% of those are in apartments.
>
> The first example is the Listings Form:
> 1. 3 combo boxes with Address, ParcelID, HomeID each currently having 669
> and less than 200/year added to each at the current rate.
> 2. A subform which is a continuous form with contacts--currently have
> 1143 and about 625 will be added during 2012. I would anticipate that in
> following years it will be the same amount or less that will be added each
> year since it's increasingly likely that many will be repeat buyers.
>
> The second example would be the Tracking Form with the following combos:
> 1. Property Name--This looks up all listings. Currently we have 669.
> Less than 200 will be added each year.
> 2. Address--Currently we have 669. Less than 200 will be added each year
> at the current rate. This is the same as Property Name except a different
> way to approach it. We generally refer/remember by Property Name but
> there are a few listings that only have the address so we need both.
> 3. Buyers -- Approx 100 will be added each year at our current rate.
> 4. Unbound boxes in the header that are part of creating the filter.
> There are two which duplicate #1 and #2 above.
>
> Thanks!
> Connie
>
>
>
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
.
__,_._,___
[MS_AccessPros] Change Visible propery of control on parent form.
Hello Friends!
I'm trying to make a button visible on a parent form whenever the sub form gets dirty. In the sub form's dirty event I have the following code that is not working:
If Me.Dirty Then
Forms!frmCitySemiInquiry!btnSave.Visible = True
End If
The parent form is frmCitySemiInquiry. The sub form is [TagDates Subform]
Seems simple enough but I just can't get this to work.
Doyce
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (1) |
.
__,_._,___
Re: [MS_AccessPros] First record in a form
Phil-
Conversion is pretty straightforward. Any custom menus and toolbars you
have move to a "user" tab on the Ribbon, but I suppose you've already
discovered that if you have any. Moving up does let you take advantage of
new features available only in the newer database format. Because ULS
will be going away, you'll have to come up with another way to "secure"
your database. The Environ("UserName") function lets you fairly reliably
fetch the Windows login ID of the current user. You perhaps can use that
instead in your existing code. But you will have to add code to "lock
down" forms and reports yourself. For users who have read-only access,
open the form in read only mode. Hide the Navigation Pane, define and set
the "AllowBypassKey" property, and give your users a .accde file to run
that has all the code stripped out of it.
We still haven't figured out why that one form behaves differently with
different security settingsŠ
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)
-----Original Message-----
From: Phil Knowles <pdk444444@yahoo.co.uk>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, October 31, 2012 4:50 PM
To: "MS_Access_Professionals@yahoogroups.com"
<MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi John
I am in an mdb file along with a wif file - user level security seems to
work ok for what I want and I do get returned the correct user name (ie
the logged on user) when I am comparing currentuser() - (and not "admin")
which enables me to apply logic dependent on user.
I came from a 2003 version originally with this database and so stayed
with the mdb format that i am familiar with.
If it is easy to break and is not very reliable then I suppose that is a
concern.
I have no other experience of building databases in the more advanced
format or converting from an older format. What do you recommend? Is a
conversion from an mdb format trouble free? Are there any aspects of an
old database which don't work in the new format? Is it a steep learning
curve to convert? Any good articles covering what is required?
cheeers
Phil
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, 31 October 2012, 13:15
Subject: Re: [MS_AccessPros] First record in a form
Phil-
No, there is not CurrentGroup function. And if you're using CurrentUser,
that gets you the Access UserID from the old user-level security (ULS)
system. In most cases, it'll return "Admin" - which isn't very useful.
But that tells me you're in an mdb file and have set up user-level
security. Is that true? It's not very reliable and can be easily broken.
As should be obvious to you, ULS was deprecated as of 2007 and only works
in old-style mdb format files. It's not a way to go looking forward.
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Wednesday, October 31, 2012 1:36 PM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi John
We are using 2010.
I need to look more closely into differences but the first obvious
difference is that this particular form is called from (on click of a
button) a parameter entry form - I will provide more details later.
Is there a currentgroup() variable ?
cheers
Phil
________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Wednesday, 31 October 2012, 10:42
Subject: Re: [MS_AccessPros] First record in a form
Phil-
I need to know how these permissions are being enforced. What version of
Access are you using, and if 2003 or earlier, are you using the built-in
Access User-Level security? What is different about this form from the
ones that work regardless of the permission?
I'll just keep asking questions until you stumble on the answer!
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Wednesday, October 31, 2012 11:12 AM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
John
You have asked the key question - you are a true exorcist !!!
In Permissions on this form, the 2 groups were different
the permissions are Open/Run, Read Design, Modify Design and Administer
One group had all of these the other just had Open/Run
The group with Open/Run only was the group which was working properly -
the other group with all the permissions was getting the first record
I took away the 3 permissions and hey presto the current record is now
maintained in moving from datasheet view to form view.
I never imagined that this could be causing this problem - in fact when I
think about it, it must be this in conjunction with something else because
I have other forms with the same permissions setup which work consistently
when moving from datasheet view to form view.
With this in mind could you please explain why having those permissions
could cause the effect I was getting
Thankyou for all your help.
cheers
Phil
________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Wednesday, 31 October 2012, 9:39
Subject: Re: [MS_AccessPros] First record in a form
Phil-
Are there any restrictions applied to this form? If so, where is it done,
and what are the restrictions?
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Wednesday, October 31, 2012 10:30 AM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi John
Thanks for sticking with me on this one!
The reason for the 2 different groups is only to prevent or restrict
access to certain parts of the database.
For example, there are some reports that groupA can view but GroupB can't
and there are some areas of data where GroupA can delete data but GroupB
can't
These restrictions are achieved in the Manage Users and Permissions area
by setting diiferent permissions to the
reports/forms/queries/macros/tables for the 2 groups.
All logic that has been added does what it needs to do by actions
dependent on currentuser()
As I mentioned briefly before, I maybe could achieve some of this by
testing currentgroup() but I don't know and haven't checked whether such a
variable is available - so this is not relevant to my current problem -
but could you alos please tell me if currentgroup() is valid?
cheers
Phil
PS. Hi Liz - haha - I'm hoping John is my exorcist on this one!
________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Tuesday, 30 October 2012, 21:20
Subject: Re: [MS_AccessPros] First record in a form
Phil-
What is different about GroupA and GroupB then? You claim that all
authorization is done by user, but then why have two groups? There must
be something that is different about how your app is handled between the
two groups.
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Tuesday, October 30, 2012 8:29 PM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi John
the embedded macro 'code' is
Openform
Form Name Form of info
View form
Filter Name
Where condition = [id]=[forms]![FormA]![id]
Data Mode read only
window mode normal
but as i said - this works perfectly for all users.
I have just tried moving userA1 from GroupA to GroupB and then userB1 from
groupB to GroupA and the effect for those 2 users changes.
In other words, whatever is causing the 2 different effects is definitely
to do with the groups and not the individual users
This strengthens my view that the problem must somehow lie in the area of
permissions rather than logic - because I don't have ANY logic that is
based on groups ONLY logic based on users.
?????
cheers
Phil
________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Tuesday, 30 October 2012, 16:05
Subject: Re: [MS_AccessPros] First record in a form
Phil-
The only event that should be firing is the Current event when switching
between views. I just tried it with a simple form in 2007, and it
maintains the position when switching, but the Current event fires again.
You've got a Requery somewhere or something else is going on to put the
form back on the first row when switching views.
What, exactly, is the code in the Current Event embedded macro?
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Tuesday, October 30, 2012 4:36 PM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi John (and Glenn)
Glenn - I know something is in there somewhere that is causing this and I
accept that I might be blinded by being so close to the problem.
John- to answer your questions
there is an embedded macro in the 'on current' event of FormA which opens
a form (based on a query) of information relating to the current record.
This works fine for both groups of users and there is nothing in it to
distinguish between the 2 types of users.
All of the users in both groups can see the built in access menus /
toolbars / ribbon and the same thing occurs if they use the drop down view
options on the 'home' tab of the ribbon or use the right mouse button to
select form view.
It is not related to how the user is switching views because I am logging
on myself as the different users and using the same switching methods
cheers
Phil
________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Tuesday, 30 October 2012, 13:50
Subject: Re: [MS_AccessPros] First record in a form
Phil-
Is there any code in "FormA" that runs in the Open, Load, or Current
event? How do users "switch to Form view?" Are users allowed to use the
built-in Access menus / toolbars / Ribbon?
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)
-----Original Message-----
From: Phil Knowles <mailto:pdk444444%40yahoo.co.uk>
Reply-To: <mailto:MS_Access_Professionals%40yahoogroups.com>
Date: Tuesday, October 30, 2012 2:22 PM
To: "mailto:MS_Access_Professionals%40yahoogroups.com"
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] First record in a form
Hi Glenn
Thanks for your prompt reply.
There is no logic in the whole system that differentiates between either
GroupA or GroupB or the users in those 2 groups - any logic that there is
is to deal with users of GroupC - users of groupA and groupB always fall
into the 'else' logic of an IF statement and therefore all conditions
should be exactly the same. Or should they?
The only differences between GroupA and GroupB users lie in the
permissions area.
cheers
Phil
________________________________
From: Glenn Lloyd <mailto:argeedblu%40gmail.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Tuesday, 30 October 2012, 11:22
Subject: RE: [MS_AccessPros] First record in a form
Phil,
It is difficult to diagnose this sort of problem without being able to see
the actual form and the code associated with it, or at least the code. My
guess is that there is something in either the Load or Open event handler
for the form that tests either the user name or group and adjusts the
recordset accordingly.
Glenn
From: mailto:MS_Access_Professionals%40yahoogroups.com
[mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of
Phil Knowles
Sent: October-30-12 7:07 AM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] First record in a form
Hi Guys
I got no response on this particular problem and I could really do with
some guidance - I'll try and explain it more fully.
I have 2 groups of user - say GroupA and GroupB
GroupA has 2 users - say UserA1 and User A2
GroupB has 2 users - say UserB1 and UserB2
All the users use FormA with datasheet view as default.
When GroupA users change from datasheet view to form view the record
displayed is whatever record was current in datasheet view but when GroupB
users change views the record displayed is always the first record of the
datasheet view (ie it seems to lose the current record flag)
GroupB has higher privileges than GroupA and I am also in GroupB and in
addition I have ADMINS privileges but it also happens to me. (ie when I
change views I get the first record rather than the current record)
Everything other than permissions is the same for both groups so I have
been searching in there for an answer but am now stuck.
There is some code in a macro to direct a completely different group of
users (GroupC) to a different form based on an If command dependent on
current user along the lines of
if current user()="userC1" then openform FormB else openform FormA
*** [Side Note - I bet I could achieve this by testing current group
rather than current user - is this possible]
Has anyone any ideas about what this could be or how I could track it down?
cheers
Phil
________________________________
From: pdk444444 <mailto:pdk444444%40yahoo.co.uk
<mailto:pdk444444%40yahoo.co.uk> >
To: mailto:MS_Access_Professionals%40yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Tuesday, 23 October 2012, 20:39
Subject: [MS_AccessPros] First record in a form
I have a form which my users display in datasheet view and then switch to
form view.
It behaves differently for 2 groups of users.
For one group it retains the record from datasheet view but for the other
group it reverts to the first record.
The group for which it reverts have higher privileges than the other
group!!
What could be causing it to revert to the first record?
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (31) |
.
__,_._,___
Langganan:
Postingan (Atom)