How can I use a SELECT statement in VB?

[nick]

Hi there,

I just want to write a function that takes in an employee number and returns
their name. All my tables that refer to an employee use their ID number, but
I'd like their names to display on each page they're logged into.

I originally wrote it with DoCmd.RunSQL but I read somewhere that you can
only use that on update queries. I'm still quite new to VB/Access so I don't
really know how to use Dao or even what it does =(

Public Function getEmployeeName(empNum As Integer)
Dim strLastName As String
strLastName = DoCmd.RunSQL("SELECT Employees.Last_Name" & _
" FROM Employees" & _
" WHERE (((Employees.Employee_ID)=empNum)")

getEmployeeName = strLastName
End Function

Thanks in advance

Nick

 

[fredg]

Hi there,

I just want to write a function that takes in an employee number and returns
their name. All my tables that refer to an employee use their ID number, but
I'd like their names to display on each page they're logged into.

I originally wrote it with DoCmd.RunSQL but I read somewhere that you can
only use that on update queries. I'm still quite new to VB/Access so I don't
really know how to use Dao or even what it does =(

Public Function getEmployeeName(empNum As Integer)
Dim strLastName As String
strLastName = DoCmd.RunSQL("SELECT Employees.Last_Name" & _
" FROM Employees" & _
" WHERE (((Employees.Employee_ID)=empNum)")

getEmployeeName = strLastName
End Function

Thanks in advance

Nick

Read VBA help regarding RunSQL.
You can not run a Select query using RunSQL.
Only Action Queries.

You don't need a Select query.
All you need is a DLookUp:

=DLookUp("[EmployeeLastName]","Employees","[Employee_ID]= " & EmpNum)

You can place the above expression directly as control source in an
unbound control on a form, or in a report, that includes the EmpNum
control, or in your existing function :

getEmployeeName = DLookUp(etc...)

The above assumes EmpNum is a Number datatype.

 

[nick]

Hey thanks very much!

Hi there,

I just want to write a function that takes in an employee number and returns
their name. All my tables that refer to an employee use their ID number, but
I'd like their names to display on each page they're logged into.

I originally wrote it with DoCmd.RunSQL but I read somewhere that you can
only use that on update queries. I'm still quite new to VB/Access so I don't
really know how to use Dao or even what it does =(

Public Function getEmployeeName(empNum As Integer)
Dim strLastName As String
strLastName = DoCmd.RunSQL("SELECT Employees.Last_Name" & _
" FROM Employees" & _
" WHERE (((Employees.Employee_ID)=empNum)")

getEmployeeName = strLastName
End Function

Thanks in advance

Nick

Read VBA help regarding RunSQL.
You can not run a Select query using RunSQL.
Only Action Queries.

You don't need a Select query.
All you need is a DLookUp:

=DLookUp("[EmployeeLastName]","Employees","[Employee_ID]= " & EmpNum)

You can place the above expression directly as control source in an
unbound control on a form, or in a report, that includes the EmpNum
control, or in your existing function :

getEmployeeName = DLookUp(etc...)

The above assumes EmpNum is a Number datatype.