Solved

Recordset .MoveNext Not Working

Posted on 2014-04-21
8
747 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 34

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

17 Experts available now in Live!

Get 1:1 Help Now