CreganTur
02-01-2010, 02:47 PM
I was finally forced to move to Office 2007 this past week because my newest project required me to build an Access Project and Access 2003 wouldn't play nice with the server. After an hour's download through our company support system I stepped into the world of intrusive security and ribbons.
Part of this project required a search page. When working in a regular Access database, I would handle this by creating a form parameterized query which would be the recordset for a subform which would be displayed on a main form. The main form would contain objects to feed parameters to the query and a Search button to display the subform.
For those who don't know, a parameterized query is a query that has parameters in the Criteria row (in Design view) or WHERE clause (in SQL view). If a query is Form Parameterized, the parameters use a designator like this: [forms]![formName]![objectName]; to point the query to a specific object on an open form to get the value to use. This is the simplest way to feed user input to a query.
After a little research, I discovered that to do something similiar in an Access Project, you have to use a Stored Procedure. After a little trial and error I was able to create a stored procedure that would use parameters to return a recordset, but I could not get it to become Form Parameterized (I will call it FP for the rest of this rant). I followed the directions in every article I could find online, but none of them worked. These articles had you build a stored procedure like this:
SELECT
FROM tblTheaters
WHERE tblTheaters.TheaterNumber = @TheaterNumber
and said you could FP it by putting a line like this in the Form's Input Parameters property:
@TheaterNr INT = forms!frmTheaterSchedule!cboTheater
Every time I entered something like that into the Input Parameters property it would throw an error because of the INT. Upon removing the INT, it would automatically change to this:
? = TheaterNr
It should be noted that I was doing all of this work in the Build window (in the Properties window for the Form right click in the box next to Record Source and select Build).
I manaully changed the value of Input Parameters to this:
? = forms!frmTheaterSchedule!cboTheater
But it would change back as soon as I clicked somewhere else.
From here I began to wonder why I couldn't put the form designator directly into the sotred procedure, so I tried using this:
SELECT
FROM tblTheaters
WHERE tblTheaters.TheaterNumber = @forms!frmTheaterSchedule!cboTheater
I tried with and without the at-sign, but the Build window would throw errors no matter what.
After about 30 to 45 minutes more of messing with this I selected the Zoom window (right click next to Record Source and select Zoom). This brings up the record source as pure text, without any of the Build window's oversight. I typed in something like the following:
SELECT
FROM tblTheaters
WHERE tblTheaters.TheaterNumber = @[forms]![frmTheaterSchedule]![cboTheater]
when I clicked in the Input Parameters window it changed to:
? = @[forms]![frmTheaterSchedule]![cboTheater]
and the form was miraculously FPed.
It makes absolutely no sense for the Build window to throw a hissy fit about adding in the form designator when the Zoom window will allow it and cause it to work. For that matter, why does the stored procedure Build window not have the ability to pull in form object designators with a couple of clicks the same way a regular Query Build window can in an Access Database? Did the group working on Access Projects dream this crap up themselves, or did they not talk to the dev group working on Access Databases? Why is there never concise continuity of developer tools within the Office environment?
Part of this project required a search page. When working in a regular Access database, I would handle this by creating a form parameterized query which would be the recordset for a subform which would be displayed on a main form. The main form would contain objects to feed parameters to the query and a Search button to display the subform.
For those who don't know, a parameterized query is a query that has parameters in the Criteria row (in Design view) or WHERE clause (in SQL view). If a query is Form Parameterized, the parameters use a designator like this: [forms]![formName]![objectName]; to point the query to a specific object on an open form to get the value to use. This is the simplest way to feed user input to a query.
After a little research, I discovered that to do something similiar in an Access Project, you have to use a Stored Procedure. After a little trial and error I was able to create a stored procedure that would use parameters to return a recordset, but I could not get it to become Form Parameterized (I will call it FP for the rest of this rant). I followed the directions in every article I could find online, but none of them worked. These articles had you build a stored procedure like this:
SELECT
FROM tblTheaters
WHERE tblTheaters.TheaterNumber = @TheaterNumber
and said you could FP it by putting a line like this in the Form's Input Parameters property:
@TheaterNr INT = forms!frmTheaterSchedule!cboTheater
Every time I entered something like that into the Input Parameters property it would throw an error because of the INT. Upon removing the INT, it would automatically change to this:
? = TheaterNr
It should be noted that I was doing all of this work in the Build window (in the Properties window for the Form right click in the box next to Record Source and select Build).
I manaully changed the value of Input Parameters to this:
? = forms!frmTheaterSchedule!cboTheater
But it would change back as soon as I clicked somewhere else.
From here I began to wonder why I couldn't put the form designator directly into the sotred procedure, so I tried using this:
SELECT
FROM tblTheaters
WHERE tblTheaters.TheaterNumber = @forms!frmTheaterSchedule!cboTheater
I tried with and without the at-sign, but the Build window would throw errors no matter what.
After about 30 to 45 minutes more of messing with this I selected the Zoom window (right click next to Record Source and select Zoom). This brings up the record source as pure text, without any of the Build window's oversight. I typed in something like the following:
SELECT
FROM tblTheaters
WHERE tblTheaters.TheaterNumber = @[forms]![frmTheaterSchedule]![cboTheater]
when I clicked in the Input Parameters window it changed to:
? = @[forms]![frmTheaterSchedule]![cboTheater]
and the form was miraculously FPed.
It makes absolutely no sense for the Build window to throw a hissy fit about adding in the form designator when the Zoom window will allow it and cause it to work. For that matter, why does the stored procedure Build window not have the ability to pull in form object designators with a couple of clicks the same way a regular Query Build window can in an Access Database? Did the group working on Access Projects dream this crap up themselves, or did they not talk to the dev group working on Access Databases? Why is there never concise continuity of developer tools within the Office environment?