[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ACCESS 2000 - Execute.db question

Posted on 2007-09-29
32
Medium Priority
?
278 Views
Last Modified: 2011-10-03
Now that I have learned, from a genius, how to know when I am in a new record, I have to back up to the previous record.

Ina continous form line item set the user has completed some entries.  Then the user hits the Navigation button control and moves to the next record.

At that point the Form_Current event notices that the user left something undone on the previous record - and now the problem is for the system to move back to the previous record.

I was hoping that something like   CurrentDb.Execute ".MovePrevious"    would work.  But it won't!  

Perhaps you have a better solution.
0
Comment
Question by:ljcor
  • 10
  • 9
  • 8
  • +2
32 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 19985884
Try this:

docmd.RunCommand acCmdRecordsGoToPrevious
0
 
LVL 11

Assisted Solution

by:Angelp1ay
Angelp1ay earned 200 total points
ID: 19985900
DoCmd.GoToRecord acDataForm, "form_name", acPrevious

http://msdn2.microsoft.com/en-us/library/bb237964.aspx
0
 

Author Comment

by:ljcor
ID: 19985938
Bad news - for me.

Neither of these pass the runtime test - though they both look great.

docmd.RunCommand acCmdRecordsGoToPrevious
Gets error msg:  This command or action [name] isn't available now  (like the store is closed or whatever)

DoCmd.GoToRecord acDataForm, "frmPurchaseOrder", acPrevious
Gets error msg:  You can't go to the specified record.

In both cases the Navigation button advanced the record from record 1 to record 2.

In the project window the form is named:  Form_frmPurchaseOrder
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 61

Expert Comment

by:mbizup
ID: 19985942
> At that point the Form_Current event notices that the user left something undone on the previous record

Why are you doing this validation check in the current event (which occurs after the record change) and not the before update event (which occurs right before the record change, and can be cancelled)?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19985943
Also, are you running this code as a new record is being created, or as you are navigating through existing records?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 600 total points
ID: 19985955
FWIW, I just ran a test in a sample db using a continuous form.

docmd.RunCommand acCmdRecordsGoToPrevious seems to work fine...

Can you post the rest of the code in your current event?
0
 

Author Comment

by:ljcor
ID: 19985970
It's running, as you can see, as the new record becomes current & before any other processing.
They are not happy with the validation that tells them it is out of whack.  They don't trust their operators to notice the message.  Wow!

Moreover, I have just noticed that the Me.NewRecord is now always zero - and I thought I had checked it earlier and it worked fine.  Press the navigation button, go to Rec 1 to Rec 2 as seen on the Navigation control, Form_Current appears (debugger), and Me.NewRecord is zero.  

The event code:
Private Sub Form_Current()
    If Me.NewRecord Then
        If BalanceDue Then
            'DoCmd.RunCommand acCmdRecordsGoToPrevious

            'DoCmd.GoToRecord acDataForm, "frmPurchaseOrder", acPrevious
            Me.Requery
        End If
    End If
    Me!cmbVendID.SetFocus    
End Sub
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19986006
How is BalanceDue defined?

Also, are you pre-populating any of the fields in the record?  This could prevent Me.Newrecord from working as expected.
0
 

Author Comment

by:ljcor
ID: 19986061
   InvAmt = Forms!frmPurchaseOrder!txtInvoiceAmount.Value
    Summed = Forms!frmPurchaseOrder![AP_Job SubForm]!txtSum.Value
   
    BalanceDue = InvAmt - Summed               (All dimensioned.  BalanceDue is Public in Module1)

There are not any fields that are populated before  Form_Current but there are a few that are set to zero when the first subform cbo has focus.  Also, in the mainform, the CompanyCode is set to 12.

jc




0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 600 total points
ID: 19986065
Jack .. are you showing mbiz ALL the code on the Current event?

This

"If BalanceDue Then"

sort of implies Balance Due is a Yes/No field - is that the case?

Also ... Me.NewRecord  means ... a brand new, previously unpopulated record - just so we are clear on the definition of NewRecord..

So .. IF ... that's the only code you have in the Current event ... then *why* would BalDue be True in a new record - unless that's the default for that field ... which would make much sense to me.

So, I''m not quite following the logic so far in the Current event ???

mx
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19986071
Echoing mx's comments, I'm confused too.

Are you sure you don't need something like this?

    If Me.NewRecord Then
        If BalanceDue >0  Then
...
> This command or action [name] isn't available now  
Also, you *WILL* get this error message if there is no previous record (ie:  the first record is in focus at the time the code runs).  Is the error consistent, or does it only happen some of the time?
0
 
LVL 75
ID: 19986077
typo correction
"which would make much sense to me." >> "which would NOT make much sense to me."


And actually it seems to me it would be more like:

Private Sub Form_Current()
    If Me.NewRecord = False Then   ' Test Bal Due for an existing record for whatever purpose
        If BalanceDue Then
            ' something
        Else
0
 
LVL 75
ID: 19986082
oops .. incomplete post - accidental submit:


Private Sub Form_Current()
    If Me.NewRecord = False Then   ' Test Bal Due for an existing record for whatever purpose
        If BalanceDue Then
            ' something
        Else
            ' Something else
        End If
    Else
        ' whatever hey what's  up here case - New Record
   End If
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19986085
try this:

Private Sub Form_Current()
On Error Goto EH
    If Me.NewRecord Then
        If BalanceDue Then
            DoCmd.RunCommand acCmdRecordsGoToPrevious

'            Me.Requery    *** try omitting this
        End If
    End If
    Me!cmbVendID.SetFocus    
exit sub
EH:
  If Err.Num = 2046 then
     resume next
 else
      Msgbox "Error! " & err.description
end if
End Sub


MX,
   I'd suggested  If BalanceDue >0 , because the function BalanceDue looks like a numeric calculation
0
 

Author Comment

by:ljcor
ID: 19986087
mbizup

I have to give up on this  and many other debugging items for the time being.  I am just too worn down to continue until later or tomorrow.  Probably later this evening.

The code you mention is the code I have:
    If Me.NewRecord Then
        If BalanceDue Then
            'DoCmd.RunCommand acCmdRecordsGoToPrevious

The error has been happening since after the first time I tested it (I do BELIEVE I tested it.)
And I have always used the Nav button after having data in record 1.

So I'm with all the brilliant guys - confused, too.

But I have to check out for the time being.  Sorry about that because I know you always give your very best and I like very much to have the benefit of that.
0
 

Author Comment

by:ljcor
ID: 19986092
MX

I meant all those nice words from the heart for you, too.

Both of you are teriffic.

I'll get back to this later on.

Jack
0
 
LVL 75
ID: 19986099
"So I'm with all the brilliant guys "

And Girls ...

Jack ... is BalanceDue a Function call or ... a Yes/No / Check box ???

Anyway ... lets all take a break ... I'm gonna watch the Japanese Gran Prix on the Speed channel - live at 21:00 Pst tonight ...

Mbiz ... Soon I'm emailing you and LSM about something I found out about Macro (in)Security today at our 1x/month Sat am 'consultants' breakfast ... kind of a little mini users group meeting.  Stay tuned.

mx
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19986115
MX,
looking forward to it.

ljcor,

Not a problem.  It's 11:30 PM so I'm hitting the sack.  

<OT>
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22857945.html

While I have your ears, if you don't mind please post a request in the communtity support zone to have this other quesiton reopened, and divide the points up.  The answer I gave you largely tied together these contributions:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22857945.html#19974560  (jimhorn)
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22857945.html#19975832  (mx)

</OT>
0
 
LVL 75
ID: 19986125
mzip ... I didn't really think I added that much to the mix on that Q ... don't worry about me.

mx
0
 
LVL 75
ID: 19986129
Also mbiz .... where are the Experts in this zone - for my Q?

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Windows/XP/Q_22861154.html

So far, all I've got is answers to a question I did not ask, lol ..

mx
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19986146
mx,

Try cross-posting that question to a couple of other zones (maybe at a higher level such as Windows and OS)... Windows XP may be too specific.  If you are unable to do that with a question that already has comments on it, post a note in General Community Support, and they'll help you out.

You may also need to give it a little more time. Those other zones aren't always as fast paced as the Access zones :-)
0
 
LVL 75
ID: 19986161
thx ... will do. It's not an emergency.  thanks for looking.  Don't think I can add zones at this point, so - will need to ask CS.

mx
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 19986165
I tried to read through all that....it seems to me that you're trying to keep your user from moving to the next record if not all fields are filled. Wouldn't a simple Before_Update event work better to cancel the MoveNext rather than trying to capture everything on Current?
I hope I'm not too far off base. Just want to help out.
J
0
 

Author Comment

by:ljcor
ID: 19986402
I'm not even sure where to start - or stop!  This is like reading a book - or at least a manual.  If I ever get any hungrier I will publish all of this and make millions.

About BalanceDue:    
   InvAmt = Forms!frmPurchaseOrder!txtInvoiceAmount.Value
    Summed = Forms!frmPurchaseOrder![AP_Job SubForm]!txtSum.Value
BalanceDue = InvAmt - Summed      it's just arithmetic.
When I said:  If BalanceDue then    I was just using shorthand in the If statement - just not zero.

I don't use BeforeUpdate because:  In the line item set the user enters data into several fields including txtAmount, which sums all txtAmount entries into txtSum   { control source =Sum([amount]) }.  In OnExit of txtAmount I calculate BalanceDue
0
 

Author Comment

by:ljcor
ID: 19986434
HIT A KEY ACCIDENTALLY AND POSTED THE PREVIOUS STUFF.  SORRY.

I'm not even sure where to start - or stop!  This is like reading a book - or at least a manual.  If I ever get any hungrier I will publish all of this and make millions.

About BalanceDue:    
    InvAmt = Forms!frmPurchaseOrder!txtInvoiceAmount.Value
    Summed = Forms!frmPurchaseOrder![AP_Job SubForm]!txtSum.Value
    BalanceDue = InvAmt - Summed                      << it's just arithmetic.

When I said:  If BalanceDue then    I was just using shorthand in the If statement.  Should have written
If BalanceDue <> 0 then     but I was getting tired.  I don't do that normally.

In the line item set the user enters data into several fields including txtAmount, which sums all txtAmount entries into txtSum   { control source =Sum([amount]) }.  In OnExit of txtAmount I calculate BalanceDue as show above.  BalanceDue is a global Currency, dimensioned as Public in Module1.

I don't use BeforeUpdate because: There are two other related fields that have to balance before the user should be able to use the Navigation button to move to a new record: txtInvoiceAmount is data entry and txtSum is calculated, as shown above.  If these are not balanced to zero then on the continuous form portion of the screen a stop sign appears with a small text message.  This saves having a message box pop up from various and sundry places; After entry of txtInvoiceAmount or txtAmount, new lineitem when not balanced - and it seemed like a few other places that were getting really ungovernable.

Still the client wants one to pop up if the operator goes to a new record! just because it would be bad news if the operator continued on and left an incomplete balancing behind.  This is accounting, as was explained.  As if I didn't know.  And you, too.

Sure there is a big stop sign on the screen, but that does not stop his operators from screwing up.  And I can only say, TRUST ME - I don't want to do it this way.  The STOP sign is RED RED RED.  Are they colorblind, I ask.

So I need to stop before the next record gets anywhere and cycle back to the previous record and put dancing monkeys on the screen.  Or something like that.

What can I say that hasn't been said?  Or what can you say?  Not much I suppose.

But maybe!

Jack

BTW - I didn't know that Experts had to actually ASK questions.  Live and learn.  I shouldn't be surprised, I am 75 and still asking.  Asking a lot of each of you, mbizup, mx, and Jeff (who responded to the very first question I ever asked here.)

0
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 600 total points
ID: 19986444
Let's step this out some

the user is on record x

he enters some stuff

he hits "next record"

your form current fires and examines a calculated field for a value

if the value is 0 you want to go back to that record.

The normal flow of operations with forms and records can be found here

http://office.microsoft.com/en-gb/access/HP051867611033.aspx

your form current event needs to be examined to make sure it's capturing the value at the right time. You may still want to try a different approach in my opinion.

Maybe using the after update event even...set a variable using this

public myval  <---form level variable


me.dirty = false     <---commit the record
myval = me.bookmark  <---grab the bookmark


that way, if you want to go back to that record after your on_current event finds things to be out of whack you can use

me.bookmark = myval   <---go back to the bookmarked record.
0
 

Author Comment

by:ljcor
ID: 19986460
I used the full routine code posted above by mbizup.  It starts:

Private Sub Form_Current()
On Error Goto EH
    If Me.NewRecord Then
        If BalanceDue Then
            DoCmd.RunCommand acCmdRecordsGoToPrevious

The Me.NewRecord still did not work - and that's a shame because the rest of it worked like magic after I tweaked it to go to the next line.  Perfect!, I tell you.  Went back to the previous record to the right spot waiting for entry.  I'll add the dancing monkeys later, after Me.NewRecord works.

That brings up a question I forgot to answer:  the new record has never before been seen by human beings -  or the computer.

So I still don't know what to do about that.

jack
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 19986489
This is a focus problem. if you leave that record to go push a button your subform loses focus and there's no new record...it's been canceled out by losing focus and me.dirty not being "true" anymore. On current doesn't fire in the right order.
J

0
 

Author Comment

by:ljcor
ID: 19986534
Aaha, I think.

If I gather your suggestions correctly, and that is always chancy, I don't need the NewRecord test in Form_Current, only a test of BalanceDue.  That will tell me whether have to move back or not.

I do understand your discussion of losing focus relating to the subform.  However, the mainform still is (let's say) Record 1.  Then the user hits the Nav button on the mainform and goes to Record 2.  I do miss understanding the lost focus argument.

I don't have time right now to read the "flow" document - as I have to go to bed.  But I will get to it, perhaps tomorrow - although I have a load of work to do on this project.  If I have to I will excuse myself from doing this  new record-previous record  deal until Monday.

0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 19986925
<< I don't use BeforeUpdate because: There are two other related fields that have to balance before the user should be able to use the Navigation button to move to a new record: txtInvoiceAmount is data entry and txtSum is calculated, as shown above.  If these are not balanced to zero then on the continuous form portion of the screen a stop sign appears with a small text message. >>
Can you not just check the calculated field at the beginning of the BeforeUpdate, and if it's wrong Cancel back, possibly with a msgbox or something detailing why the update has been blocked? Then you have the option to use cancel in before update to get back to the previous record whatever your reason.
0
 

Author Comment

by:ljcor
ID: 19996859
Well, the final result is that I learned a great deal but I made up my own mind for the solution.  I did that because although there were significant answers provided to me (truly like gifts from the gods) I felt that none actually solved my problem,

In the final analysis I decided to apply Occam's Razor (to my way of thinking) and get rid of the navigation control and create my own: a few buttons and a text box.  This gives me the control I want when a user wants to go to another record - but also has skipped over something that should have been entered.  And I don't have to move back a page; just tell them there is an error right here on this page and moving on can be accomplished when their problem is resolved.

As for points, I felt obligated to give everyone double maximum points and lottery tickets as well.  Plus a free first class Air France flight to Paris, dinner at L'Ambroisie in magnificent Place des Voges, and a couple of nights of splendor at the Hotel de Crillon.  But EE won't let me do that.  Dang!!  So I have done what I hope is fair - or fair enough.

More than that - waaay more than that - I thank you all for your most generous contributions of your great knowledge and most valuable time.

Mahalo nui loa,
Jack
0
 
LVL 75
ID: 20000327
jack ... fyi  fwiw re navigation:

http://www.lebans.com/recnavbuttons.htm

mx
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question