long question

J

JoMcGuire

this program is tracking documents, in this case scanned "books"
(scentist notes)

In a single row on the form there is:

Check box Scanned Date Scanned by
Name chkScanned txtScannedDate cmbScannedBy
ctrl BookScanned BookScannedDate BookScannedBy

1)cmbScannedBy is a cmb box populated by a qry pulling active staff
members: SELECT [qryStaff_Active].[FullName], [qryStaff_Active].[Login]
FROM qryStaff_Active;

2)cmbScannedBy is also auto-populated with current user's name when
check box is checked.. cleared when check box is unchecked.. here's
that vb code

If Me.chkScanned.Value Then
Me.cmbScannedBy = CurrentUser()
'Me.BookScannedBy = cmbScannedBy.Column(1)
Me.txtScannedDate.Value = Now()

Else
Me.cmbScannedBy.Value = Null
Me.txtScannedDate = Null
End If

3) the cmb box must still be 'editable' after it is auto populated, as
in.. If I'm the user, it autopopulates my name, but then I need the
option to drop down and select someone else. I'm not sure of the
practical application of this feature, but it is the user's request...
thus..both 1) & 2)

The user wants to see the user name, not the login captured with
CurrentUser() on the screen and have the user name recorded in the
database. The properties of the cmbScannedby combo box have been set
to pull the correct colum of the query that puts the username in the
form, infact, from the screen view this is working perfectly.

I can't seem to get the user's name recording in the database, no
matter what I do..it still records the user ID. You can see that I
commented out one attempt that didn't work. I also tried that same
line Me.BookScannedBy=cmbScannedBy.column(1) in event properties,
afterupdate, beforeupdate..etc.

Any advice ?
 
J

John Nurick

Hi Jo,

If you want to store the FullName of the logged-in user, use something
like this:

Me.cmbScannedBy = DLookup("FullName", "qryStaff_Active", _
"Login='" & CurrentUser() & "'")

In that case you don't need to have the second (Login) column in the
combobox.

But what happens if there are two users with the same name (two Smith,
Johns, say)? They'll have different user names, but if you store the
FullName you won't be able to tell them apart later. Maybe you _should_
store the Login rather than the FullName. In that case, you should have
Login as the _first_ column in the combo box, not the second. If you set
the ColumnWidth property of the combo box to 0 it will hide the first
column and display only the FullName). So the query for the combo box
would be

SELECT LogIn, FullName FROM qryStaff_Active ORDER BY FullName;


this program is tracking documents, in this case scanned "books"
(scentist notes)

In a single row on the form there is:

Check box Scanned Date Scanned by
Name chkScanned txtScannedDate cmbScannedBy
ctrl BookScanned BookScannedDate BookScannedBy

1)cmbScannedBy is a cmb box populated by a qry pulling active staff
members: SELECT [qryStaff_Active].[FullName], [qryStaff_Active].[Login]
FROM qryStaff_Active;

2)cmbScannedBy is also auto-populated with current user's name when
check box is checked.. cleared when check box is unchecked.. here's
that vb code

If Me.chkScanned.Value Then
Me.cmbScannedBy = CurrentUser()
'Me.BookScannedBy = cmbScannedBy.Column(1)
Me.txtScannedDate.Value = Now()

Else
Me.cmbScannedBy.Value = Null
Me.txtScannedDate = Null
End If

3) the cmb box must still be 'editable' after it is auto populated, as
in.. If I'm the user, it autopopulates my name, but then I need the
option to drop down and select someone else. I'm not sure of the
practical application of this feature, but it is the user's request...
thus..both 1) & 2)

The user wants to see the user name, not the login captured with
CurrentUser() on the screen and have the user name recorded in the
database. The properties of the cmbScannedby combo box have been set
to pull the correct colum of the query that puts the username in the
form, infact, from the screen view this is working perfectly.

I can't seem to get the user's name recording in the database, no
matter what I do..it still records the user ID. You can see that I
commented out one attempt that didn't work. I also tried that same
line Me.BookScannedBy=cmbScannedBy.column(1) in event properties,
afterupdate, beforeupdate..etc.

Any advice ?
Click to expand...
 
J

JoMcGuire

Ahhhhhh.. thank you very much. Both suggestions are right-on. Trust
me.. I have argued that saving the user ID is the better solution, but
since this is a change to an existing database, they've been selecting
from a drop down for 4 years which has been saving the username.. and
they don't want to change. The first solution meets the end user's
request perfectly.
THANKS !
 
Top