Pass variable from VBA function to Stored Procedure? 1

[BobJacksonNCI]

I've searched, read and scratched my head ... NOTHING has helped!

In an Access Project I identify Users by their NT (2000 actually) logon. I want to pass that logon to a Stored Procedure so that a form is populated by members of that employees team, only.

Is is possible?

Thanks!
Bob

 

[cmmrfrds]

You can return the NT login in name from sql server that the project is connected on by a little vba/ADO code. Here is an example function.


Public Function ReturnUser() as String

Dim cn As New adodb.Connection, sql1 As String
Dim rs As New adodb.Recordset

Set cn = CurrentProject.Connection
sql1 = "Select suser_sname() "
Set rs.ActiveConnection = CurrentProject.Connection
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

ReturnUser = rs(0)

rs.Close
Set rs = Nothing
End Function

 

[BobJacksonNCI]

Thanks for your speedy reply, cmmrfrds!!

This is new ground for me...

Once I have the variable, ReturnUser, loaded with the current User's logon, I need to use it in a stored procedure. (I'm passing form controls, a date range, to a stored procedure for a report.) I'm not following threads I've read through on how to pass a variable. (Dense option ON as I read a post doing that by Duane, but can't see how to apply it.)

Thanks for assisting!
Bob

 

[BobJacksonNCI]

Reread my last post...

What I meant by form controls, etc, is that I know I could load the logon into a form control, make it invisible, and reference that to accomplish my goal.

If I can do it without cluttering the form, I'd rather.

Thanks Again,
Bob

 

[cmmrfrds]

Lets say the recordsource of the Form is a stored procedure named yoursp with 1 parameter called @myuser.

The data tab on the Form
DataSource is dbo.yoursp

Input Parameters is @myuser=ReturnUser()

 

[BobJacksonNCI]

cmmrfrds,

A belated star for you...

I understand my question was pretty basic, but help is to be rewarded. And part of the challenge/frustration for me is already knowing how to do something in straight Access and then struggling to do the same thing in an Access Project.

Thanks again!
Bob

 

[LeanneGodney]

Anyone have any idea how to feed a parameter value to a recordsource that is expecting a parameter through VBA?

I have set the record source of my form to be a view/function that expects a TradeRef. I would like to open the form and tell it what the traderef is without it actually asking, if you know what I mean?

Any clues?

Thanks,
Leanne

 

[cmmrfrds]

It will be clearer if you show what is in the recordsource now.

 

[LeanneGodney]

Hi cmmrfrds,

I've figured it out, so don't worry. :0) I've used a parameter of @GetRef in the query, and on the actual form that opens up I've put @GetRef = SupplyRef()

The SupplyRef function picks up the reference which is set just before, and returns it to the form. It also empties the public reference.

So it all works like a charm. :0)

Leanne