Why am I getting this Error?

[Steve]

I am getting an error code, "You can't carry out this action at the
present time" with only an OK button. It occurs after I update a record
set and try to close my form or navigate to some other record. I have
to close Access to get out. What is happening, and what am I doing wrong?

TIA

Steve

 

[Allen Browne]

After you update a recordset: in code? or manually in a form?

What code is running? Post the event procedure.

It is important to solve this, Steve, because crashing out of Access is a
fairly good way of corrupting the database.

 

[Steve]

I'm trying to figure out exactly what is causing the crash. I thought
it was just when I updated/added a transaction, but yesterday it
happened without that. I can open my form and close it without
problems, but it often freezes on me if I added the transaction. The
following is the code that happens:

Private Sub btnSave_Click()
Dim patkey As String, oldMASTER As String, MsgChoice As Variant
'do something to save the record, then clear things for another...
patkey = [MASTER] & "." & VisitChoice
oldMASTER = [MASTER]
MsgChoice = MsgBox("Patkey: " & patkey & vbCrLf & "ProcCode: " &
NewProcCode & vbCrLf & "Amt: " & NewAmt & vbCrLf & _
"Loc: " & NewLOC & vbCrLf & "Prov: " &
NewProvider, vbOKCancel + vbCritical, "Confirm that this is correct!")
If MsgChoice Then
Call ApplyPmt([MASTER], VisitChoice, NewProcCode, Trmt, NewAmt,
NewProvider, NewLOC) '\in Module1
Call UpdateDemog([MASTER], NewAmt)
'/
'MsgBox "back from update"
End If

HideInput

If MsgChoice Then
Me.Requery 'I _think_ this is what re-sets the VisitChoice query
Me.Refresh
End If
MASTER.SetFocus
' DoCmd.FindRecord oldMASTER

'for some reason it won't let me exit the form except when it does...
End Sub

Sub ApplyPmt(inpORNUM, Vst, PmtType, PmtTrCode, PmtAmt, Prov, Place)
'started 1/2/05
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As Field

Set db = CurrentDb()

Set rs = db.OpenRecordset("NewTransactionList")
rs.AddNew
rs!ORNum = inpORNUM '3.10.05 changed for NewTransactionList
Table
rs!Visit = Vst
rs!KEY = UCase(inpORNUM) & "." & Vst
rs!SYSDATE = Format(Date, "mm/dd/yyyy")
rs!TRDATE = Format(Date, "mm/dd/yyyy")
rs!PROCCODE = PmtType
rs!QTY = 1
rs!AMT = Format(PmtAmt, "###0.00")
rs!DRNO = Prov 'ProvID
rs!LOCNO = Place 'POS
rs!TRCode = PmtTrCode 'for payment,charge,adjustment, etc

rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub



Sub UpdateDemog(inpORNUM, Pmt)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As Field

Set db = CurrentDb()

Dim OldBal As Single, NewBal As Single
Dim FoundIt As Boolean
FoundIt = False
Set rs = db.OpenRecordset("NewDemog") 'SELECT DEMOG.* FROM Demog WHERE
DEMOG.MASTER = 'inpORNUM'") '2.27.05
rs.MoveFirst
Do ' is there any
chance this will work??? 2.22.05
If rs!MASTER = inpORNUM Then
FoundIt = True
OldBal = rs!BALANCE
NewBal = OldBal + Pmt 'if Pmt, should be a
neg number
'if
Adj, also neg
'if
charge, should be positive
rs.Edit
rs!BALANCE = Format(NewBal, "##,##0.00")
'MsgBox "Bal: " & rs!BALANCE & vbCrLf & "NB " & NewBal
rs.Update
End If
rs.MoveNext '("Master = " & [inpORNUM])
If FoundIt And rs!MASTER <> inpORNUM Then
'MsgBox "moving out of loop" & vbCrLf & rs!MASTER

Exit Do 'I think this breaks the loop
when rs!master changes....
End If
Loop Until rs.EOF '!MASTER <> [inpORNUM]
Set rs = Nothing
Set db = Nothing

End Sub
Private Sub HideInput()
Me.VisitChoice.Value = Null

[Child26].Visible = False
setfields False
MASTER.SetFocus
BtnSave.Enabled = False
BtnSave.Visible = False
BtnCancel.Visible = False
End Sub

Sub setfields(onoff As Boolean)

NewVisit.Visible = onoff
NewVisit.Value = Null
NewLOC.Visible = onoff
NewLOC.Value = Null
NewProvider.Visible = onoff
NewProvider.Value = Null
NewProcCode.Visible = onoff
NewProcCode.Value = Null
NewAmt.Visible = onoff
NewAmt.Value = Null
Trmt.Visible = onoff
Trmt.Value = Null

TrtmtCode_Label.Visible = onoff
Label50.Visible = onoff
Label52.Visible = onoff
Label54.Visible = onoff
Label59.Visible = onoff
Label61.Visible = onoff
End Sub


These are <I think> the relevant routines. The only other think I can
think of that happens is the requery of the "VisitChoice" query, which
is a query for a combobox.

Every time I quit/crash, a copy of my database is saved as db2.mdb,
db3.mdb, etc. I'm not sure that is relevant.

Thanks for your help. I, too, am worried.

Steve

 

[Allen Browne]

Sorry, Steve. I don't see the point of any of this.

It looks like something that could be done in a bound form without the need
for any code. Just enter the record and Access saves it.

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

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

I'm trying to figure out exactly what is causing the crash. I thought it
was just when I updated/added a transaction, but yesterday it happened
without that. I can open my form and close it without problems, but it
often freezes on me if I added the transaction. The following is the code
that happens:

Private Sub btnSave_Click()
Dim patkey As String, oldMASTER As String, MsgChoice As Variant
'do something to save the record, then clear things for another...
patkey = [MASTER] & "." & VisitChoice
oldMASTER = [MASTER]
MsgChoice = MsgBox("Patkey: " & patkey & vbCrLf & "ProcCode: " &
NewProcCode & vbCrLf & "Amt: " & NewAmt & vbCrLf & _
"Loc: " & NewLOC & vbCrLf & "Prov: " &
NewProvider, vbOKCancel + vbCritical, "Confirm that this is correct!")
If MsgChoice Then
Call ApplyPmt([MASTER], VisitChoice, NewProcCode, Trmt, NewAmt,
NewProvider, NewLOC) '\in Module1
Call UpdateDemog([MASTER], NewAmt) '/
'MsgBox "back from update"
End If

HideInput

If MsgChoice Then
Me.Requery 'I _think_ this is what re-sets the VisitChoice query
Me.Refresh
End If
MASTER.SetFocus
' DoCmd.FindRecord oldMASTER

'for some reason it won't let me exit the form except when it does...
End Sub

Sub ApplyPmt(inpORNUM, Vst, PmtType, PmtTrCode, PmtAmt, Prov, Place)
'started 1/2/05
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As Field

Set db = CurrentDb()

Set rs = db.OpenRecordset("NewTransactionList")
rs.AddNew
rs!ORNum = inpORNUM '3.10.05 changed for NewTransactionList
Table
rs!Visit = Vst
rs!KEY = UCase(inpORNUM) & "." & Vst
rs!SYSDATE = Format(Date, "mm/dd/yyyy")
rs!TRDATE = Format(Date, "mm/dd/yyyy")
rs!PROCCODE = PmtType
rs!QTY = 1
rs!AMT = Format(PmtAmt, "###0.00")
rs!DRNO = Prov 'ProvID
rs!LOCNO = Place 'POS
rs!TRCode = PmtTrCode 'for payment,charge,adjustment, etc

rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub



Sub UpdateDemog(inpORNUM, Pmt)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As Field

Set db = CurrentDb()

Dim OldBal As Single, NewBal As Single
Dim FoundIt As Boolean
FoundIt = False
Set rs = db.OpenRecordset("NewDemog") 'SELECT DEMOG.* FROM Demog WHERE
DEMOG.MASTER = 'inpORNUM'") '2.27.05
rs.MoveFirst
Do ' is there any
chance this will work??? 2.22.05
If rs!MASTER = inpORNUM Then
FoundIt = True
OldBal = rs!BALANCE
NewBal = OldBal + Pmt 'if Pmt, should be a neg
number
'if Adj,
also neg
'if
charge, should be positive
rs.Edit
rs!BALANCE = Format(NewBal, "##,##0.00")
'MsgBox "Bal: " & rs!BALANCE & vbCrLf & "NB " & NewBal
rs.Update
End If
rs.MoveNext '("Master = " & [inpORNUM])
If FoundIt And rs!MASTER <> inpORNUM Then
'MsgBox "moving out of loop" & vbCrLf & rs!MASTER

Exit Do 'I think this breaks the loop when
rs!master changes....
End If
Loop Until rs.EOF '!MASTER <> [inpORNUM]
Set rs = Nothing
Set db = Nothing

End Sub
Private Sub HideInput()
Me.VisitChoice.Value = Null

[Child26].Visible = False
setfields False
MASTER.SetFocus
BtnSave.Enabled = False
BtnSave.Visible = False
BtnCancel.Visible = False
End Sub

Sub setfields(onoff As Boolean)

NewVisit.Visible = onoff
NewVisit.Value = Null
NewLOC.Visible = onoff
NewLOC.Value = Null
NewProvider.Visible = onoff
NewProvider.Value = Null
NewProcCode.Visible = onoff
NewProcCode.Value = Null
NewAmt.Visible = onoff
NewAmt.Value = Null
Trmt.Visible = onoff
Trmt.Value = Null

TrtmtCode_Label.Visible = onoff
Label50.Visible = onoff
Label52.Visible = onoff
Label54.Visible = onoff
Label59.Visible = onoff
Label61.Visible = onoff
End Sub


These are <I think> the relevant routines. The only other think I can
think of that happens is the requery of the "VisitChoice" query, which is
a query for a combobox.

Every time I quit/crash, a copy of my database is saved as db2.mdb,
db3.mdb, etc. I'm not sure that is relevant.

Thanks for your help. I, too, am worried.

Steve

After you update a recordset: in code? or manually in a form?

What code is running? Post the event procedure.

It is important to solve this, Steve, because crashing out of Access is a
fairly good way of corrupting the database.

 

[Steve]

I have a couple of tables that need to be updated as I enter information.

The point, however, is that <after> the update happens, my form is
frozen and I get the warning box I originally described in this thread:

"You can't carry out this action at the present time"

when trying to close the form or open any other form. <That> is my real
problem. Do you know why Access should give me this warning?

Again, I thank you for considering my problems and helping me understand
the errors of my ways! <g>

Steve

 

[Steve]

OK, I've done some more playing... ah... investigating and have come up
with this:

I have a number of forms:
Main
NameLookup
MasterDemogOnly
MasterDemog
Demog-Visit

I usually go in that order to "drill down" to the actual payment entry
status in Demog-Visit. I get the freeze when I open Demog-Visit from
MasterDemog. If I open Demog-Visit directly from Main (and look up a
patient directly within Demog-Visit), the error does not occur!

The following is my code in MasterDemog that opens Demog-Visit:

Private Sub BtnPayment_Click()
On Error GoTo Err_BtnPayment_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Demog-Visit"

stLinkCriteria = "[MASTER]=" & "'" & Me![MASTER] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_BtnPayment_Click:
Exit Sub

Err_BtnPayment_Click:
MsgBox Err.Description
Resume Exit_BtnPayment_Click

End Sub

The same code is in MasterDemogOnly and in NameLookup, and it also
initiates the problem.

The Demog-Visit form has the following form code:

Private Sub Form_Current()
VisitChoice.Requery

End Sub

Private Sub Form_Load()
[VisitChoice] = ""
'ClearFields
setfields False
BtnSave.Enabled = False
BtnSave.Visible = False

BtnCancel.Visible = False

End Sub

Does this seem to shed any light on an answer? It appears none of my
previously posted code is causing any problems, kludgy as it is....<g>

Thanks again. I really appreciate this...

Steve

 

[Steve]

Try, try again. It just froze on me after I went directly to
Visis-Demog and posted an item.

ARRRGGGHHHH

Steve

 

[Allen Browne]

The code looks okay, assuming that the field "Master" is actually a Text
type field (not a Number type field). When drilling down like that, it can
help to ensure that any edits are saved before progressing to the next form,
so you might like to add:
If Me.Dirty Then Me.Dirty = False
to the top of the procedure.

Steve, the symptoms you describe are typical of a corruption in the
database, so I'm going to suggest a sequence to try to rescue it. Follow the
steps in order.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are
resolved.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

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

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

OK, I've done some more playing... ah... investigating and have come up
with this:

I have a number of forms:
Main
NameLookup
MasterDemogOnly
MasterDemog
Demog-Visit

I usually go in that order to "drill down" to the actual payment entry
status in Demog-Visit. I get the freeze when I open Demog-Visit from
MasterDemog. If I open Demog-Visit directly from Main (and look up a
patient directly within Demog-Visit), the error does not occur!

The following is my code in MasterDemog that opens Demog-Visit:

Private Sub BtnPayment_Click()
On Error GoTo Err_BtnPayment_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Demog-Visit"

stLinkCriteria = "[MASTER]=" & "'" & Me![MASTER] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_BtnPayment_Click:
Exit Sub

Err_BtnPayment_Click:
MsgBox Err.Description
Resume Exit_BtnPayment_Click

End Sub

The same code is in MasterDemogOnly and in NameLookup, and it also
initiates the problem.

The Demog-Visit form has the following form code:

Private Sub Form_Current()
VisitChoice.Requery

End Sub

Private Sub Form_Load()
[VisitChoice] = ""
'ClearFields
setfields False
BtnSave.Enabled = False
BtnSave.Visible = False

BtnCancel.Visible = False

End Sub

Does this seem to shed any light on an answer? It appears none of my
previously posted code is causing any problems, kludgy as it is....<g>

Thanks again. I really appreciate this...

Steve

Sorry, Steve. I don't see the point of any of this.

It looks like something that could be done in a bound form without the
need for any code. Just enter the record and Access saves it.

 

[Steve]

Yes, [Master] is text. I will carry out the steps you suggest, and get
back to you. Thanks again.
Your web site has been very helpful to me in the past, so I will
continue to rely on it...
Steve

 

[Steve]

Well, I've decompiled, compacted, de-referenced, and even copied into a
fresh .mdb file, and it still freezes and says "You can't carry out this
action at the present time" after I add transactions.
I did all this for both my front-end and back-end data files.
I will try to re-write the form and code to see if this makes a
difference.
Any other ideas? Thanks for your help.

Steve

 

[Allen Browne]

"After I add transactions"???

Are you trying to create a transaction around a bound form which has its own
implicit transactions?

 

[Steve]

I'm trying to understand what you are asking me...

Perhaps I'm doing this all wrong.

I have a form of the Patient's name and demographics, with a subform
showing transactions filtered for a particular "Visit number". There
might be 5 or 6 or up to 12 transactions per visit. I select the visit
via a combobox which has as its RowSource a query from
"NewTransactionList" for visits in which the balance is not 0. [As I
look at this further, the query in the VisitChoice Combobox is based on
a query of a query of "NewTransactionList". Could that be the problem?]

I then created unbound text boxes to collect transaction
information(Pmt, Date, TransactionType, etc) as variables, and then add
them to the transaction list using the code I posted on 3/13 7:29AM (at
least that's the listing on my server - the 3rd posting of this thread).

I update the transaction list, "NewTransactionList" {Sub ApplyPmt) which
is also the underlying record set of my subform, then update the
Demographics table "NewDemog" (Sub UpdateDemog) which is the underlying
recordset of the main form.

Is this where I am causing some type of hangup? That I am updating a
table or 2 that is already being displayed on the current form, outside
of directly entering it into the form itself? or that my query for the
VisitChoice combobox changes after I update a record? If so, then I
don't understand Access at all.....

I'm not sure exactly what you mean by a "bound form which has its own
implicit transactions"....

Thanks again for your continuing assistance.

Steve

 

[Allen Browne]

Steve, let's start over.

1. Open the form in design view.

2. Open the Properties box. (View menu.)

3. Make sure the title bar of the Properties box reads "Form", so you are
looking at the properties of the form (not those of a control.

4. On the Data tab, of the Properties box, what do you see beside the
RecordSource property?

Is this property the name of a table? the name of a query? a SELECT query
statement? completely blank?

If bound then Access will write any changes to the table(s) when you enter
anything into the text boxes and controls. You do not need code to make
these changes. In fact, attempting to make changes while Access is already
in the process of making changes itself is likely to cause problems.

If the RecordSource property is blank, we say the form is unbound (not bound
to any table or query). In that case, it does make sense to make the changes
yourself, but this is NOT the normal way to develop an application in
Access, and takes much more work to achieve.

So, if possible, just bind the form to a table, and you don't have to code
the changes at all.

HTH

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

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

I'm trying to understand what you are asking me...

Perhaps I'm doing this all wrong.

I have a form of the Patient's name and demographics, with a subform
showing transactions filtered for a particular "Visit number". There
might be 5 or 6 or up to 12 transactions per visit. I select the visit
via a combobox which has as its RowSource a query from
"NewTransactionList" for visits in which the balance is not 0. [As I look
at this further, the query in the VisitChoice Combobox is based on a query
of a query of "NewTransactionList". Could that be the problem?]

I then created unbound text boxes to collect transaction information(Pmt,
Date, TransactionType, etc) as variables, and then add them to the
transaction list using the code I posted on 3/13 7:29AM (at least that's
the listing on my server - the 3rd posting of this thread).

I update the transaction list, "NewTransactionList" {Sub ApplyPmt) which
is also the underlying record set of my subform, then update the
Demographics table "NewDemog" (Sub UpdateDemog) which is the underlying
recordset of the main form.

Is this where I am causing some type of hangup? That I am updating a
table or 2 that is already being displayed on the current form, outside of
directly entering it into the form itself? or that my query for the
VisitChoice combobox changes after I update a record? If so, then I don't
understand Access at all.....

I'm not sure exactly what you mean by a "bound form which has its own
implicit transactions"....

Thanks again for your continuing assistance.

Steve

"After I add transactions"???

Are you trying to create a transaction around a bound form which has its
own implicit transactions?