Solved

Using a sql stored proc as an access combobox rowsource

Posted on 2014-03-12
15
1,105 Views
Last Modified: 2014-03-20
So, I am trying, as the title suggests, to use a SQL stored proc as the rowsource for a combobox, and I am having a great deal of difficulty.  Here is the latest code I have tried:

Private Sub Form_Load()

    Dim conn As ADODB.Connection
    Set conn = SQLConnection()

    conn.Open

    Me!cboProject.RowSource = "EXEC Ren.up_RenManJobSelect"

    conn.Close
   
End Sub

I have also attempted to use a recordset, but that didn't work either.  Is there something I am missing?
0
Comment
Question by:mjelec
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 39924103
Does your STored Proc return a recordset?

If so:

Dim conn As ADODB.Connection
    Set conn = SQLConnection()

    conn.Open
    Dim rst As New ADODB.Recordset
    Set rst = conn.execute "EXEC Ren.up_renManJobSelect"
   
    Me!cboProject.Recordset = rst

    conn.Close
0
 

Author Comment

by:mjelec
ID: 39926317
It does, and I had tried something like what you have suggested, but without the conn.execute part.

When I tried what you posted, and added parens around the "EXEC Ren.up_RenManJobSelect", I get an "Object doesn't support this property or method" error on the

Me!cboProject.Recordset = rst

line.

I tried to change it to

Me!cboProject.RowSource = rst

but that didn't work either.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39930734
Please post your Stored Procedure.
0
 

Author Comment

by:mjelec
ID: 39934269
ALTER PROC [Ren].[up_RenManJobSelect] @Action INT = 0
AS
    IF @Action = 0
        BEGIN
            SELECT  LTRIM(JMMCU) AS JobNo ,
                    JMDL01 AS JobDesc
            FROM    JDE.dbo.F5600006 J
            WHERE   JMRP07 = 81
                    AND JMSTYL LIKE 'J_'
                    AND ( JMRP25 = ''
                          OR RIGHT(JMMCU, 2) = RIGHT(RTRIM(JMRP25), 2)
                        )
        END
    ELSE
        BEGIN
            SELECT  Job AS UserJob
            FROM    MJE.Master.Users U
                    JOIN ( SELECT   LTRIM(JMMCU) AS JobNo ,
                                    JMDL01 AS JobDesc
                           FROM     JDE.dbo.F5600006 J
                           WHERE    JMRP07 = 81
                                    AND JMSTYL LIKE 'J_'
                                    AND ( JMRP25 = ''
                                          OR RIGHT(JMMCU, 2) = RIGHT(RTRIM(JMRP25),
                                                              2)
                                        )
                         ) J ON U.Job = J.JobNo
            WHERE   U.DomainUserID = SYSTEM_USER      
        END
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39935746
It looks like the stored procedure is fine.  Please post your VB code as it stands now, so that we can see why it is failing.
0
 

Author Comment

by:mjelec
ID: 39936953
Private Sub Form_Load()

    Dim conn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Set conn = SQLConnection()

    conn.Open

    Set rs = conn.Execute("EXEC Ren.up_RenManJobSelect")

    Me!cboProject.Recordset = rs

    conn.Close
   
End Sub

And the error I get is Run-time '438':

Object doesn't support this property or method
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 39938514
Assuming that the error occurs on the line Me!cboProject.Recordset = rs and forgive me if I am wrong, I have never used MS Access before, but a Recordset is an object so I believe it should be:
Set Me!cboProject.Recordset = rs

Having said, you should also know that your recordset is what is called a fire hose (forward only, read only) and this type of recordset may not be supported by the combo box.  

I can show you how you can retrieve the values from the recordset if that is any help.  At least this way you know it is returning the right results.

Unrelated but this:
Dim rs As New ADODB.Recordset
Should be:
Dim rs As ADODB.Recordset

As the connection's Execute method instantiates a new object and therefore no need to instantiate one first.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:mjelec
ID: 39939505
So, I added Set to the line of code you recommended, and I get a different error now:

Run-time error '7965':

The object you entered is not a valid Recordset property.

Here is the current code:

Private Sub Form_Load()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = SQLConnection()

    conn.Open

    Set rs = conn.Execute("EXEC Ren.up_RenManJobSelect")

    Set Me!cboProject.Recordset = rs

    conn.Close
   
End Sub

The error is occurring on the Set Me!cboProject.Recordset = rs line.

I am not certain if a fire hose is what is causing the problem here, but I am open to any advice.  I can say that the data going into the combobox will be static, so the concept of a fire hose seems like it would work.  I have used what I am doing here, or something similar, to populate access forms.  At this point, I am just looking for a good way to get data into a combobox, whether it is similar to this or not.  We are trying to avoid putting the SQL code directly into the VB for Access, to avoid SQL injections and maintain security.  Beyond that, I am open to suggestions.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 39941124
See if this returns the correct values (untested):
Private Sub Form_Load()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = SQLConnection()

    conn.Open

    Set rs = conn.Execute("EXEC Ren.up_RenManJobSelect")

    ' Set Me!cboProject.Recordset = rs
    While Not rs.EOF
    	Debug.Print rs.Fields("JobNo").Value
    	rs.MoveNext
    Wend

    conn.Close
    
End Sub

Open in new window

If yes, then it is a problem how you are binding to the MS Access combobox.
0
 

Author Comment

by:mjelec
ID: 39941388
Yes, that works.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 39941870
If the data is static you can use the AddItem method of a combo.

Set the RowSourceType to "Value List", then do this:


   Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = SQLConnection()

    conn.Open

    Set rs = conn.Execute("EXEC Ren.up_RenManJobSelect")

    Do Until rs.EOF
    	YourCombo.AddItem rs("JobNo")
    	rs.MoveNext
    Wend

    conn.Close

Open in new window

0
 

Author Comment

by:mjelec
ID: 39942234
I did try this at one point, but I was uncertain how to add multiple columns using that method, Scott.  I need both JobNo and JobDesc for each record.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39942270
No points please:

          YourCombo.AddItem rs("JobNo") & ";" & rs("JobDesc")

Make sure your combo is setup for 2 columns.
0
 

Author Comment

by:mjelec
ID: 39942781
Okay, I actually found something that seems to be working, but I would love to get comments and feedback on it, let me know if I am missing anything.

Private Sub Form_Load()

    Dim qdf As DAO.QueryDef
   
    Set qdf = CurrentDb.QueryDefs("Projects")
    qdf.Connect = "ODBC;DRIVER={sql server};DATABASE=MyDb;SERVER=MyServer;Trusted_Connection=Yes;"
    qdf.SQL = "EXEC Ren.up_RenManJobSelect"

    Me.cboProject.RowSource = "Projects"

End Sub
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 39943939
That method would work as well ...
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now