Code problems

MS_Access_Amature

Registered User.
Local time
Yesterday, 22:54
Joined
Nov 10, 2010
Messages
56
What am I doing wrong or is there another way to go about this?
I have control buttons instead of navigation buttons on my form to go to previous and next records. I did not use the wizard just VBA code. So what I am trying to now do is make the ButtonPreviousRecord disabled when its on last record so it doesn't show me that error message you can't go to the specified record. This is my code:

Private Sub Form_Open(Cancel As Integer)

Dim RS As Recordset

With RS
If .AbsolutePosition = .RecordCount - 1 Then
Me.ButtonPreviousRecord.Enabled = False
Else
If Not .AbsolutePosition = .RecordCount - 1 Then
Me.ButtonPreviousRecord.Enabled = True
End If
End If
End With


End Sub

THis code is giving me an error message that says Object Variable or With block variable not set.
How can I fix that?
Or am I doing this thing completly wrong??

Thanks in advance!
 
You never set RS. Also, the second If/Then is unnecessary; just leave the Enabled line in the Else clause of the first.
 
Greetings I use four buttons (first, last, next and previous). If it is on the last record then a msgbox appears (same with first). Two text boxes display current record and total record count. You should be able to mod this for your .enabled

Code:
Private Sub btn_next_Click()
On Error GoTo Err_btn_next_Click


If Not txt_record.Value = Me.Recordset.recordCount Then
    DoCmd.GoToRecord , , acNext
    txt_record.Value = txt_record.Value + 1
Else
    MsgBox "This is the last Record", vbInformation, "Last Record"
End If
    
Exit_btn_next_Click:
    Exit Sub

Err_btn_next_Click:
    MsgBox Err.Description
    Resume Exit_btn_next_Click
    
End Sub

Private Sub btn_last_Click()
On Error GoTo Err_btn_last_Click
If Not txt_record.Value = Me.Recordset.recordCount Then
    DoCmd.GoToRecord , , acLast
    txt_record.Value = Me.Recordset.recordCount
Else
    MsgBox "This is the last Record", vbInformation, "Last Record"
End If
    
    
theCalendar.Visible = False

Exit_btn_last_Click:
    Exit Sub

Err_btn_last_Click:
    MsgBox Err.Description
    Resume Exit_btn_last_Click
    
End Sub
Private Sub btn_First_Click()
On Error GoTo Err_btn_First_Click
If Not txt_record.Value = 1 Then
    DoCmd.GoToRecord , , acFirst
    txt_record.Value = 1
Else
    MsgBox "This is the First Record", vbInformation, "First Record"
End If

theCalendar.Visible = False



Exit_btn_First_Click:
    Exit Sub

Err_btn_First_Click:
    MsgBox Err.Description
    Resume Exit_btn_First_Click
    
End Sub
Private Sub btn_Previous_Click()
On Error GoTo Err_btn_Previous_Click


If Not txt_record.Value = 1 Then
    DoCmd.GoToRecord , , acPrevious
    txt_record.Value = txt_record.Value - 1
Else
    MsgBox "This is the First Record", vbInformation, "First Record"
End If

Exit_btn_Previous_Click:
    Exit Sub

Err_btn_Previous_Click:
    MsgBox Err.Description
    Resume Exit_btn_Previous_Click
    
End Sub

'you also need this on Form open
txt_count.Value = Forms!frm_MaintenanceMgt.Recordset.recordCount
 
Pbaldy : I set my RS and took the second IF out and now is giving me an error message that Operation is not supported for this type of object. I pressed Ctrl Break and It went to the Me.ButtonPreviousRecord.Enabled = False.

Any idea??
 
How did you set it? Also, this looks like the kind of code that belongs in the current event, which will fire on load and with each change of records. Not sure it will work in the open event, as it probably doesn't have the records yet.
 
Dim DB As Database
Dim RS As Recordset

Set DB = CurrentDb
Set RS = DB.OpenRecordset("Employees")

With RS
If .AbsolutePosition = .RecordCount - 1 Then
Me.ButtonPreviousRecord.Enabled = False
End If
End With

RS.Close

Set RS = Nothing
Set DB = Nothing
 
With that, there's no relation between where they are in the form and where they are in the recordset. You probably want RecordsetClone. You may be interested in this sample by ChrisO.
 

Attachments

Users who are viewing this thread

Back
Top Bottom