Using VBA functions in ADP

[Ed Warren]

VBA Module

Public Function getDepartment() as integer
getDepartment = 1 (I have several departments with their own copy of the
*.adp front end and each will have different values for the getDepartment()
function
End Function

What I want to do:

Select all records from dbo.Departments where the DepartmentID =
getDepartment()

I have built the following ADP function

SelectDepartment:

SELECT DepartmentID, DepartmentName
FROM dbo.Departments
WHERE (DepartmentID = @selectDepartment)


What I need now is to know specifically how to call the SelectDepartment
function, setting the parameter

@selectDepartment = getDepartment()

Thanks in advance

Ed Warren

 

[Graham R Seach]

Ed,

What are you actually trying to do? Get the Department Name? If so, just
open a recordset:
Dim rs As ADODB.Recordset
Dim sSQL As String

Set rs = New ADODB.Recordset
sSQL = "SELECT DepartmentName " & _
"FROM dbo.Departments " & _
"WHERE DepartmentID = " & getDepartment

rs.Open CurrentProject.Connection, sSQL
MsgBox rs!DepartmentName

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Ed Warren]

Thanks for your thoughts, I was hoping for a way to set the parameters for
the Sql Server Function via an Access call and not have to build the entire
SQL Query in Access.

Ed Warren

 

[Graham R Seach]

Ed,

From memory, you'd have to call the UDF from within a query. Given that, I'd
recommend using a stored procedure instead, or maybe just a view.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[aaron.kempf]

what you should do is store your variables in a table on the server and
then use subqueries

 

[Ed Warren]

Thanks for your thoughts, but the application still has to 'talk' to the
database, passing parameters (variables, etc) into the query or some such.

In my 'normal' access front end, I use local tables linked via the query
objects to do this, but, alas, it appears local tables are not an option for
the *.adp file. Therefore I'm trying to store some local constants then
pass them to the database, in order to select the specific rows of interest
to that user. Looks like I'm into building the SQL statement locally (at
least that will work).

Again, thanks for your input.

Ed Warren.

 

[aaron.kempf]

yeah.. totally
when you pass variables; what you are really doing is pushing these
variables into a variables table; and then you can get to these
variables using simple subqueries and SPID.

make a table called 'SysAppSettings'

create table sysappsettings
SPID INT NOT NULL,
MyVariable1 INT,
MyDateRangeStart DateTime,
MyDateRangeEnd DateTime

And then you push variables into this and you have
SPID = @@SPID as the PK.

So
insert into sysappsettings (SPID)
values (@@SPID)

when you start the app.

Then you can

Update SysAppSettings Set MyVariable1 = 12235 where SPID = @@SPID

then your report would just say 'select * from my table where int353 =
(select myvariable1 from sysappmysettings where spid = @@spid)

I've done this in about 20 different complex ADP; and it's a pretty
good fit for passing variables around all the time.

-Aaron

 

[Ed Warren]

Thanks for the specifics, I will give this a try.

Ed Warren

 

[Simon Shaw]

Just interested by this - is DLOOKUP not fashionable any more for this
purpose? Much less code.

Dim strDeptName As String

strDeptName=Dlookup("[DepartmentName]","dbo.[Departments]","[DepartmentID]="
& getDepartment)

Simon

 

[Graham R Seach]

Simon,

Domain functions are notoriously slow.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Just interested by this - is DLOOKUP not fashionable any more for this
purpose? Much less code.

Dim strDeptName As String


strDeptName=Dlookup("[DepartmentName]","dbo.[Departments]","[DepartmentID]="
& getDepartment)

Simon

Ed,

What are you actually trying to do? Get the Department Name? If so, just
open a recordset:
Dim rs As ADODB.Recordset
Dim sSQL As String

Set rs = New ADODB.Recordset
sSQL = "SELECT DepartmentName " & _
"FROM dbo.Departments " & _
"WHERE DepartmentID = " & getDepartment

rs.Open CurrentProject.Connection, sSQL
MsgBox rs!DepartmentName

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia