By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,651 Members | 1,694 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,651 IT Pros & Developers. It's quick & easy.

Parameter queries in Access 2003

codegecko
Expert 100+
P: 533
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 MyOrganisationID 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
Oct 21 '08 #1
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello.

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

Expand|Select|Wrap|Line Numbers
  1.     Set qry = CurrentDb.QueryDefs(".....")
  2.     qry.Parameters(".....") = ......
  3.     Set rs = qry.OpenRecordset
  4.     Set Me.Combo0.Recordset = rs
  5.  
BTW, why did you prefer to use query parameter?

Regards,
Fish.
Oct 22 '08 #2

codegecko
Expert 100+
P: 533
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 :-)
Oct 26 '08 #3

NeoPa
Expert Mod 15k+
P: 31,263
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.
Oct 27 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.