Re: form-based parameters in a query (access 2003 - ADP)

If you go with #3, then whenever you change the value of the combo box in the form (or at least whenever you close the form...whichever makes sense in your scenario), you would need to change the value in a table as well. So, assuming it's not already bound to a field in your database, you'd have code like the following behind the combo box or form:

CurrentProject.Execute "UPDATE MyTable SET MyField = " & Me!region.Value

Of course, if Region is a string, you'll have to enclose it in single quotes first.

Oh and if you're using an ADP, your saved queries are on the server as well (as either Views or Stored Procedures), not just the tables.

Malcolm has a point as well...I'd forgotten about that functionality as I'm suspicious by nature and don't trust the magic. ;)


Rob

Alan wrote:
So running with the #3 option, How would I insert the value if queries are unaware of forms?

I may have described my current scenario incorrectly. I ran the upsizeing wizard and all the tables now live in Access, but queries forms & reports live in the Access Project

Thank you



Robert Morley wrote:
Unfortunately, there isn't really an equivalent. The reason, of course, is that all queries are run on the server side, which is unaware of any "forms".

Probably the three most common solutions are:

1. Re-write your query/view as a stored procedure with parameters. Personally, I ran into some problems with this method early on and have avoided ever since, so I can only tell you that the method exists, but little else.

2. Use dynamically generated SQL. Create the SQL text and plop in the value of Region just before you execute it.

3. Transfer the value of Region to a table in the database so that the necessary value is now server-side. With that there, you can now run the entire query on the server without a hitch.



Rob

Alan wrote:
I have done some Access work in the past, but keeping an Access Back-end. I recently had to up-size to SQL for an ADP file without much research or preparation. I've been able to pass parameters to queries and reports from a form by using =[forms]![frm_Name]![region] and the like. But since converting to ADP format, I've not been able to figure out the new thought process.

I've spent an hour in Google and in help and have dound some cool things, but none relevant to this situation.

Thoughts or links?

Many thanks
~alan

.

Loading