Solved

Confirm a record has been added MS Access

Posted on 2016-10-11
4
26 Views
Last Modified: 2016-10-11
I have an MS Access database that has a form with an "Add Record" command button.    How can I confirm that a record has actually been added to the table when the button is clicked?  Obviously I can look at the table itself but data entry personally, who will be the ones entering the records once the database is up and running will not. All they will have is the form to look at (with the textboxes for data entry and the command button for adding the records).  I want to ensure when they're doing their data entry that the records are actually being added.  Thank you.
0
Comment
Question by:dbfromnewjersey
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41838459
if they are already on a new record, another new record won't be added. You can test if it is on a newrecord by:
if me.newrecord then
   msgbox "This is a New Record"
end if

Open in new window

however, it is a good idea to first save the record before adding one:
if me.dirty then me.dirty = false
if me.newrecord then "You are already on a new record"

Open in new window

0
 

Author Comment

by:dbfromnewjersey
ID: 41838469
I attempted to edit the question but too late.  I wanted to also ask for the code that will actually add the record.  There are 3 textboxes for data entry and a command button. MyTable has 3 fields.  Can I get the code to actually write the data from the textboxes to the table?
0
 
LVL 19
ID: 41838605
the form should be bound to the table -- turn on the Property Sheet by right-clicking in Design View and choosing "Properties" from the shortcut menu

click in the upper left, where the rulers intersect, to select the form

on the DATA tab of the Property sheet, choose your table in the Record source property

for each of the controls, on the DATA tab, set the Control Source to the field

here is code I put in a general module to call to add a record:
'~~~~~~~~~~~~~~~~~~~~~~~~~~ RecordNew
Function RecordNew(Optional pF As Form _
   , Optional pFirstControlName As String = "") As Byte
' Crystal (strive4peace)
'3-20-09... 160819
   
   'example useage: Click [Event Procedure] for a New Record command button
   ' Call RecordNew(Me)
   ' RecordNew Me, "Controlname"
   'can also be assigned on the property sheet
   'to make logic copy when buttons are copied
   ' =RecordNew([Form])
   
   'NOTE: specify form parameter for subforms
   
   On Error Resume Next
   'if form reference was not passed, use the active form
   If pF Is Nothing Then Set pF = Screen.ActiveForm
   
   'with the referenced (or active) form ...
   With pF
      'if there have been changes to the current record, save them
      If .Dirty Then .Dirty = False
      DoEvents 'do it now
      On Error GoTo Proc_Err
      'create new record
      If Not .NewRecord Then
         If Not .AllowAdditions Then
            .AllowAdditions = True
         End If
         .Recordset.AddNew
      End If
      'set focus to first control, if specified
      On Error Resume Next
      If pFirstControlName <> "" Then
         .Controls(pFirstControlName).SetFocus
      End If
   End With
   
   DoEvents
   
Proc_Exit:
   On Error Resume Next
   Exit Function
Proc_Err:
   If Err.Number = 2046 Then
      ' already on a new record
      Exit Function
   End If
   MsgBox Err.Description, , _
     "ERROR " & Err.Number & "   RecordNew"
 
   Resume Proc_Exit
   Resume
   
End Function

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41838662
When you use bound forms, Access handles the details.  It ALWAYS saves dirty records
1) when you move on to a new record
2) when you move from a main form to a subform or vice versa
3) when you close the form
4) when you close the database
5) there are probably other places.   The point is that Access ALWAYS saves the data or if it can't, gives you an error message.

What exactly are you worried about?
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now