Access save current record without moving to the next record

Upload a sample database demonstrating the issue.

Are the two forms open all the time?

You may save the ID of the main form, and when aborting, use that saved ID to go back to the required record.

To abort use Undo command, instead of dirty.

Yes the two forms are open all the time

Say for example by next available record id in tblcustomers was 800.  When I load the form frmMain I want my text box IDNumber to show 800.  When the main form loads it shows null in the text box IDNumber.  The only way I have found to show the value of IDNumber is to force access to do a save but when I do this the id becomes 801 (so it moves onto the next record).

When you say "you may save the ID of the main form" - do you mean you know a way to show the value of IDNumber as 800 when the form loads?

Why would you want to use the undo command, instead of dirty? - will this stop it moving forward a record?

When you say "you may save the ID of the main form" - do you mean you know a way to show the value of IDNumber as 800 when the form loads?

One can check if the IDNumber exists in frmMain recordsource before opening the form to add a new record.

Why would you want to use the undo command, instead of dirty? - will this stop it moving forward a record?

Undo allows exiting without saving the record.
The IDNumber is an autonumber, and that will increment the IDNumber by 1 although not saved.

It will not go back automatically to previous record unless it is told to do so, by saving the previous IDNumber before starting a new record. If the record is undone, then a search for the saved IDNumber positions the form to the previous record.

A sample database will help and speed achieving a solution.

Why does the user need to see the next ID before he is ready to create a record?  If your BE is Jet/ACE, as soon as the user types the first character into any control on the form, Access will populate the autonumber.  Isn't that good enough?  If the BE is SQL Server, the autonumber won't populate until the record is saved.  I would never want to save a completely empty record simply to make the "next" autonumber visible.  Don't you have any data integrity rules that require certain pieces of data?

Personally, I make it a practice to NEVER dirty a new record programmatically.  Any code I need that populates fields runs in the form's BeforeInsert event.  That event runs as soon as the first character is typed but ONLY for new records.  Once that event runs, I know that the user intends to add a new record and so it is safe for me to populate the foreign key and possibly other fields.

DoCmd.OpenForm "frmMain", , , , acFormAdd  ''' this opens a form to a "new" record
     
 DoCmd.GoToRecord , , acNewRec  ''' this is redundant and could cause you to save empty records if your code has dirtied them.

Ron -  I have removed the Me.Dirty = False from the onclick of the abort button.  The text box IDNumber I have changed the control source to =DMax("[ID]","tblCustomers")+1 so now when the form opens I can see the id number of the record (yes it is an auto number).  When I press the abort button the call is aborting.  When I go to the tblcustomer table there are two records produced - 1 when I first load the main form and second one is produced when I click the abort button.  (the two records are created seconds apart).  Am i missing something else as I am still unsure why its creating two records?

gustav - I tried using this code but it is still creating an extra record

hnasr - sorry im still not following your explanation - If in my customer table I have 799 records populated and I receive a call and I want my table to open at record 800 and start populating the data in this record are you saying that when I force it to save it will go to record 801 but I can then tell it to go back to record 800 using the undone command and any information I enter will be populated in record 800?

PatHartman - We can receive calls and when answered the user can hang up due to this I need to know that next id number so that when I store this information in the aborted calls table I can say this aborted call belongs to that customer ID.  I tried removing this line DoCmd.GoToRecord , , acNewRec  but when I did I nearly overwrite a customer record

> .. it is still creating an extra record

Then you have other code doing that.
Setting Dirty = False cannot create a new record, it saves the current (edited) record only.

/gustav

Access will ALWAYS save a dirtied record.  Your code is causing the duplicates.

Why do you have a separate table for aborted calls?  Why isn't that simply a status of the calls table?  Get rid of the code that moves to a new record.  That is what is causing the duplicates.  Simply opening the form in add mode already puts you on a "new" record.  If you have code in a form event that will dirty a record, when you execute the "goto", the original "new" record is saved and a second "new" record is created.  Add a status field to the table and click on the aborted call option.

FWIW, ...
I am with Pat.

All of these machinations seem far too complicated...
If it were me, I would:
Get rid of the "Aborted" button and instead create an "Aborted" Yes/No field to the table.
Then Simply add all records normally.
Then set the Aborted flag to Yes, if the call was aborted.

If something like this (or something like what Pat suggested), won't work for you, then perhaps you will have to explain a bit more about how users interact with your system.

Are you saying that you must have this unorthodox functionality, ...or are you open to other approaches? (redesigning your interface so this is more standardized.)

What you are asking for here is simply not done normally, and may turn out to be a nightmare to maintain. (in thee event of unforeseen scenarios)
This is why no one solution is working for you...
There may very well be no "easy" answer for a question like this...

JeffCoachman

What I have done now is the code that was in my start form behind the new call button

DoCmd.OpenForm "frmMain", , , , acFormAdd
DoCmd.GoToRecord , , acNewRec

I have replaced this with

DoCmd.OpenForm "frmMain", , , , , , "inbound"

Then in the onload event of the frmMain I have included this code

Select Case Form.OpenArgs
    Case "inbound"

        DoCmd.OpenForm "frmMain"
        Form_frmMain.DataEntry = True
            
        Form_frmMain.RecordSource = "tblcustomer"
        DoCmd.GoToRecord , , acNewRec
    
    End Select

Select allOpen in new window

Also in the onload event I am assigning a bound field from the start form to a text box in the main form.  I have also added (behind the onclick of the abort button)

If Me.Dirty Then
    Me.Dirty = False
End If

This seems to be working now.

Can I ask for an explanation so I understand whats happening here

When I removed this code yesterday DoCmd.GoToRecord , , acNewRec - I nearly ended up over writing a current customer record.  The only reason why I didnt is I aborted the call without entering any info but if I had of proceeded then I would have changed a current customers order. If opening the form in add mode already puts you on a "new" record I don't understand how this could have happened.

Also im not sure why the database was creating two records one when you pressed the new call button on the start form and the second when the main form was loaded.

Also I cant change the design of the tables/database as I have to work with whats there

When I removed this code yesterday DoCmd.GoToRecord , , acNewRec - I nearly ended up over writing a current customer record.

DoCmd.OpenForm frmMain, , , , acFormAdd

opens the form in add mode for me.  Did you change the final argument or remove it?  Do you have code behind the form that is doing something to move to an existing record?

Running code that modifies a new record before the user starts typing is asking for trouble.  YOU don't want to dirty a new record before the user does.  That is what leads to creating empty records.  If you need to add data to a "new" record, always do it in the BeforeInsert event so your code only runs for new records and only runs AFTER the user himself has started typing.

Hi Pat

I changed my code from

DoCmd.OpenForm "frmMain", , , , acFormAdd
DoCmd.GoToRecord , , acNewRec

To

DoCmd.OpenForm "frmMain", , , , acFormAdd

And this was when I nearly overwrite the customer record.

The record source of frmMain is tblcustomer.  

When a new call comes in I stash the values of the call id and the client id on the start form (unbound text boxes).  When the user presses the new call button the code below was used

    DoCmd.OpenForm "frmMain", , , , acFormAdd
    DoCmd.GoToRecord , , acNewRec

    
    Form_frmMain.txtClient = Me.txtClient
    Form_frmMain.txtCallID = Me.txtCallID

Select allOpen in new window

Then on the onload event of the main form was this code

Me.txtCallID = [Forms]![frmStart]![txtCallID]

This has obviously caused the issue because when I did a test database and removed this line of code it only takes one line in the database.  I just want to understand why this has caused it to take two records in the table.  I know you have mentioned that the line DoCmd.GoToRecord , , acNewRec will take a duplicate record but when I used the code embedded above (and removed the duplicate of assigning the value txtCallID twice) it only takes one record in the table.

This is my thought please correct me if I am wrong.

When the user pressed the new call button the line DoCmd.GoToRecord , , acNewRec took a new record.  In this new record the columns call id and client id were bound to the table tblcustomer.  When the main form loaded the code was trying to bind the call id again and as there was already a value in that column it caused access to take a new record in the table?

Running code that modifies a new record before the user starts typing is asking for trouble.  YOU don't want to dirty a new record before the user does.

Why is this asking for trouble? Are you saying I shouldn't have the code

If Me.Dirty Then
    Me.Dirty = False
End If

behind the abort button to get the current ID of the table tblcustomer?  The reason why I have done this is because once we receive a call the user presses the new call button and the user gets ready to input the information but the caller could then hang up.  At this stage the user has not entered any data but needs to abort the call.  Its at this stage that I need the current tblcustomer id number to populate in another table. Is this wrong?

Thanks

Thanks for the feedback guys - appreciate it