DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

Unbound Forms in MS Access - Part 2

In part 1 of this article we created a simple database, consisting of one table and one form. The form was an unbound form. The database showed one way of editing existing records using unbound text boxes.

In part 2 we will look at a method for adding new records. BUT, before we do that, Richard J. Raszkiewicz ("Rich") has suggested I tell you a nice way of making the text boxes and buttons accessible using a common keyboard shortcut.

Rich likes to use the Alt key method. Notice how, in a standard Windows application, all the items in a menu have one letter underlined? If you hold the Alt key down and press that letter, that menu becomes active. How do we do that in Access? Easy. Put an ampersand (&) in front of the desired letter in the Caption property of the object. Thus, &Save in the caption of the Save button will make Alt+S act the same as a mouse click on the button.  

By the way, if you are in the Litchfield/Medina/Cleveland area and need some "in person" help with an Access programming situation, contact Rich at rraszkX@YhughesZ.net. (to stop spammers, I have put in some extra upper case "XYZ" characters. You will have to remove them before emailing Rich). The newest version of the sample Access database available below has these Alt key shortcuts implemented.

Back to the Add New button.

I added another button to my form, called it cmdAddNew and gave it a caption of "&Add New". The VBA code in the button's OnClick event is shown below. All it does is blank out the text boxes and put the focus on the first text box. That works great, however, when we go to save the new record we have a problem. In order to add a new record to a recordset, we need to use the addnew method. The code behind the Save button uses the Edit method. How can we tell the program the difference?

I can think of 2 possible methods. (1) declare a boolean variable to track which one we are using, or (2) have a hidden field that does the same thing. I prefer method 2, and I have no solid reasons for doing so!! editor's note: after writing this article, I started trying a 3rd method. I will write an article on it soon. Subscribe to e-zine (see link in menu to the left) to be kept up to date

So, I'll add a hidden check box named chkNew to the form, and make its default value False. I'll also modify the VBA behind the Add New button to change the value of the checkbox to True, and the VBA behind the Save button so it resets this checkbox to False. As well, the VBA behind the Save button now has an IF statement to check the value of the checkbox.

I have also added code to requery the list box (so a new addition shows up immediately) and to move the focus to the appropriate place after clicking the Add or Save buttons. Finally, I added a button to exit the form. (DoCmd.Close)

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Code for cmdAddNew (OnClick event)

txtCompany = null
txtContact = null
txtAddress = null
txtCity = null
txtID = 0
chkNew = True
txtCompany.SetFocus

New version of code for cmdSave (OnClick event)

Dim rst As Recordset
'--- next line assumes that txtID is numeric Set rst = CurrentDb.OpenRecordset("select * from " & _
  "tblCustomers where CustID=" & txtID)
if chkNew = True then
  rst.AddNew
Else
  rst.Edit
End If
rst!CustCompany = txtCompany
rst!CustContact = txtContact
rst!CustAddress = txtAddress
rst!CustCity = txtCity
rst.Update
rst.Close
Set rst = Nothing
chkNew = False
lstData.Requery
lstData.SetFocus

Further Suggestions:

You should add some error checking to the Save routine. For example, nothing stops a user from clicking the Add button, and then the Save button, without typing any data. Something like "if isnull(txtCompany) then ..." protects the system.

What's next? Part 3 of this article will add a Cancel button. That way, if the user clicks Add New and then changes their mind ...

By the way. Whether you like the idea of unbound text boxes or not, you may find it interesting how I avoid subforms by using this technique. That will come in a future part of this series.

A personal comment follows. I do NOT like subforms. And I see so many comments on the newsgroups about hassles with subforms. I realize that this unbound method requires more programming, but the concept is very easy to program for, and I feel much more in control. End of personal comment.

If you would like the sample database that I created to test this series (Access 2000 version only) please visit this page. The sample database is stored in a 21K zip file, so you will need WinZip, or similar.

(article continues after sponsor spot)



 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy