Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Apr 2006
    Location
    Middlesbrough, UK
    Posts
    13

    Question Unanswered: input parameters into stored procedure from vb

    Hi right now i just have a general query...

    I have an sql database with an access front end. What I'd like to do is have various forms and reports that are populated based on user input from a form with combo boxes.

    I have a search form where the use can select a course from a drop down menu then the stored procedure takes that input and then returns the results. only problem i have is passing the value into the stored procedure, at the moment i have to type it in to a box that pops up.

    i found a working version in access with this line in the query [forms]![form]![combobox] and it worked but I cant get this to work in a stored procedure.

    To give you an idea how it should work:
    From the search form I select a course from a drop down, click a command button which opens the report (the report is based on a stored procedure which displays all records where the course_id is the same as the input).

    Can anyone suggest a method that works or point me to any tutorials?
    If I've been too vauge then I'll try to provide more details.

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi DBN

    Welcome to the forum

    Is this an Access Data Project or a standard mdb file?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2006
    Location
    Middlesbrough, UK
    Posts
    13
    Hello!

    As far as i can tell its mdb. I have 2 files with the extension ldb and mdb. I'm not familiar Access Data Project so i wouldnt know how to tell other than the file extensions.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coo - what is your current code to call the stored procedure?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2006
    Location
    Middlesbrough, UK
    Posts
    13
    Quote Originally Posted by pootle flump
    Coo - what is your current code to call the stored procedure?
    I simply use this line to open the report:
    docmd.openreport "register" ,acviewpreview

    and the reports data field is the stored procedure which expects input that i mentioned.

    I've attached an example access database i found somewhere that does exactly what i want in access but when i try to use the same method in the stored procedure i get "ado error" with no other description.

    i found this suggested somewhere but have been unable to make it work, would be very usefull if i could.
    dim outputvariable as string

    outputvariable = "me.combobox.value"

    docmd.openreport "register" ,acviewpreview, outputvariable
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    But how does your stored procedure populate the report? For example - do you use a pass through query and asign that as the record source of the report?

    Is it a SQL Server sproc?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2006
    Location
    Middlesbrough, UK
    Posts
    13
    Quote Originally Posted by pootle flump
    But how does your stored procedure populate the report? For example - do you use a pass through query and asign that as the record source of the report?
    don't know what you mean by pass through query. I wanted to create a query but in the access front end the option i used was create stored procedure. I added 1 table: book_seminar and ticked the fields i wanted to display then added @course_id to the criteria field of the field i want to search on.

    this is essentially what the sp does.
    SELECT dbo.book_seminar.Course_id, dbo.book_seminar.Staff,
    FROM dbo.book_seminar
    WHERE (dbo.book_seminar.Course_id = @course_id)

    hope this all makes sense

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm a bit confused. Could you pop this in a module, run it and post the result please?

    Code:
    Sub GetMyName()
    On Error Resume Next
     
        Debug.Print "DB: " & Application.CurrentDb.Name
        Debug.Print "Proj: " & Application.CurrentProject.Name
     
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2006
    Location
    Middlesbrough, UK
    Posts
    13
    i went to modules, new, put in the code you suggested and clicked run. it brought a macro box with getMyName but when i clicked ok nothing happened. i have access 2003 if that helps.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    click on View-> Immediate Window. The results will be in there (you may need to rerun the code). Note - it will be well worth your time reading up on the immediate window (and debugging generally) - I use it constantly.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Apr 2006
    Location
    Middlesbrough, UK
    Posts
    13
    all it says is
    proj: trainingdatabase.adp

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by DatabaseNoob
    all it says is
    proj: trainingdatabase.adp
    Yay! I'm not going mad then.
    Ok - it seems this is an Access Data Project after all. That's the good news.
    The reason I was so keen to know is because.... I don't use them - never have and probably never will. That's the bad news.
    Others on this forum have and do though so I imagine they will jump in and fill the void (so to speak). That's the best news of all.


    Data Project experts - your assistance is needed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Lowtech:

    Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "EXEC yourProc param1, param2"
    End Sub
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Apr 2006
    Location
    Middlesbrough, UK
    Posts
    13
    Quote Originally Posted by Teddy
    Lowtech:

    Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "EXEC yourProc param"
    End Sub
    thanks for the help so far guys. your answer sounds just what I'm looking for but unfortunetely I'm now getting the error "error converting nvarchar to int when i try to open the report"

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Check the recordsource you're sending to your sproc, you might be supplying the wrong datatype for a particular parameter.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •