Error when Closing New Record - Access must close and repair

J

Joshua Powell

I have what, were it not for this incredibly frustrating error, is a
pretty much completed database. The form in question causes me much
headache whenever I close it while in a new record. I describe just
about everything I think could be important to understanding this
problem below.

The form is called frmGroups and is based off of tblGroups. tblGroups
has these fields -
lngGroupID(Primary/Autonumber), lngAgentID, strGroupName, hypWebsite,
hypEmail

frmGroups has two subforms-

subfrmGroupDetails is based off of tblGroupDetails and is made up of
lngGroupID(Primary/Long Int) and a few other fields which are probably
of no consequence. tblGroups and tblGroupDetails are inner joined on
Group ID without Referential Integrity. frmGroup and
subfrmGroupDetails link child/master on GroupID

subfrmAgents is based off of qryLIST_Agents the SQL of which is below:
SELECT tblGroups.lngGroupID, tblAgents.lngAgentID,
tblAgencies.lngAgencyID, tblIndividuals!strFirstName & " " &
tblIndividuals!strLastName AS strAgentName, tblAgencies.strAgencyName,
tblIndividuals.lngIndividualID
FROM (tblAgencies INNER JOIN (tblIndividuals INNER JOIN tblAgents ON
tblIndividuals.lngIndividualID = tblAgents.lngIndividualID) ON
tblAgencies.lngAgencyID = tblAgents.lngAgencyID) INNER JOIN tblGroups
ON tblAgents.lngAgentID = tblGroups.lngAgentID
WHERE (((tblGroups.lngGroupID)=[Forms]![frmGroups]![txtGroupID]))
ORDER BY tblIndividuals!strFirstName & " " &
tblIndividuals!strLastName;

The error I get is "Microsoft Access has encountered a problem and
needs to close. We are sorry for the inconvenience. The information
you were woking on might be lost. Microsoft Access can try to compact
and repair your open database." Then gives me the option to compact
and repair and reopen, and tells me there is an error report it wants
to send to Microsoft.

There are two ways that I've run into this message. Both times they
occur while I am in a new record, which I get to by pressing cmdNew.
If I press cmdClose while in a new record I get an error. I tried to
sidestep this by saving the record and having cmdClose send me back to
the first record before closing but that still causes a problem. In
fact, If I use cmdNext to scroll through the records until I get to
the last one, and pressing it one more time brings me to a new record,
then scroll back again - closing the record still generates this
problem, but if I never go into new record mode, everything is peachy
keen.

I encountered this type of error earlier on in designing when I was
leaving DAO Recordsets open, but I have gone through and fixed all of
those. This error occurs on three different computers, three totally
different locations, so I don't think it's a software issue. I've
exported everything into new .mdb's one piece at a time trying to
isolate what's causing the error and still no luck. All my code is
compiled without error, I've tested everything I could think and I
don't get errors anywhere else, my database is compacted (by my choice
and by being forced to by this error!). I have two other forms that I
close while in a new record using the exact same method and no
problem!

So I attach the relevant Visual Basic code and hopefully someone can
help me out! The first sub is called by Form_Load and the
"Error_Handler" is the same and present for all of my procedures, I
don't include them for space's sake.

Private Sub ResetForm()

On Error GoTo Error_Handler

Me!lstMembers.RowSource = "qryLIST_GroupMembers"
Me.txtIndividualID.Value = 0
Me!lstPhones.RowSource = "qryLIST_GroupMemberPhones"
Me!lstEmails.RowSource = "qryList_GroupMemberEmails"
Form_subfrmAgents!lstAgencyPhones.RowSource =
"qryLIST_AgencyPhones"
Form_subfrmAgents!lstAgentEmails.RowSource = "qryLIST_AgentEmails"
Form_subfrmAgents!lstAgentPhones.RowSource = "qryLIST_AgentPhones"
Form_subfrmGroupDetails!lstPerformerGenres.RowSource =
"qryLIST_GroupGenres"
Me.TabCtl0.Enabled = True
If (IsNull(Me.txtAgentID)) Then
Me!cmdDeleteBookingAgent.Enabled = False
Else
Me!cmdDeleteBookingAgent.Enabled = True
End If

Error_Exit:

Exit Sub

Error_Handler:

MsgBox "Please take note of the following information:" & vbCrLf &
vbCrLf & _
"Number: " & Err.Number & vbCrLf & _
"Source: " & Me.Name & ".ResetForm." & Err.Source & vbCrLf
& _
"Description: " & Err.Description, _
vbCritical + vbOKOnly, _
"ERROR"

Resume Error_Exit

End Sub

Private Sub cmdNew_Click()

DoCmd.GoToRecord , , acNewRec
ResetForm
Me.txtGroupName.SetFocus
Me.cmdAddBookingAgent.Enabled = False 'Without which subfrmAgents
is usless
Me.TabCtl0.Enabled = False 'This Tab is where subfrmGroupDetails
is
Me.cmdDelete.Enabled = False
Me.cmdNew.Enabled = False
Me.cmdNext.Enabled = False
Me.cmdPrevious.Enabled = False
Forms!frmStart.cmdClose.Enabled = False 'this is a switchboard
that is always visible

Private Sub cmdCancel_Click()

Dim intResult As Integer

If Me.NewRecord Then
intResult = MsgBox("do you want to save this record?",
vbYesNo, "Save?")
If intResult = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
ElseIf intResult = vbNo Then
DoCmd.GoToRecord , , acFirst
End If
End If
Forms!frmStart.cmdClose.Enabled = True
DoCmd.Close acForm, Me.Name
Forms!frmStart.cmdGroups.SetFocus

End Sub
 
A

Allen Browne

Hi Joshua.

If this is Access 2002 or 2003, try adding a text box to the subforms for
the fields named in the LinkChildFields of your subform controls. Often
these foreign key fields are not shown in the subform, but a new bug in the
latest versions causes Access to crash unless they have a control in the
subform. You can set the Visible property of these text boxes to no: they
don't have to be shown; they just have to be present, so the LinkChildFields
refer to a member of the Controls collection and not merely to an
AccessField type object.

If that does not fix the problem, make sure you have unchecked the Name
AutoCorrect boxes under Tools | Options | General. Then compact to get
completely rid of this junk. Explanation:
http://allenbrowne.com/bug-03.html

ResetForm() is called in Form_Load? So as soon as the form loads, you are
altering the values of the fields such as txtIndividualID? If this is a
bound form, I did not follow that.

While I did not go through your code in detail, it may be worth checking
that there is nothing in the Current event of any form that would cause it
to dirty the form. Anything that assigns a value to a field or bound control
dirties the record, and does not belong in the Current event. You may also
want to consider setting the Required property of your foreign key fields to
Yes, so the subform cannot make a new record while the parent is at a new
record. The code to verify the record probably belongs in Form_BeforeUpdate,
as there are many ways a record can be updated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Joshua Powell said:
I have what, were it not for this incredibly frustrating error, is a
pretty much completed database. The form in question causes me much
headache whenever I close it while in a new record. I describe just
about everything I think could be important to understanding this
problem below.

The form is called frmGroups and is based off of tblGroups. tblGroups
has these fields -
lngGroupID(Primary/Autonumber), lngAgentID, strGroupName, hypWebsite,
hypEmail

frmGroups has two subforms-

subfrmGroupDetails is based off of tblGroupDetails and is made up of
lngGroupID(Primary/Long Int) and a few other fields which are probably
of no consequence. tblGroups and tblGroupDetails are inner joined on
Group ID without Referential Integrity. frmGroup and
subfrmGroupDetails link child/master on GroupID

subfrmAgents is based off of qryLIST_Agents the SQL of which is below:
SELECT tblGroups.lngGroupID, tblAgents.lngAgentID,
tblAgencies.lngAgencyID, tblIndividuals!strFirstName & " " &
tblIndividuals!strLastName AS strAgentName, tblAgencies.strAgencyName,
tblIndividuals.lngIndividualID
FROM (tblAgencies INNER JOIN (tblIndividuals INNER JOIN tblAgents ON
tblIndividuals.lngIndividualID = tblAgents.lngIndividualID) ON
tblAgencies.lngAgencyID = tblAgents.lngAgencyID) INNER JOIN tblGroups
ON tblAgents.lngAgentID = tblGroups.lngAgentID
WHERE (((tblGroups.lngGroupID)=[Forms]![frmGroups]![txtGroupID]))
ORDER BY tblIndividuals!strFirstName & " " &
tblIndividuals!strLastName;

The error I get is "Microsoft Access has encountered a problem and
needs to close. We are sorry for the inconvenience. The information
you were woking on might be lost. Microsoft Access can try to compact
and repair your open database." Then gives me the option to compact
and repair and reopen, and tells me there is an error report it wants
to send to Microsoft.

There are two ways that I've run into this message. Both times they
occur while I am in a new record, which I get to by pressing cmdNew.
If I press cmdClose while in a new record I get an error. I tried to
sidestep this by saving the record and having cmdClose send me back to
the first record before closing but that still causes a problem. In
fact, If I use cmdNext to scroll through the records until I get to
the last one, and pressing it one more time brings me to a new record,
then scroll back again - closing the record still generates this
problem, but if I never go into new record mode, everything is peachy
keen.

I encountered this type of error earlier on in designing when I was
leaving DAO Recordsets open, but I have gone through and fixed all of
those. This error occurs on three different computers, three totally
different locations, so I don't think it's a software issue. I've
exported everything into new .mdb's one piece at a time trying to
isolate what's causing the error and still no luck. All my code is
compiled without error, I've tested everything I could think and I
don't get errors anywhere else, my database is compacted (by my choice
and by being forced to by this error!). I have two other forms that I
close while in a new record using the exact same method and no
problem!

So I attach the relevant Visual Basic code and hopefully someone can
help me out! The first sub is called by Form_Load and the
"Error_Handler" is the same and present for all of my procedures, I
don't include them for space's sake.

Private Sub ResetForm()

On Error GoTo Error_Handler

Me!lstMembers.RowSource = "qryLIST_GroupMembers"
Me.txtIndividualID.Value = 0
Me!lstPhones.RowSource = "qryLIST_GroupMemberPhones"
Me!lstEmails.RowSource = "qryList_GroupMemberEmails"
Form_subfrmAgents!lstAgencyPhones.RowSource =
"qryLIST_AgencyPhones"
Form_subfrmAgents!lstAgentEmails.RowSource = "qryLIST_AgentEmails"
Form_subfrmAgents!lstAgentPhones.RowSource = "qryLIST_AgentPhones"
Form_subfrmGroupDetails!lstPerformerGenres.RowSource =
"qryLIST_GroupGenres"
Me.TabCtl0.Enabled = True
If (IsNull(Me.txtAgentID)) Then
Me!cmdDeleteBookingAgent.Enabled = False
Else
Me!cmdDeleteBookingAgent.Enabled = True
End If

Error_Exit:

Exit Sub

Error_Handler:

MsgBox "Please take note of the following information:" & vbCrLf &
vbCrLf & _
"Number: " & Err.Number & vbCrLf & _
"Source: " & Me.Name & ".ResetForm." & Err.Source & vbCrLf
& _
"Description: " & Err.Description, _
vbCritical + vbOKOnly, _
"ERROR"

Resume Error_Exit

End Sub

Private Sub cmdNew_Click()

DoCmd.GoToRecord , , acNewRec
ResetForm
Me.txtGroupName.SetFocus
Me.cmdAddBookingAgent.Enabled = False 'Without which subfrmAgents
is usless
Me.TabCtl0.Enabled = False 'This Tab is where subfrmGroupDetails
is
Me.cmdDelete.Enabled = False
Me.cmdNew.Enabled = False
Me.cmdNext.Enabled = False
Me.cmdPrevious.Enabled = False
Forms!frmStart.cmdClose.Enabled = False 'this is a switchboard
that is always visible

Private Sub cmdCancel_Click()

Dim intResult As Integer

If Me.NewRecord Then
intResult = MsgBox("do you want to save this record?",
vbYesNo, "Save?")
If intResult = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
ElseIf intResult = vbNo Then
DoCmd.GoToRecord , , acFirst
End If
End If
Forms!frmStart.cmdClose.Enabled = True
DoCmd.Close acForm, Me.Name
Forms!frmStart.cmdGroups.SetFocus

End Sub
Click to expand...
 
J

Joshua Powell

Allen,

Well I solved the problem, I just rebuilt the form from scratch saving
all of my naming conventions, and suddenly it wants to work just fine!
I assume some odd part of the form was corrupted, do you know much
about this - how it happens and how to prevent it?

Your advice about the Name Auto Correct is well heeded, I'm definitely
glad to know about that now.

The ResetForm() which is being called by Form_Load resets the
txtIndividualID control, originally because txtIndividualID was saving
one of the fields from a list, it's a little bit superfluous and your
mentioning it gave me a heads up that I can eliminate it and use the
lst.Column(#) to find the same exact value.

About the issue with sub forms creating new records while the main
form is creating a new record... if I disable the subform while adding
a new record and then enable it again after a 'save' button or 'cancel
new record' button is clicked, would this eliminate the concern of
subforms unintentionally dirtying the record?

If you have the patience for it, I have one more question. The
tblGroups table and tblGroupDetails table have a one to one inner
join. I'd like to have the cascade update deletes delete the record
in tblGroupDetails if the corresponding record in tblGroups is
deleted, but I don't want to require that a record in tblGroupDetails
exist for every record in tblGroups. Currently if I switch ref.
integrity to on, if I try to add a new record in tblGroups without
simultaneously adding the corresponding record in tblGroupDetails it
gives me an error that a value was needed in tblGroupDetails. Any
thoughts on this? I apologize if that is not information to go off
of.

Thanks for your fantastic help,

Joshua


Allen Browne said:
Hi Joshua.

If this is Access 2002 or 2003, try adding a text box to the subforms for
the fields named in the LinkChildFields of your subform controls. Often
these foreign key fields are not shown in the subform, but a new bug in the
latest versions causes Access to crash unless they have a control in the
subform. You can set the Visible property of these text boxes to no: they
don't have to be shown; they just have to be present, so the LinkChildFields
refer to a member of the Controls collection and not merely to an
AccessField type object.

If that does not fix the problem, make sure you have unchecked the Name
AutoCorrect boxes under Tools | Options | General. Then compact to get
completely rid of this junk. Explanation:
http://allenbrowne.com/bug-03.html

ResetForm() is called in Form_Load? So as soon as the form loads, you are
altering the values of the fields such as txtIndividualID? If this is a
bound form, I did not follow that.

While I did not go through your code in detail, it may be worth checking
that there is nothing in the Current event of any form that would cause it
to dirty the form. Anything that assigns a value to a field or bound control
dirties the record, and does not belong in the Current event. You may also
want to consider setting the Required property of your foreign key fields to
Yes, so the subform cannot make a new record while the parent is at a new
record. The code to verify the record probably belongs in Form_BeforeUpdate,
as there are many ways a record can be updated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Joshua Powell said:
I have what, were it not for this incredibly frustrating error, is a
pretty much completed database. The form in question causes me much
headache whenever I close it while in a new record. I describe just
about everything I think could be important to understanding this
problem below.

The form is called frmGroups and is based off of tblGroups. tblGroups
has these fields -
lngGroupID(Primary/Autonumber), lngAgentID, strGroupName, hypWebsite,
hypEmail

frmGroups has two subforms-

subfrmGroupDetails is based off of tblGroupDetails and is made up of
lngGroupID(Primary/Long Int) and a few other fields which are probably
of no consequence. tblGroups and tblGroupDetails are inner joined on
Group ID without Referential Integrity. frmGroup and
subfrmGroupDetails link child/master on GroupID

subfrmAgents is based off of qryLIST_Agents the SQL of which is below:
SELECT tblGroups.lngGroupID, tblAgents.lngAgentID,
tblAgencies.lngAgencyID, tblIndividuals!strFirstName & " " &
tblIndividuals!strLastName AS strAgentName, tblAgencies.strAgencyName,
tblIndividuals.lngIndividualID
FROM (tblAgencies INNER JOIN (tblIndividuals INNER JOIN tblAgents ON
tblIndividuals.lngIndividualID = tblAgents.lngIndividualID) ON
tblAgencies.lngAgencyID = tblAgents.lngAgencyID) INNER JOIN tblGroups
ON tblAgents.lngAgentID = tblGroups.lngAgentID
WHERE (((tblGroups.lngGroupID)=[Forms]![frmGroups]![txtGroupID]))
ORDER BY tblIndividuals!strFirstName & " " &
tblIndividuals!strLastName;

The error I get is "Microsoft Access has encountered a problem and
needs to close. We are sorry for the inconvenience. The information
you were woking on might be lost. Microsoft Access can try to compact
and repair your open database." Then gives me the option to compact
and repair and reopen, and tells me there is an error report it wants
to send to Microsoft.

There are two ways that I've run into this message. Both times they
occur while I am in a new record, which I get to by pressing cmdNew.
If I press cmdClose while in a new record I get an error. I tried to
sidestep this by saving the record and having cmdClose send me back to
the first record before closing but that still causes a problem. In
fact, If I use cmdNext to scroll through the records until I get to
the last one, and pressing it one more time brings me to a new record,
then scroll back again - closing the record still generates this
problem, but if I never go into new record mode, everything is peachy
keen.

I encountered this type of error earlier on in designing when I was
leaving DAO Recordsets open, but I have gone through and fixed all of
those. This error occurs on three different computers, three totally
different locations, so I don't think it's a software issue. I've
exported everything into new .mdb's one piece at a time trying to
isolate what's causing the error and still no luck. All my code is
compiled without error, I've tested everything I could think and I
don't get errors anywhere else, my database is compacted (by my choice
and by being forced to by this error!). I have two other forms that I
close while in a new record using the exact same method and no
problem!

So I attach the relevant Visual Basic code and hopefully someone can
help me out! The first sub is called by Form_Load and the
"Error_Handler" is the same and present for all of my procedures, I
don't include them for space's sake.

Private Sub ResetForm()

On Error GoTo Error_Handler

Me!lstMembers.RowSource = "qryLIST_GroupMembers"
Me.txtIndividualID.Value = 0
Me!lstPhones.RowSource = "qryLIST_GroupMemberPhones"
Me!lstEmails.RowSource = "qryList_GroupMemberEmails"
Form_subfrmAgents!lstAgencyPhones.RowSource =
"qryLIST_AgencyPhones"
Form_subfrmAgents!lstAgentEmails.RowSource = "qryLIST_AgentEmails"
Form_subfrmAgents!lstAgentPhones.RowSource = "qryLIST_AgentPhones"
Form_subfrmGroupDetails!lstPerformerGenres.RowSource =
"qryLIST_GroupGenres"
Me.TabCtl0.Enabled = True
If (IsNull(Me.txtAgentID)) Then
Me!cmdDeleteBookingAgent.Enabled = False
Else
Me!cmdDeleteBookingAgent.Enabled = True
End If

Error_Exit:

Exit Sub

Error_Handler:

MsgBox "Please take note of the following information:" & vbCrLf &
vbCrLf & _
"Number: " & Err.Number & vbCrLf & _
"Source: " & Me.Name & ".ResetForm." & Err.Source & vbCrLf
& _
"Description: " & Err.Description, _
vbCritical + vbOKOnly, _
"ERROR"

Resume Error_Exit

End Sub

Private Sub cmdNew_Click()

DoCmd.GoToRecord , , acNewRec
ResetForm
Me.txtGroupName.SetFocus
Me.cmdAddBookingAgent.Enabled = False 'Without which subfrmAgents
is usless
Me.TabCtl0.Enabled = False 'This Tab is where subfrmGroupDetails
is
Me.cmdDelete.Enabled = False
Me.cmdNew.Enabled = False
Me.cmdNext.Enabled = False
Me.cmdPrevious.Enabled = False
Forms!frmStart.cmdClose.Enabled = False 'this is a switchboard
that is always visible

Private Sub cmdCancel_Click()

Dim intResult As Integer

If Me.NewRecord Then
intResult = MsgBox("do you want to save this record?",
vbYesNo, "Save?")
If intResult = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
ElseIf intResult = vbNo Then
DoCmd.GoToRecord , , acFirst
End If
End If
Forms!frmStart.cmdClose.Enabled = True
DoCmd.Close acForm, Me.Name
Forms!frmStart.cmdGroups.SetFocus

End Sub
Click to expand...
Click to expand...
 
A

Allen Browne

Hi Joshua. Answer embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Joshua Powell said:
Well I solved the problem, I just rebuilt the form from scratch saving
all of my naming conventions, and suddenly it wants to work just fine!
I assume some odd part of the form was corrupted, do you know much
about this - how it happens and how to prevent it?
Click to expand...

For general advice on preventing corruption, see:
Preventing Corruption
at
http://allenbrowne.com/ser-25.html

[snip]
About the issue with sub forms creating new records while the main
form is creating a new record... if I disable the subform while adding
a new record and then enable it again after a 'save' button or 'cancel
new record' button is clicked, would this eliminate the concern of
subforms unintentionally dirtying the record?
Click to expand...

Sounds like quite a bit of effort. Why not cancel the subform's BeforeInsert
event if the main form is at a new record?
If you have the patience for it, I have one more question. The
tblGroups table and tblGroupDetails table have a one to one inner
join. I'd like to have the cascade update deletes delete the record
in tblGroupDetails if the corresponding record in tblGroups is
deleted, but I don't want to require that a record in tblGroupDetails
exist for every record in tblGroups. Currently if I switch ref.
integrity to on, if I try to add a new record in tblGroups without
simultaneously adding the corresponding record in tblGroupDetails it
gives me an error that a value was needed in tblGroupDetails. Any
thoughts on this? I apologize if that is not information to go off
of.
Click to expand...

Sounds like you have the one-to-one relation the wrong way around.
Double-click the line joining the 2 tables in the Relationships window.
Make sure that tblGroups is the primary table, if it is the one that may
have a record when the other may not.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

filedialog of user cancels 5
How can this be "no current record"? 8
Error 3201 You can't add or change a record...when delete/deleting 2
Access frontend with SQL Server backend: my solution for ODBC Call error 1
Autofill subform when move to new record on Main form 30
Record locking - Integrating Access with Sharepoint List 1
Avoiding Error message (3314) when creating a new record 4
Making form go blank when new Record is called 1

Top