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
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
The "Users" table consists of the following fields.
UserID Text - Primary Key
LastLoggedIn Date (Medium)
The "TimeSheet" table contains the fields
Record AutoNumber (Primary Key)
Date Date (Medium)
TimeIn Time (Medium)
TimeOut Time (Medium)
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.)
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
' Populate recordset.
' Find first record satisfying search string. Exit
' loop if no such record exists.
'If the user details are not found, then
'add a new record to the database.
If .NoMatch Then
UserId = Text10
Name = MyName
Surname = MySurname
'If user already exists tell the use and .....
MsgBox "A record already exists for " & strUserName2
'...Reset the field names to blank
Text10 = ""
MyName = ""
MySurname = ""
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.