Security log ins

[Guest]

I currently have a database in Access 2003 with user permissions and
passwords. When the relevant user logs in they go to the form and assign
themselves to a job.
However, at the moment it would be possible for them to sign in and assign a
job to another worker.

Is it possible to create a piece of security where a user logs in and can
only assign him/her self from the drop down list within the form?

Any help would be much appreciated,
Matt

 

[John Novak]

I currently have a database in Access 2003 with user permissions and
passwords. When the relevant user logs in they go to the form and assign
themselves to a job.
However, at the moment it would be possible for them to sign in and assign
a
job to another worker.

Is it possible to create a piece of security where a user logs in and can
only assign him/her self from the drop down list within the form?

Any help would be much appreciated,
Matt

 

[Guest]

It appears your response did not get posted John!

Could you please repost.

Many Thanks

 

[Joan Wild]

The CurrentUser() function will give you the Access username of the person
currently logged in. Instead of offering a combobox of usernames, just
automatically assign the username, using CurrentUser() to the field that
holds this information.

 

[Guest]

Joan,

I would remove the combo box, but where would i enter the currentuser()
formula. Would this be within the control source as if so i cannot get this
to work

Matt

 

[Guest]

Joan,

The only problem I have now is that when the quote is sent by an agent, the
corresponding report now doesnt show the name of the user who posted the
quote.
Is there anyway around this?

Matt

 

[Joan Wild]

You haven't really given me enough information for me to provide specifics.

Presumably you have a field in your table that stores the user to whom the
job is assigned. You can put a textbox on your form (set it's locked
property to yes, so the user can't change it), and set the default value of
the textbox to CurrentUser() - bind this textbox to the field in your table.

As for the report, you are now talking about a quote (I don't know if that's
a job or not), but take the same approach. Your report would be bound to
the table that includes the field with the user's username. Since this
would have been set using the form (I assume), then all you need is a
textbox on your report bound to this field.

If your Access username isn't friendly i.e. mdawson and you want the report
to show Matt Dawson, then you can create a table with three fields:
AccessUsername, FirstName, LastName. Then you can include this table in the
recordsource of the report, and have it show the real name like:
FirstName & " " & LastName

 

[Guest]

The problem I am having is that when the text/combo field is bound, the
default value of CurrentUser(), does not work. If the field is unbound, then
this is not a problem.
The report does not then work and the tables do not update accordingly if
the field is unbound.

The agents will be assigned a job, and when they log in and open the form
they type in a contract ID, go to that and call the owner and offer a quote.
If they issue one, this produces a report called quoted and this is listed by
Agent ID and their relevant quotes.

Does this make more sense?

Matt

 

[Joan Wild]

You shouldn't need a combobox anymore, since the user doesn't need to choose
their username.

Can you describe what 'does not work' means? Default values apply only to
new records; from your description it sounds like they aren't creating new
records, but looking up existing ones. Do they do something on the form to
'issue the quote'? Like maybe they enter the date of the call or a quote #
or update the record in some way.

If so you could use the afterupdate event of that control to set the
username field to currentuser().

Or if you have a button they click to produce the report, you could assign
the currentuser() name to the username field.

 

[Guest]

When the user goes into the form. they type in a request ID and then call
that related person in the hope of issuing a quote.

What I want to happen is that the Agent ID, which they previously selected
via a combo box, to show the current user in a text box but this does not
work when the field (Agent ID) is bound. I need it to be bound as this form
then updates the related reports!

Matt

 

[Joan Wild]

Matt, please remember that I cannot see what your form looks like, or how
the user does their job.

They type in a request ID - is this on a new record? Is this textbox bound?
Is it just a 'search' type box?

They call in the hope of issuing a quote. What if the call is unsuccessful?

Is the Agent ID the same as the username? I'm guessing that it's not, so...
In the afterupdate of the combobox that selects the Agent ID, you would use
Me!NameOfControlThatHoldsAccessUsername = CurrentUser()

It's assumed that this control is bound to the field in your table where you
store the Access username.
It doesn't prevent someone from selecting another Agent, however it will
store who updated the record.

If I were doing this, I'd add the username field to the Agent table. Have
the combobox rowsource restricted to the Agent ID of the
currentuser()...Select AgentID from AgentTable Where AccUserName =
CurrentUser()

 

[Guest]

Sorry, I realise you cannot see the form, I was trying to give an
understanding of it.

Request ID is unbound and is used to search for a record so in effect just a
search.

If the call is unsuccessful, then no worries and i will run a delete query
to rid these.

I have created it so that Agent ID is exactly the same as the username which
is why I was confused about it.

Where should I enter Select AgentID from AgentTable Where AccUserName =
CurrentUser() as this wouldnt be entered within control source???
Do I need to do this within SQL?

Matt

 

[Joan Wild]

Request ID is unbound and is used to search for a record so in effect
just a search.

OK, good.

I have created it so that Agent ID is exactly the same as the
username which is why I was confused about it.

OK Agent ID is a text field that equals their Access username.

Where should I enter Select AgentID from AgentTable Where AccUserName
= CurrentUser() as this wouldnt be entered within control source???

This would be put in the Row Source property for the combobox where they
select their AgentID. Since that is the same as their username, change it
to
Select [Agent ID] from AgentTable Where [Agent ID] = CurrentUser()

Change AgentTable to the name of the actual table that holds the Agent ID;
and put square brackets around the name if it has spaces.

 

[Guest]

Brilliant that works. Thank you very much

If it were to be a list box, how would I go about it as it does not have a
row source!
You have been a great help and it is much appreciated

Matt

Request ID is unbound and is used to search for a record so in effect
just a search.

OK, good.

I have created it so that Agent ID is exactly the same as the
username which is why I was confused about it.

OK Agent ID is a text field that equals their Access username.

Where should I enter Select AgentID from AgentTable Where AccUserName
= CurrentUser() as this wouldnt be entered within control source???

This would be put in the Row Source property for the combobox where they
select their AgentID. Since that is the same as their username, change it
to
Select [Agent ID] from AgentTable Where [Agent ID] = CurrentUser()

Change AgentTable to the name of the actual table that holds the Agent ID;
and put square brackets around the name if it has spaces.

 

[Joan Wild]

A listbox does have a row source, however I don't see the value of using a
listbox, since it is restricted to one entry.

--
Joan Wild
Microsoft Access MVP

Brilliant that works. Thank you very much

If it were to be a list box, how would I go about it as it does not
have a row source!
You have been a great help and it is much appreciated

Matt

Request ID is unbound and is used to search for a record so in
effect just a search.

OK, good.

I have created it so that Agent ID is exactly the same as the
username which is why I was confused about it.

OK Agent ID is a text field that equals their Access username.

Where should I enter Select AgentID from AgentTable Where
AccUserName = CurrentUser() as this wouldnt be entered within
control source???

This would be put in the Row Source property for the combobox where
they select their AgentID. Since that is the same as their
username, change it to
Select [Agent ID] from AgentTable Where [Agent ID] = CurrentUser()

Change AgentTable to the name of the actual table that holds the
Agent ID; and put square brackets around the name if it has spaces.