Identify the Current Record?

[bgoad]

How can I identify the current record I am on using VBA? Also how can I determine if this is the first or last record in the table?

 

[Mile-O]

You'll have to learn how to use either DAO or ADO.

 

[bgoad]

Are you saying that I would need to load the table as a Rescordset and then utilize the recordset to ID the current, first and last record? I am very familiar with ADO, unless Access uses something other than a normal DE control.

Brian

 

[Mile-O]

I'm guessing you are trying to make your own navigation buttons.

Maybe this will be of some use.

Private Sub Form_Current() 

    Dim rs As DAO.Recordset 
    Dim intNewRecord As Integer 
     
    Set rs = Me.RecordsetClone 
     
    intNewRecord = IsNull(Me.Date) 
     
    If intNewRecord Then 
        Me.cmdPrev.Visible = True 
        Me.cmdNext.Visible = False 
        Exit Sub 
    End If 
         
    If rs.RecordCount = 0 Then 
        Me.cmdPrev.Visible = False 
        Me.cmdNext.Visible = False 
    Else 
     
    rs.Bookmark = Me.Bookmark 
     
    rs.MovePrevious 
    Me.cmdPrev.Visible= Not (rs.BOF) 
    rs.MoveNext 
     
    rs.MoveNext 
    Me.cmdNext.Visible = Not (rs.EOF) 
    rs.MovePrevious 
     
    End If 
     
    rs.Close 

    Set rs = Nothing

End Sub

Me.Date is a field on my form.

 

[bgoad]

That does solve one of problems thanks for the pointer. However, I had to convert this app from Fox for DOS and the original DB was not normalized at all. In saying that, I have a field that has a relationship to another table, this field contains a int and can not be set to AutoNum because it contains data. So I will need to identify the last number used and increment by 1 when a new record is added. That is the real reason I am trying to identify the current record I am on.


Thanks again, that di get me going in the right direction.


Also I made change to have the disabled vs invisible. It looks much better!
Brian

 

[Access_Person]

!

I tried your code (the first batch that you gave ) but got the following error in the following line:

Private Sub One_Forward_Command_Click()

Dim rs As DAO.Recordset
Dim intNewRecord As Integer
Set rs = Me.Recordset.Clone

intNewRecord = IsNull(Me[color=red].Date[/color])

"Compiler error: Method or data member not found"

The private sub.. line also becomes highlighted. Help!

 

[Rich]

Pat's already given you the answer, try reading her reply

 

[bgoad]

Pat,

Thanks for the reply. I did try what youy suggested and was not able to get it to work in Access 2003. So I then moved to another machine that had Access 2000 on it and was able to get it to work. I think part of my problem is Access 2003.

SJ,

Thanks for your code, I was able to do some things that I needed by using portions of it.

Thanks to all of you, I have a good handle of the project and expect to be able to complete the project today.

Brian

 

[The_Doc_Man]

Possible references problem in the A2003 installation?

If you don't know about references yet, search this thread for more articles than you can shake a stick at for how to repair references.