Filling access 2003 list box with a sql 2005 stored procedure

[skyline666]

Hi,

I have a database called Risk using Access 2003, with Risk linked to SQL Server 2005. I have a form on Access (called Form1 for now), with a subform linked to that form. On the form (not subform) I have a combo box called ProjectCombo and a list box called ProjectList. At the moment ProjectCombo returns a list of all master projects (the field called ProjectName) from a table in a different database (called Master) on SQL, and ProjectList returns a list of all sub projects (the field called Project) from the same different database.

There can be many sub projects for each master project. For example, a master project called Deep Water has many sub projects called Deep Water - Storage, Deep Water - Test, etc. Both master and sub project have code identifiers (fields called ProjectMasterCode and ProjectSubCode respectively). For example Deep Water could be DW, and Deep Water - Storage could be DW01, Deep Water - Test could be DW02 etc.

What I would like is for the user to select a master project from ProjectCombo, and then all sub projects associated with that master project appear in ProjectList automatically. I can't make the list box bound to the combo box as it can only be bound to the main form.

Some code that might help. Note that all stored procedures were made in SQL.
ProjectCombo uses a stored procedure called usp_MasterProjects:

ALTER PROCEDURE [dbo].[usp_MasterProjects]
AS

set nocount on

SELECT *
FROM Master.dbo.MasterProjects
ORDER BY ProjectName

set nocount off

ProjectList uses a stored procedure called usp_ProjectList:

ALTER PROCEDURE [dbo].[usp_ProjectList]
AS

set nocount on

SELECT ProjectMasterCode + ' - ' + CAST(ProjectSubCode AS varchar(6)) + ' - ' + ProjectName AS Project
FROM Master.dbo.SubProjects
WHERE Master.dbo.SubProjects.ProjectID = Master.dbo.SubProjects.ProjectID
ORDER BY Project

set nocount off

The master projects and sub projects are linked by a ProjectID (each master project has a ProjectID, and so all sub projects for each master project have the same ProjectID).

In usp_ProjectList, I would like Master.dbo.SubProjects.ProjectID to equal [Forms]![Form1]![ProjectCombo], or something along those lines. If this can't be done, then some code in VBA will most probably have to be done.

Note I did try the [Forms!]... bit, but got an error when executed in SQL saying Incorrect syntax near !. I think this is becuase you cant do this in SQL, but if the stored procedure is to be changed, it will need to be done in SQL as Access wont allow changes to the stored procedures.

So to sum up, I want ProjectList to return values when ProjectCombo is selected, and either a modification to the stored proc usp_ProjectList, or some code in VBA, to do it with.

If you need any more info please ask.

Many thanks and I hope this makes sense,

Andrew

 

[Remou]

Does this

http://support.microsoft.com/kb/q131534/

help?

 

[jrbarnett]

Firstly, all you need to do is parameterise the input of your usp_ProjectList, thus:

ALTER PROCEDURE [dbo].[usp_ProjectList]
  @ProjectID varchar (10) -- change this to your appropriate data type
AS

set nocount on

SELECT ProjectMasterCode + ' - ' + CAST(ProjectSubCode AS varchar(6)) + ' - ' + ProjectName AS Project
FROM Master.dbo.SubProjects
WHERE Master.dbo.SubProjects.ProjectID = @Projectid
ORDER BY Project

set nocount off

You then in your code that you call it, set the parameter thus:

cmd.Parameters("@ProjectID") = Forms!WhateverForm!cboYourCombobox

before you call the execute function on your ADODB command object or Connection object.

Another thing:

The master database in SQL Server is a special one for storing system configuration information, preferences etc.

If you put your own items in there, you will make it very difficult to port this to another SQL server, and increase drastically the disaster recovery and maintenance of it.
I strongly recommend creating a new database just for this application and moving your own objects across to it, then dropping them from the master database.

John

 

[skyline666]

Hi John,

I had a look at another database that has a similar thing to what im doing at work and took the @ProjectID idea that you put from that database. With only that though it didn't work. I tried putting your line of code in aswell, on the OnClick event for the combo box, now im assuming theres more code that I need as I get an error saying "object required". The line I put in is:

cmd.Parameters("@cmbProjectCombo") = Forms!Form1!cmbProjectCombo

I don't know a lot of vba at the moment as ive only been doing it for a couple months now, but im getting there slowly! What code do I need in there for it to work?

My stored procedure I have now is:

ALTER PROCEDURE dbo.usp_ProjectListSelect
@cmbProjectCombo int
AS

set nocount on

SELECT Master.dbo.SubProjects.ProjectMasterCode + ' - ' + CAST(Master.dbo.SubProjects.ProjectSubCode AS varchar(6)) + ' - ' + Master.dbo.SubProjects.ProjectName AS Project
FROM Master.dbo.SubProjects
WHERE Master.dbo.SubProjects.ProjectID = @cmbProjectCombo
ORDER BY Project

set nocount off

Im not sure if you need the Master.dbo.SubProjects in front of the field names in select, but as it weren't working and the stored proc in the other database had them in, I put them in, but without success!

Also, is the master database your refering to part of SQL itself, as the database im using isn't that. The database isn't called Master, it has the companies name in front of the name, but I wanted it to be more anonymous, so just put in Master instead, hope that didn't confuse you!

Andrew

PS. Remou, I took a quick look at the link you posted, but as I already did something similar to what John posted, and as it didn't really look like what I needed, it didn't help, thankyou anyway.

 

[jrbarnett]

To check whether it is SQL Server or your Access code, open SQL SErver management studio, login to the database server with the same login ID and password that your application uses, open a new query then type:

EXEC dbo.usp_ProjectListSelect 1

then run it - does this show all the projects for a combo value of 1?

If not, does:

SELECT Master.dbo.SubProjects.ProjectMasterCode + ' - ' + CAST(Master.dbo.SubProjects.ProjectSubCode AS varchar(6)) + ' - ' + Master.dbo.SubProjects.ProjectName AS Project
FROM Master.dbo.SubProjects
WHERE Master.dbo.SubProjects.ProjectID = 1
ORDER BY Project

retrieve the correct data?

John

 

[skyline666]

Hi John,

The query was actually working, it was just the list box wasnt refreshing itself, so I put Me.ProjectList.Requery in the OnClick event of the combo box, and the sub projects appeared. Its always the simplest of things that make things not work!

Andrew