Auto-populate User's name field

[Jay]

I'm looking to revise a DB at work for collecting call details. If I'm
going to have two or three users entering data to the same table, how
can I arrange for the StaffName field in the main table to be
auto-populated with the user's name, to prevent them from having to
select it for each call record. I assume I'm going to have to capture
it at some point - on entering the DB? like a sign-in?

I appreciate there may be a few ways to do it, but the simplest would be
ideal bearing in mind I'm a relative newbie and still slipping & sliding
my way up a steep learning curve.

I welcome your help.

Regards,

Jason

 

[tina]

recommend you create a tblStaff, to list all staff who enter calls in the
database (doesn't matter if it's 2 people, or 200), such as

tblStaff
StaffID (autonumber, primary key field)
FirstName
LastName
<if this is all the data you need to store about individual staff members,
that's fine.>

tblCalls
CallID (primary key field)
StaffID (foreign key from tblStaff)
<other fields that describe a call.>

each staff member may enter many calls, but each call is entered by one
staff member. that's the definition of a one-to-many relationship, with
tblStaff as the parent table, and tblCalls as the child table.

as you say, there are a number of ways to set up the data entry default
you're asking about. here's one suggestion: create a "Start" or "Login"
form, from which the user will open the data entry form. in the Start form,
create an unbound combo box control - i'll call it cboPerson; set its'
properties as follows

RowSource: SELECT StaffID, FirstName & " " & LastName As StaffName FROM
tblStaff ORDER BY FirstName, LastName;
<the above goes all on one line in the RowSource property.>
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0"; 1"
LimitToList: Yes

create a command button in the Start form, to open the data entry form,
using the following code, as

If IsNull(Me!cboPerson) Then
Msgbox "Choose your name from the droplist, please."
Me!cboPerson.SetFocus
Me!cboPerson.DropDown
Else
DoCmd.OpenForm "DataEntryFormName", , , , , , _
Me!cboPerson
End If

in the data entry form, add code to the form's BeforeInsert event procedure,
as

Me!StaffID = Me.OpenArgs

suggest you read up on the OpenForm action, and the OpenArgs property, so
you'll understand how the code works.

if you don't know how to create an event procedure, go to
http://home.att.net/~california.db/instructions.html and click the
CreateEventProcedure link for illustrated step-by-step instructions.

hth

 

[Steve Schapel]

Jay,

The StaffName will only be good if you can guarantee that you will
*never* have more than one staff member with the same name. Otherwise,
it is best to use an ID of some sort.
If you want simple, one way would be to have the staff member select
their ID/Name from a combobox on the login/menu form from which your
call details entry form is launched. Then, on the Open event of the
call details entry form, you can put code like this...
Me.StaffID.DefaultValue = Forms!MenuForm!StaffID
or, if you decide to stick with the name (and hence text instead of
numerical)...
Me.StaffName.DefaultValue = """" & Forms!MenuForm!StaffName & """"

 

[Jay]

Thanks Steve. There are only going to be three or four people using the
DB so I'm pretty confident that I'm unlikely to get two people with the
same name.

Your suggestion sounds great, but I'm a little unclear on the
'login/menu form' (my inexperience I'm afraid). Would the combobox
effectively not be linked to any table and then it's the Open event
prcocedure that automatically carries forward the StaffName selection
into the StaffName field of the main calldetails table?

So instead of having the combobox on the main form (and having to select
a value for each record), it's on a form before the main form, so that
it only has to be selected once. Is this the general principle?

Many thanks,

Jason
(And sorry for what may seem like simple questions)

 

[Steve Schapel]

Jason,

First of all, please never apologise for asking questions. "Simple", of
course, is in the eye of the beholder, but whatever, we're all here to
learn.

I made an assumption that your database would be set up such that you
have a form (which I tentatilvely named 'MenuForm') where your users can
select from various activities, one of which activities would be the
entry of call details, and hence they would have a button or some such
to launch the call details form. No reason, just that this is a
commonly used approach with Access applications, and you didn't tell us
anything to indicate otherwise

If this is what you have, then yes, the "general principle" is as you
stated. You can have a combobox where the user can select their name
(or, if you want to risk typos, a textbox where they can enter their
name), and then this can be set, via the code I gave you, as the Default
Value of the StaffName on the call details form, so this staff member's
name will be the one that is automatically entered for each new record -
until someone else takes over.

If, on the other hand, your application opens straight to the call
details form, then you could put the unbound combobox for the user's
name in the form header, and use the After Update event of the combobox
to set the Default Value of FormName - but if you do that, and if it's a
continuous view form, it will require a little finetuning to get this
working for the first new record. Either that, or use the Before Insert
event of the form, similar to what Tina suggested.

Hope that didn't make it more confusing!

 

[Jay]

Hi Steve. I'd just like to thank you and Tina for your suggestions.

I went with your suggestion initially Steve. If only because I'm trying
not to bite off more than I can chew at the moment (getting too involved
in VB code until I figure out the basics

Anyway I've got it working, doing as you advised. It took a while as I
didn't realise at first that the 'StaffName' after the MenuForm!
referred to the combobox and not the table field. Anyway I figured it
out (I'd called it cboStaffName - you see,naming conventions are one
thing I *have* picked up at least

But one thing I'd like is if the StaffName field in the Calls Form (the
one that we've been auto-populating) is excluded from the Tab Order.

At the moment, the Calls form opens with the StaffName field completed
but the cursor is still in the field. And from what I can see changing
the tab order will just make the cursor enter the field after all the
other fields.

Can it be excluded? Considering it will never be changed from within
the Calls Form? Or would it need putting in the header or something?

Many thanks for your help,

Regards

Jay

 

[Steve Schapel]

Jay,

I am delighted to hear that you are making such good progress with this.

In design view of the form, select the StaffName control, and look at
the Properties. You will see a property called Tab Stop - set it to No.
If you like, an alternative would be to set the Enabled property to No
and the Locked property to Yes.

 

[Jay]

Hi Steve,

Thanks for that, I'm really learning a lot from you. One thing I want to
add is this:

In my FrmMenu, where the user selects their name from a combo box (to
auto-populate the staffname field in the calls form) I have a command button
to open the Calls Form. I added this and simply chose the Open form option
from the wizard.

However, it opens the Calls form but leaves the Menu form open. How can I
have it close the Menu form as well. I tried just pasting DoCmd.Close from
another DB I use but it didn't work. The code behind the command button
even procedure is:


Private Sub cmdOpenFrmCalls_Click()
On Error GoTo Err_cmdOpenFrmCalls_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmCalls"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenFrmCalls_Click:
Exit Sub

Err_cmdOpenFrmCalls_Click:
MsgBox Err.Description
Resume Exit_cmdOpenFrmCalls_Click

End Sub

Now I don't claim to understand half of this, but what can I add so that
FrmMenu closes, as well as FrmCalls opening. And is this something that has
to be done like this? in the code window only?

Many thanks,

Jason

 

[Steve Schapel]

Jay,

Change it like this...

Private Sub cmdOpenFrmCalls_Click()
On Error GoTo Err_cmdOpenFrmCalls_Click

DoCmd.OpenForm "FrmCalls"
DoCmd.Close acForm, Me.Name

Exit_cmdOpenFrmCalls_Click:
Exit Sub

Err_cmdOpenFrmCalls_Click:
MsgBox Err.Description
Resume Exit_cmdOpenFrmCalls_Click

End Sub

Your choices are pretty much to use VBA procedure like this, or else a
macro. Either would be fine. Future versions of Access will have more
of an emphasis on macros, rather than code, for tasks like this.