Adding a New Record woes

Posted on 2011-10-08
Last Modified: 2012-05-12
I am trying to add a new payment for a booking to the Payments table – should be simple – but it isn’t!.  I have a button on the fJDWEventPay called AddButton (code below)

Here’s the status:

Form fJDWEventPay is open and selected / in focus:  
If I debug ‘? Form.Name’ then I get “fJDWEventPay”

It’s RecordSource is the tJDWPayments

There is a filter set to the current BookingID:
I have tried switching off the filter but it makes no difference

There is an order set to the payment date.

The form properties that matter are set thus:
Allow Filters = Yes
Allow Edits = Yes
Allow Additions = Yes
Data Entry = Yes
Recordset Type = Dynaset
Record Locks = No Locks

The form is not a Popup but it is opened with:
DoCmd.OpenForm stDocName, , , , , acDialog, lngBookingID  'pass Open Args

If I use DoCmd.GoToRecord , , acNewRec
I get an error “You can't go to the specified record”

If have tried using:

Me.Recordset.AddNew  and Form.Recordset.AddNew

Neither command errors but no record is added to the tJDWPayments table

Private Sub AddButton_Click()
On Error GoTo Err_AddButton_Click
    'disable other command buttons
    Me.PrevButton.Enabled = False
    Me.NextButton.Enabled = False
    Me.ReceiptButton.Enabled = False
    'Cancel filter - didn't make a difference
    'Me.Filter = ""
    'Me.FilterOn = False
    'DoCmd.GoToRecord , , acNewRec
    jdwp_Payment = 2952 'just testing here
    jdwp_Date = Date 'and here

    Exit Sub

    MsgBox Err.Description
    Resume Exit_AddButton_Click
End Sub

Open in new window

Question by:MikeDTE
    LVL 77

    Expert Comment

    The location of the code you are trying to run is crucial in this case.

    I can't see the Openform command so where is that?
    And where is the AddButton?

    The issue here is that if you use acDialog to open the second form, then code will stop running in the calling form until the opened form is closed.

    Author Comment

    Look in my text above the code

    Here it is in full
    lngNoBookings = DCount("*", "tJDWBookings", "jdwb_Event=" & lngEventID & " And jdwb_BookingID=" & lngBookingID & " and jdwb_Status=" & Chr(34) & strStatus & Chr(34))
    If lngNoBookings > 0 Then
        Dim stDocName As String
        stDocName = "fJDWEventPay"
        DoCmd.OpenForm stDocName, , , , , acDialog, lngBookingID  'pass Open Args
        MsgBox error
    End If

    Open in new window

    LVL 84
    Assuming the form is a data-entry style, where the users can directly enter data: Can you add a record directly through the interface?

    Author Comment

    The Add Button is on the same form (fJDWEventAdd) - again as I said in the text.

    Yes I understand the purpose of acDialog - but everything I am trying to do is on the opened and current form - i.e. I set the filter, I set the order and I find the current records belonging to the BookingID, and I try add the record to a recordset that is bound to the same opened and current form.

    Author Comment

    Sorry LSMConsulting

    I don't fully understand the question.  If you mean can I go to the table and enter data - then yes.  I had disabled the Navigation Buttons.  I re-enabled them and Add (>*) was available and when I clicked the number of records incremented by one - so I saved the record.   But when I opened the table no new record had been added.
    LVL 75

    Accepted Solution

    "If I use DoCmd.GoToRecord , , acNewRec"

    You have

    Data Entry = Yes

    So, you are *already* at the New Record position.  That's what Data Entry = Yes does.

    So, what is the issue beyond that ?

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Is the intent of this popup form just to add a single new payment, then close ?

    Or, do you need to add more than one payment when this form is open?


    Author Comment

    Hi DatabaseMX

    I've changed the Data Entry property back to No.  I changed it in deperation of finding a solution.

    The purpose of the form would be to enter as many payments as required - albeit that payments made on one BookingID are vey rarely going to exceed a single payment.  I envisage that as each payment is added the form will be closed and a different Booking, chosen from the parent form, would be selected.  Then the fJDWEventPay form would be opened again under a different BookingID.

    I can now see that the record will not save because the Primary Key field remains in the dataset and thus the record cannot be writen back to the table because that would create a duplicate primary key.  So the way forward would be to clear all data including the Primary Key field and then perform a save operation with new data.  I would have thought that the Primary Key field (which is a AutoNumber) would generate it's own unique value (i.e. the next number in sequence)?

    I think I was expecting acNewRec and Form.Recordset.Addnew to create the blank record on the table - now I can see is that it just provides the entry fields and it is the save operation that creates the record - is that correct?
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    "now I can see is that it just provides the entry fields and it is the save operation that creates the record - is that correct?"

    Exactly.  When you open in Data Entry mode = Yes,  you are at the New Record 'position'. A new record is actually started when any bound field on that form is changed, or if you were to set a field value in the underlying recordsource.

    As I see it, this form only needs two buttons. Save & Cancel (Or Done)
    So ... IF ... you want to be able to add more than one payment in the same 'session' on that Form, then when you click Save, it will save that payment, then execute the DoCmd.GoToRecord , , acNewRec.  Otherwise, it will close the form.  Cancel/Done would always close the form - if not Dirty.

    All your save button needs is

    Me.Dirty = False.

    This is the basic drift.


    Author Comment

    Excellent - I can now work on this.  I have 3 'action' buttons - Cancel, Save and Close.  The first two are self explanitory and will allow multiple payments to be entered to a booking under it's unique BookingID.  The Close button will also do a save (in common with other forms in my project) and then exit the form.  This latter methodology is probably unconventional but it's in the user notes and training.

    Thank you DatabaseMX - the points are yours.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    You are welcome ...


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    737 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

    21 Experts available now in Live!

    Get 1:1 Help Now