What is VBA that enables to make a form blank after an entry

[A kinason]

The VBA code below is used to add a new user to the DB using the form "frmAddNewUser". Am trying to get the knowledge here to apply on many other things. When a new user is added information pops up "New user has been successfully added to the database." The issue is that the information on the form is saved to the table but is still on a standstill on the form. What do i need to add to this code so that after a new user is added, the form should be blank waiting for another entry?


On Error GoTo Err_cmdAdd_Click

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = currentdb
Set rst = dbs.OpenRecordset("tblSecurity", dbOpenDynaset)

With rst
.AddNew
![Password] = Me.txtPassword
![UserID] = Me.txtUserID
![Active] = Me.chkActive
![AccessID] = Me.cboAccessID
![ViewID] = Me.cboViewID
.Update
End With

If IsLoaded("fmnuUsers") Then
Forms!fmnuUsers.Requery
Forms!fmnuUsers.lstUsers.Requery
End If

MsgBox "New user has been successfully added to the database.", vbInformation + vbOKOnly, "User Update"

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description


Resume Exit_cmdAdd_Click


End Sub

[ssanfu]

The answer is a definite.... "It depends"!

Not a lot of info about your dB, but here goes.

From your code, it appears that you are using an unbound form. Why??
It is so much easier to use bound forms......

If the form is used to only add new students, I would have the form bound to the table "TblStudent", the controls bound to the fields and the form property "Default View" set to "Single Form". Access takes care of all the heavy lifting. When you move off of the last control or close the form, the record is saved. The form moves to the "New" record which clears the controls, ready for new data. All you need is a button to close the form.

MOD EDIT: Cleaning up duplicate threads and posts - thought there was a duplicate post here and deleted. Sorry, it was not an exact duplicate but removing doesn't really impact the thread continuity. The suggested code is relevant to both procedures.

I have created a form and on the OK button, I have attached the following VBA to the Event on Click.

If you want to keep the current design, you need a little more code. You posted two versions of the code, so I'm using the second version... but you get the idea.

    On Error GoTo Err_cmdOK_Click

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("TblStudent", dbOpenDynaset)

    With rst
        .AddNew
        ![StudentID] = Me.TxtStudentID
        ![StudentName] = Me.StudentName
        ![DateOfAdmission] = Me.DoDate
        ![Level] = Me.Level
        ![ValueOfContract] = Me.Amount
        ![SecurityID] = user.SecurityID
        .Update
    End With

    ' clear the unbound controls on the form
    Me.TxtStudentID = Null
    Me.StudentName = Null
    Me.DoDate = Null
    Me.Level = Null
    Me.Amount = Null


Exit_cmdOk_Click:
    On Error Resume Next
    'cleanup
    ' - if you open it, close it
    ' - if you create it, destroy it
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

Err_cmdOK_Click:
    MsgBox Err.Description
    Resume Exit_cmdOk_Click

The code I added is in BLUE

Does this help?

[A kinason]

thanks ssanfu.

I have added the code to my db but when i click OK to validate a record, i recieve the message "Data type Conversion error" and the record is not saved to the table.
What should i do?

[ssanfu]

Have you set a breakpoint in the code to find out what at line the error occurs?
Or you could comment out the new lines and add in one at a time until an error occurs.

I don't have your dB, so it is very hard to know what is causing the error.
Still don't know anything about your dB except the code you posted.

[Missinglinq]

...i recieve the message "Data type Conversion error"...

What line does Access highlight when it errors out? The line

![SecurityID] = user.SecurityID

looks somewhat suspect! I see SecurityID no where in the previous posts, but perhaps it was in the post that was accidentally deleted.

I have to reiterate ssanfu's statement about your use of an Unbound Form! Several developers I know, experienced in the use of Unbound Forms in Visual Basic and C++ database development, as well as Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That's because with Bound Forms, Access takes care of the 'heavy lifting,' as ssanfu said, but with Unbound Forms, as you are now finding out, the developer has to write code to take care of just about everything! Using a Bound Form the task in question would have been accomplished by simply moving to another Record!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end, because:

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

Linq ;0)>

[A kinason]

I have decided for now not to used unbound forms. and to identify the person who entered a particular record, I have attaced a VBA on the Before update event of the form.

Thanks for your assistance. Am very greatful.

[Missinglinq]

Glad we could help! While there are a few cases where using Unbound Forms are necessary, as a rule it really does defeat the whole purpose of a Rapid Application Development system such as Access. Let us know if we can be of any further help!

Linq ;0)>