MS Access form is locked up

Clarifications

Adding New Records to and Modifying Existing Records in a Microsoft Access Database

We?ve decided to let you in on a little secret: the Scripting Guys (unlike the rest of you, of course) have their good days and their bad days. Some days we come in excited and ready to work; other days (well, OK, most days) it?s a bit different. How are you, the readers of the Office Space column, supposed to know whether you?ve hit the Scripting Guys on a good day or a bad day? Well, here?s a hint: any time the Scripting Guys decide to ?expand upon a previous column? then you know they?re taking the lazy way out and not even trying to think up something new.

So what does that have to do with this week?s column? Why, nothing, nothing at all?. Since you mentioned it, though, this week we?ve decided to expand upon a previous column, one in which we showed you how to connect to and read from a Microsoft Access database. Today we?re going to take the next logical step and show you how you can add records to an Access database. And then, just for the heck of it, we?ll take another step and show you how to modify existing records as well.

OK, we admit it: we didn?t exactly knock ourselves out trying to come up with a brand-new topic for this week?s column. But we?ll rationalize that by pointing out that, as useful as it is to be able to extract data from a database, it?s just as useful (if not more so) to be able to add data to that database. So while we are being a tad bit lazy at least we?re covering an important topic. Besides, you can simply think of this as the second part in a multi-part series: you don?t have to worry that we?re just taking the easy way out!

Seeing as how we are being a tad bit lazy, we won?t review any of basic concepts behind connecting to an Access database; if you need a brief refresher on that, take a look at last week?s column. Today we?re going to get right down to business: let?s show you how to add a new record to an existing database.

To begin with, we?re assuming you have an Access database named Inventory.mdb living in the C:\Scripts folder. We?re also assuming that this database has a table named GeneralProperties, and that the GeneralProperties table includes the following fields:
?

ComputerName
?

Department
?

OperatingSystem
?

Owner

Needless to say, if you don?t have such a database then none of these sample scripts will work. That doesn?t mean you can?t learn anything from them; you just won?t be able to run them and get them to do anything.

With that in mind, let?s take a look at a script that adds a new record to the database:

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = c:\scripts\inventory.mdb"

objRecordSet.Open "SELECT * FROM GeneralProperties" , _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.AddNew
objRecordSet("ComputerName") = "atl-ws-99"
objRecordSet("Department") = "Human Resources"
objRecordSet("OperatingSystem") = "Microsoft Windows XP Professional"
objRecordSet("Owner") = "Ken Myer"
objRecordSet.Update

objRecordSet.Close
objConnection.Close

Note. We should note that this is not the only way to add a new record to a database. For example, SQL aficionados can also use an Insert Into query. We?re using the AddNew method simply because it?s easier; Insert Into queries can get extremely complicated, especially when you?re dealing with many fields, values stored in variables, and fields of different data types. In the lazy spirit of the day, we?re taking the easy way out.

The script starts out by connecting to the database C:\Scripts\Inventory.mdb. Like we said, we won?t discuss this in any detail, but that?s what these lines of code are for:

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = c:\scripts\inventory.mdb"

After making the connection we then use the Recordset object?s Open method to run a SQL query that retrieves all the records from the GeneralProperties table; that?s what we do here:

objRecordSet.Open "SELECT * FROM GeneralProperties" , _
objConnection, adOpenStatic, adLockOptimistic

What gives us is a recordset that we can add a new record to. And that?s what we do here:

objRecordSet.AddNew
objRecordSet("ComputerName") = "atl-ws-99"
objRecordSet("Department") = "Human Resources"
objRecordSet("OperatingSystem") = "Microsoft Windows XP Professional"
objRecordSet("Owner") = "Ken Myer"
objRecordSet.Update

We begin by calling the AddNew method; this sets up a blank template to work with, but doesn?t actually add anything to the database. (At the moment, all the work we?re doing is taking place solely in memory.) We then have a series of lines that assign values to the individual fields for this new record. (These fields, of course, are the fields found in the table GeneralProperties.) For example, this line sets the value of the ComputerName field to atl-ws-99:

objRecordSet("ComputerName") = "atl-ws-99"

Note that the value being assigned (atl-ws-99) is enclosed in double quote marks; that?s because ComputerName is a text field and is expecting to be assigned a string value. If a database field is numeric or Boolean (true/false) then you should not enclose the values in double quote marks:

objRecordSet("IsLaptop") = False
objRecordSet("NumberOfPrcoessors") = 2

But you knew that already, didn?t you?

Finally, we call the Update method, which actually writes the new record to the database:

objRecordSet.Update

Don?t forget that line of code: if you do, the recordset in memory will be updated to contain the new record, but the actual database will remain unchanged.

As you can see, adding a new record to an Access database is pretty easy. But what about modifying an existing record? For example, suppose we no sooner add the computer atl-ws-99 to the database then Ken Myer gets transferred from the Human Resources department to the Finance department. That means we now have to update the department field for that particular computer. How hard is that going to be?

Relax: it?s not going to be hard at all. Again, there are different ways to update records in a database, but for now we?ll focus on what we feel is the easiest way to update a record: you search for the record, update the appropriate field (or fields), and then call the Update method. In other words, a process that looks remarkably like this:

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = c:\scripts\inventory.mdb"

objRecordSet.Open "SELECT * FROM GeneralProperties" , _
objConnection, adOpenStatic, adLockOptimistic

strCriteria = "ComputerName = 'atl-ws-99'"

objRecordSet.Find strCriteria
objRecordset.Fields.Item("Department") = "Finance"
objRecordset.Update

objRecordSet.Close
objConnection.Close

You probably already noticed that the first half of this script is identical to the script that adds a new record: we connect to the database, then use the Open method to return a recordset consisting of all the records in the GeneralProperties table. In fact, you won?t find any differences between the two scripts until we get to here:

strCriteria = "ComputerName = 'atl-ws-99'"

In this line of code we?re simply assigning the search criteria to a variable named strCriteria. We want to update the record for the computer named atl-ws-99; as you might expect, that becomes our search criteria, using the format FieldName = Value. Or, ComputerName = ?atl-ws-99?.

After establishing the search criteria we call the Find method to locate that record in the recordset:

objRecordSet.Find strCriteria

We then use these two lines of code to update the Department field and then to save the changes:

objRecordset.Fields.Item("Department") = "Finance"
objRecordset.Update

Note the somewhat unusual syntax we use when referring to the Department field: objRecordset.Fields.Item(?Department?). Don?t worry too much about that; that?s just the way you have to do things. On the bright side, odd as it might look, at least it?s the same way we refer to fields when reading from a database (as we did last week).

Also, don?t overlook the Update method; once again a record will not be changed in the database until you call Update.

As we said, there are other ways we can modify existing records; however, those will have to wait for another day. Which day? Let?s put it this way: the next time we?re too lazy to think up a brand-new topic for this column, well, you can probably figure out for yourself what the topic will be.
http://www.microsoft.com/technet/scriptcenter/resources/officetips/default.mspx

Please post back if you have any more problems or questions.

Just so we can help you better, can you start by eliminating all obvious troubleshooting you have already done.

Such as closing the form and table and then reopening the form.

or closing Access and reopening the database.

Once we get those out of the way we can delve into the problem better. :)

In the Form Properties check the following on the Data tab:

Allow Edits
Allow Deletions
Allow Additions

They should be set to Yes.

Thanks! I did try creating a brand new form. A funny thing happened. The firsat time I created the form I based it on the query that I based the original form on. I simply chose two fields. The same 133 records came up and I had the same problem- no editing old record and no adding new records. I created another new form but this time I based it on the table. To this new form I can add new records and edit old ones! Aaarrrggghhh! I would just recreate the form based on the table but I wanted to use the query because I added an age field that automatically calculates the person's age based on the birthdate entered.

I would advise using the "On Exit" event for the Birthdate field.

If you click within the event area on the properties dialog box, it should give you 3 options: Event Procedure, Macro, and (ack forgive me I'm not at a PC with Access at the moment) I believe...Expression.

Since you are a beginner. Do the Expression one. It will take you to a window where you can choose functions and fields to put together an equation or statement.

In this case you'll want take the current DATE (there should be a function) minus the birth year. - which incidentally, may be easier figured if you make each part of the birthdate a separate field.

If I had more time at work (the PC with Access) I'd figure out a good expression for you. But currently I'm working on my own Access database. If I can squeeze in some figuring for you I will. :)

Feel free to use the Microsoft Help. It actually is useful, although a bit difficult to navigate at times.

Also use Microsoft's site - useful if you can find what you want. I don't know what version of Access you are using but this page may help:

http://office.microsoft.com/en-us/access/CH062526691033.aspx

Good luck!

Or insert lines into the VBA code for events?

Most beginners tend to use Macros I think, but I do better with the VBA coding (Event Procedure). I'll try to go slow:

1- In design mode for your form, Right Click on the field where you enter birthdate. Select "Properties". A small box will appear.
2- on that small box, look for the "Events" tab. Then scroll down and look for the "On Exit"
3- Click the button with 3 dots to the right of "On Exit". Select Code Builder. A whole new window will open and your cursor will be placed under something that reads

Private Sub [b]birthdate[/b]_Exit(Cancel As Integer)

The part in bold should match whatever you named the field.

4- Copy the code below and paste it EXACTLY where the cursor is (again, note that everything in bold should match the name of your birthdate field. The part in bold and caps should match your Age field. Once you paste, save and close the Microsoft Visual Basic windows. Then try it out. :) -

On Error GoTo Err_[b]birthdate[/b]_Exit

'create variables to store data
Dim birthdate
Dim birthyear
Dim currentyear
Dim agevalue

'get the Birthdate just entered and store in variable "birthdate"
birthdate = Me![b]birthdate[/b]
'extract the year out of the birthdate and store in variable "birthyear"
birthyear = Year(birthdate)
'get the Current Year and store in variable "currentyear"
currentyear = Year(Date)
'figure the age by subtracting the birth year from the current year and storie in variable "agevalue"
agevalue = currentyear - birthyear
'Fill in the Age field with the new value
Me![b]AGE[/b].Value = agevalue

Exit_[b]birthdate[/b]_Exit:
Exit Sub

Err_[b]birthdate[/b]_Exit:
MsgBox Err.Description
Resume Exit_[b]birthdate[/b]_Exit