Creating a new entry using recordset?

**Hammilton** · 12-17-2015, 06:25 PM

Hello,

I figured out how to change a users password using a recordset, but now I'm trying to create a new entry in my table using a recordset as well, and I can't figure out how to make it work. I'm not sure what I'm doing wrong, because for some reason it keeps popping up the "user already exists" but then I go and look at the table and there is no such user. Am I doing something wrong that the updates I'm making to the recordset aren't updating in the table for some reason? This is the code I'm using:

Code:

Private Sub cmdCreateUser_Click()
    Dim rs As Recordset
    Dim db As Database
    Dim tempUserID As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Users", dbOpenDynaset, dbSeeChanges)
    
         Me.lblAlready.Visible = False
         Me.lblFullAlready.Visible = False
    
    rs.FindFirst "Username = '" & Me.txtUserName & "'"
    If rs.NoMatch = True Then
             rs.FindNext "[Full Name] = '" & Me.txtFullName & "'"
     
                 If rs.NoMatch = True Then
                               rs.AddNew
                               rs!ID = Me.txtID
                               rs![Full Name] = Me.txtFullName
                               rs!Username = Me.txtUserName
                               rs!Password = Me.txtPassword
                               rs!Admin = Me.chkAdmin.Value
                               rs.Update
                
                                    'clear boxes
                               Me.txtFullName = ""
                               Me.txtUserName = ""
                               Me.txtPassword = ""
                               Me.chkAdmin.Value = False
                     
                               Exit Sub
     
                 Else
                          Me.lblFullAlready.Visible = True
                 End If
     
         Else
                  Me.lblAlready.Visible = True
                  Exit Sub
    End If
 
End Sub

Edit: How do I post code and get the formatting to copy over? I apologize for it being entirely left justified, this isn't how I write code! Figured it out!

**ranman256** · 12-17-2015, 07:35 PM

Just run an append query,
insert into table ([field]) value ("word")

**Hammilton** · 12-17-2015, 07:43 PM

Okay, I've got it working, I had the table open already, but if I close the table and re-open it, then I see that it's been added, this code is actually working...

KIND OF!

For some reason when I run it it's adding the record TWICE, and I can't figure out why.

**Hammilton** · 12-17-2015, 08:08 PM

Here is the code I'm using:

Code:

Dim rs As Recordset
Dim db As Database
Dim tempUserID As String
Private Sub cmdCreateUser_Click()
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Users", dbOpenDynaset, dbSeeChanges)
    
    If Me.txtFullName = "" Then
        Me.lblFullAlready.Caption = "*Enter Full Name"
        Me.lblFullAlready.Visible = True
        Exit Sub
    End If
    If Me.txtUserName = "" Then
        Me.lblUsername.Caption = "*Enter Username"
        Me.lblUsername.Visible = True
        Exit Sub
    End If
    If Me.txtPassword = "" Then
        Me.lblEnterPassword.Visible = True
        Exit Sub
    End If
    
    Me.lblAlready.Visible = False
    Me.lblFullAlready.Visible = False
    Me.lblEnterPassword.Visible = False
    Me.lblFullAlready.Caption = "*User Already Exists"
    Me.lblAlready.Caption = "*User Already Exists"
    
    rs.FindFirst "Username = '" & Me.txtUserName & "'"
    If rs.NoMatch = True Then
        rs.FindFirst "[Full Name] = '" & Me.txtFullName & "'"
            If rs.NoMatch = True Then
                rs.AddNew
                rs![Full Name] = Me.txtFullName
                rs!Username = Me.txtUserName
                rs!Password = Me.txtPassword
                rs!Admin = Me.chkAdmin.Value
                rs.Update
            Else
                Me.lblFullAlready.Visible = True
            End If
    Else
        Me.lblAlready.Visible = True
    End If
 
End Sub

The code is correctly creating the new entry in the table, however, it's creating the entry twice.

Also, I apparently don't understand the exit sub command properly. In the red highlighted section, if any of those conditions are true, I want it to immediately exit the sub, but instead it's continuing down through it- so where it makes the lbl.visible = true within the If Then statement, it keeps going down the line and makes it invisible, and then creates the new entry in the table, a blank one, which I don't want it to do. Do I need to nest the code that creates a new entry within a complicated bunch of IF THEN statements? I thought if I use the Exit Sub command it'll stop running the code in that subroutine right there when that IF THEN statement is true. No?

Thanks!

**ssanfu** · 12-17-2015, 09:04 PM

You have the 3 declarations outside of the subroutine. This makes them module variables.
I re-arranged the code a little.
See if this works better:
(nothing wrong with the placement of the Exit Sub command)

Code:

Private Sub cmdCreateUser_Click()
    ' moved these 3 lines inside the sub
    Dim db As DAO.Database   'added DAO.
    Dim rs As DAO.Recordset   'added DAO.  
    Dim tempUserID As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Users", dbOpenDynaset, dbSeeChanges)

    ' hide captions
    Me.lblAlready.Visible = False
    Me.lblFullAlready.Visible = False
    Me.lblEnterPassword.Visible = False
    Me.lblFullAlready.Caption = "*User Already Exists"
    Me.lblAlready.Caption = "*User Already Exists"
    
    'check if entries made
    If Me.txtFullName = "" Then
        Me.lblFullAlready.Caption = "*Enter Full Name"
        Me.lblFullAlready.Visible = True
        Exit Sub
    End If
    If Me.txtUserName = "" Then
        Me.lblUsername.Caption = "*Enter Username"
        Me.lblUsername.Visible = True
        Exit Sub
    End If
    If Me.txtPassword = "" Then
        Me.lblEnterPassword.Visible = True
        Exit Sub
    End If

    rs.FindFirst "Username = '" & Me.txtUserName & "'"
    If rs.NoMatch = True Then
        rs.FindFirst "[Full Name] = '" & Me.txtFullName & "'"
        If rs.NoMatch = True Then
            rs.AddNew
            rs![Full Name] = Me.txtFullName
            rs!UserName = Me.txtUserName
            rs!Password = Me.txtPassword
            rs!Admin = Me.chkAdmin.Value
            rs.Update
        Else
            Me.lblFullAlready.Visible = True
        End If
    Else
        Me.lblAlready.Visible = True
    End If
    
    'always need to clean up
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
End Sub

I think the problem was having module level variables

Notes:
Shouldn't use punctuation, special characters or spaces in object names.

Using one field for first name and last name breaks normalization rules. (The normalization police are looking for you....

)
The data is not atomic if First and Last names are in one field. It is far easier to concatenate fields than to split them.

**Hammilton** · 12-21-2015, 06:15 PM

No, moving it inside doesn't solve the problem. It's still creating two entries when I click on the button!

**ssanfu** · 12-21-2015, 06:49 PM

Something else/somewhere else is causing the 2nd entry to be made.
Nothing in the code in Post#5 is or can create a 2nd entry.

Has to be something other than the posted code.

It is a click event.
There is no looping.
There is only one ".AddNew" and no "INSERT INTO" code.

**Hammilton** · 12-21-2015, 06:56 PM

I went through all of the other objects in the form, none of them have any code in them, other than the one with a macro to close the form when it's done. I've attached an updated zipped empty database so you can take a look. I can't see anything that could cause that.

Let me know if you can see anything I'm missing.

Empty WLO.zip

**ssanfu** · 12-21-2015, 08:09 PM

OK, I finally figured out that the problems is.

The code is working perfectly. (except that you don't have "DAO." before Database and Recordset in the declaration lines AND the clean up lines - all in blue.... see post #5)

You ALSO have the controls bound to fields in the form record source and the new user form set to "Data Entry" mode.

So you click on the "Create New User" button.
The code executes and a new user record is created.
Then, when the form closes, the form also creates a new record.
Now you have two records.

Creating a new entry using recordset?

Thread Tools

Creating a new entry using recordset?

Similar Threads

Recordset importing from second entry, not the first

Help creating a VBA recordset, Pulling the data I need into it, and placing them in VBA variables.

Creating a public Function and passing the recordset.

creating recordset based on query

Creating an updatable query recordset using Access 2010

Posting Permissions