Form Behavior after ReQuery (1 Viewer)

gakiss2

Registered User.
Local time
Today, 00:17
Joined
Nov 21, 2018
Messages
168
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

Registered User.
Local time
Today, 00:17
Joined
Nov 21, 2018
Messages
168
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
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:17
Joined
May 21, 2018
Messages
8,463
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:17
Joined
Sep 21, 2011
Messages
14,050
I would have thought you would need to store the mrr_num of the next record?
 

gakiss2

Registered User.
Local time
Today, 00:17
Joined
Nov 21, 2018
Messages
168
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

Registered User.
Local time
Today, 00:17
Joined
Nov 21, 2018
Messages
168
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

Registered User.
Local time
Today, 00:17
Joined
Nov 21, 2018
Messages
168
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.
 

Attachments

  • Capture.JPG
    Capture.JPG
    36.1 KB · Views: 90
  • tempsnip.png
    tempsnip.png
    28.1 KB · Views: 73

gakiss2

Registered User.
Local time
Today, 00:17
Joined
Nov 21, 2018
Messages
168
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

Registered User.
Local time
Today, 00:17
Joined
Nov 21, 2018
Messages
168
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

Registered User.
Local time
Today, 00:17
Joined
Nov 21, 2018
Messages
168
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
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:17
Joined
Sep 21, 2011
Messages
14,050
Yes, if the recordset count is 1 then you should not move or just ignore the error if you do.?
 

gakiss2

Registered User.
Local time
Today, 00:17
Joined
Nov 21, 2018
Messages
168
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
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:17
Joined
Sep 21, 2011
Messages
14,050
With situations like this I tend to walk through the code step by step with F8 and inspect the relevant variables as I do.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:17
Joined
Sep 12, 2006
Messages
15,614
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.
 

gakiss2

Registered User.
Local time
Today, 00:17
Joined
Nov 21, 2018
Messages
168
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.

For clarification, I am not deleting any records, they just don't show up after a requery because the query the form is built has a filter that disallows 'Ignore' or 'Closed' values in the Decision field. I'll come back to this later to work on the out of the ordinary situations like the Zero, 1 record and being on the last record. In a practical sense, if you started at the top and worked down (as would be the natural way based on the way the form presents the information) then you should be done when you get to the last record so it really shouldn't matter. Its just something I'll clean up for completeness when I circle back to it. You have given me some good pointers for that. who knows, maybe I'll be back on this board pulling out my hair again when I get to that point.

Thank You
 

Users who are viewing this thread

Top Bottom