How can I use a SELECT statement in VB?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...

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.
 
Hey thanks very much!
fredg said:
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
Click to expand...

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.
Click to expand...
 
Back
Top