Parameter queries in Access 2003

[JamieHowarth0]

Hi guys,

I'm building a GUI for a small Access DB and basically what I want is the following:

Two drop-down boxes on a form. The form is bound to a table (Contacts).
The first drop-down box is not bound, but populated from a table (Organisations) .
When I change this value (and when the form is first loaded) I want the second drop-down box to be a list of all addresses associated with the selected Organisation.
Sounds simple, but I want to use a parameter query for the second drop-down and pass the integer value of the first drop-down as the parameter (so my SQL code started PARAMETER MyOrganisationI D Integer;).

I've tried using EXECUTE as part of the datasource to no avail.

Any assistance with this most annoying of bugs would be greatly appreciated!

Cheers,

codegecko

[FishVal]

Hello.

You may open DAO recordset after assigning query parameter and set Combobox.Record set to it. Seems it works.

[code=vb]
Set qry = CurrentDb.Query Defs(".....")
qry.Parameters( ".....") = ......
Set rs = qry.OpenRecords et
Set Me.Combo0.Recor dset = rs
[/code]

BTW, why did you prefer to use query parameter?

Regards,
Fish.

[JamieHowarth0]

Hi Fish,

Brilliant, thanks for the response. I wasn't sure if it was possible to databind straight to the query param in the Access Form Designer, without using VB code behind it, but if it's not the case and has to be done with DAO/ADO then fair enough.

FYI I'm choosing to use a parameterised query cause I'm using the database to teach the fundamentals of good database design, and at some stage this DB is going to be ported to SQL Server, so using parameters now in the queries (which will end up as SPs) will make it much easier to explain the use of variables and how to make them type- and database-safe later.

codegecko :-)

[NeoPa]

I have often struggled to understand why this is not implemented in a more flexible way. Probably my own lack of understanding, but frustrating nevertheless.

BTW, if you are planning on moving to a SQL Server back-end I would suggest starting with ADODB rather than DAO. DAO, while it works nicely within an Access environment, is less portable and standards compliant.