Disable nav button if first or last record 3

[HenryAnthony]

Hi all,

I have two commands buttons. One goes to the next record, the other goes to the previous record. On the first record, I would like to disable the "previous" button. On the last record, I would like to disable the "next" button.

Any suggestions will be greatly appreciated.

Best regards,

Henr¥

 

[Sawatzky]

dear henry,

if you were using adodb.recordset and did not change the order or the filter of the recordset by code you could
check after moving to previous
nextbutton.enabled =true
if rec.absoluteposition = 1 then
prebutton.enabled = false
end if

after moving forward
prevbutton.enabled =true
if rec.abcoluteposition = rec.recordcount then
nextbutton.enabled =false
end if

HTH, if not give me more details about your navigating

regards astrid

 

[HenryAnthony]

You got my wheels turning (not an easy thing to do )

I think what you have suggested will work. EOF/BOF may work as well with your code.

Thanks so very much for the help. I'll post as to how things work out.

Best regards,

Henr¥

 

[Sawatzky]

Dear henry,

I am aware of eof an bof

but if eof is true you are infact behind the last record.
recordset.movelast 'go to the last record: eof is still false
recordset.movenext ' now eof is set to true as you are behind any valid data.

same with BOF
recordset.movefirst ' go to the first record: bof is still false
recordset.moveprevious ' now bof becomes true as you are before any valid data

that why I check position and not eof or bof.

to do it with eof and bof would be like dancing:

rec.moveprevious ' assuming we were on the second record
'now we have to check whether there is any record before the one where we are so go another step back

rec.moveprevious
if bof then
cmdmoveprev.enabled = false
end if
'and reposition to the rec where we wanted to go
rec.movenext

for movenext it would be analogue.

and this would be done everytime you move 2 steps back and one forward

 

[roddy17]

hi there Henry,
if you ended up getting this working, would you be willing to submit a segment of code to help me?
that would be great and i appreciate it muchly.
Or also Astrid, i am assuming that what you have entered up above is pseudo code. Could you help with that please? Is absoluteposition a reserved word for VB? I'm thinking that your code would be implemented in the 'before update' event procedure?
thanks vm
roddy

 

[HenryAnthony]

Hi Roddy,

Yep, looks like pseudo code to me, but enough to at least get me in trouble ) It may take a couple days for me to work this out given existing priorities. When (if) I do, I will be happy to post any actual code since I take so much more from this forum than I am able to give back in return. Stay tuned.

Best regards,

Henr¥

 

[roddy17]

Thanks Henry,
I've got other relationship issues to deal with in the meantime (don't we all?) s-) , so i'll keep an ear to the ground while i continue to code-away on this thing i'm constructing.
thanks
roddy

 

[Sawatzky]

dear henry,dear roddy,

i have defined an adodb.recordset

Dim rec As ADODB.Recordset
which is opened somewhere with

Set rec = New ADODB.Recordset
rec.Open "Select * from mysampltable", CurrentProject.Connection, adOpenDynamic


this i s the clickevent of the gotoprev-button I do:

Private Sub gotoprev_Click()
On Error GoTo Err_gotoprev_Click

rec.MovePrevious ' assuming we were on the second record
'now we have to check whether there is any record before the one where we are so go
' another step back

rec.MovePrevious
If rec.BOF Then
gotonext.SetFocus ' just put it anywhere as enabled cannot be set to false for the current controle
gotoprev.Enabled = False
End If
'and reposition to the rec where we wanted to go
rec.MoveNext



Exit_gotoprev_Click:
Exit Sub

Err_gotoprev_Click:
MsgBox Err.Description
Resume Exit_gotonext_Click

End Sub


so my 'pseudo-code' was not so pseudo at all , only a bit buggy (isn't that even worse? LOL )

regards astrid

 

[Sawatzky]

still buggy

must be:

resume Exit_gotoprev_click

regards astrid

 

[HenryAnthony]

Astrid, Roddy

I am getting there. Here is my complete code to date. It is specific to my situation and not intended as a tutorial example so I hope you can follow. What this does, at this point, is when my form pops up, if there is only one location, no navigation buttons appear. If there is more than 1 location, the previous button is disabled and the next button is enabled. I figure I can put something similar in the on click event of the next button disable and enable buttons as required. Not done yet but making positive progress. Not bad for a graphic designer if I do say so myself. Couldn't have gotten anywhere without the help from the fine folks on this forum.

Here is the code and thanks so much. I'll post more as it comes.

Private Sub Form_Current()

Dim dbs As Database
Dim strSQL As String
Dim qdf As QueryDef
Dim rst As Recordset
Dim qdfTemp As QueryDef

Set dbs = CurrentDb
strSQL = "SELECT TblDocID.ExprNameCon, Count(TblAddress.ADDRESS1) AS CountOfADDRESS1 FROM TblDocID INNER JOIN TblAddress ON TblDocID.ExprNameSort = TblAddress.ExprNameSort GROUP BY TblDocID.ExprNameCon, TblDocID.ExprNameSort HAVING (((TblDocID.ExprNameSort)=[zzz]));"
Set qdf = dbs.CreateQueryDef("", strSQL)

qdf.Parameters!zzz = [Forms]![frmmain]![List7] 'fills the parameter

Set rst = qdf.OpenRecordset

If [Forms]![form1]![FrmPopUpTotalAddress]![countofAddress1].Value = 1 Then
Me![CmdNext].Enabled = False
Me![CmdNext].Visible = False
Me![CmdPrev].Enabled = False
Me![CmdPrev].Visible = False
Me![Label31].Visible = False
ElseIf [Forms]![form1]![FrmPopUpTotalAddress]![countofAddress1].Value > 1 Then
Me![CmdNext].Enabled = True
Me![CmdNext].Visible = True
Me![CmdPrev].Enabled = False
Me![CmdPrev].Visible = True
Me![Label31].Visible = True
End If

rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing

End Sub

 

[Sawatzky]

Dear henry,

now I see you are using DAO.recordset not ADO, what I asumed when I posted my first answer.

first of all:
you should dim your variables fully qualified like this:

Dim dbs As dao.Database
Dim strSQL As String
Dim qdf As dao.QueryDef
Dim rst As dao.Recordset
Dim qdfTemp As dao.QueryDef

It depends on the order of the references in a project if recordset is interpreted as dao or ado, and they are not compatible.

with dao you can use the property .nextrecordset (boolean) to test if your on the last position in your recordset set.
and absoluteposition to test for the first position.

if rst.absoulteposition = 1 then
Me![CmdPrev].Enabled = False
Me![CmdPrev].Visible = False
else
Me![CmdPrev].Enabled = False
Me![CmdPrev].Visible = True
end if


if rst.nextrecordset = false then ' you are on the last record
Me![CmdNext].Enabled = False
Me![CmdNext].Visible = False
else
Me![CmdNext].Enabled = true
Me![CmdNext].Visible = true
end if

hth
regards astrid

 

[HenryAnthony]

Hi Astrid,

The code I posted is flawed yet, although I am not getting the desired results, I am at least able to manipulate the buttons.

What is the difference between DAO and ADO. I can't find ADO in help.

Again, thanks for the help. I think I will eventually hammer this out.

Best regards,

Henr¥

 

[jazzz]

Here is what I use, place it in the On Current Event of the form. In your references for VBA make sure you have Microsoft DAO 3.6 selected and it is before Microsoft ADO or if you use both ADO and DAO then be specific on the recordset Dim recClone as DAO etc.

Dim recClone As Recordset

Set recClone = Me.RecordsetClone()

If Me.NewRecord Then
Me("cmdPrevious&quot.Enabled = False
Me("cmdNext&quot.Enabled = False
End If

'* Based on our location in the recordset turn on or off
'* the appropriate buttons.

If recClone.RecordCount = 0 Then
Me("cmdNext&quot.Enabled = False
Me("cmdPrevious&quot.Enabled = False

Else

'*Sync the clone to the current db.
recClone.Bookmark = Me.Bookmark

'*check are we on this first record
recClone.MovePrevious
Me("cmdPrevious&quot.Enabled = Not (recClone.BOF)
recClone.MoveNext

'* check are we on the last record
recClone.MoveNext
Me("cmdNext&quot.Enabled = Not (recClone.EOF)
recClone.MovePrevious

End If

'* Now close the recordset
recClone.Close

 

[HenryAnthony]

Hey Myron,

Thanks I'll give this a try.

Best regards,

Henr¥