 |
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

September 12th, 2005, 11:15 AM
|
Registered User
|
|
Join Date: Sep 2005
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

September 12th, 2005, 01:07 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
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
|

September 12th, 2005, 01:32 PM
|
Registered User
|
|
Join Date: Sep 2005
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

September 12th, 2005, 02:49 PM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

September 12th, 2005, 04:50 PM
|
Registered User
|
|
Join Date: Sep 2005
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

September 12th, 2005, 04:56 PM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

September 12th, 2005, 09:57 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

September 13th, 2005, 11:30 AM
|
Registered User
|
|
Join Date: Sep 2005
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

September 13th, 2005, 07:22 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

September 14th, 2005, 06:36 AM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
 |