Passing variables from Access forms to sp's

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Passing variables from Access forms to sp's

Author

Topic

guido09
Starting Member

2 Posts

Posted - 2006-03-12 : 13:42:24

Hi everyone,

passing variables (values) from a form (MS Access Project) to a stored procedures in order to select records shouldn't be all that difficult. However after searching for hours and hours in various forums and discussions i'm still nowhere. I really hope someone can give me the missing hint.

OK All I whant to do is select data from the sql server via an access interface. the value entered into the access form by the user (for example the client name) should then be transferred to the stored procedure which will then return all the records of the chosen client.

The simple task of transferring this form value to the stored procedure is driving me crazy. Does anyone have an idea.

Thanx in advance

Guido

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-03-13 : 12:21:31

Try researching Microsoft ActiveX Data Objects (ADO) connections, commands and record sets. Essentially you'll need to include a reference in your Access project to Microsoft ADO whatever the latest version is, then create a connection object to the database; a command object that you pass the connection, stored procedure name and parameter list, and a recordset to hold the results.

Do some searching on Google and you should find sample code to show you how to do everything.

~Travis

guido09
Starting Member

2 Posts

Posted - 2006-03-14 : 07:40:30

Thanx Travis,

it seems to be a fact that such a simple thing is so work intensive. I found lots of references via google, but i couldn't believe that the complexity is so high (for me in any case ;-). In any case the only thing that helped was buying a book.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 08:05:13

guido09,

Just some idea on how to start (I know how to do this in VB & ASP & I'm pretty sure that there is not much of a difference in Access - VBA)
- U may need to read some ADO docs
- If u don't know any of the following feel free to ask
-- Reference the ADO to the Access Project
-- Define a connection object and a recordset object
-- Make use of above objects, u can pass parameters to the SP and get the data to ur rec set

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-14 : 09:00:11

let me know if this helps you out.

As mentioned, you will need to know ADO, but it's pretty easy.

Here's what I would do:

1) Create a local, temporary table with some data, using the same output from the stored proc. Same columns, same datatypes, etc.

2) Use that local temp table to design your form. Bind text boxes, etc. Test it, make sure it works.

3) Now, one the Form's Load event, you need to simply build an ADO command and open up a recordset. Then you can set the Form's "recordset" property to the ADO recordset. It is when you build the command that you have the ability to set parameters, and the command itself will call your stored procedure.

So, here is some sample code for you using Northwind. Just create a new form, put on two text boxes, and bind one to Productname and the other to Total.

Then, add this in the Form_Open event (put in a valid connect info, of course):


Private Sub Form_Open(Cancel As Integer)
    Dim r As New ADODB.Recordset
    Dim cm As New ADODB.Command
    Dim cn As New ADODB.Connection
    
    cn.Open ("Provider=SQLOLEDB.1;User ID=sa;pwd=xxxxx;Initial Catalog=Northwind;Data Source=.")
    
    cm.ActiveConnection = cn
    cm.CommandType = adCmdStoredProc
    cm.CommandText = "CustOrderHist"
    
    cm.Parameters.Append cm.CreateParameter("@CustomerID", adVarChar, , 10, "ALFKI")
       
    r.Open cm, , adOpenKeyset, adLockReadOnly
    
    Set Me.Recordset = r
    Me.Requery
End Sub

I hope this helps.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-03-14 : 10:48:21

You can pass a variable directly from the form or even from another form.
To do this you need to go to the propertys view for the form, scroll down untill you see Input Paramiters

Here is an example of a SP that wants a Variable @SLSContact
that I am picking up from the Form Customer contact list

@SLSContact = Forms![Customer contact list]!SLSContact

One thing to note be sure to use the form field name if it is different than the DB field name.

By the way you can pass more than one just seperate with commas.

@Shift = Forms![Main Menu]!Shift,@Sub_job_number = Forms![Active job]!Sub_Job_number,@Machine = 'RA'

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources