Solved

Another FindRecord problem

Posted on 2004-08-25
14
589 Views
Last Modified: 2008-02-01
I have Form1 that is built from Query1, Form2 is built off of Query1 also, but is used as a quick or small 'update' form that is called from Form1, you could say that Form2 is a pop-up form called from Form1.
While Form1 is loaded, I use Form2 to update or make changes on certain items. I would like to be able to close Form2, perform a requery on and maintain my position on Form1.

So on the 'OnClose' event of Form2, I tried:

Dim Ttemp As String

Forms![Form1].SetFocus

Ttemp = Forms![Form1]!FullN

DoCmd.FindRecord Ttemp

Forms![Form1].SetFocus

DoCmd.Requery


It errors out at:

DoCmd.FindRecord Ttemp

With "Run-time error '2046' - The command or action 'FindRecord' isn't available now".
If I hover my mouse over Ttemp, the correct value is listed in the intellisense.

Any ideas or other suggestions?

Thanks
0
Comment
Question by:Windsailor
14 Comments
 
LVL 84
ID: 11895366
Try (a) saving the record on Form2 and then (b) doing a .Refresh on Form1:

'/Form2
Sub Form_Close()

If Me.Dirty Then Me.Dirty = FAlse
Forms!Form1.Refresh

End Sub
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11895396
Try writing your requery function in Form1's OnActivate event, this should fire when Form2 closes and passes the focus back to Form1
0
 

Author Comment

by:Windsailor
ID: 11896312
Thanks for the replys...  but no success yet.

Without any code when Form2 closes, Form1 will requery and the item that was changed in Form2 will by default change in Form1. Form1 justs requeries and starts from the very begining.

I think that I should give more information.

Form1 and Form2 are built off of Query1.
Query1 is sorted descending in Amount1.
Form1 and Form2 has FullN and Amount1 on it, Form1 just has additional fields for additional information.
When Form2 is open through Form1, I am making changes to Amount1. All which works fine, but...

I want to go back to the original record or value of FullN on Form1 that I was viewing at the time of making a change instead of moving back to the first record in Query1 and then scrolling back to the record I was just on. I honestly thought that I had this working at one time... but it isn't now!

I can get the requery to work, but not the action of going back to the original record that I was viewing after the requery.

Thanks

0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11896953
You need to get a way to bookmark the record before the requery and then return to that bookmark after. Let me look a moment and see if I can find the code for this.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11897234
After searching high and low I think I found something that should work. Put the following code in your Form1's On Activate event:

Private Sub Form_Activate()
   
    Dim myBookmark As Variant
    myBookmark = Bookmark
    Me.Requery
    Bookmark = myBookmark

End Sub

this saves the current bookmark, then requeries, then returns the form to the bookmark.

God bless!
0
 

Author Comment

by:Windsailor
ID: 11897273
Ok... I will try that.

I have tried...

     Me.RecordsetClone.FindFirst "FullN = '" + Ttemp + "'"
     DoCmd.Requery
     Me.Bookmark = Me.RecordsetClone.Bookmark

with no luck. But now that I look at it, the syntax just doesn't look right...
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Windsailor
ID: 11897693
Not yet on the Form Activate... still doing research. I found this which helps... and just may be exactly my case...

Requerying a form invalidates any bookmarks set on records in the form. However, clicking Refresh on the Records menu doesn't affect bookmarks.
Since Microsoft Access creates a unique bookmark for each record in a form's recordset when a form is opened, a form's bookmark will not work on another recordset, even when the two recordsets are based on the same table, query, or SQL statement. For example, suppose you open a form bound to the Customers table. If you then open the Customers table by using Visual Basic and use the ADO Seek or DAO Seek method to locate a specific record in the table, you can't set the form's Bookmark property to the current table record. To perform this kind of operation you can use the ADO Find method or DAO Find methods with the form's RecordsetClone property.

I will try this shortly or tomorrow.

I did try...

    Me.RecordsetClone.FindFirst "FullN = '" + Ttemp + "'"
    Forms![Form1].SetFocus
    Forms![Form1].Refresh
    Me.Bookmark = Me.RecordsetClone.Bookmark

still no go... this was still on the 'close' event of Form2.

To me I dont know if its right, but instictively I still want to code it on the 'close' event of Form2, save the value of FullN, do the refresh or requery and then return to that value on Form1. On the code that is immediately above the .Refresh just seems to refresh the form and not re-run the query which is what I want to do and then return to the last viewed record.
Just a thought, maybe save the current value of FullN, close Form1, reopen Form1 with criteria and presto chango... not what I want... as always... open to suggestions.  :)

0
 

Author Comment

by:Windsailor
ID: 11919664
OK... I got it to work with help from ghudson...
Here is the solution:


Public sSSN As String

   Forms![Form1].SetFocus
   sSSN = Forms![Form1]!FullN
   DoCmd.Echo False
   DoCmd.Requery
   Forms![Form1]!FullN.SetFocus
   DoCmd.FindRecord sSSN, acEntire, False, acSearchAll, False, acCurrent, True
   DoCmd.Echo True



Thanks for the replies!
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11919704
Glad to hear you got it working! Sorry I was not much help.

You can ask the moderators to close the question and refund your points.

God bless!

Sam.
0
 

Author Comment

by:Windsailor
ID: 11920458
Actually I dont think that I have made it through the woods yet. I just found an interesting point.

The code above will run OK with no errors... but if I put a 'breakpoint' in and step through the code in the VB editor, it will error out at:

DoCmd.Requery

With: Run-time error '2046'  The command or action 'Requery' isn't available now.

It is interesting that the only way to produce an error is to step through the code... new to me.

What I am worried about is that there are a few examples at Microsofts KB which lists this error with other commands, not the Requery command, but the concern is that Microsoft states this error of '2046' will fail in a run-time environment, which is basically what I want to make. Ouch.

I may have to use the ADO Find method or DAO Find methods with the form's RecordsetClone property, if you care to expand on that issue it would be great.

Thanks

0
 

Author Comment

by:Windsailor
ID: 11922514
1 solution...

Use:

Forms![Form1].Requery

instead of:

DoCmd.Requery

This will run without a run-time error.

Public sSSN As String

   Forms![Form1].SetFocus
   sSSN = Forms![Form1]!FullN
   DoCmd.Echo False
   Forms![Form1].Requery
   Forms![Form1]!FullN.SetFocus
   DoCmd.FindRecord sSSN, acEntire, False, acSearchAll, False, acCurrent, True
   DoCmd.Echo True


0
 

Author Comment

by:Windsailor
ID: 11922522
Thanks Rich
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 11978837
PAQd, 125 points refunded.

GhostMod
Community Support Moderator
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now