Solved

Recordset .MoveNext Not Working

Posted on 2014-04-21
8
801 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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 36

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

679 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