Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 12th, 2005, 11:15 AM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default intelligent navigation buttons

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
Reply With Quote
  #2 (permalink)  
Old September 12th, 2005, 01:07 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old September 12th, 2005, 01:32 PM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old September 12th, 2005, 02:49 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old September 12th, 2005, 04:50 PM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old September 12th, 2005, 04:56 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #7 (permalink)  
Old September 12th, 2005, 09:57 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Reply With Quote
  #8 (permalink)  
Old September 13th, 2005, 11:30 AM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old September 13th, 2005, 07:22 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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



Reply With Quote
  #10 (permalink)  
Old September 14th, 2005, 06:36 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Code:
Dim recClone as DAO.recordset
(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

Code:
    Dim con As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim rstClone As ADODB.Recordset
    Dim DAOClone As Recordset 'Note You dont need DAO references as recordset clone pty is built in to access

    'First, create the connection object for the rst, use the same one access uses
    Set con = CurrentProject.AccessConnection

    'Now create the rst and set its ptys
    Set rst = New ADODB.Recordset
    With rst
        Set .ActiveConnection = con
        .Source = Me.RecordSource 'Point to current objects record source
        .Open
    End With
    MsgBox "ADO recordset contains " & rst.RecordCount & " records."

    'To create a clone just call the ado clone method
    'and assign to an existing rst object.
    Set rstClone = rst.Clone
    MsgBox "ADO Clone contains " & rstClone.RecordCount & " records."

    'using the Access clone pty (DAO!)
    Set DAOClone = Me.RecordsetClone
    MsgBox "DAO Clone contains " & DAOClone.RecordCount & " records."


Hope this makes some sense to you.

J
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Intelligent Complaint-need help please rsearing Forum and Wrox.com Feedback 1 October 12th, 2006 08:45 AM
Wizard generated navigation buttons mgirard BOOK: Access 2003 VBA Programmer's Reference 3 January 11th, 2005 02:33 AM
Navigation buttons terry s Dreamweaver (all versions) 3 August 3rd, 2004 04:03 PM
Intelligent Navigation Buttons twsinc Access VBA 1 October 7th, 2003 09:07 AM
Animated Buttons Inside Navigation Bars? Ben Horne Dreamweaver (all versions) 0 October 5th, 2003 09:59 PM



All times are GMT -4. The time now is 11:09 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.