VB code Save Record and Go To New Record Using Same Button In Form

[Vanita]

We are saving records in table using VB code, since "add button" is not working ,I tried to write code DoCmd.GoToRecord , , acNewRec to go to new record using same button but that is also not working.How to add new record to a single button which save data in table as well go to new record in form?


Please can anyone find solution for this problem.

Code used in our Project

Private Sub Save_Click()
If IsNull(DCNo) Or IsNull(Billing_Type) Or IsNull(Outward_Date) Or IsNull(Item_No) Or IsNull(Customer) Or IsNull(GRNNumber) Or IsNull(Ref_DC) Or IsNull(Sinter_Id) Or IsNull(Processed_Qty) Or IsNull(Accepted_Qty) Or IsNull(Rejected_Qty) Or IsNull(Inward_Rejection) Or IsNull(Un - Processed_Qty) Then
MsgBox "Please Fill Empty Fields ", , "Details required"
Else
MsgBox "Do you want to submit the details of Outward Register Form"
CurrentDb.Execute "INSERT INTO Outward([DCNO],[Billing_Type],[Outward_Date],[Item_No],[Customer],[GRNNumber],[Ref_DC],[Sinter_Id],[Processed_Qty],[Accepted_Qty],[Rejected_Qty],[Inward_Rejection],[Un-Processed_Qty],[Remarks])" & _
"VALUES('" & Me.DCNo & "','" & Me.Billing_Type & "','" & Me.Outward_Date & "','" & Me.Item_No & "','" & Me.Customer & "','" & _
Me.GRNNumber & "','" & Me.Ref_DC & "','" & Me.Sinter_Id & "','" & Me.Processed_Qty & "','" & Me.Accepted_Qty & "','" & Me.Rejected_Qty & "','" & Me.Inward_Rejection & "','" & Me.Un_Processed_Qty & "','" & Me.Remarks & "')"
End If


Dim strMsg As String
strMsg = "Do you want to enter a new record?"
If MsgBox(strMsg, vbYes Or vbQuestion) = vbYes Then
DoCmd.GoToRecord , , acNewRec
End If
End Sub

[isladogs]

What do you mean by 'the Add button isn't working?
Do you mean its disabled? If so your form/query/table is read only … or you have set Allow Additions to No

Is this an unbound form? If so, why?

[Vanita]

Form is not "Read Only"
Allow additions is set to "Yes"
As per My knowledge Form is Bound to a table, how ever attaching a screen shot of design view of the form.

[Missinglinq]

Despite the fact that you give the Record Source as Outward (which I assume is a Table) your Form is, in fact, Unbound...because all of the Controls, as shown on your screenshot of the Design View, are Unbound! And hence, you cannot go to a New Record...because Unbound Forms have no Records! They only contain Controls that you can enter data into...you are not creating a 'record.'

To do what I think you want...you need to send your data to the Table, via your

CurrentDb.Execute "INSERT INTO Outward...

statement...then simply clear the Controls on your Form.

But as isladogs asked...why are you using an Unbound Form?

Why not simply give each of your Unbound Controls a Control Source from your Record Source, i.e. Outward?

Using Unbound Forms really does away with the basic function of Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t! You don't need Unbound Forms to

1. Do Data Validation
2. Prevent Duplicate Records
3. Do Formatting of Data before it's Saved
4. Decide whether or not to actually Save a New or Edited Record

which are the most common reasons given. Nor are they needed for another dozen reasons I've seen people give!

Several developers I know, experienced in Visual Basic database development and 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 the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything...even the most mundane tasks!

Bottom line is…with Bound Forms you end up writing code for a few specialized situations, such as #1-#4, as listed above…and with Unbound Forms you have to write code for virtually everything that needs to be done!

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.

• 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

Don't misunderstand me...there are a few, specialized situations, where an Unbound Form is preferable...but anyone who routinely uses them for everything, has simply made a bad choice in deciding to work in Access.

Linq ;0)>

[Vanita]

1. Thanks a lot for your detailed explanation!!
2. We are using Unbound form to "Prevent Duplicate Records" as mentioned by you.
3. What is the solution for#2 with the bound form.

[isladogs]

2. We are using Unbound form to "Prevent Duplicate Records" as mentioned by you.
3. What is the solution for#2 with the bound form.

Make sure your table has a primary key field. This may be an autonumber field or a meaningful field where this is guaranteed to be unique
e.g. National Insurance number, Unique Pupil Number (UPN) etc
If using an autonumber PK field, you may also need to assign a composite unique index for two or more fields that together will ensure uniqueness
Once that is setup correctly Access will not allow any duplicate records