Accessing data in a table/query from VBA

Tim L · Nov 19, 2003

How can I go about accessing records in a table/query from within VBA? I would not be using modules as I don't know how they work, it would be some code attached to a control on a form.

Also, the table/query being accessed would not be the one that is the Record Source of the form on which the control is situated.

I would want to locate a particular record and then be able to access a particular field.

I'm trying to be as broad reaching in my description here because I don't have that much experience with VBA (for Access 2k, using Access XP) and need to keep things simple...

If there's a thread in here that deals with what I need I can't find it but then there's a lot of information and I'm probably using the wrong search criteria...

Anyway, thanks in advance for any useful replies.

Tim

pono1 · Nov 19, 2003

Tim,

Yes, you can do this using VBA. You can, for example, connect to a table or a query using a "recordset" (a set of records that are created in memory, derived from records in a table).

Code:

'A simple code sample that shows how to connect to a table
'and move from row to row, examining values in a field.

'Create a recordset object variable 
	Dim rst as New ADODB.Recordset

'Open the recordset, filling it with recs from 
'a table (called "TblNames") in the current database.
	rst.Open "TblNames", CurrentProject.Connection

'Move to the first row.
	rst.movefirst

'Display the value in a field called, say, MyName,
'in that first row (record).
	Msgbox rst.fields("MyName")

'Move to the next row.
	rst.moveNext

'Display the value of the same field in this row.
	Msgbox rst.fields("MyName")

'All done, close the recordset...
	rst.close

'and remove object variable pointer.
	Set rst = Nothing

Put this code behind the click event of a command button, using your own table and field names, and work with it. There's much more to know, but hopefully this will give you a place to start.

Regards,
Tim

EMP · Nov 19, 2003

How can I go about accessing records in a table/query from within VBA? ..... I would want to locate a particular record and then be able to access a particular field.

It seems the DLookup() function can also do it for you.

The following simple example will return the LastName of the employee whose EmloyeeID is equal to 12, from the Employee table or query:-

Dim LName As String
LName = DLookup("LastName", "Employee", "[EmployeeID] = 12")

or, if EmployeeID is a text field:-

Dim LName As String
LName = DLookup("LastName", "Employee", "[EmployeeID] = '12'")

Look up Access' help or do a search for the function on these forums. The most difficult part about DLookup is how to correctly set the criteria involving more than one field.

If the table is very large, DLookup may be slow. You may find it much faster using a select SQL statement to select the record and open it as a recordset using the code shown by pono1:-

Dim rst As New ADODB.Recordset
Dim SQL As String
Dim LName As String

SQL = "Select [LastName] from [Employee] where [Employee1D] = 12"

rst.Open SQL, CurrentProject.Connection

LName = rst![Lastname]

Pat Hartman · Nov 20, 2003

The simplest solution is to learn how to make queries that join tables. You can join the two tables on their common identifier and use the query as the ControlSource for your Form or Report. No coding is necessary.

Accessing data in a table/query from VBA

Tim L

Registered User.

pono1

Registered User.

EMP

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread