input directly into a table

[csdrex87]

So i did a little research on recordsets and tried to get my code to put a new record in for a new user. The form has 4 fields that input and 2 that are a access check. The code basically checks if the desired password and confirmed password are the same.. if the admin and password are in the database and if they are correct and then does a message box confiming the creation. These all work fine but my problem is now trying to put a new entry in. here is what i have so far:

Private Sub Command10_Click()
Dim adminPwd As String
Dim valSelect As Variant, MyDB As DAO.Database, MyRS As DAO.Recordset


If DCount("[EmployeeName]", "tblEmployeeId", "[Employeename]='" & Me.AdminUN & "'") > 0 Then
adminPwd = DLookup("[Emp_Password]", "tblEmployeeId", "[Employeename]='" & Me.AdminUN & "'")

If "'" & adminPwd & "'" = "'" & Me.adminPwd & "'" Then

If IsNull(Me.DPwd) = False And IsNull(Me.Cpwd) = False Then

If "'" & Me.DPwd & "'" = "'" & Me.Cpwd & "'" Then

If MsgBox("Are you sure you want to create this account?" & vbCrLf & _
"Please make sure to set the User Access Level", vbQuestion + vbYesNo, "Cancel Confirmation") = vbYes Then

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblEmployeeId", dbOpenDynaset)
MyRS.MoveFirst
MyRS.AddNew
MyRS![EmployeeName] = Me.userName
MyRS![Emp_Password] = Me.DPwd
MyRS![Emp_ULaccess] = Me.DULA
MyRS.Update
MyRS.Close
Set MyRS = Nothing

End If
End If
End If
End If
End If
End Sub

 

[boblarson]

A couple of questions...

1. Why use a recordset. A more efficient way is an Append Query

2. You don't need

MyRS.MoveFirst

as it is moot when using .AddNew

3. You haven't said what the problem is. You stated that this is what you currently have. So is there a problem?

 

[csdrex87]

Im looking for whatever will add a new row i.e. record or whatever you want to call it to the table tblEmployeeId (holding all information on the employee).

The current code does not work as it will not run anything. If i do a breakpoint in the code i found that it gets through all of the if statements to the message box. The problem is that my recordset change does not work. Anything that can be used to add a new employee to the database would work and any help would be greatly appreciated

 

[Atomic Shrimp]

If "'" & adminPwd & "'" = "'" & Me.adminPwd & "'" Then...

This could be simplified to:

If adminPwd = Me.adminPwd Then...

Also, it also probably isn't a great idea to have a variable named the same as a control on your form (or a field in your table).

Furthermore, I'd replace all those fully-nested IFs with a single:

If [condition1] AND [condition2] AND [condition3]... Then
'code here
End If

To add a record using an append query, you want to replace the whole recordset code block with something like:

DoCmd.SetWarnings False 'without this, it will prompt you to confirm the append

DoCmd.RunSQL "INSERT INTO tblEmployeeId ( EmployeeName, Emp_Password, Emp_ULaccess ) SELECT '" & Me.userName & "' AS Expr1, '" & Me.DPwd & "' AS Expr2, '" & Me.DULA & "' AS Expr3;

DoCmd.SetWarnings True

 

[csdrex87]

Thanks. Took your suggestions into account and changed some of the formatting stuff of my code. I also managed to get the append working on my form so it now adds a new row in. Thank you for your help!