• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Adding a New Record woes

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

1 Solution
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.
MikeDTEAuthor Commented:
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

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Assuming the form is a data-entry style, where the users can directly enter data: Can you add a record directly through the interface?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MikeDTEAuthor Commented:
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.
MikeDTEAuthor Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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 ?

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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?

MikeDTEAuthor Commented:
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?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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.

MikeDTEAuthor Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are welcome ...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now