Quick question

[One confused underwriter]

I'm working on a database that stores a series of company audits that are
done every year. I was handed a database that was supposed to be near
completion (only a simple error or two) and I was asked to complete it. I'm
relatively new to Access (used it before in college but it's been a couple of
years now), and my question might be rather vague. I've made sure that all of
my information is correct, there are 3 tables and all keys are clearly
defined, correct, and relationships have been formed between the 3 tables.
I've gone over this database with two other associates who have more
experience than I do. We all agree that the database looks fine and should
work. However, whenever we start up the database it allows us to enter in the
name of the person who is doing the audit (which puts that person's name on
all audits they enter. But just after that point, the Access freezes. I have
no idea why and I was wondering if there might be some common bugs or certain
areas one should typically look first to find a solution. Any suggestions
would be VERY much appreciated.

Thank you all very much

 

[Klatuu]

First, what version of Access was the original database written it?
Are you using the same version or a newer one?
If it was written in an older version, have you converted it?

If that is not the problem, open the form you are using to enter the
person's name and see if there is any VBA code associated with the form and
specifically with the control where you enter the name. I would first look
to see if there is a before update or after update event for the control. If
so, put a break point on the first line of code, swith to form view, enter
the name and trace the code until it fails.

See what happens and post back.

 

[John Vinson]

I'm working on a database that stores a series of company audits that are
done every year. I was handed a database that was supposed to be near
completion (only a simple error or two) and I was asked to complete it.

owwww....

I hate to tell you how many times I've heard THAT line.

The only good thing about it from my perspective is that (as a
consultant) it usually means "Billable Hours" - *lots* of them,
typically.

I'm relatively new to Access (used it before in college but it's been a couple of
years now), and my question might be rather vague. I've made sure that all of
my information is correct, there are 3 tables and all keys are clearly
defined, correct, and relationships have been formed between the 3 tables.

That's better than most.

I've gone over this database with two other associates who have more
experience than I do. We all agree that the database looks fine and should
work. However, whenever we start up the database it allows us to enter in the
name of the person who is doing the audit (which puts that person's name on
all audits they enter. But just after that point, the Access freezes. I have
no idea why and I was wondering if there might be some common bugs or certain
areas one should typically look first to find a solution. Any suggestions
would be VERY much appreciated.

Please post a bit more information. Are you entering this data on a
Form (good) or directly into tables (not the best idea)? WHere are you
entering the name? Do you get any error messages? What "freezes" - do
you need to Ctrl-Alt-Del and shut down Access, or what?

John W. Vinson[MVP]

 

[Klatuu]

LOL,
Did one of those a few years back.
"It is Almost Done, it just has a few errors to clean up, but we had to fire
our previous consultant"

One year and $75,000 later, it worked.

I knew it was going to be good when they said they fired the other guy

 

[One confused underwriter]

That's pretty much where I'm at right now. The last consultant was fired and
I'm picking up the slack, and learning all about Access along the way. Not a
bad way to do so if I may say myself.

When I open the program, there's a drop-down menu that allows you to chose a
reviewers name. I select the name, hit ok, and it closes the form, and
freezes.

I'm entering data into a form, but can't even get to that point.

It looks painfully simple and neatly laid out, the next problem lies
therein. The database is on a server on the other side of the country.
Therefore, there may be a problem with memory that is causing the crash, it's
my job to make sure there are no other possible problems before a memory
issues is addressed.

Thank you to both of you who posted, I'm looking over the possible errors
suggested by you right now. I'll post again if this fixes my problem.

Jim Francis

 

[One confused underwriter]

Also, I am using Access 2003. There's no need for me to convert. So I'm all
set on that front.

 

[One confused underwriter]

Access locks up after I select the reviewer, and I have to hit ctrl-alt-del
and close access.

 

[One confused underwriter]

Update:

I have just got passed the freezing aspect of my issue. Turns out that the
code that was bringing in the name of the reviewer was looking in the wrong
column of the datasheet, once fixed, it opens. Now I have one more dilemma.

We are entering the information into a form that is supposed to have a click
even to add the new records to the main table. The delete even works, as does
the save (for saving changes), but the adding records does not. Where can I
find some basic code that would allow this click event to add the new
information to the main table??

Thanks guys
Jim

 

[One confused underwriter]

Almost forgot, here's what the Add Click event looks like now:


Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click


DoCmd.GoToRecord , , acNewRec

**Do you know what's missing?

 

[Dennis]

Personally, I use ADO to do my database work. While it doesn't take advantage
of Access' automated abilities (gag), it allows me complete control, and I
know EXACTLY what's happening...

 

[One confused underwriter]

ADO huh? I think I'm gonna have to look into that.

 

[Klatuu]

That code will create a new blank record in your form. If you see any values
in the controls, the control has a Default Value property that is putting it
in.
The way Access works it that it will add the record with the data filled in
on the form once you move off the record, that is move to the previous
record, the next record, select another record, or close the form. You can
also force the record to update using Me.Dirty = False.

Can you describe what happens when you click this button now?

 

[Dennis]

Here's a specific example from one of my forms. When a button is clicked, I
execute this code. It's pretty easy to read....

Set dbConn = CurrentProject.Connection
Set recSet = New ADODB.Recordset
recSet.CursorLocation = adUseServer
recSet.CursorType = adOpenKeyset
recSet.LockType = adLockOptimistic
recSet.Open "[tblPCInfo]", dbConn, , , adCmdTable

recSet.AddNew
recSet!LabID = Me.fldLab
recSet!PCAssetNum = Nz(Me.fldAssetNbr)
recSet!TME = Nz(Me.fldTME)
recSet!PCModel = Nz(Me.fldModel)
recSet!BIOS_Level = Nz(Me.fldBIOS)
recSet!IPAddress = Nz(Me.fldIP)
recSet!StaticIP = Nz(Me.chkStatic)
recSet!OperatingSystem = Nz(Me.fldPCOS.Column(1))
recSet!OS_Version = Nz(Me.fldOSversion)
recSet!Comment = Nz(Me.fldComment)
recSet.Update

MsgBox "Record added successfully", vbInformation

DBEngine.Idle dbRefreshCache

recSet.Close
Set recSet = Nothing
dbConn.Close

 

[Klatuu]

If you use bound forms, you don't have to deal with that at all.

Note that Microsoft chose to abandon ADO as the default for Access and went
back to DAO

 

[Dennis]

I don't use bound forms because of the complexities of the applications. I
execute a lot of behind-the-scenes processing, and need total control over
what's happening.

However, in the ADO vs DAO debate, I thought it was the other way around
(which is why I've been employing ADO). I seem to have read several recent
papers on that. However, I'd be more than happy to read anything you'd care
to reference in this thread.

Thanks!

 

[Klatuu]

I'm not sure which version, Dennis. It may be 2003 or I may be confused. I
did a little research, but don't have time to track it down. I do know that
in 2002 ADO was the default.

In either case, it doesn't matter which you use. ADO has some abilities
that DAO does not, but comes with a different set of headaches.

As to your position on bound vs unbound forms. Four years ago, I would have
agreed with you 100%. I have found, however, that if you really learn how to
use bound forms, they will make your life easier.

One of the advantages is bound forms are usually lighter. That is, a
completely "Lite" form has no code at all. It therefore loads and executes
more quickly.

When you have an unbound form, you really don't have any better control than
you do with a bound form. Trust me on this, Dennis, I have done it both ways
with complex applications. You also have to have a lot of code in the form
which degrades performance to some degree.

One trick you can use regardless of whether you use bound or unbound forms
it to keep as much code out of the form as possible by putting it in a
standard form and only putting enough code in the form to call the procedures
in the standard form.

Unbound forms are okay, nothing wrong with them. We each have our own
style. And this is one of the reasons this forum is here - to trade ideas
and opinions.

 

[Dennis]

Headaches?? I could tell you stories... ;^)

As to using unbound forms - I need to code for future support capability. NO
ONE here knows much about Access (or databases for that matter) except me.
So... if I use ADO and unbound forms, it's more clear to someone coming along
later as to exactly what is going on. From a maintenance standpoint, it's the
only way to go (in the case of my organization).

Then you have the users, who don't have a CLUE what the standard navigation
controls do. I find it's far easir for them to create buttons that say:

ADD REC, DEL REC, UPDATE REC, etc. Those they can understand....


*sigh*

 

[Klatuu]

I agree with you on the standard buttons. My users never see them. I also
use descriptive buttons and my own set of Nav buttons. Those, of course, are
not bound objects. How I hande them is always name the command buttons
exactly the same so in case I have to address them. Then I have code in a
standard form I call modFormOperation that handles each situation. I try to
avoid referencing the control itself. Here is an example of my Delete button
routine. All it takes is entering the following in the Click Event property
box of the command button:

=DelCurrentRec(Me)

Public Sub DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Sub

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Sub

If other things have to be done because of special circumstances in the
code, I call it from the event code.

 

[One confused underwriter]

Nothing appears to happen when I press the button. If I press the button for
next record (a small arrow at the bottom of the screen) then it goes to a
blank record. But pressing Add Record doesn't do anything. So I've gone over
to a blank record, pressed Add Record, entered the data, pressed Save Record,
gone to the next record and closed the form. And it didn't save.

The code: Me.Dirty = False - Where do I enter this in? How does it force the
record to update?

 

[One confused underwriter]

I can save changes made to previous records and the changes are stored in the
main table, but no new records can be added.

Now this brings me to my next question. The two other tables that a linked
to the main table have keys that are numbers and not autonumbers, does that
make a difference? Shouldn't the main table need the autonumber and then it
should make the update to the two records that are linked to it?