Solved

Recordset .MoveNext Not Working

Posted on 2014-04-21
8
775 Views
Last Modified: 2014-04-23
Hi Folks,
I have a SubForm that is unbound (also all fields are unbound).  The fields are populated initially by doing a lookup on a table based on a record in a MainForm  and using the results to update the SubForm unbound fields.  I then want click a button to use the Current Key Value on the SubForm  to lookup a record on a table and then move to the next record to display its data on the SubForm.

The code below looks up the data for the Current Key value when the .MoveNext line is commented out.  However is the .MoveNext is uncommented it doesn't lookup the next record..  No errors are generated.

How do I fix this?  My intention is to use similar code to for .MovePrevious, MoveFirst, MoveLast.
Private Sub CmdGoToNextRecord_Click()
On Error GoTo Err_CmdGoToNextRecord_Click
'DoCmd.GoToRecord , , acNext
MsgBox "Step 010-Lookup SC Membership Listing Report Data."
Dim PARM_PERIOD_ORG_CHECK_KEY As String
[PARM_PERIOD_ORG_CHECK_KEY] = [WRK_PERIOD_ORG_CHECK_KEY]
Dim RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR As New ADODB.Recordset
Dim CMD_29_SC_MEMBERSHIP_LISTING_RPT_MSTR As String
CMD_29_SC_MEMBERSHIP_LISTING_RPT_MSTR = "SELECT [PERIOD_ORG_KEY], [PERIOD_ORG_CHECK_KEY], " & _
                                        "[ORG_LONG_NAME_250], [PERIOD_KEY], [PADDED_PERIOD_KEY], " & _
                                        "[ORG_LONG_NAME], [ORG_HIERARCHY_250] " & _
                                        "FROM [29_SC_MEMBERSHIP_LISTING_RPT_MSTR] " & _
                                        "WHERE [PERIOD_ORG_CHECK_KEY] = " & Chr$(34) & PARM_PERIOD_ORG_CHECK_KEY & Chr$(34)
On Error Resume Next 'Required.
RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR.Open CMD_29_SC_MEMBERSHIP_LISTING_RPT_MSTR, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR.MoveLast
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_ORG_LONG_NAME_250] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!ORG_LONG_NAME_250
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_ORG_LONG_NAME] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!ORG_LONG_NAME
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_ORG_HIERARCHY_250] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!ORG_HIERARCHY_250
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_PERIOD_KEY] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!PADDED_PERIOD_KEY
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_PADDED_PERIOD_KEY] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!PERIOD_KEY
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_PERIOD_ORG_KEY] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!PERIOD_ORG_KEY
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_PERIOD_ORG_CHECK_KEY] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!PERIOD_ORG_CHECK_KEY
RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR.Close
Exit_CmdGoToNextRecord_Click:
    Exit Sub
Err_CmdGoToNextRecord_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_CmdGoToNextRecord_Click
End Sub

Open in new window

Thanks.
Bob Collison
0
Comment
Question by:Bob_Collison
8 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 total points
ID: 40012979
Bob,

 I only see a .MoveLast here.

 A couple of comments:

1. First, I'd check that the recordset has records and returning what you expect.  Put a stop at the top of the procedure, then execute.  When you hit the stop, use F8 to step through each line and make sure that what you expect to happens is happening.

 For example, in the debug window (Ctrl/G), you can do:

? RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR.Recordcount

or

RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR.Movenext

2. Don't use on Error resume next.  Your hiding any errors that might occur.  Times when you want to run with no error trapping are rare.

3. Shorten your table, field, and control names - Your going to get in trouble as your queries/SQL become more complex with length limitations.   Don't use spaces or special characters in any of the names.

 Rick Fisher's Find & Replace (www.rickworld.com) can help immensely with that and it's very reasonably priced.  Every Access developer should have it.

Jim.
0
 
LVL 57
ID: 40012995
Ah here's the real problem:

"WHERE [PERIOD_ORG_CHECK_KEY] = " & Chr$(34) & PARM_PERIOD_ORG_CHECK_KEY & Chr$(34)

 Your restricting your recordset to the current key; there are no other records in the recordset then.   You need to use > or < the current key to see other records.

Jim.
0
 

Author Comment

by:Bob_Collison
ID: 40013013
Hi Jim,
There is only the .MoveNext as I was trying to get this to work with one Method before doing the others.

The recordset does have four valid records in it.

I'm not sure what a 'Stop' is?

Thanks for the recommendation for Rick's Product.  I'll purchase it shortly.

Do you have any explanation why the .MoveNext doesn't work in the code?

Thanks.
Bob Collison.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 57
ID: 40013114
<<I'm not sure what a 'Stop' is?>>

 Stop, which is a statement in VBA.  So:

Private Sub CmdGoToNextRecord_Click()

On Error GoTo Err_CmdGoToNextRecord_Click
'DoCmd.GoToRecord , , acNext

MsgBox "Step 010-Lookup SC Membership Listing Report Data."

STOP

Dim PARM_PERIOD_ORG_CHECK_KEY As String
[PARM_PERIOD_ORG_CHECK_KEY] = [WRK_PERIOD_ORG_CHECK_KEY]
Dim RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR As New ADODB.Recordset
Dim CMD_29_SC_MEMBERSHIP_LISTING_RPT_MSTR As String
CMD_29_SC_MEMBERSHIP_LISTING_RPT_MSTR = "SELECT [PERIOD_ORG_KEY],

Now when you execute and the code hits the STOP, you'll have the VBA editor window open up and code execution will pause.

You can then step line by line with F8, resume execution from that point with F5, or skip to the end of a procedure and back to the calling procedure with Ctrl/Shift/F8.

You can also hove over variables, etc to get values, and use the debug window to look at things, execute statement, etc.

Jim.
0
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 40013215
Your code is difficult to read due to the long names with all the separators.  To make the names more readable, set a form object.

Dim MyForm as Form
Set MyForm = Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form

Then use MyForm![WRK_ORG_LONG_NAME_250]

1. Why must the form be unbound?  Access does all this (and more) for you when you use bound forms?
2. Access maintains only a single set of form properties.  That means that by definition, unbound forms will only show a single record.  So I hope you aren't trying to create a continuous subform.

The only way to get them to show more is to define multiple sets of columns and then populate them yourself.  Of course Access doesn't support control arrays so this will be more difficult than it needs to be.  You would need to do it by suffixing the control names.

PS - best practice calls for table/column/object names that include only A-Z, a-z, 0-9, and the underscore.  Good names never include embedded spaces or special characters.  I prefer CamelCase.  Others swear by The_Underscore.  And surely there is some way to reduce the length of the recordset names.  There are after all only three characters that are actually different.
0
 
LVL 84
ID: 40013754
Line 16 below does a MoveLast, so you'd only have one record to display. Does it not work like that?
Private Sub CmdGoToNextRecord_Click()
On Error GoTo Err_CmdGoToNextRecord_Click
'DoCmd.GoToRecord , , acNext
MsgBox "Step 010-Lookup SC Membership Listing Report Data."
Dim PARM_PERIOD_ORG_CHECK_KEY As String
[PARM_PERIOD_ORG_CHECK_KEY] = [WRK_PERIOD_ORG_CHECK_KEY]
Dim RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR As New ADODB.Recordset
Dim CMD_29_SC_MEMBERSHIP_LISTING_RPT_MSTR As String
CMD_29_SC_MEMBERSHIP_LISTING_RPT_MSTR = "SELECT [PERIOD_ORG_KEY], [PERIOD_ORG_CHECK_KEY], " & _
                                        "[ORG_LONG_NAME_250], [PERIOD_KEY], [PADDED_PERIOD_KEY], " & _
                                        "[ORG_LONG_NAME], [ORG_HIERARCHY_250] " & _
                                        "FROM [29_SC_MEMBERSHIP_LISTING_RPT_MSTR] " & _
                                        "WHERE [PERIOD_ORG_CHECK_KEY] = " & Chr$(34) & PARM_PERIOD_ORG_CHECK_KEY & Chr$(34)
On Error Resume Next 'Required.
RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR.Open CMD_29_SC_MEMBERSHIP_LISTING_RPT_MSTR, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR.MoveLast
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_ORG_LONG_NAME_250] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!ORG_LONG_NAME_250
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_ORG_LONG_NAME] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!ORG_LONG_NAME
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_ORG_HIERARCHY_250] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!ORG_HIERARCHY_250
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_PERIOD_KEY] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!PADDED_PERIOD_KEY
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_PADDED_PERIOD_KEY] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!PERIOD_KEY
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_PERIOD_ORG_KEY] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!PERIOD_ORG_KEY
Forms![F-19-020 - SC - SYS Organization Link Maintenance Form]![F-19-022 - SC - SYS Organization Link Maintenance SubForm].Form![WRK_PERIOD_ORG_CHECK_KEY] = RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR!PERIOD_ORG_CHECK_KEY
RS_29_SC_MEMBERSHIP_LISTING_RPT_MSTR.Close
Exit_CmdGoToNextRecord_Click:
    Exit Sub
Err_CmdGoToNextRecord_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_CmdGoToNextRecord_Click
End Sub

Open in new window

0
 

Author Closing Comment

by:Bob_Collison
ID: 40018339
The 'Solution' was provided by Jim so I gave him the majority or the points.  The coding standards / comments by Pat Hartman were also much appreciated but not a solution.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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