Hi, there:

I just start to learn VBA, and met a problem on navigation buttons. I can implement intelligent navigation buttons with frmCompany in IceCream.mdb according to the example code. But when I try to do exatly same thing on my own form, they are not successful. I tried the code with two forms, and get the problems described as follows:

1. Command buttons can work, but if the record is the first record, the Previous button is not disabled as expected. Click on it, there is no error message. The same case with Last record or New record, Next button is not disabled. Click on it, no error message as well.

2. Command buttons can work, but not any buttons disabled as expected. When the record is the first record, the Previous button is not disabled, and click on it, there is error message "You can't go to the specified record....". Same problems with last record or new record.

Is there anybody has a clue what caused it?

Thank you for your kind help!

NeoCat

What is the code? I am not familiar with this database. Is there a module involved that you didn't code? It should be something like "If rs.BOF Then Me.PreviousButton.Disabled = True Else Me.PreviousButton.Disabled = False End if"


mmcdonal

Thank you for reply. It seems there is type mismatch on this
Set recClone = Me.RecordsetClone()

Here I'd like to attach the code. Would you please check for me? Thank you very much!

NeCat


Option Compare Database

Private Sub Form_Current()

Dim recClone As Recordset

'Make a clone of the recordset underlyng the form so
'we can move around that without affecting the form's
'recordset

Set recClone = Me.RecordsetClone()

'if we are in a new record, disable the <Next> button
'and enable the rest of the buttons

If Me.NewRecord Then
  cmdFirst.Enabled = True
  cmdPrevious.Enabled = True
  cmdLast.Enabled = True
  cmdNew.Enabled = True
  cmdNext.Enabled = False
  Exit Sub
End If

'if we reach here, we know we are not in a new record
'so we can enable the <New> button if the form allows
'new records to be added

cmdNew.Enabled = Me.AllowAdditions

'but we need to check if there are no records. if so
'we disable all buttons except for the <New> button

If recClone.RecordCount = 0 Then
  cmdFirst.Enabled = False
  cmdPrevious.Enabled = False
  cmdLast.Enabled = False
  cmdNext.Enabled = False
Else

'if there are records, we know that the <First> and
'<Last> button will always be enabled, irrespective
'of where we are in the recordset

  cmdFirst.Enabled = True
  cmdLast.Enabled = True

  'synchronize the current pointer in the two recordsets

  recClone.Bookmark = Me.Bookmark

  'next, we must see if we are on the first record
  'if so, we should disable the <Previous> button

  recClone.MovePrevious
  cmdPrevious.Enabled = Not (recClone.BOF)
  recClone.MoveNext

  'and then check whether we are on the last record
  'if so, we should disable the <Next> button

  recClone.MoveNext
  cmdNext.Enabled = Not (recClone.EOF)
  recClone.MovePrevious
End If

'finally close the cloned recordset

recClone.Close

End Sub

NeoCat,

Try this code:

  Set rs = Me.RecordsetClone()
  With rs
     myreccount = .RecordCount
  End With
  Set rs = Nothing
  Me.cmdFirst.Enabled = True
  Me.cmdLast.Enabled = True
  If myreccount = 0 Then
     Me.cmdNew.Enabled = True
     Me.cmdPrevious.Enabled = False
     Me.cmdNext.Enabled = False
  Else
    If Me.CurrentRecord = 1 Then
       Me.cmdNew.Enabled = True
       Me.cmdPrevious.Enabled = False
       Me.cmdNext.Enabled = True
    Else
       If Me.CurrentRecord = myreccount Then
          Me.cmdNew.Enabled = True
          Me.cmdPrevious.Enabled = True
          Me.cmdNext.Enabled = False
       Else
          If Me.NewRecord Then
             Me.cmdNew.Enabled = False
             Me.cmdPrevious.Enabled = True
             Me.cmdNext.Enabled = False
          Else
             Me.cmdNew.Enabled = True
             Me.cmdPrevious.Enabled = True
             Me.cmdNext.Enabled = True
          End If
      End If
    End If
  End If


Hope this helps,

Kevin

dartcoach

Hi, Kevin:

Your code works very well. Thanks a lot!

I noticed you did not declare rs, I did the same thing in my previous code, that is comments out "Dim recClone As Recordset", and also works. It seems the declaration brought the trouble. Do you think it right?

Thanks again for your time.

NeoCat

NeoCat,
Actually I did declare rs as recordset, but I missed that line when I copied it to the forum. It could be that "recClone" is some sort of reserved word, and that's why you were having problems. Try declaring rs as recordset and it should still work. Also, if you want to use the other code, try changing the name recClone to something else.

Glad it worked for you. Thanks for letting me know.

Kevin

dartcoach

NeCat,
Just a thought...... What version of Access are you running? Did you check References to be certain DAO is checked and you are not just running ADO as default?

Loralee

Hi, Loralee:

I am using Access 2002. I am checking the references. and find only 4 items are checked. They are
Visual Basic for Application
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft Active Data Objects 2.1 Library

Would you please tell me in detail which one should be checked and unchecked.

Do you think I should check Microsoft DAO 3.6 Object Library?

Thank you very much for your kind help!

NeoCat

Hi NeoCat,

Could someone with knowledge of ADO could tell us if there is a RecordsetClone in ADO?

I'm just a beginner, too, but want to learn. You're working through Beginning Access VBA 2002, right? (Page 241.... They are teaching DAO there.

Just for grins, I cut and pasted your posted code into a form and created the 5 buttons it calls. I got the same error on the line ("Set recClone = Me.RecordsetClone()" but it ran okay after I checked Microsoft DAO 3.6 Object Library (and unchecked Microsoft Active Data Objects 2.1 Library).

As for the funny enabling/disabling, are your buttons really named what you think they are? (is there a caption that is not consistent with it's name? ) The buttons on this example seem to behaving..... If you want, I'll e-mail it to you.......


HTH,
Loralee

Hi ya,

You can clone an ADO recordset although it does not have a recordsetclone property like Access/DAO. You use the Clone Method of the ADO model to create a clone in a new recordset object. (After you have created the ADO original recordset)

In regards to the original issue, either explicitly declare the recordset variable i.e. (make sure you have your DAO reference!)

OR

Prioritise your DAO 3.6 library ABOVE your ADO library in your references list. Then you do not have to explicity declare the recClone object. (first reference in the list is DAO and is used as default)

OR

Declare the recClone as an object or leave as variant and let access determine its object type. This will assume it to be a DAO/Access recordset. Note - you dont need to have the DAO reference for this to work and it will not assume it to be an ADO recordset either because it 'Copies' the forms recordsetclone type which is DAO.

Your problem was that you were declaring a recordset as an ADO (DAO reference was not in your list) recordset even though you didnt specify it as that. Because your DAO library was not used, any Recordset declarations are assumed to be of ADO type.

I have included a snippet to help you understand the ADO model. In general terms the ADO model is more flexible. The DAO model is more integrated into the Access/JET way of working



Hope this makes some sense to you.

J