VBA - findfirst for registering new users to database

[gdesruis]

New to MS Access, and it is bringing back memories from using VB in highschool. Quite beginner here in terms of VBA knowledge and the Access itself.

Through researching on google. I managed to find code to create a user login screen. I added a new button to allow for new user creation. I have figured out how to create a new user and have it show back up in the login screen. My problem is when someone registers a "new user" with the same field entries under User Name, the database creates the user by overwriting the previous information of that User.

I am attempting to use the findfirst to search a specific field in a table to check and see if it exists already to return an error message displaying "User Name is on use"

I cannot seem to target the find first function properly, and not sure if it is in the correct area.

Primary key: EmpName (Indexed to allow no duplicates)

Code on create user button

Private Sub cmdcreate_Click()
Dim dbsemployees As DAO.Database
Dim rstemployees As DAO.Recordset

Set dbsemployees = CurrentDb
Set rstemployees = dbsemployees.OpenRecordset("tblEmployees", dbOpenDynaset)
Dim strloginsearch As String
strloginsearch = "me.EmpName"
rstemployees.FindFirst strloginsearch
'Check to see if Employee Name is filled out'
If IsNull(Me.EmpName.Value) Then
MsgBox "Please Complete All Fields.", vbOKOnly, "Missing User Name or Password"
ElseIf IsNull(Me.EmpPassword.Value) Then
MsgBox "Please Complete All Fields.", vbOKOnly, "Missing User Name or Password"
'check to see if information exists or not'

'Add records to employee table'
ElseIf rstemployees.NoMatch Then
Dim stform2 As String
stform2 = "frmLogin"
rstemployees.AddNew
rstemployees.Update
MsgBox "User Created", vbOKOnly
DoCmd.Close acForm, "frmnewuser", acSaveNo
DoCmd.OpenForm stform2
Else
MsgBox "User name exists.", vbOKCancel, "User name in use"
End If

End Sub

Any help would be greatly appreciated.

Garrett

 

[billmeye]

To test for an existing record, I would use:

If Dcount("*","tblEmployees","[EmpName]='" & Me.EmpName & "'") =0 Then
'no record with that user name exists so add new user
Set rstemployees = currentDb.OpenRecordset("tblEmployees", dbOpenDynaset)
With rstemployees 
.addnew
.fields!EmpName = Me.EmpName
.fields!EmpPassword = Me.EmpPassword
.update
.close
set rstemployees = nothing
Me.requery 'have new user show on login screen
Me.EmpName.setfocus
Else
Msgbox "User name exists.", vbOKCancel, "User name in use"
End If

 

[gdesruis]

Hey Bill,

thanks for the quick response. I have tried to use the code you provided, and i keep getting the error "else without if". Also, can I add my blank data entry checks for the two textboxes to your block of code? (ideal at the end or beginning?)

Your code:

Dim dbsemployees As DAO.Database
Dim rstemployees As DAO.Recordset
Set dbsemployees = CurrentDb
'check to see if information exists or not'
If DCount("*", "tblEmployees", "[EmpName]='" & Me.EmpName & "'") = 0 Then
'no record with that user name exists so add new user
Set rstemployees = dbsemployees.OpenRecordset("tblEmployees", dbOpenDynaset)
With rstemployees
.AddNew
.Fields!EmpName = Me.EmpName
.Fields!EmpPassword = Me.EmpPassword
.Update
.Close
Set rstemployees = Nothing
Me.Requery 'have new user show on login screen
Me.EmpName.SetFocus
Else
MsgBox "User name exists.", vbOKCancel, "User Name in use"
End If

Blank entry checks

'Check to see if Employee Name is filled out'
If IsNull(Me.EmpName.Value) Then
MsgBox "Please Complete All Fields.", vbOKOnly, "Missing User Name or Password"
ElseIf IsNull(Me.EmpPassword.Value) Then
MsgBox "Please Complete All Fields.", vbOKOnly, "Missing User Name or Password"
End If

 

[billmeye]

Sorry, I left out the End With statement:

.Close
End With
Set rstemployees = Nothing

And you should add your blank data checks before.