Recreating a "legacy" function

[Ronald J. Notarius]

Hi,

For reasons to lengthy to go into here, I have to use a legacy dBase .dbf
table for some mailing purposes involving a 3rd party package. In order for
everything to work right, I have to fill a field with a string equivalent to
the record number.

Because of the nature of this 3rd party package, modifying the table field
is not possible. I have to use the structure as-is, so I can't use the
autonumber function.

I need to create a simple function, equivalent to the old dBase RECNO()
function, that will return the current record's record number. I know I can
do this using the CurrentRecord property, but being new to VBA, I'm having
some trouble setting up and configuring the function.

Any assistance in writing or implementing this function would be greatly
appreciated! Thanks!

 

[Klatuu]

The difficulty you will have with this is there is no such thing in Access as
a record number like there is in FoxPro or DBase (That is what type of
database I am assuming from the file extension .dbf).

In a form, you can get the current relative record number with
Me.CurrentRecord. In a DAO recordset, you can get it with
rst.AbsolutePosition. The problem is, these are not consistent. That is,
these values depend on the current order and number of records in the
recordset. So the same record may return different values each time your
code executes.

If this is unimportant to the situation, and it is a form you can use the
CurrentRecord property:

Assume rstDdf is the recordset of the external .dbf table you are writing to:

rstDbf!RecNumber = Me.CurrentRecord

or if you are doing this with an Access recordset (rstAccess) then it would
be:

rstDbf!RecNumber = rstAccess!AbsolutePosition

If it is important to be consistent on each execution of your code so that
the same record in Access always updates the dbf with the same record number,
then you will need either an Autonumber field in your Access table or a Long
Integer field you can manipulate to get sequential numbers. To do this, you
will need to do the following whenever you create a record in the Access
table:

lngNextRecNo = Nz(DMax("[RecNo]", "AccessTableName"),0) + 1
With rstAccess
.AddNew
![RecNo] = lngNextRecNo
.Update
End With

Then when you transfer it to the dbf:

rstDbf!RecNumber = rstAccess![RecNo]

 

[Ronald J. Notarius]

OK, I think we're on the right track here, but...

(1) I'm not using any Access tables at this point. I'm directly massaging
the XBase .dbf table (and I don't know which XBase language it's written in,
not that that's critical) without changing it's structure. This may not have
been the best way to do this, but it was the fastest.

(2) The record number is not critical. There simply has to be a value
there. So if there's no easy way to do it directly, I supposed I could set
up an Update Query to accomplish the same thing, assuming that I can use a
counter in some fashion.

Please keep in mind that this is my first crack at dealing with Access at
this level! I'm just now back in the IT field after a long layoff (due to a
layoff), and most of my past experience has been dBase programming, so I
still think in dBase. Dealing with macros and VBA is all relatively new...

Thanks!

The difficulty you will have with this is there is no such thing in Access as
a record number like there is in FoxPro or DBase (That is what type of
database I am assuming from the file extension .dbf).

In a form, you can get the current relative record number with
Me.CurrentRecord. In a DAO recordset, you can get it with
rst.AbsolutePosition. The problem is, these are not consistent. That is,
these values depend on the current order and number of records in the
recordset. So the same record may return different values each time your
code executes.

If this is unimportant to the situation, and it is a form you can use the
CurrentRecord property:

Assume rstDdf is the recordset of the external .dbf table you are writing to:

rstDbf!RecNumber = Me.CurrentRecord

or if you are doing this with an Access recordset (rstAccess) then it would
be:

rstDbf!RecNumber = rstAccess!AbsolutePosition

If it is important to be consistent on each execution of your code so that
the same record in Access always updates the dbf with the same record number,
then you will need either an Autonumber field in your Access table or a Long
Integer field you can manipulate to get sequential numbers. To do this, you
will need to do the following whenever you create a record in the Access
table:

lngNextRecNo = Nz(DMax("[RecNo]", "AccessTableName"),0) + 1
With rstAccess
.AddNew
![RecNo] = lngNextRecNo
.Update
End With

Then when you transfer it to the dbf:

rstDbf!RecNumber = rstAccess![RecNo]

Hi,

For reasons to lengthy to go into here, I have to use a legacy dBase .dbf
table for some mailing purposes involving a 3rd party package. In order for
everything to work right, I have to fill a field with a string equivalent to
the record number.

Because of the nature of this 3rd party package, modifying the table field
is not possible. I have to use the structure as-is, so I can't use the
autonumber function.

I need to create a simple function, equivalent to the old dBase RECNO()
function, that will return the current record's record number. I know I can
do this using the CurrentRecord property, but being new to VBA, I'm having
some trouble setting up and configuring the function.

Any assistance in writing or implementing this function would be greatly
appreciated! Thanks!

 

[Scott Burke]

HI Ronald, I went from Clipper -> Access. I know how you feel. I suggest
that you google the following "Access", "Module", "Free". You will get a lot
of old stuff but that is perfect. Download a bunch and go thru it one line
at a time. It will teach you the flavor of Access and VBA.

You might wont to start here:
http://www.accessmvp.com/djsteele/AccessIndex.html

have fun.
Scott Burke

 

[Klatuu]

I spent may years in Dbase, Foxbse, and Foxpro, so I understand your plight.
My advantage was that I had had several years with other versions of Basic.
That gave me a leg up. There are some basic concepts in relational database
systems, so the transition, though painful, is doable.

First thing I notice is you mention Marcos. Stay away from them. They are
evil. Well, not really, but you will be better served to spend your time
learning and using VBA. Marcos are shortcuts, but you don't have the
programmic control you do with VBA.

As to you specific problem, you really will not be able to do an incremental
count easily in a query. I would suggest some VBA code using a For Next
loop, which should be pretty familiar to use. I don't know how you are
creating records in you dbf file currently, so you may be able to fold this
code or some version of it into what you are doing now. For example
purposes, I will assume the table has been built and all you need to do is
add a record number to it. In this example, we can use the AbsolutePosition
of the recordset to put a record number in a field in the table. Please be
aware this is untested "air" code.

Dim rst As DAO.Recordset
Dim dbf as DAO.Database

Set dbf = CurrentDb
Set rst = CurrentDb.OpenRecordset("Your Table Name Here")
If rst.RecordCount = 0 Then
MsgBox "No Records to Process"
Else
rst.MoveLast 'This line and the next are a technique to ensure
the recordset
rst.MoveNext 'is fully populated before we start
Do While Not rst.EOF
With rst
.Edit
!RecNoField = .AbsolutePosition
.UPdate
.MoveNext
End With
Loop
End If
rst.Close
Set rst = Nothing
Set dbf = Nothing

OK, I think we're on the right track here, but...

(1) I'm not using any Access tables at this point. I'm directly massaging
the XBase .dbf table (and I don't know which XBase language it's written in,
not that that's critical) without changing it's structure. This may not have
been the best way to do this, but it was the fastest.

(2) The record number is not critical. There simply has to be a value
there. So if there's no easy way to do it directly, I supposed I could set
up an Update Query to accomplish the same thing, assuming that I can use a
counter in some fashion.

Please keep in mind that this is my first crack at dealing with Access at
this level! I'm just now back in the IT field after a long layoff (due to a
layoff), and most of my past experience has been dBase programming, so I
still think in dBase. Dealing with macros and VBA is all relatively new...

Thanks!

The difficulty you will have with this is there is no such thing in Access as
a record number like there is in FoxPro or DBase (That is what type of
database I am assuming from the file extension .dbf).

In a form, you can get the current relative record number with
Me.CurrentRecord. In a DAO recordset, you can get it with
rst.AbsolutePosition. The problem is, these are not consistent. That is,
these values depend on the current order and number of records in the
recordset. So the same record may return different values each time your
code executes.

If this is unimportant to the situation, and it is a form you can use the
CurrentRecord property:

Assume rstDdf is the recordset of the external .dbf table you are writing to:

rstDbf!RecNumber = Me.CurrentRecord

or if you are doing this with an Access recordset (rstAccess) then it would
be:

rstDbf!RecNumber = rstAccess!AbsolutePosition

If it is important to be consistent on each execution of your code so that
the same record in Access always updates the dbf with the same record number,
then you will need either an Autonumber field in your Access table or a Long
Integer field you can manipulate to get sequential numbers. To do this, you
will need to do the following whenever you create a record in the Access
table:

lngNextRecNo = Nz(DMax("[RecNo]", "AccessTableName"),0) + 1
With rstAccess
.AddNew
![RecNo] = lngNextRecNo
.Update
End With

Then when you transfer it to the dbf:

rstDbf!RecNumber = rstAccess![RecNo]

Hi,

For reasons to lengthy to go into here, I have to use a legacy dBase .dbf
table for some mailing purposes involving a 3rd party package. In order for
everything to work right, I have to fill a field with a string equivalent to
the record number.

Because of the nature of this 3rd party package, modifying the table field
is not possible. I have to use the structure as-is, so I can't use the
autonumber function.

I need to create a simple function, equivalent to the old dBase RECNO()
function, that will return the current record's record number. I know I can
do this using the CurrentRecord property, but being new to VBA, I'm having
some trouble setting up and configuring the function.

Any assistance in writing or implementing this function would be greatly
appreciated! Thanks!