Default value based on data in the same record

[Sam D]

Hi and thanks for your thoughts,

A Students table (and form) has LastName, FirstName and UserName fields
(controls). I've written a function to create/check UserName.

This code is executing in the forms beforeupdate event:

UserName = GetUniqueUserName(StudentID, LastName, FirstName, UserName)

The function is:

Public Function GetUniqueUserName(StudentID, LastName, FirstName,
CurrentUserName) As String

Dim tmpUserName As String
Dim newUserName As String
Dim c As Long

'is there already a CurrentUserName
If IsNull(CurrentUserName) Then
tmpUserName = LCase(Left(FirstName, 1) & Left(LastName, 4))
Else
tmpUserName = CurrentUserName
End If

'force username to be at least 5 characters
'simply append left part of abcde to ensure this is so
If Len(tmpUserName) < 5 Then
tmpUserName = tmpUserName & Left("abcde", 5 - Len(tmpUserName))
End If

'need to check this username is unique
newUserName = tmpUserName
c = 1
While DCount("*", "Students", "Username = '" & newUserName & "' AND
StudentID <> " & StudentID) > 0
newUserName = tmpUserName & c
c = c + 1
Wend

'return the username
GetUniqueUserName = newUserName

End Function

All works great when entering new students one at a time via the form.
However if a class of student names is "Paste Appended" then problems occur:

e.g.
Davis Sam
Davis Steve

Fails on Davis Steve as the function does not detect Davis Sam's username
when doing the DCount. ie. Davis Sam has UserName = "sdavi" yet DCount
returns 0.

Is there a command to force each new record to be written to the table as
the PasteAppend occurs so DCount can use it?

Sam

 

[John Vinson]

Is there a command to force each new record to be written to the table as
the PasteAppend occurs so DCount can use it?

Not that I'm aware of. Can you perhaps link to the (spreadsheet? text
file?) that you're paste-appending from and run an Append query
instead? This query could (with some difficulty perhaps) generate your
"intelligent" key.

The trouble you're having is one good reason (among many) not to use
such composite keys!

John W. Vinson[MVP]

 

[Sam D]

Thanks for your reply John (sorry it took me some time to respond)

One thought I'll try is to detect/supress the "paste errors" error message
and then iterate through the paste errors table performing one insert at a
time.

Sam

 

[John Vinson]

Thanks for your reply John (sorry it took me some time to respond)

One thought I'll try is to detect/supress the "paste errors" error message
and then iterate through the paste errors table performing one insert at a
time.

<shrug>

Ok, if you want to do it the hard way. I take it you chose not to
follow my suggestion? Or did it not work, and you don't want to try to
fix it?

John W. Vinson[MVP]

 

[Sam D]

<shrug>

Ok, if you want to do it the hard way. I take it you chose not to
follow my suggestion? Or did it not work, and you don't want to try to
fix it?

I certainly do not wish to do it the hard way! And I definitely
appreciate/respect your suggestions.

This Access FE application is the teacher version of a new commercial
package I'm developing/marketing that monitors/sets tests accross a
classroom network - the tests are undertaken by students via a VB
application - includes video, images, multichoice, match the term, etc...
the VB app can operate alone or it is able to detect set tests within the BE
DB (and much more). The paste append functionality is to alllow teachers to
simply paste student names from any other (unknown) windows application (ie
via the clipboard) into the FE app and needs to be extremely simple if it is
to be used - just one or two classes would be pasted - maximimum of 60
students.

In usability terms, having to create a file that contains just student names
would not (in practice) be used - simply entering the names would be just as
quick, given the data entry would occur once each year for each class. This
is not a critical requirement - if time runs out it wont happen.

I'm now thinking of reading the clipboard myself (allowing me to parse the
data, check with the user and then write each record one at a time). I shall
post the final outcome in due course...

Sam