Solved

Another FindRecord problem

Posted on 2004-08-25
14
586 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

757 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

22 Experts available now in Live!

Get 1:1 Help Now