MS Access: Access a value from a table using VBA code in Access 2003/XP/2000/97
Question: In Microsoft Access 2003/XP/2000/97, how can I access a value directly from a table using VBA code?
Answer: To access a value directly from a table using VBA code, you first need to create a new Module. To do this, open the Database window, select the Modules tab and click on the New button.
When the Microsoft Visual Basic window appears, you can create a new function to retrieve the value from the table. In our example, we're going to create a function called GetGST that retrieves the GST field from a table called GST.
We've pasted the following code into our new module:
Function GetGST() As String Dim db As Database Dim Lrs As DAO.Recordset Dim LSQL As String Dim LGST As String 'Open connection to current Access database Set db = CurrentDb() 'Create SQL statement to retrieve value from GST table LSQL = "select GST from GST" Set Lrs = db.OpenRecordset(LSQL) 'Retrieve value if data is found If Lrs.EOF = False Then LGST = Lrs("GST") Else LGST = "Not found" End If Lrs.Close Set Lrs = Nothing GetGST = LGST End Function
You should now be able to reference this new function in a query, form, report, etc.
The query below depicts how you could reference the function called GetGST in a query.
If after trying this example, you receive a "not defined" error on the "Dim db as Database" declaration, you will need to follow some additional instructions.