Can't get data entry form to add new record after first time

S

SyntaxSocialist

Registered User.
Local time
, 21:30
Joined
Apr 18, 2013
Messages
109
I've got a data entry form bound to one table. The form has four buttons:
- Clear Fields
- Cancel
- Save and exit
- Save and add another (which should save the user input to the subform/table, clear the input fields, and allow the user to add another record)

I can't quite seem to get the "Save and add another" button to work. When I put some information in the input fields and click the button, it saves it to the subform/table perfectly, but when I try to do it again, it just edits the last record (the one just created).

How can I get that button to place the information from the input fields in a new record every time?

The _Click event for the button looks like this:
Code: 
If Len(Me.field1 & Me.field2 & Me.field3) > 0 Then
    Me.Refresh
    btnClear_Click
    DoCmd.Save
End If
 
Last edited:
What is the current code you have placed behind the buttons?
 
Solved.

I used DoCmd.GoToRecord ,,acNewRec. There ended up being some kind of issue with the interaction between that and btnClear_Click, which, as was implicit in the name, simply set all the controls to be empty. Textboxes were set to "", comboboxes received the focus and had their .Text property set to "", checkboxes were set to 0, and the control at the top of the page was given the focus again:
Code: 
Private Sub btnClear_Click()

    Me.txt1 = ""
    Me.txt2 = ""
    Me.txt3 = ""
    
    Me.cmb1.SetFocus
    Me.cmb1.Text = ""
    
    Me.cmb2.SetFocus
    Me.cmb2.Text = ""
    
    Me.chk1 = 0
    Me.chk2 = 0
    
    Me.txt1.SetFocus

End Sub

I played around with the code a lot before getting it to work, and this is what I ultimately used:
Code: 
Private Sub btnSaveAdd_Click()
    
    Me.Refresh
    DoCmd.RunCommand acCmdSaveRecord
    Me.subForm.Form.Recordset.MoveLast
    DoCmd.GoToRecord , , acNewRec
                
End Sub

I removed the If-Then statement as well because I ended up just writing some code to disable the button if there was no valid input.
 
SynSoc,

I see what you are trying to do here and I see that the question posed in the subject line has been resolved. However, I want to share my code with you for clearing the controls that doesn't require you to remember to add new lines when you add a new control to your form.

I copy and paste this code into every project that uses a Clear or Reset (or whatever the client wants to call it) button and it works universally and recursively. That means that it will clear the subforms on your form and the subforms on those subforms.

I wish I could remember where I got this from so that I could give the original coder credit, but I've modified it a little. The original code had EVERY possible type of control included in the CASE statement. I only include the controls that I use (text boxes, combo boxes, check boxes, subforms). I put this code into Module1:
Code: 
Function ResetControls(frm As Form)
    On Error GoTo Err_Handler
    Dim ctl As Control
 
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        Case acSubform
            If Len(Nz(ctl.SourceObject, vbNullString)) > 0 Then
                Call ResetControls(ctl.Form)
            End If
        End Select
    Next
 
Exit_Handler:
    Set ctl = Nothing
    Exit Function
 
Err_Handler:
'    Use next line for debugging
'    MsgBox "Error " & Err.Number & " - " & Err.Description
    Resume Exit_Handler
 
End Function
Then I enter this command for the On Click property of my Reset button:
Code: 
ResetControls Me
I know this is going off topic, but I hope it helps.
_________________
Regards,
Marvin M :cool:
Windows 7 Professional, MS Access 2007/2010
Windows 8 Professional, MS Access 2013
---------------------------------------------------------------------------------------------
If my post has helped you, please click the scales or click the 'Thumbs up'. Thanks!
---------------------------------------------------------------------------------------------
 
Hey All,

I think I just figured out where I got the original idea for my code and I want to give a tip of the hat to Allen Browne. His code for locking controls, found here, http://allenbrowne.com/ser-56.html , put me on the right path for developing my function for reseting controls. Thanks, Allen.
_________________
Regards,
Marvin M :cool:
Windows 7 Professional, MS Access 2007/2010
Windows 8 Professional, MS Access 2013
---------------------------------------------------------------------------------------------
If my post has helped you, please click the scales or click the 'Thumbs up'. Thanks!
---------------------------------------------------------------------------------------------
 
You must log in or register to reply here.

Similar threads

M
Solved VBA To Add Record
Replies
6
Views
163
Gasman
Gasman
B
Write to next row in continuous subform
Replies
7
Views
509
Pat Hartman
P
C
Recordsource Doesn't work after adding data
Replies
2
Views
271
chuckcoleman
C
C
Change the Sub-Form View by using VBA
2
Replies
21
Views
526
Uncle Gizmo
Uncle Gizmo
N
Solved How to force MS Access display a primary key on the forms after entering data in the first field
Replies
6
Views
224
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom