Using code to validate existing data (1 Viewer)

[Mark Liddle]

I am an extremely frustrated (And/Or slow) learner who has been trying to
learn how to use code in "M.S.Access" (On and off for about a year now)
but without much success. I have embarked on what should be a VERY simple
"Timesheet" project.

Its easy to add and edit records using the Switchboard Manager tools ETC,
but I have no idea how to validate (and then ADD or NOT add) data BEFORE
the database is updated.

My Database consists of 2 tables - called "Users" and
"timesheet" respectively.

The "Users" table consists of the following fields.
UserID Text - Primary Key
Name Text
Surname Text
LoggedIn Yes/No
LastLoggedIn Date (Medium)

The "TimeSheet" table contains the fields
Record AutoNumber (Primary Key)
UserId Text
Date Date (Medium)
TimeIn Time (Medium)
TimeOut Time (Medium)
Remarks Text

The very first form is used to "AddNewUsers" and should perform
the following checks.

1. A user cannot enter an exisiting "UserID" by virtue of the fact
that the "UserID" is the Primary key. (No duplicates allowed)

2. A "UserID" must consist of at least 3 characters and 1 number.
(Surprize - I know how to check this.)

3. Check that the user is not adding a duplicate record with
a different code. (I.E. Check that "Name" and "Surname"
does not exists already.)


Questions
A. How do I convert the text to uppercase before writting it
to the table? (This to ensure that the data is always
entered in the same case for validation purposes.)
I keep getting an error when using the "Upper" , "ConverToUpper"
orthe "UpperCase" function.
(Is "Function" the correctword to use here ??)

B. I tried to create the "AddNewUsers" form where the fields are
NOT bound to any records, but when trying to check if a user
already exists, I got an error saying that a field must be
bound to a record. I therefore included the "UserID" field
on the form.

Surely If I am adding a new record, then the fields should not
be bound ? There must be a way around this problem ?

c. The "AddNewUsers" form contains the following fields....
MyUserID - To get the "UserID"
MyName - Get the users name
MySurname - Get the users Surname
UserID - Listbox (So that the form is bound to
records in some way.)

....and (Taking tips from the so-called "Help") the following code.
(Please see comments in this code.)

Private Sub MySurname_LostFocus()
Dim dbsTimesheet As Database
Dim rstMyUsers As Recordset
Dim strUserName As String
Dim strUserName2 As String

' set the database and recordset variables
Set dbsTimesheet = OpenDatabase("timesheet.mdb")
Set rstMyUsers = dbsTimesheet.OpenRecordset( _
"SELECT UserId, Name, Surname from Users", dbOpenSnapshot)

'How do I get the system NOT to re-open the database? - I tried
'the following statement but then I get an error at the point
'at the "With rstMyUsers..... End With".......
'rstMyUsers = "SELECT UserId, Name, Surname from Users"

'Set the strUserNamevariables
strUserName = "Name = " & "'" & MyName & "'" & " and Surname = " & "'" & MySurname & "'"
strUserName2 = " " & MyName & " " & MySurname

With rstMyUsers
' Populate recordset.
.MoveLast
' Find first record satisfying search string. Exit
' loop if no such record exists.
.FindFirst strUserName

'If the user details are not found, then
'add a new record to the database.
If .NoMatch Then
rstMyUsers.AddNew
UserId = Text10
Name = MyName
Surname = MySurname
rstMyUsers.Update
Else
'If user already exists tell the use and .....
MsgBox "A record already exists for " & strUserName2
'...Reset the field names to blank
Text10 = ""
MyName = ""
MySurname = ""
End If
End With

End Sub


P.S. I am in possession of the following books, but have not been able to
find any help for my specific questions.

1. Mastering Access.
2. SAMS - Teach yourself Visual Basic in 24 Hours.
3. Examprep - Visual Basic desktop.

Is there a manual / tutorial that teaches how to create a "PROPER" application ?
I have found that any of the "northwind" type samples do not describe howe
to do validation as described above. I.E. Customers, Suppliers and even
Staff can be dulicated in these sample databases.

ANY help would be truly appreciated.

 

[D B Lawson]

I can help with Question A:

On the AfterUpdate event of the field you want to convert to uppercase:

Me.[fieldname] = UCase (Me.[fieldname])

HTH

Dawn

[This message has been edited by D B Lawson (edited 04-13-2001).]

 

[MHM]

Your check must be changed:

Set dbsTimesheet = CurrentDB ' referencing the DBEngine instead of opnening a new one

and then:

If .NoMatch Then
rstMyUsers.AddNew
' UserId = Text10
Name = MyName
Surname = MySurname
rstMyUsers.Update

You need (!) to leave rstMyUsers!UserID without value - it will be filled automatically!

Yet leaving the controls "unbound" is OK all through!

Mic