Unbound User form

G

Guest

Need assistances
I have a form that will create user login for my database. Its unbound.
I need a way where if the admin click the "Add user" button, before the data
copies to the table, it looks at the table to see if there is any dups. If
there is then error msg should appear promptin user that User name has
already been taken, try again, if not, then add and clear form after
the code i got only adds but doesn't "refresh" the form, all teh data is
still there. Please advise. Thanks

code:
Sub Add_Users()
Dim currentDbs As Database
Dim rcsTable As Recordset

Set currentDbs = CurrentDb

Set rcsTable = currentDbs.OpenRecordset("Employees", dbOpenDynaset)

'Loops through Table Definintions and appends Table Names to Table

rcsTable.AddNew

rcsTable("Employees") = Forms![New User]![Employees]
rcsTable("Novell Login") = Forms![New User]![Novell Login]
rcsTable("Password") = Forms![New User]![Password]
rcsTable("Email Address") = Forms![New User]![Email Address]
rcsTable("SecurityLevel") = Forms![New User]![SecurityLevel]
rcsTable("SOX Update") = "-1"
rcsTable("Date Created") = Date
rcsTable("Date Update") = Date
rcsTable("Created by") = Forms![New User]![Created by]

rcsTable.Update

Set currentDbs = Nothing
End Sub
 
G

Guest

Try this

Sub Add_Users()
Dim currentDbs As Database
Dim rcsTable As Recordset

Set currentDbs = CurrentDb

Set rcsTable = currentDbs.OpenRecordset("Select * From Employees Where
Employees = '" & Forms![New User]![Employees] & "'", dbOpenDynaset)

'Loops through Table Definintions and appends Table Names to Table
If rcsTable.Eof then
rcsTable.AddNew

rcsTable("Employees") = Forms![New User]![Employees]
rcsTable("Novell Login") = Forms![New User]![Novell Login]
rcsTable("Password") = Forms![New User]![Password]
rcsTable("Email Address") = Forms![New User]![Email Address]
rcsTable("SecurityLevel") = Forms![New User]![SecurityLevel]
rcsTable("SOX Update") = "-1"
rcsTable("Date Created") = Date
rcsTable("Date Update") = Date
rcsTable("Created by") = Forms![New User]![Created by]

rcsTable.Update
Else
MsgBox "Employee exist in table
Forms![New User]![Employees] = "" ' To clear the employee field
End If

Set currentDbs = Nothing
End Sub
==================================
If the Employees field is number type, then change the OpenRecordSet to

Set rcsTable = currentDbs.OpenRecordset("Select * From Employees Where
Employees = " & Forms![New User]![Employees], dbOpenDynaset)

And
Forms![New User]![Employees] = Null
 
G

Guest

Thank you, it works
Question, I have this one issue. I created a msg box when user clicks on the
"add user" button on the form. It will prompt the admin if he wants to add
this user.
If yes, run process, if no, clear screen.
If I say yes, and username isn't in db, then it adds and msg appear telling
the admin that the name has been stored
But if username is in db, it gives me the error msg that this entry is a dup
AND then the confirmation msg that name has been store (but teh data didn't
store)
How can I fix this?
Thanks

==code from form===
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Style = vbOKOnly + vbCritical ' Define buttons.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic

If IsNull(Employees) = True Then
Msg = "Please Enter Employee Name"
Title = "Data Entry Error"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
Employees.SetFocus
Exit Sub
End If

If IsNull([Novell Login]) = True Then
Msg = "Please Enter Novell Login"
Title = "Data Entry Error"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
[Novell Login].SetFocus
Exit Sub
End If

If IsNull(SecurityLevel) = True Then
Msg = "Please pick a Security Level for user"
Title = "Data Entry Error"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
SecurityLevel.SetFocus
Exit Sub
End If

If IsNull(Password) = True Then
Msg = "Please make a temporary password"
Title = "Data Entry Error"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
Password.SetFocus
Exit Sub
End If

If IsNull([Email Address]) = True Then
Msg = "Please type in users email address"
Title = "Data Entry Error"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
[Email Address].SetFocus
Exit Sub
End If

Msg = "Employee Name: " & Employees & Chr(10) & _
"Novell Login: " & [Novell Login] & Chr(10) & _
"Security Level: " & SecurityLevel & Chr(10) & _
"Is this information correct? Do you want to add user?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "?????" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Add_Users ' From Module Code (sql_codes)
MsgBox ("User: " & Employees & " with" & Chr(10) & _
"Novell Login: " & [Novell Login] & " has been added"),
vbInformation, "New User/Novell Login"
Forms![New User]![Employees] = ""
Forms![New User]![Novell Login] = ""
Forms![New User]![SecurityLevel] = ""
Forms![New User]![Password] = ""
Forms![New User]![Email Address] = ""
Else ' User chose No.
MyString = "No" ' Perform some action.
Forms![New User]![Employees] = ""
Forms![New User]![Novell Login] = ""
Forms![New User]![SecurityLevel] = ""
Forms![New User]![Password] = ""
Forms![New User]![Email Address] = ""
DoCmd.SetWarnings False
DoCmd.Close
End If

End Sub

==code from module===
Option Compare Database
Sub Add_Users()
Dim currentDbs As Database
Dim rcsTable As Recordset
Dim strUser As String
Dim strNovell As String
Dim sql1 As String

Set currentDbs = CurrentDb

strUser = Forms![New User]![Employees]
strNovell = Forms![New User]![Novell Login]

sql1 = "SELECT * FROM Employees" & _
" WHERE (((Employees.Employees)= '" & strUser & "') AND ((Employees.[Novell
Login])='" & strNovell & "'));"

Set rcsTable = currentDbs.OpenRecordset(sql1, dbOpenDynaset)
'Loops through Table Definintions and appends Table Names to Table
If rcsTable.EOF Then
rcsTable.AddNew

rcsTable("Employees") = Forms![New User]![Employees]
rcsTable("Novell Login") = Forms![New User]![Novell Login]
rcsTable("Password") = Forms![New User]![Password]
rcsTable("Email Address") = Forms![New User]![Email Address]
rcsTable("SecurityLevel") = Forms![New User]![SecurityLevel]
rcsTable("SOX Update") = "-1"
rcsTable("Date Created") = Date
rcsTable("Date Update") = Date
rcsTable("Created by") = Forms![New User]![Created by]
rcsTable.Update
Else
MsgBox ("Employee name: " & UserName & " with " & Chr(10) & _
"Novell Login: " & NovellLogin & Chr(10) & _
"Already exists in Database and cannont be added. Please try
again"), vbCritical, "UserName/Novell Login"
'======Clear out form=====
Forms![New User]![Employees] = ""
Forms![New User]![Novell Login] = ""
End If

Set currentDbs = Nothing
End Sub
 
G

Guest

Few steps:
1.
Change the Add_Users from Sub to Function, so it will return a value if no
error

Function Add_Users()

End Function
====================================
2.
Add Error capture

Function Add_Users()
On Error Goto Add_Users_Err

' Your code

Add_Users_Exit:
Exit Function
Add_Users_Err:
MsgBox Error
Add_Users = False
Resume Add_Users_Exit
End Function
===================================
3.
In the call to the function write

If Add_Users() Then
MsgBox ("User: " & Employees & " with" & Chr(10) & _
"Novell Login: " & [Novell Login] & " has been added"),
End If

================================
About the duplicates, check the validation if the forms return the right
values, and this is why it's trying to insert the data again

--
\\// Live Long and Prosper \\//
BS"D


Justin said:
Thank you, it works
Question, I have this one issue. I created a msg box when user clicks on the
"add user" button on the form. It will prompt the admin if he wants to add
this user.
If yes, run process, if no, clear screen.
If I say yes, and username isn't in db, then it adds and msg appear telling
the admin that the name has been stored
But if username is in db, it gives me the error msg that this entry is a dup
AND then the confirmation msg that name has been store (but teh data didn't
store)
How can I fix this?
Thanks

==code from form===
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Style = vbOKOnly + vbCritical ' Define buttons.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic

If IsNull(Employees) = True Then
Msg = "Please Enter Employee Name"
Title = "Data Entry Error"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
Employees.SetFocus
Exit Sub
End If

If IsNull([Novell Login]) = True Then
Msg = "Please Enter Novell Login"
Title = "Data Entry Error"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
[Novell Login].SetFocus
Exit Sub
End If

If IsNull(SecurityLevel) = True Then
Msg = "Please pick a Security Level for user"
Title = "Data Entry Error"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
SecurityLevel.SetFocus
Exit Sub
End If

If IsNull(Password) = True Then
Msg = "Please make a temporary password"
Title = "Data Entry Error"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
Password.SetFocus
Exit Sub
End If

If IsNull([Email Address]) = True Then
Msg = "Please type in users email address"
Title = "Data Entry Error"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
[Email Address].SetFocus
Exit Sub
End If

Msg = "Employee Name: " & Employees & Chr(10) & _
"Novell Login: " & [Novell Login] & Chr(10) & _
"Security Level: " & SecurityLevel & Chr(10) & _
"Is this information correct? Do you want to add user?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "?????" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Add_Users ' From Module Code (sql_codes)
MsgBox ("User: " & Employees & " with" & Chr(10) & _
"Novell Login: " & [Novell Login] & " has been added"),
vbInformation, "New User/Novell Login"
Forms![New User]![Employees] = ""
Forms![New User]![Novell Login] = ""
Forms![New User]![SecurityLevel] = ""
Forms![New User]![Password] = ""
Forms![New User]![Email Address] = ""
Else ' User chose No.
MyString = "No" ' Perform some action.
Forms![New User]![Employees] = ""
Forms![New User]![Novell Login] = ""
Forms![New User]![SecurityLevel] = ""
Forms![New User]![Password] = ""
Forms![New User]![Email Address] = ""
DoCmd.SetWarnings False
DoCmd.Close
End If

End Sub

==code from module===
Option Compare Database
Sub Add_Users()
Dim currentDbs As Database
Dim rcsTable As Recordset
Dim strUser As String
Dim strNovell As String
Dim sql1 As String

Set currentDbs = CurrentDb

strUser = Forms![New User]![Employees]
strNovell = Forms![New User]![Novell Login]

sql1 = "SELECT * FROM Employees" & _
" WHERE (((Employees.Employees)= '" & strUser & "') AND ((Employees.[Novell
Login])='" & strNovell & "'));"

Set rcsTable = currentDbs.OpenRecordset(sql1, dbOpenDynaset)
'Loops through Table Definintions and appends Table Names to Table
If rcsTable.EOF Then
rcsTable.AddNew

rcsTable("Employees") = Forms![New User]![Employees]
rcsTable("Novell Login") = Forms![New User]![Novell Login]
rcsTable("Password") = Forms![New User]![Password]
rcsTable("Email Address") = Forms![New User]![Email Address]
rcsTable("SecurityLevel") = Forms![New User]![SecurityLevel]
rcsTable("SOX Update") = "-1"
rcsTable("Date Created") = Date
rcsTable("Date Update") = Date
rcsTable("Created by") = Forms![New User]![Created by]
rcsTable.Update
Else
MsgBox ("Employee name: " & UserName & " with " & Chr(10) & _
"Novell Login: " & NovellLogin & Chr(10) & _
"Already exists in Database and cannont be added. Please try
again"), vbCritical, "UserName/Novell Login"
'======Clear out form=====
Forms![New User]![Employees] = ""
Forms![New User]![Novell Login] = ""
End If

Set currentDbs = Nothing
End Sub
Click to expand...
 
G

Guest

Thank you once again for your help.
Everything is working except when I try to add the confirmation msg, stating
that user has been added. I placed the code when the function is being called
upon but I am getting it isn't working the way I thought it was supposed to
work
WHat i mean is that, when I enter a new name, no msg pops up, but if i enter
a name that is already in teh table, the error msg pops up followed by the
confirmation I don't need that. this is the code that calls the function.
Please help

Private Sub New_Click()

UserName = Forms![New User]!Employees
NovellLogin = Forms![New User]![Novell Login]


If Add_Users() Then
MsgBox ("User: " & Employees & " with" & Chr(10) & _
"Novell Login: " & [Novell Login] & " has been added"),
vbExclamation, "Added User"
End If

Msg = "Employee Name: " & Employees & Chr(10) & _
"Novell Login: " & [Novell Login] & Chr(10) & _
"Security Level: " & SecurityLevel & Chr(10) & _
"Is this information correct? Do you want to add user?"
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
Title = "Add User?" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Add_Users
Forms![New User]![Employees] = ""
Forms![New User]![Novell Login] = ""
Forms![New User]![SecurityLevel] = ""
Forms![New User]![Password] = ""
Forms![New User]![Email Address] = ""
Else ' User chose No.
MyString = "No" ' Perform some action.
End If
 
G

Guest

Help!
I'm just switching to a tablet PC from a MAC so I have no idea how to use my
new machine. While adventuring today, somewhere I put a password on my
Windows program. I DO recall the password.
I have Novell installed on my computer from school, however, now i can't log
on. As of yesterday I'd start my computer through Novell workstation with
user: owner and no password.
Now it won't let me in. I've tried entering the password I made earlier
today, but that won't work either. My brother-in-law suggest I might have to
change the user name from owner. I don't recall making a new user name
anywhere.
Can anyone help me get on my computer?!
 
J

John Vinson

Help!
I'm just switching to a tablet PC from a MAC so I have no idea how to use my
new machine. While adventuring today, somewhere I put a password on my
Windows program. I DO recall the password.
I have Novell installed on my computer from school, however, now i can't log
on. As of yesterday I'd start my computer through Novell workstation with
user: owner and no password.
Now it won't let me in. I've tried entering the password I made earlier
today, but that won't work either. My brother-in-law suggest I might have to
change the user name from owner. I don't recall making a new user name
anywhere.
Can anyone help me get on my computer?!
Click to expand...

I suggest that you ask in a Novell or Windows support group. I suspect
the Microsoft webpage may have misled you - this newsgroup is for
people developing VBA coding for Microsoft Access database forms, and
your question is way the heck offtopic... <g>

Good luck.

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Users log 4
help with coding - for creating secure login forms 1
Unbound Combo box Value 10
Search form via filter 5
ADO unbound multi user form 3
use password prompt for editing form 8
Auto store date created & updated, EmpId created by and Updated by 1
storing user id in continuous form 1

Top