Records not in order as input

[CEV]

I have a form that users enter info into. In this particular case, the user
was entering info in what appeared to be record 201 according to the record
selector at the bottom of the page. When they went to look at this record
later it was not there. The user had to use the record selector at the
bottom to find it and it was actualy record number 165. Why does this
happen?

Thanks,

CEV

 

[ruralguy via AccessMonster.com]

Access does not store records in any particular order. Tables are basically
just a container of records and the order of the records would need to be
determined by using a query rather than the table directly.

 

[Ron2006]

Keep this in mind about the numbers at the bottom.

They have nothing to do with the real record number.

If you had a table with a single record for each letter of the
alphabet, you would have 26 records. If it was sorted Ascending, and
you sat on the record for the letter Z the number at the bottom would
say record 26 of 26. If the query was then sorted Descending and you
sat on the record for the letter Z the number at the bottom would say
record 1 of 26.

The same actual record can be any record number depending on the
sequence and other selection criteria for the query that is selecting
the record.

Ron

 

[Klatuu]

Guess why I have my own navigation buttons and never show the Access
navigation.

 

[CEV]

Can you tell me more about that and maybe where I can read about doing this?

Thanks,

CEV

 

[Klatuu]

It isn't that hard. Here is some code. This first bit is the Click event for
the button to go to the first record:

Private Sub cmdFirstRec_Click()
On Error GoTo cmdFirstRec_Click_Error

On Error GoTo Err_cmdFirstRec_Click

If Me.NewRecord Then
Me.Dirty = False
Else
DoCmd.GoToRecord , , acFirst
End If

Exit_cmdFirstRec_Click:
Exit Sub

Err_cmdFirstRec_Click:
MsgBox Err.Description
Resume Exit_cmdFirstRec_Click

cmdFirstRec_Click_Exit:

On Error Resume Next
Exit Sub

cmdFirstRec_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cmdFirstRec_Click of VBA Document
Form_frmAttributetable"
GoTo cmdFirstRec_Click_Exit

End Sub
--------------------------------

The rest are the same except which record you go to. Here is the line for
the Next record:

DoCmd.GoToRecord , , acNext

Then, in the current event of the form, you enable or disable the buttons
depending on where you are in the recordset:

Private Sub Form_Current()
Call SetNavButtons(Me)
End Sub

Then the code for SetNavButtons:

Sub SetNavButtons(ByRef frmSomeForm As Form)

On Error GoTo SetNavButtons_Error

With frmSomeForm
If .CurrentRecord = 1 Then
.cmdNextRec.Enabled = True
.cmdLastRec.Enabled = True
.cmdNextRec.SetFocus
.cmdFirstRec.Enabled = False
.cmdPreviousRec.Enabled = False
ElseIf .CurrentRecord = .Recordset.RecordCount Then
.cmdFirstRec.Enabled = True
.cmdPreviousRec.Enabled = True
.cmdPreviousRec.SetFocus
.cmdNextRec.Enabled = False
.cmdLastRec.Enabled = False
Else
.cmdFirstRec.Enabled = True
.cmdPreviousRec.Enabled = True
.cmdNextRec.Enabled = True
.cmdLastRec.Enabled = True
End If
End With

SetNavButtons_Exit:

On Error Resume Next
Exit Sub

SetNavButtons_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetNavButtons of Module modFormOperations"
GoTo SetNavButtons_Exit

End Sub

 

[ruralguy via AccessMonster.com]

Maybe you can start with Stephan Lebans' Nav Buttons.
http://www.lebans.com/recnavbuttons.htm

 

[CEV]

Thanks for the info, I will definitely be checking these out.

Thanks,

CEV