Assigning data from a table to a variable

[MichaelJohnson168]

I keep getting an error message at the indicated at the line below:

The error message says: "Object Required"

and is located at the line of code below.
-----
clientName = rstLoans.[BorrowerFirstName]
-----



Dim dbs1 As Database
Dim rstLoans As DAO.Recordset
Dim strSQL As String
Dim strLoanNumber As String


strSQL = "SELECT Loans.[BorrowerFirstName] " & _
"FROM Loans " & _
"WHERE Loans.[BorrowerFirstName]='Gordon';"


Set dbs1 = CurrentDb
Set rstLoans = dbsMachineProblem1.OpenRecordset(strSQL, dbOpenDynaset)


clientName = rstLoans.[BorrowerFirstName] <-- Error Occurs Here


Could someone let me know what is going on and how to rectify it?


Thanks in advance.

 

[Jeff Boyce]

I'll guess that you have

Option Explicit

set, which requires the definition of all variables before their use.

Your "Dim" statements cover the db, the recordset, the SQL statement and
something about a LoanNumber...

So where's the Dim/definition of "clientName"?!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Klatuu]

Syntax problem.
Should be:
clientName = rstLoans![BorrowerFirstName]

 

[Paolo]

Hi MichaelJohnson,
I think that what you are doing is a bit trivial 'cause you search for a
name and then
you assign it to a variable but if you know the name you can assign it to
the variable
clientname="Gordon"
For your code the following should work

add a dim as string for clientname and write the statement in this way:
clientName = rstLoans![BorrowerFirstName]

You can also use a dlookup so you can shrink all your code to just 1 line

dim clientname as string
dlookup("BorrowerFirstName","Loans,"BorrowerFirstName='Gordon'")

HTH Paolo

 

[John Spencer]

dbsMachineProblem1? Where did this come from? Do you really mean dbs1?

Also, What is clientName. Is this a variable, a control, a field name?

Dim dbs1 As Database
Dim rstLoans As DAO.Recordset
Dim strSQL As String
Dim strLoanNumber As String


strSQL = "SELECT Loans.[BorrowerFirstName] " & _
"FROM Loans " & _
"WHERE Loans.[BorrowerFirstName]='Gordon';"


Set dbs1 = CurrentDb

'Set rstLoans = dbsMachineProblem1.OpenRecordset(strSQL, dbOpenDynaset)
Set rstLoans = dbs1.OpenRecordset(strSQL, dbOpenDynaset)

'clientName = rstLoans.[BorrowerFirstName]
clientName = rstLoans![BorrowerFirstName]

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..