How to pass parameter to SQL condition?

[Dogbert Dilbert]

Dear All,

I hope someone can help me, becuase I'm really struggling with this
one. I have an Access ADP with several tables in it. One table has the
names of our suppliers (tbl_CROlist), while another has details of
work we've placed with these suppliers (tbl_Index).

I want to be able to select records from tbl_Index according to which
supplier I choose. I can make a stored procedure which prompts me to
enter the supplier name, and this works fine (by using
=@Enter_CRO_name as a criterion), however to do this, you need to know
in advance what supplier names we have.

So I'm trying to make a form with a combo box which shows a list of
supplier names (cboCROname). This combo box gets its names from
tbl_CROlist. I have a button beside this combo box with an event
procedure as follows:

Private Sub Command3_Click()
On Error Resume Next
If IsNull(Me.cboCROname) Then
DoCmd.OpenReport "rptIndex", View:=acViewPreview
Else
DoCmd.OpenReport "rptIndex", View:=acViewPreview,
_WhereCondition:="CROname=" & Me!cboCROname
End If
Me.cboCROname = Null
End Sub

The report rptIndex is simply an autoreport based on tbl_Index.

If anyone has Chipman and Baron's book "Microsoft Access developers
guide to SQL server" you'll recognise this procedure from Chapter 12
(p 596). It works on their system - I know, because I've downloaded
the code and tried it, but on my system this does not work. If I press
the button once, the cboCROname goes null. If it press the button
again, it pulls up my report, but with all records present (ie. no
WHERE filter has been applied).

I know this is kind of complex, but if anyone can see where I've gone
wrong I'd be very grateful if you could point it out, or alternatively
suggest for me another way to pass the parameter from a combo to a
stored procedure.

Many thanks,

Dogbertd

 

[Brendan Reynolds]

Here's an example using the 'NorthwindCS' sample ADP. First, I created the
following stored procedure ...

SELECT dbo.Products.*
FROM dbo.Products
WHERE (CategoryID = @CategoryID)

Then I created a form with this stored procedure as its recordsource. I
added a combo box, 'cboTest' to the form, with the following SQL statement
as the rowsource for the combo box ...

SELECT CategoryID, CategoryName
FROM Categories

I added the following code as the AfterUpdate event procedure of the combo
box ...

Private Sub cboTest_AfterUpdate()

Me.Requery

End Sub

And finally, I added the follow as the Input Parameters property of the form
....

@CategoryID INT = Forms!frmProductsByCategory!cboTest

The result is that the form shows all products in the category selected from
the cboTest combo box.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.