By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,395 Members | 2,524 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,395 IT Pros & Developers. It's quick & easy.

Using a command button in a form to save & then open a new record

P: 4
[z{mod-edit:Start of Original post}]
I wanted to have a Command Button on a form to save a record & then ask to open a new record. I am new to access.

I have MDFID on a form with primary key.

[z{mod-edit:Start of what was post 9}]

Thanks Seth,

To be frank, I wanted the save button to do the following:
1. Check if any fields are left blank.
2. If any fields are left blank then the button should not allow the user to save & prompt by giving message of the corresponding field which is left blank.
3. If all fields are filled then the button should save the record by enabling New Record button of a form (new record button should not be enable at the time of form entry).

Hope, I explained. Is it possible to do ?
Aug 29 '13 #1
Share this Question
Share on Google+
10 Replies

Expert 100+
P: 1,221
Your question leads me to think you might be best served with some of the lessons one can learn from the sample databases that come with Access. Under the Help menu you will find "Sample Databases." Explore those, they are very helpful for those who will invest sufficient time with them.

There is a wizard for adding a command button. The wizard can lead you through saving a record on a form, and many other useful tasks, in a very simple manner.

Aug 29 '13 #2

P: 4

Actually, i am using the below code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Save_Click()
  2. If IsNull(MDFDate) Then
  3. MsgBox ("Field 'Form Date' cannot be blank!"), vbOKCancel
  4. End If
  7. DoCmd.Save
  8. End Sub
I want save button to first save the record then prompt to open a new record "like do you want to enter new record".
Aug 29 '13 #3

Seth Schrock
Expert 2.5K+
P: 2,941
What do you want to happen if the user doesn't want to enter a new record?

My preferred method of saving a record is
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdSaveRecord
To produce a messagebox that would go to a new record, you would use the following
Expand|Select|Wrap|Line Numbers
  1. Dim strMsg As String
  2. strMsg = "Do you want to enter a new record?"
  3. If MsgBox(strMsg, vbYes or vbQuestion) = vbYes Then
  4.     DoCmd.GoToRecord ,, acNewRec
  5. End If
Aug 29 '13 #4

Expert Mod 5K+
P: 5,397

Line 2: If IsNull(MDFDate) Then
I would change this to If (ME.MDFDate & "")="" Then
You will catch both the null state and empty string (there is no space between the double quotes).

Then follow Seth's advice.

Of Note:
If you are using either V2007 or V2010 you are better off not using the Control Design Wizards unless you will be publishing the database to Sharepoint.

Something that many people do not know is that MS has moved the default language for the Wizards from VBA to Macro. The two languages are not interchangable, and despite the improvements in Macro, VBA is still much more robust.

I also agree with jimatqsi. What you asked is covered in most of the basic tutorial sites and books. A foundational level of understanding of the basics would be most helpful for you and will make it much easier to understand our advice. :-D (please note, kind voice and BIG SMILE... we loose so much in the text)

Also, when posting Code/SQL/etc... or a table of data, please select the text and then click on [CODE/] formatting. :)
Aug 29 '13 #5

Expert 100+
P: 1,221
I didn't know about that with the wizards and the "default language." Does that mean the language preference can be changed for the wizards?

Aug 30 '13 #6

Seth Schrock
Expert 2.5K+
P: 2,941
I don't believe so Jim. That is why I never use those wizards anymore.
Aug 30 '13 #7

Expert Mod 5K+
P: 5,397
V2007 - there is a setting; however, buried.
V2010 - no, you can no longer change Control Wizards from Macro to VBA. Converting the Embeded macro from the wizard to VBA requires a multi step process... often easier to just write the code from scratch.
Aug 30 '13 #8

Seth Schrock
Expert 2.5K+
P: 2,941
If you know of a way to convert a macro created by a wizard to VBA, please let me know. I have never been successful at doing this as the convert button is grayed out on these macros. I always have to create my own macro as a copy of the wizard's macro to be able to convert it.

Sorry for getting off topic. Maybe you could post an article about it and then delete this post.
Aug 30 '13 #9

Seth Schrock
Expert 2.5K+
P: 2,941
Absolutely. In post #5, ZMBD gives the code to test for the fields being empty. You would just put your message about the fields being required in the true side of the If-Then-Else statement. In the False side, you would the save code that I posted in post #4 and then the enable the button, which is done with the following code:
Expand|Select|Wrap|Line Numbers
  1. Me.button_name.Enabled = True
You would probably want to set the NewRecord button's enabled property to false in the true side so that you can be sure of whether or not the button will have the proper enabled setting when you click the save button.
Aug 31 '13 #10

Expert Mod 5K+
P: 5,397

We are not a code writing service. Several answers have been given and we'll help you if you get stuck implementing them; however, you really need to do the work... think of this as site that teaches how to fish so that you can eat everyday.

By way of helping yo learn the basics, this is a site that I've used off and on... not my favorite site due to the age now; however, the Basics are still solid.

Some things to note:
  1. - The following is old.
  2. - It is for Access95.
  3. - Many of the new features are not covered.
  4. - Much of the user interface has changed... not so much from V95 to V2003; however, from V95 to V2007/2010 that ribbon makes a muck of things until you learn where things were moved.
  5. - If you are using Access 2007/2010 ACCDB files then ignore the part about the switchboard (in fact, I ignored the switchboard after the first time I used it and the end users just about hung me [:(] )
  6. - It covers some neat tricks with the Macro Language... be leary of these... they do have their uses and I use some of these upon occation when nothing else will do the trick!
  7. - the Author is BIASED against VBA! Do not let his negative comments about VBA un-nerve you. What he doesn't cover there is covered in the online help documents and help is available here and elsewhere on the Net :D next to SQL, VBA is the biggest workhorse and most powerfull tool you have in the Access database.
  8. - I mention this here only because of the age of the site I'm sending you to: There was a change in the database model... from ADO to DAO back to ADO and now back to DAO... the default model in V2007/2010 is back to DAO. ADO is still there; however, many of us use it only if we'll be upsizing to larger database such as SQL-Server, MySQL, Oracle, etc.. (others swear by ADO over DAO... you can find that argument elsewhere and I'll delete any posts in this thread that attempt to start it as a hijack };-) )

MS Access 95 Tutorial
Aug 31 '13 #11

Post your reply

Sign in to post your reply or Sign up for a free account.