I Need VBA Assistance To Set a Variable to a table record

[Guest]

Good afternoon,

I need to know a VBA command to set an integer variable in an Access module
to be pointed to a certain record number in a table so that the variable
takes on whatever value is in the record. Any suggestions? Thanks.

 

[Mike Painter]

Good afternoon,

I need to know a VBA command to set an integer variable in an Access
module to be pointed to a certain record number in a table so that
the variable takes on whatever value is in the record. Any
suggestions? Thanks.

Access does not have record numbers. You might store a bookmark but that is
only relative to a particular recordset.

Usually in a relational database the key is what you should use.

MyVariable = SomeRecordSet!Somefieldname.

 

[John Vinson]

Good afternoon,

I need to know a VBA command to set an integer variable in an Access module
to be pointed to a certain record number in a table so that the variable
takes on whatever value is in the record. Any suggestions? Thanks.

Well, a "record" doesn't have "a value" - it could have as many as 255
different fields, each with its own value. And records don't have
record numbers.

What you *can* do is dimension a variable of Variant type, and use it
to store a Bookmark for a recordset based on the table:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim vBkmrk As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset)
<do something to locate the desired record, such as FindFirst>
vBkmrk = rs.Bookmark

<do some other stuff>

rs.Bookmark = vBkmrk ' go back to the marked record
variable = rs!Fieldname

Or... more simply, depending on the circumstances:

Dim iVar As Integer
iVar = DLookUp("FieldName", "Tablename", "<criteria>"

John W. Vinson[MVP]