Requery Question

[jadeverell]

Hi,

I have a form which contains some dates and some calculated fields based on these dates e.g. Days until contract expires (difference between today and contract end date) etc....I also have an update button that re-calculates the derived fields if the dates are manually changed.

The update button has the following macro behind it:

SetWarnings
Requery
OpenQuery
OpenQuery (update queries)
OpenQuery
Requery
Save
Msgbox


If i am updating record 123 for example, when i press the update button it goes back to the first record. I know this is due to the Requery statement but i cant take this out as it will not update my record.

Do you know how to prevent it going to the first record and keeping it on the record i was editing once i press the update button??

thanks

[jadeverell]

Can anyone help with this? If they need me to explain to problem clearer please let me know.

thanks

[Jim Doherty]

Can anyone help with this? If they need me to explain to problem clearer please let me know.

thanks

Given you are using macros the logic would be to identify your primary key control on your form and its current value prior to any activity of updating taking place. This would give you the pointer to return to.

Your trick would then to pass this value to an unbound screen control that you could hide on screen by using the SETVALUE macro command. You could then invoke the process and then end the sequence with a GOTOCONTROL action (your primary key field the cursor needs to sit in the field itself) and then FINDRECORD action in the macro the syntax of that in the 'Find What' arguments section would simply reference your unbound control like this =[MyFieldName] where the 'find record' action is finding the value that exists in the hidden field

You might want to include the macro action 'echo' to turn off screen updates during the process and turn then back on at the appropriate point thereby reducing to the eye the visual shifts in records that the process would inevitably otherwise show.

Hope this helps you

Regards

Jim :)

[jadeverell]

Hi Jim,

Thanks for your quick response. The logic seems to make sense by using a pointer, but i cant understand the difference between setvalue / Gotocontrol & FindRecord options.
I have aprrox 200 records, but the primary key isn't simply 1-200, one record might have a primary key of 34A-7, so when i tell the macro to either go to or find =[Primary Key] its says that no record exists?

Is there any chance you could tell me in a more simpler / idoits way, so i can grasp it better.

Thanks for all your help.

James

[Jim Doherty]

Hi Jim,

Thanks for your quick response. The logic seems to make sense by using a pointer, but i cant understand the difference between setvalue / Gotocontrol & FindRecord options.
I have aprrox 200 records, but the primary key isn't simply 1-200, one record might have a primary key of 34A-7, so when i tell the macro to either go to or find =[Primary Key] its says that no record exists?

Is there any chance you could tell me in a more simpler / idoits way, so i can grasp it better.

Thanks for all your help.

James

I'll see if I can replicate your scenario using a simple db that i will create James my end and post it as an attachment. Obviously it will be rather generic by nature (as I don't know exactly what you have there) and you will have adjustments to make but give me some time (have meetings today) but this evening will address it and hopefully you'll gain an understanding I will also try and introduce similarities by doing it in code. (which you I take it will not be familar with but at least it gives you differences to chew over and examine)

Regards

Jim :)

[jadeverell]

Thanks for all your efforts Jim.

I look forward to hearing from you.

Cheers

James

[Jim Doherty]

Thanks for all your efforts Jim.

I look forward to hearing from you.

Cheers

James

ok James,

As promised..attac hed is a simple database that demonstrates what I was talking about. In it you will see it open a form called frmYourForm which is something like I visualise you having your end. The form displays two editable dates derived from a table upon which the form is based as its control source. The form has a number of demo functions that work off the date from editable field things like day of week and so on. This form demonstrates the find by the macro action based on the contents of a hidden field. The macro called mcrMyMacro has the relevant actions needed to do what you were seeking to do and that is refind the current record.

On this same form is a button that opens a form called frmMyForm which is based on a 'query' as its control source....subtl e difference and included basically to show you that differences can be had here in terms of method.

Examine the differences between the two forms and the way that using a 'query' on which to base your form has advantages in the sense that you can embed your calculations (as it were) in the query at which point they become useable columns in an underlying dataset

The two forms essentially look the same but I have coloured them differently so you can tell which one you have open more readily.

On the form frmMyForm examine the code behind the form and see how very much less is required to do what you need to do when you couple useage of a query as the basis for a forms controlsource and using together with code to perform the updating actions instead of a macro. Macros do not have error handling as part of their makeup and are less powerful than the coding method overall but do have advantages in that to the non coder they are user friendly.

Hope this goes some way to helping you

Regards

Jim

[jadeverell]

Jim,

You are a gentleman! Thank you ever so much for your help.

I have finally got it to work, and have managed to simplify it slightly.

Cheers.

James

[Jim Doherty]

Jim,

You are a gentleman! Thank you ever so much for your help.

I have finally got it to work, and have managed to simplify it slightly.

Cheers.

James

Glad to be of help

Jim :)