By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,504 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Custom Navigation Controls Odd Behavior

P: 25
On the form in question i have records from 5 related tables. In the footer of the form I have navigation controls (default navigation controls are turned off). When the form is launched, the navigation controls always appear disabled (grayed out). If I debug the code and put a break point where it enters the if conditions for the On Current event of the form and step through it, the navigation controls work fine. I do have a combobox on the form where the user can page through the records or type in characters of a customer they wish to view. Once a record is selected, the navigation controls work fine. Ideas?
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2.       If Me.CurrentRecord = 1 Then
  3.          Me.cmdPrevious.Enabled = False
  4.          Me.cmdFirst.Enabled = False
  5.       Else
  6.          Me.cmdPrevious.Enabled = True
  7.          Me.cmdFirst.Enabled = True
  8.       End If
  9.       If Me.CurrentRecord >= Me.Recordset.RecordCount Then
  10.          Me.cmdNext.Enabled = False
  11.          Me.cmdLast.Enabled = False
  12.       Else
  13.          Me.cmdNext.Enabled = True
  14.          Me.cmdLast.Enabled = True
  15.       End If
  16.  
Feb 8 '17 #1

✓ answered by jforbes

This actually makes sense. If it's on Record #1 and it thinks there are only 1 record(s), then
Expand|Select|Wrap|Line Numbers
  1. If Me.CurrentRecord >= Me.Recordset.RecordCount Then
will evaluate to True, disabling your fields.

You could try something like the following:
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim oClone As Recordset    
  3. Set oClone = Me.RecordsetClone
  4. oClone.MoveLast
  5.  
  6. If Me.CurrentRecord = 1 Then
  7.     Me.cmdPrevious.Enabled = False
  8.     Me.cmdFirst.Enabled = False
  9. Else
  10.     Me.cmdPrevious.Enabled = True
  11.     Me.cmdFirst.Enabled = True
  12. End If
  13. If Me.CurrentRecord >= oClone.RecordCount Then
  14.     Me.cmdNext.Enabled = False
  15.     Me.cmdLast.Enabled = False
  16. Else
  17.     Me.cmdNext.Enabled = True
  18.     Me.cmdLast.Enabled = True
  19. End If
This should make a copy of the Form's RecordSet and then move to it's end, somewhat forcing it to load completely, at least load completely into the Clone.

Lastly, this line will act differently depending on if you can Add records to your Form:
Expand|Select|Wrap|Line Numbers
  1. If Me.CurrentRecord >= Me.Recordset.RecordCount Then
The .CurrentRecord for the New Record will be the RecordCount + 1.
You may want to flip flop the If Statement:
Expand|Select|Wrap|Line Numbers
  1. If Me.CurrentRecord < oClone.RecordCount Then
  2. -or when new records can be added- 
  3. If Me.CurrentRecord <= oClone.RecordCount Then
  4.     Me.cmdNext.Enabled = True
  5.     Me.cmdLast.Enabled = True
  6. Else
  7.     Me.cmdNext.Enabled = False
  8.     Me.cmdLast.Enabled = False
  9. End If

Share this Question
Share on Google+
7 Replies


PhilOfWalton
Expert 100+
P: 1,430
I use similar code, but hide the buttons again in the On Currant

Expand|Select|Wrap|Line Numbers
  1.     If Me.CurrentRecord = 1 Then
  2.         CmdPrevious.Visible = False
  3.     Else
  4.         CmdPrevious.Visible = True
  5.     End If
  6.  
  7.     If Me.CurrentRecord = Me.Recordset.RecordCount Then
  8.         Cmdnext.Visible = False
  9.     Else
  10.         Cmdnext.Visible = True
  11.     End If
  12.  
Can't see why your method won't work as there is very little difference. However on opening my form the next button is visible.

Phil
Feb 8 '17 #2

P: 25
I can't explain what may be causing this. I thought it might be due to it taking too long to read records and report back but the table doesn't have very many records.
Feb 8 '17 #3

P: 25
A bit of an update: If I enable Navigation Buttons in the form properties, my custom navigation buttons work as designed. Disabke Navigation Buttons in the form properties and my custom buttons fail when launching the form. Obviously I am missing something important here.
Feb 8 '17 #4

jforbes
Expert 100+
P: 1,107
Two things I can think of:
  • Comment out the Error Handling and see if you get an error. Access can get odd about Focus when disabling controls.
  • Put this code into your OnCurrent code: Debug.Print "CurrentRecord is " & Me.CurrentRecord & " out of " & Me.Recordset.RecordCount Then you can verify a little more of what is going on in the OnCurrent. You may want to put a debug.print for each branch of the IF statements.
Feb 9 '17 #5

P: 25
I didn't get an error by commenting out the error handling. On the first entry into the event, debug.print shows 1 out of 1 records instead of showing 1 out of 10 records (currently there are 10 records in the table.) As long as the breakpoint at the entry of the event is active, the controls work as planned. Remove the breakpoint and it doesn't read the table.

In case this sheds a bit of light into what I'm doing. The tables being used on the form consist of a company table, city table, state table, company contact table and a company classification table. The company contact table is the many side of the company table. The company table is the many side of the city, state and classification tables. The record source for the form is a query with the three masters and the company table. The company contact table is in a subform by itself, has the parent and child fields set and is the only subform on the main form.
Feb 9 '17 #6

jforbes
Expert 100+
P: 1,107
This actually makes sense. If it's on Record #1 and it thinks there are only 1 record(s), then
Expand|Select|Wrap|Line Numbers
  1. If Me.CurrentRecord >= Me.Recordset.RecordCount Then
will evaluate to True, disabling your fields.

You could try something like the following:
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim oClone As Recordset    
  3. Set oClone = Me.RecordsetClone
  4. oClone.MoveLast
  5.  
  6. If Me.CurrentRecord = 1 Then
  7.     Me.cmdPrevious.Enabled = False
  8.     Me.cmdFirst.Enabled = False
  9. Else
  10.     Me.cmdPrevious.Enabled = True
  11.     Me.cmdFirst.Enabled = True
  12. End If
  13. If Me.CurrentRecord >= oClone.RecordCount Then
  14.     Me.cmdNext.Enabled = False
  15.     Me.cmdLast.Enabled = False
  16. Else
  17.     Me.cmdNext.Enabled = True
  18.     Me.cmdLast.Enabled = True
  19. End If
This should make a copy of the Form's RecordSet and then move to it's end, somewhat forcing it to load completely, at least load completely into the Clone.

Lastly, this line will act differently depending on if you can Add records to your Form:
Expand|Select|Wrap|Line Numbers
  1. If Me.CurrentRecord >= Me.Recordset.RecordCount Then
The .CurrentRecord for the New Record will be the RecordCount + 1.
You may want to flip flop the If Statement:
Expand|Select|Wrap|Line Numbers
  1. If Me.CurrentRecord < oClone.RecordCount Then
  2. -or when new records can be added- 
  3. If Me.CurrentRecord <= oClone.RecordCount Then
  4.     Me.cmdNext.Enabled = True
  5.     Me.cmdLast.Enabled = True
  6. Else
  7.     Me.cmdNext.Enabled = False
  8.     Me.cmdLast.Enabled = False
  9. End If
Feb 9 '17 #7

P: 25
Thanks Jforbes... That took care of the problem. Appreciate the solution.
Feb 9 '17 #8

Post your reply

Sign in to post your reply or Sign up for a free account.