UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Requery macro causing error on form close    
 
   
squirrellmaster
post Sep 18 2005, 10:28 AM
Post #1

UtterAccess Member
Posts: 47
From: Massachusetts, USA



Can someone help me solve a problem with navigation on a form, and specifically, an error message on closing the form caused by a requery command in a macro? When the form is closed I get error message “the command or action ‘Requery’ isn’t available now.”
I have a form "Markdowns" (source data is table "Orders") with 2 subforms, which are "Line_Items" (source is table "Line_Items") and "Shipping" (source is table "Shipping"). I think I am only having a problem with one of the subforms, but just to be sure, I’m mentioning both.
Table "Orders" has a one-to-many relationship with "Line_Items," which has a one-to-many relationship with "Shipping."
For orders of the type that are entered on the "Markdowns" form there can be only one record in "Line_Items" and only one record in "Shipping", so after the user has entered data in the last field of the record in each subform I want them to be able to return to the main form by hitting the tab key (rather than moving to the next record in either of the subform tables).
In each subform there are only 2 fields that need to be completed: a date and a quantity. Because the quantity must always be value -1 for this type of order, I made its control invisible on the subform, and on the other control, Shipment_Date, I created 2 macros. For the event On Enter I have a SetValue command, setting the value in the quantity field to -1. On the Lost Focus event I have a macro as follows:
Requery (control name is left blank)
GoToControl (control name is the next field on the main form)
This works fine when navigating in the form, as it allows the user to intuitively tab from field to field. However, when the form is closed after creating a new record or having tabbed through the subforms, I get error message “the command or action ‘Requery’ isn’t available now.” When I click OK it says the action failed for the lost focus macro on one of the subforms - even if the cursor is now in a control on my main form.
I have spent a week trying to fix this but so far no luck. Thanks in advance for any help.
Go to the top of the page
 
+
strive4peace
post Sep 19 2005, 12:07 AM
Post #2

UtterAccess Leader
Posts: 20,347



use VBA, not macros
why code is better than macros
Go to the top of the page
 
+
squirrellmaster
post Sep 19 2005, 06:24 AM
Post #3

UtterAccess Member
Posts: 47
From: Massachusetts, USA



Thanks for the reply. I should have mentioned that I am a beginner and I don't know anything about VBA. Since my application is working and this is the only issue I am having right now, I was hoping this would work with macros. How would you recommend a beginner learn enough about VBA to develop a bit of code for this? (I think that all I need is code that would, on pressing the tab key in a certain control, perform a requery and go to the next control.)
Go to the top of the page
 
+
strive4peace
post Sep 19 2005, 06:37 AM
Post #4

UtterAccess Leader
Posts: 20,347



post the macro you are using -- it is REAL easy to convert them to VBA ...
hen, error handling can be included to find out the problem. I have a thought about why this may be happening, but you need code to resolve it anyway.
Becoming a better Access Programmer
Chapters 1,2,3 of my VBA manual have been (re)posted at the end of this thread:
http://www.utteraccess.com/forums/showflat...;Number=1002042
Go to the top of the page
 
+
squirrellmaster
post Sep 20 2005, 10:41 AM
Post #5

UtterAccess Member
Posts: 47
From: Massachusetts, USA



Thanks so much for your help. Please bear with me - I don't know how to post the macro.
Go to the top of the page
 
+
strive4peace
post Sep 20 2005, 12:44 PM
Post #6

UtterAccess Leader
Posts: 20,347



Make a table (or comma list)
ction, Arguement1, Arguement2, Arguement3, Arguement4, etc
If you have Conditions, include that as well
You can just type it in -- that will be good anyway, you will have the paramters you need already typed for the VBA code
Go to the top of the page
 
+
squirrellmaster
post Sep 20 2005, 02:54 PM
Post #7

UtterAccess Member
Posts: 47
From: Massachusetts, USA



This is the macro that is causing me trouble:
On Lost Focus, Requery, GoToControl [Shipments for Markdowns]
I'm probably not wording that exactly right but hopefully you get the idea.
Go to the top of the page
 
+
strive4peace
post Sep 20 2005, 03:03 PM
Post #8

UtterAccess Leader
Posts: 20,347



Requery what? The form? A control? Why are you requerying?
Perhaps it would be best to post the db and specify what form to look at...
btw, is
[Shipments for Markdowns]
the control NAME?
Look at the property sheet, control Name is not the same as ControlSource, which is what show up in the form design view... NAMEs are what is used for references
Go to the top of the page
 
+
squirrellmaster
post Sep 21 2005, 05:21 AM
Post #9

UtterAccess Member
Posts: 47
From: Massachusetts, USA



Here is the db. The form is [Markdowns]. The macros I am having trouble with are on control [Shipment Date] on subform [Shipping for Markdowns]. (The subform is labeled "Markdown date" on the form.)
I am requerying because after entering a date and hitting Enter and moving out of the control, the field goes blank, and I had to move to the next record on the main form and then come back to the same record to see the date. I looked on this forum and it sounded lke requery was the best solution.
My objective is to allow the user to tab from the control [Cost] in subform [Lineitems for Markdowns] to control [Shipment Date] on subform [Shipping for Markdowns]. From there they should be able to tab back to the control [Comments] on the main form.
Go to the top of the page
 
+
squirrellmaster
post Sep 21 2005, 05:24 AM
Post #10

UtterAccess Member
Posts: 47
From: Massachusetts, USA



Sorry, I messed up when attaching.
Attached File(s)
Attached File  CopyofUnison.zip ( 367.31K ) Number of downloads: 2
 
Go to the top of the page
 
+
strive4peace
post Sep 21 2005, 12:11 PM
Post #11

UtterAccess Leader
Posts: 20,347



can you post the db in Access 2000 format?
ools, Database Utilities, Convert ... to prior version
Go to the top of the page
 
+
squirrellmaster
post Sep 21 2005, 02:51 PM
Post #12

UtterAccess Member
Posts: 47
From: Massachusetts, USA



Here it is.
Attached File(s)
Attached File  Unison_2000.zip ( 285.67K ) Number of downloads: 1
 
Go to the top of the page
 
+
strive4peace
post Sep 21 2005, 06:06 PM
Post #13

UtterAccess Leader
Posts: 20,347



>that is because it is going to a new record
hange the CYCLE property on the Other tab for the subform --> Current Record
on LostFocus, you are running macro --> Other_Cr_Shipping_Return_to_Form
Requery --> you have nothing specified, so the active control will be requeried, but there is nothing to requery for the date ...
Then, you have
GoToControl --> [Comments]
There is no control called [Comments] on this subform
Use the OnKeyPress event to test if TAB was pushed (9) --> if so, tab to Comments control on main form
CODE
Option Compare Database
Option Explicit
  
[color="green"]'this replaces your macro to set the value[/color]

Private Sub Shipment_Date_GotFocus()
   Me.Shipment_Qty = -1
End Sub
  
[color="green"]''this tests to see if user pressed TAB
'if so, move to Comments control on main form[/color]

Private Sub Shipment_Date_KeyPress(KeyAscii As Integer)
   If KeyAscii = 9 Then
      Me.Parent.comments.SetFocus
   End If
End Sub

You can apply this same logic to your other subform
Go to the top of the page
 
+
squirrellmaster
post Sep 21 2005, 08:11 PM
Post #14

UtterAccess Member
Posts: 47
From: Massachusetts, USA



OK that is completely amazing. It took me a while to figure out how to modify the code to the other subform, but I think I got it right because now the whole form is working perfectly. Thank you so much for all your help. I really can't thank you enough.
-John
Go to the top of the page
 
+
strive4peace
post Sep 21 2005, 09:20 PM
Post #15

UtterAccess Leader
Posts: 20,347



you're welcome, John wink.gif happy to help
Welcome to Utter Access
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 20th September 2014 - 06:58 AM