RunCommand acCmdRecordsGoToNext

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

Hi,

I am trying to use the following statement in VBA in order to change
information in all records of a database.

RunCommand acCmdRecordsGoToNext

On random occasions, I get the following message
"The command or action RecordsGoToNext isn't available now"

I have done some research, and have absorbed as much information as I
can find, but haven't had any success. I have run this code on
numerous occasions without issue, and have run it on numerous occasions
with an issue. All the data has been static and unchanged. I have
seen many statements of "There is a field where you data is incorrect
and cannot move to a different record", however, this holds untrue in
this instance. Below is the entire syntax in which I am using this
command.

Please note, I do have buttons that allow me to navigate through
records (using DoCmd.GoToRecord,, acNext), and even after the loop has
failed, these buttons work without a hitch. Again, I'm not sure why it
is simply an issue here. I have also attempted the DoCmd.GoToRecord ,
, acNext rather than the RunCommand method in the code below. Any help
in this matter would be greatly appreciated.

Private Sub RemoveChanges_Click()
Dim intRecordCount As Integer
Dim strPassword As String
Dim strPasswordConfirm As String

'Go to first record so that record count for
'While Loop is correct, and extra
'records are not created.
RunCommand acCmdRecordsGoToFirst

strPassword = "YEROC"

strPasswordConfirm = InputBox("Please enter the password to confirm,
otherwise press cancel to exit without changes:", "Confirm Erase all
Change Tracking")

On Error GoTo Err_RemoveChanges_Click

If strPassword = strPasswordConfirm Then

intRecordCount = 1
While intRecordCount <= RecordsetClone.RecordCount
chgCHG = False
chgStore = False
chgProjectType = False
chgGRP = False
chgGMP = False
chgRCM = False
chgCity = False
chgState = False
chgCluster = False
chgMostRecentActivityNotice = False
chgMostRecentPlanDistribution = False
chgTwelveWeeksBeforeFixtureDate = False
chgEightWeeksBeforeDryRunDate = False
chgFixtureDate = False
chgMerchandiseStartDate = False
chgStatus = False
chgDryRunDateorCompletionDate = False
chgGrandOpeningDate = False
chgProjectTypeandCtn = False
chgCompletedLayoutType = False
chgComments = False
chgInteriorSigns = False
chgExteriorSigns = False
chgLeasedOwned = False
chgProjectNumber = False

RunCommand acCmdRecordsGoToNext <--- this is the point where the
code breaks

intRecordCount = intRecordCount + 1
Wend

RunCommand acCmdRecordsGoToFirst

Else
MsgBox ("You have cancelled the previous operation, or have entered
an incorrect password")
End If
Exit_RemoveChanges_Click:
Exit Sub

Err_RemoveChanges_Click:
MsgBox Err.Description
Resume Exit_RemoveChanges_Click
End Sub

Thanks!!

Keith
 
Hi Keith,

before you attempt to move the record pointer, you should save the
current record:

'if changes have been made (dirty), then save record
if me.dirty then me.dirty = false

also, if you have combos or listboxes that use criteria from the current
record, that could be interferring... personally, I like to rebuild the
SQL for each record to avoid that problem.

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*
 
I have noticed a couple of things. If after following Crystal's suggestions
you still have a problem, here are my thoughts.

Is there anything to prevent changes to the record? For instance, is Allow
Additions set to No?

I'm not very familiar with the Where function, but I don't see what you are
trying to do. From what I can tell you are looping through the records and
setting a number of fields to False, but to what end? Eventually you should
end up at a new record with all of those fields set to False. Why not just
set their default value to false, and go to a new record if the password is
correct? Or am I missing something?
 
Hi Bruce,

A little more background as to what is being accomplished.

MS Access has what I think is a limitation by not allowing you to color
specific records (ie coloring a record with red text when it has
changed) and then export it to excel. The database I am utilizing this
code for is distributed to a massive amount of people internally and
externally on an excel spreadsheet. Each week, we need to show which
fields have changed, and this is the only method I have found without
using the conditional format feature. Without getting into all the
details of my excel macros, the ultimate purpose is to convert these
True/False statements to Red or Black. This loop allows us to remove
all True statements at the beginning of the week, so me may track the
following weeks changes without having to reset all these controls
manually.

While intRecordCount <= RecordsetClone.RecordCount <--- Tells the while
loop to continue until the current record count has been reached, by
increasing intRecordCount on each loop. Once intRecordCount <=
RecordsetClone.RecordCount my loop will stop (If I didn't have this
comparison, the code would indefinately create records)

As for Allow Additions, I'm not really sure how I would begin to use
that.. What is it's purpose? Do you have an example? I do not want to
prevent any additions to this database?

As for anything preventing the records being changed, I do have all
controls set to Locked, and only a user with sufficient privileges can
unlock records, or even utilize this Remove Change function.

Thank you for all your help. I hope my explanation clarifies what I
am trying to accomplish.
 
Hi Crystal,

Thank you for the help, however, neither Me.Dirty, or a Save Record
command worked. I did manage to figure out when the code will break,
and when it will not break after many frustrating attempts at fixing
this.

I have noticed that when my Main Form (Used to launch this form, as
well as many other forms, programs, files, etc..) is open, the code
will break after the loop has gone through it's first cycle.

When, this Main Form is closed, the loop works without a hitch.

The only code in Sub Form_Timer() on my Main Form is to increment the
Time every second so that the Clock on the Main Form stays current with
the system time.

The code to load the form is nothing more than a standard Form Load
function automatically created by access from the toolbox.

Any other suggestions?

Thank you,
 
Allow additions is to allow or disallow adding records. Some databases are
reference only (at least for certain users), so Allow Additions could be set
to No in that case.

As for restricting data entry to certain users, one possibility is to
implement user-level security (I know about it, but have only experimented
with it), and/or split the database and provide appropriate front ends to
different people. Those with privileges to edit records could have the
controls unlocked, for instance.

Are those all Yes/No fields?

You said in another posting that the error occurs on the first record when
the main form is open. You might try removing the timer event (or
commenting it out) for now, and see if the error still occurs. If this
helps, I believe there are various ways of putting a clock on a form that
may be more efficient than what you have. A groups search should lead you
to the information. Also, maybe you could try making the form on which is
the RemoveChanges command button Pop Up and Modal. Maybe if it is on top
and maintains the focus there won't be a problem.

I would think an update query would work well for resetting the fields.
Another thing that may come in handy if that doesn't is to loop through the
controls. You could set a control's Tag property to, say, 999. Then,
instead of listing all of the controls and setting them to false, something
like:

Dim ctl as Control

For each ctl in Me.Controls
If ctl.Tag = 999 Then
ctl = False
End If
Next ctl

Or, if you are resetting all Yes/No fields, the If line could be If
ctl.ControlType = acCheckBox Then

Nothing coherent here, just a collection of thoughts and possible approaches
to troubleshooting.
 
Thank you both for all your help. Setting both Modal and PopUp to True
solved the problem!!

Also, thanks for the information on how to set the ctl type to
vbCheckBox. I was toying around with this the other day, and couldn't
get it to work. I am basically self taught in programming, with a
couple of C programming classes under my belt, so some things don't
come to me easily without some research!!

Thanks again!

Keith
 
you're welcome, Keith ;) happy to help

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top