Multiple unique keys

[Dani]

I want to be able to have a system where a user inputs their initals on entry and then these are automatically tagged onto all data input after that point, ie automatically put into every record. Is this possible? If so how can I make sure that the initials entered are unique. I cannot define it as a unique key as more than one record will belong to each user in each table.

 

[Cheryl Fischer]

Dani,

Instead of users' initials, you could use each user's network login id,
which will be unique. There is code for this function at:

http://www.mvps.org/access/api/api0008.htm

Simply copy this function into a Module and you can use the function anytime
you want to add the login id to a record.

The steps to use this function to "tag" new records with the user's login id
would generally be as follows:

1. Add the following fields to your table: AddedBy and DateAdded
2. Add controls bound to these fields to your data entry form. You can
make the Visible property of each False, if you want.
3. In the form's design view, add the following code to the Form's Before
Insert event:

Me!AddedBy = fOSUserName()
Me!DateAdded = Date()

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I want to be able to have a system where a user inputs their initals on

entry and then these are automatically tagged onto all data input after that
point, ie automatically put into every record. Is this possible? If so how
can I make sure that the initials entered are unique. I cannot define it as
a unique key as more than one record will belong to each user in each table.

 

[Cheryl Fischer]

Dani,

You can certainly do this, but you might want to consider using each user's
login id as a unique identifier. There is code for a function that will get
that information for you at:

http://www.mvps.org/access/modules/mdl0009.htm

Just copy the function into a Module and it will be available for you to use
whenever you want. The steps to "tag" a new record with the user's login id
are generally as follows:

1. Add the following fields to your table: AddedBy and DateAdded
2. Add controls bound to these fields to your data entry form. The Visible
property of each can be set to False, if you desire.
3. In the Before Insert event of the Form, add the following code:

Me!AddedBy = fOSUserName()
Me!DateAdded = Date()

hth,

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I want to be able to have a system where a user inputs their initals on

entry and then these are automatically tagged onto all data input after that
point, ie automatically put into every record. Is this possible? If so how
can I make sure that the initials entered are unique. I cannot define it as
a unique key as more than one record will belong to each user in each table.

 

[Cheryl Fischer]

I am not sure what you mean when you say "when they enter".

If you mean when they enter a new record, you can use the following in the
Before Insert event of the form:

Me!AddedBy = InputBox("Enter your initials:")
Me!DateAdded = Date()



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

The system is not networked, there is only one login id (it's a long story

to explain) and I can't change this. Would your method work if I used their
initials entered in an input box when they enter?

 

[Cheryl Fischer]

Hit the send button a little too soon ...

If you mean when they enter (open) the database, more is required:

1. You will need to create a Public variable that will be available
throughout the current Access session. To do this, open any Module and, at
the very top of the module, insert the following:

Public g_user as String

2. In the Open Event of your main form, you will need the following code:

g_user = InputBox("Enter your initials:")

The value of g_user will be available until the database is closed

3. When a user completes and saves a new record, you would use the
following code in the BeforeInsert event of the data entry form:

AddedBy = g_user
DateAdded = Date()

4. You will need to train your users to close the database when each has
finished a session, so that when a new user wants to enter records, he or
she will be forced to enter a new set of initials.

Of course, both possibilities offered will accept *any* initials. Unless
you also have a table containing acceptable initials and have some code to
compare the entered initials with acceptable initials, it is possible for
"mistakes" to get into the database.

hth,

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

The system is not networked, there is only one login id (it's a long story

to explain) and I can't change this. Would your method work if I used their
initials entered in an input box when they enter?

 

[John Vinson]

I want to be able to have a system where a user inputs their initals on entry and then these are automatically tagged onto all data input after that point, ie automatically put into every record. Is this possible? If so how can I make sure that the initials entered are unique. I cannot define it as a unique key as more than one record will belong to each user in each table.

So you want them to be unique, but you want to allow duplicates???

Since you're not networked and have no way for the computer to know
what user is at the keyboard, there is NO WAY that this can be done.
Suppose you have a user named Steve Jenkins, who dutifully replies SJ
when asked for initials. Then along comes Susan Jastrow. She types SJ
as well - how can the computer know that it's not Steve coming back to
put in another record?

I think you will need to (somehow) assign unique user ID's and store
them instead.

 

[Dani]

That's exactly what I'm after! I was thinking of having an extra table of used user initials and then doing a check. Thank you