Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-03-2019, 04:11 AM   #1
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 74
Thanks: 14
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Form Behavior after ReQuery

I want to use a Tabular for to go down a list of 'records' which the user may take action or not. There is a cbo field Decision on the form. The user makes a choice which is recorded. There are two of the five choices which means the 'record' should be removed from the form view. Those choices are 'Close' and 'Ignore'. There are other choices which are just recorded and they stay in the view. The method I use to make Close and Ignore disappear is to tie that to Query the Form is based on. In order to make them actually disappear I have coded a 'Requery' to occur based on the 'On Change' event. After some sweat and tears I even figured out how to have cursor to go back to the form the user was working on. It was RecordsetClone.FindFirst and Bookmark. I do not 100% understand what is going on since I got it from a google search but it works. Long introduction to the problem... If the user chooses 'Ignore' or 'Close' the form Requeries and the entry disappears as desired but the cursor (view) rolls to the top of the form. Not an earthshattering outcome but it could be annoying to have to scroll back to where you were each time you use those choices. An Ideal solution would have the cursor go to the next record (by mrr-num) which should also be the 'Next' record anyway since the query sorts on mrr-num. also, the mrr_num was originally a text value but I changed it to number in my attempts to solve this. I much appreciate any help. code in next post.

gakiss2 is offline   Reply With Quote
Old 05-03-2019, 04:15 AM   #2
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 74
Thanks: 14
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Form Behavior after ReQuery

Option Compare Database

Private Sub Combo34_Change()

' Try to set Decision Date and Close date when control is updated
' So far no luck. doesn't update the correct record

Dim rs As DAO.Recordset

' If Decision < 7 then ... Else... New code to get back to Record after Requery

PK = Me!mrr_num
Me.Requery
With Me.RecordsetClone
.FindFirst "[mrr_num] =" & PK
' rst.MoveNext - object required??
'Debug.Assert Not .NoMatch
.MoveNext
Me.Bookmark = .Bookmark
End With


End Sub
gakiss2 is offline   Reply With Quote
Old 05-03-2019, 04:23 AM   #3
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,599
Thanks: 26
Thanked 486 Times in 462 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Form Behavior after ReQuery

Code:
Dim TempPK as Long
TempPK = Me!mrr_num
Me.Requery
Me.recordset.findfirst "[mrr_num] =" & PK
I am guessing, because I cannot follow what you are saying.

MajP is online now   Reply With Quote
Old 05-03-2019, 04:34 AM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,669
Thanks: 398
Thanked 636 Times in 617 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Form Behavior after ReQuery

I would have thought you would need to store the mrr_num of the next record?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 05-03-2019, 04:39 AM   #5
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 74
Thanks: 14
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Form Behavior after ReQuery

I had tried that code and it did work as well as what I have now, and it shorter so that's usually good.

Sorry my explanation is probably not using the correct syntax in all case.

Let me try again.

As a tab format I see several records on screen at once. I want to go down the list and enter a 'Decision' in that field. If that Decision is 'Ignore' or 'Closed' then I want that record to disappear from view. Decision is a field in the Query that is the Forms Data Control Source. All of that works right now. The that I want to improve is that now when 'Ignore' or 'Closed' is chosen, the 'Cursor' which I think means the record that has Focus?? changes to the very first record so the view the user sees is the cursor scrolls all the way to the top of the taular form. The user then has to scroll back to where he/she was. Presumable the user would want to then work on the next record in the list.

Hope this makes more sense.
gakiss2 is offline   Reply With Quote
Old 05-03-2019, 04:42 AM   #6
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 74
Thanks: 14
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Form Behavior after ReQuery

Quote:
Originally Posted by Gasman View Post
I would have thought you would need to store the mrr_num of the next record?
Maybe so. It would be the next one in numeric order so I thought it would be easy enough to get that mathematically, Current mmr_num +1. I actually changed mrr_num from a text value to a number in case I needed to do that. I may try that later.

Thank You
gakiss2 is offline   Reply With Quote
Old 05-03-2019, 04:52 AM   #7
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 74
Thanks: 14
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Form Behavior after ReQuery

I add a view of the Decision Table in case that helps for this to make sense. And a view of the From in action. The view is already filtered (through the query) for only SQE GK.
Attached Images
File Type: jpg Capture.JPG (36.1 KB, 9 views)
File Type: png tempsnip.png (28.1 KB, 9 views)

gakiss2 is offline   Reply With Quote
Old 05-03-2019, 04:53 AM   #8
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 74
Thanks: 14
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Form Behavior after ReQuery

Quote:
Originally Posted by gakiss2 View Post
Maybe so. It would be the next one in numeric order so I thought it would be easy enough to get that mathematically, Current mmr_num +1. I actually changed mrr_num from a text value to a number in case I needed to do that. I may try that later.

Thank You
Actually I just realized that it would NOT be the next one numerically. However it should still be the Next record in the Query that is the Source for the Form.
gakiss2 is offline   Reply With Quote
Old 05-03-2019, 06:17 AM   #9
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 74
Thanks: 14
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Form Behavior after ReQuery

Gasman inspired what I think is a solution. I had to move to the next record, store the mrr_num in a Temp variable then I also had to store the Decision in a Temp variable as well. I'll test some more today to see if I run into any issues. I suspect I may find trouble if I am on the last record but that is small potatoes.New code:

Option Compare Database

Private Sub Combo34_Change()

' Try to set Decision Date and Close date when control is updated
' So far no luck. doesn't update the correct record

Dim rs As DAO.Recordset

Dim DTemp As Long
Dim PK As Long
Dim PKNxt As Long

DTemp = Me.Decision
PK = Me!mrr_num
Me.Recordset.MoveNext
PKNxt = Me!mrr_num
MsgBox "Next MRR is " & PKNxt & "Decision is" & DTemp


If DTemp < 7 Then

Me.Requery

'original
'With Me.RecordsetClone
' .FindFirst "[mrr_num] =" & PK
' .MoveNext
' Me.Bookmark = .Bookmark
'End With

Me.Recordset.FindFirst "[mrr_num] =" & PK

Else

Me.Requery

Me.Recordset.FindFirst "[mrr_num] =" & PKNxt

End If


End Sub

Private Sub Command40_Click()
'DoCmd.TransferSpreadsheet , , "NewRawimportMRRKey", "W:\deptqa\Suppliers\Quik Docs\IMPORTANT FILES DO NOT OPEN\MRRProject\MRRdownload.xlsm", True
Call DoSomething

End Sub
gakiss2 is offline   Reply With Quote
Old 05-03-2019, 07:03 AM   #10
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,669
Thanks: 398
Thanked 636 Times in 617 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Form Behavior after ReQuery

Bit of to'ing and fro'ing but look in this thread
https://access-programmers.co.uk/for...d.php?t=124337

Make sure you have more than record before moving next.
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 05-03-2019, 07:24 AM   #11
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 74
Thanks: 14
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Form Behavior after ReQuery

OK, so it seems you giving advice on dealing with what happens when I am on the last record. I could add another Temp Variable to record mrr-num for the last record then I could use an If then to do diff behavior to avoid the error from trying to move past the last record. I'll try that our when I am testing it.

Thank You
gakiss2 is offline   Reply With Quote
Old 05-03-2019, 08:45 AM   #12
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,669
Thanks: 398
Thanked 636 Times in 617 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Form Behavior after ReQuery

Yes, if the recordset count is 1 then you should not move or just ignore the error if you do.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 05-03-2019, 09:04 AM   #13
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 74
Thanks: 14
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Form Behavior after ReQuery

I tested and found the cursor just goes to the first record. OK for now but I will go back and probably have it go to the previous record if at the end of the list. and yes I suppose I may eventually need to deal with a situation where there is only one record. And probably a check to not open the form if there are zero records. Hey wait a minute! You are helping me come up with MORE work, not less. Thank You
gakiss2 is offline   Reply With Quote
Old 05-03-2019, 09:16 AM   #14
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,669
Thanks: 398
Thanked 636 Times in 617 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Form Behavior after ReQuery

With situations like this I tend to walk through the code step by step with F8 and inspect the relevant variables as I do.
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 05-03-2019, 10:23 AM   #15
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,766
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Form Behavior after ReQuery

I think this is right, without testing

It's awkward when a bookmark is no longer valid, which can happen if you delete data from the recordset. If you just have a continuous form, and delete a record from the table, you will see the data on the form shows as missing. Requerying rebuilds the form with the latest data, but clearly the bookmarked record may not now exist, and the bookmarked record may not now be in the same position. Offhand, I am not sure without checking whether you are bookmarking an ordinal position, or a specific data element - so depending on the way you manipulate the data, and what the bookmark does, you can get strange effects.

That's probably why the default behaviour for access is to start with the cursor at the first record. For fun, you could try me.refresh instead of me.requery, which wouldn't need you to reposition the cursor, but might give you a "missing" record.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Reply

Tags
bookmark , form , movenext , requery , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Odd form behavior smig Forms 4 12-01-2015 06:54 AM
Curious listbox behavior after requery platedslicer Modules & VBA 4 09-11-2012 02:04 PM
Very odd behavior in a form zkoneffko Forms 2 06-10-2008 01:50 PM
Bad form behavior muppetmad Forms 0 06-27-2007 12:10 PM
Odd form behavior... jbfraleigh Forms 0 06-07-2005 06:59 AM




All times are GMT -8. The time now is 10:45 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World