Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Access save current record without moving to the next record
Hi
I have two forms in my database.
frmStart after the user enters their name and call media they press the new call button. This code is executed with the new call button is pressed
DoCmd.OpenForm "frmMain", , , , acFormAdd
DoCmd.GoToRecord , , acNewRec
The second form then opens and the record source of this second form is tblcustomers. On the form frmMain I have a textbox called IDNumber which is linked to the ID field of the table tblcustomers. The user can press an abort button without entering any information into the form due to this I need a way of saving the current record without creating a new record as I would like to use the value IDNumber and store this in another table.
Basically IDNumber is not shown onscreen until I force access to save the record (using Me.Dirty = False in the onclick event of the abort button) - but using this code creates another record in the tblcustomers table.
Is there anyway of saving the current record without moving on to the next record?
Thanks
I have two forms in my database.
frmStart after the user enters their name and call media they press the new call button. This code is executed with the new call button is pressed
DoCmd.OpenForm "frmMain", , , , acFormAdd
DoCmd.GoToRecord , , acNewRec
The second form then opens and the record source of this second form is tblcustomers. On the form frmMain I have a textbox called IDNumber which is linked to the ID field of the table tblcustomers. The user can press an abort button without entering any information into the form due to this I need a way of saving the current record without creating a new record as I would like to use the value IDNumber and store this in another table.
Basically IDNumber is not shown onscreen until I force access to save the record (using Me.Dirty = False in the onclick event of the abort button) - but using this code creates another record in the tblcustomers table.
Is there anyway of saving the current record without moving on to the next record?
Thanks
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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?
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?
Lisa, when you are editing a record on a form, at that point, Dirty is equal to True. This means that the changes have not yet been saved. In the case of a new record, it has not yet been created. Setting Dirty equal to False is what actually saves the record and gives you the new ID. Using Undo cancels your edit so you don't have a new record but you also don't have a new ID.
Is this ID field an AutoNumber type or one that you specially create? I'm under the impression that you have it set as AutoNumber. If this is the case, you can just use a DMax to get the next number. In a textbox, add the following expression in it's Control Source:
=DMax("[ID]","tblCustomers
Ron
Is this ID field an AutoNumber type or one that you specially create? I'm under the impression that you have it set as AutoNumber. If this is the case, you can just use a DMax to get the next number. In a textbox, add the following expression in it's Control Source:
=DMax("[ID]","tblCustomers
Ron
At the OnClick event of the abort button you can have this simple code to save an unsaved record:
If Me.Dirty = True Then
Me.Dirty = False
End If
/gustav
If Me.Dirty = True Then
Me.Dirty = False
End If
/gustav
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.
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
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
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
Then you have other code doing that.
Setting Dirty = False cannot create a new record, it saves the current (edited) record only.
/gustav
"sorry im still not following your explanation "
You need to undo the new record if not required to save.
For the right edited record, sure you need to save.
I asked for a sample data base, because it takes time to reproduce the issue.
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.
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
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
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
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
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
Then on the onload event of the main form was this code
Me.txtCallID = [Forms]![frmStart]![txtCal
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?
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
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
Then on the onload event of the main form was this code
Me.txtCallID = [Forms]![frmStart]![txtCal
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
So you are saying that
DoCmd.OpenForm "frmMain", , , , acFormAdd
does NOT open your form in Add mode?
That is what "acFormAdd" says to do. If that is not working for you then one of two things is going on.
1. you have code that is overriding this setting that executes when the form opens.
2. the form is corrupt and should be rebuilt.
The Me.Dirty = False is a trick. When a person sees that expression for the first time, his assumption is almost always that the save would be cancelled when in fact, the trick forces the record to be saved. Apparently, there is a bug in Access where sometimes when you force a save the correct way with DoCmd.RunCommand acCmdSaveRecord (which is obvious in its intent) an error is raised so word of this got out and people switched to using the trick. In my experience, the trick is just as likely to fail as the standard expression. But in the cases where I have used it, I always include a comment regarding what it does.
I saw in your tag line that you are a PCP programmer so you may be an experienced programmer, just not with Access. Access is all about saving the data. If you dirty a form, it will save automatically. You don't have to do anything to make it happen. In fact, some people (the ones who don't understand form level events) have much more trouble cancelling the save than allowing it to happen. So, the only reason for forcing a save is because you know something Access doesn't. For example, you have an edit form that has a report button. You know that you need to save the record in order for it to appear in the report in its updated state. So in the click event of the button, you save the current record, THEN you open the report. Same with opening a popup form. You always save first. There are very few other situations where you have to force Access to save rather than waiting for it to save automatically. Another case might be if you are connected to a SQL Server or some other RDBMS BE so that you don't see autonumbers immediately. You have to force the save to send the update to the server and receive back the generated identity column. Jet/ACE are closely coupled with Access and so Access "sees" the autonumber as soon as you type the first character in the form. The automatic save always occurs when the current record looses focus and that can happen in several ways such as scrolling to a new record, clicking into a subform or back to the mainform from a subform, closing the form, closing the database, etc.
I've been working with Access since the beginning and before that I was an experienced developer - mostly COBOL on IBM big iron. Going from a procedural language to an event driven one was an adjustment and I have dabbled in other languages over the years. They have mostly been more like COBOL in how code is written than Access VBA. The best advice I can give you is drop your preconceptions of how Access "should" work and learn how it "does" work and you will have less of a struggle with it. Each event has a purpose and once you understand what the intent of the event is, it will be easier to place your code where it will work without adverse effects or undue excess coding.
DoCmd.OpenForm "frmMain", , , , acFormAdd
does NOT open your form in Add mode?
That is what "acFormAdd" says to do. If that is not working for you then one of two things is going on.
1. you have code that is overriding this setting that executes when the form opens.
2. the form is corrupt and should be rebuilt.
Then on the onload event of the main form was this codeWhen you do this, you are dirtying the record before the client does. It is poor practice and will lead to duplicate or empty records. I saw that you also had code in the calling form that poked data into controls on the popup form, this is also bad for the same reason - you are dirtying the record before the user does. Most tables have at least one required field. If your code populates it and the user abandons his update and doesn't add anything else, you have just saved a useless record. If you add code to the form's BeforeUpdate event to validate that certain values are present, the user isn't going to understand when you give him an error message since from his perspective, he didn't change anything! And he is correct. He didn't change anything, you did.
Me.txtCallID = [Forms]![frmStart]![txtCallID]
The Me.Dirty = False is a trick. When a person sees that expression for the first time, his assumption is almost always that the save would be cancelled when in fact, the trick forces the record to be saved. Apparently, there is a bug in Access where sometimes when you force a save the correct way with DoCmd.RunCommand acCmdSaveRecord (which is obvious in its intent) an error is raised so word of this got out and people switched to using the trick. In my experience, the trick is just as likely to fail as the standard expression. But in the cases where I have used it, I always include a comment regarding what it does.
I saw in your tag line that you are a PCP programmer so you may be an experienced programmer, just not with Access. Access is all about saving the data. If you dirty a form, it will save automatically. You don't have to do anything to make it happen. In fact, some people (the ones who don't understand form level events) have much more trouble cancelling the save than allowing it to happen. So, the only reason for forcing a save is because you know something Access doesn't. For example, you have an edit form that has a report button. You know that you need to save the record in order for it to appear in the report in its updated state. So in the click event of the button, you save the current record, THEN you open the report. Same with opening a popup form. You always save first. There are very few other situations where you have to force Access to save rather than waiting for it to save automatically. Another case might be if you are connected to a SQL Server or some other RDBMS BE so that you don't see autonumbers immediately. You have to force the save to send the update to the server and receive back the generated identity column. Jet/ACE are closely coupled with Access and so Access "sees" the autonumber as soon as you type the first character in the form. The automatic save always occurs when the current record looses focus and that can happen in several ways such as scrolling to a new record, clicking into a subform or back to the mainform from a subform, closing the form, closing the database, etc.
Its at this stage that I need the current tblcustomer id number to populate in another table. Is this wrong?No it is not wrong. Place the abort button on the popup form. Pressing the abort button can populate the necessary fields by getting them from the other open form. In essence, you "pull" instead of "push" the data. You can save the record at that time also but it isn't necessary. Access will save when you close the form.
I've been working with Access since the beginning and before that I was an experienced developer - mostly COBOL on IBM big iron. Going from a procedural language to an event driven one was an adjustment and I have dabbled in other languages over the years. They have mostly been more like COBOL in how code is written than Access VBA. The best advice I can give you is drop your preconceptions of how Access "should" work and learn how it "does" work and you will have less of a struggle with it. Each event has a purpose and once you understand what the intent of the event is, it will be easier to place your code where it will work without adverse effects or undue excess coding.
Yes,...you will spend too much time "fighting" Access.
Again, what you are asking for is not commonly done, ...so you need to re-evaluate the way you have designed your system.
These types of "endless" questions come up a lot when a user wants non-standard functionality.
One fix will lead to another oddity under certain conditions, ...
the fix to that will undo the previous fix.
the fix for that will work unless the user cancels the operation, ..
the fix for that will not work if another forms not open.
...etc
At best, you will end up with a solution that "kinda" works under specific, tightly controlled scenarios.
At worse, you end up with a mess that works unpredictably.
Anytime you design a system, ask others if this is the "standard practice".
When you design your system around the way you want it to work, you are restricting what you can do within the confines of the Access programming language.
If you want total control, then you will have to move to unbound objects, where you, (in effect), create your own programming logic.
At that point, you have to question whether Access is the right tool for you.
Access VBA is a RAD (Rapid Application Development) platform.
If you need something truly "custom", ...then you have to do the programing yourself.
Looking at your question title would indicate that this would be a pretty straightforward question.
Even the revised "cancel a record" is typically a simple task.
I mean, ...just look at the code you are dealing with, ...nothing horribly complicated, ...yet it is not working for you.
This should indicate that you need to rethink your interface and the way users interact with it.
So perhaps we need to go back to the beginning...
What is the purpose of his system?
What "specifically " are you trying to accomplish with this system, and why?
Perhaps I am missing something , but it just seems like this "simple" question is turning out to be more complex than you or the Experts here had anticipated...
That seems to indicate that we are "fighting", Access, instead of working with Access.
JeffCoachman
Again, what you are asking for is not commonly done, ...so you need to re-evaluate the way you have designed your system.
These types of "endless" questions come up a lot when a user wants non-standard functionality.
One fix will lead to another oddity under certain conditions, ...
the fix to that will undo the previous fix.
the fix for that will work unless the user cancels the operation, ..
the fix for that will not work if another forms not open.
...etc
At best, you will end up with a solution that "kinda" works under specific, tightly controlled scenarios.
At worse, you end up with a mess that works unpredictably.
Anytime you design a system, ask others if this is the "standard practice".
When you design your system around the way you want it to work, you are restricting what you can do within the confines of the Access programming language.
If you want total control, then you will have to move to unbound objects, where you, (in effect), create your own programming logic.
At that point, you have to question whether Access is the right tool for you.
Access VBA is a RAD (Rapid Application Development) platform.
If you need something truly "custom", ...then you have to do the programing yourself.
Looking at your question title would indicate that this would be a pretty straightforward question.
Even the revised "cancel a record" is typically a simple task.
I mean, ...just look at the code you are dealing with, ...nothing horribly complicated, ...yet it is not working for you.
This should indicate that you need to rethink your interface and the way users interact with it.
So perhaps we need to go back to the beginning...
What is the purpose of his system?
What "specifically " are you trying to accomplish with this system, and why?
Perhaps I am missing something , but it just seems like this "simple" question is turning out to be more complex than you or the Experts here had anticipated...
That seems to indicate that we are "fighting", Access, instead of working with Access.
JeffCoachman
Premium Content
You need an Expert Office subscription to comment.Start Free Trial