Referencing Form data in an SQL Query

[MikeBreslin]

Forgive me, I'm a newb at this, but I'm trying to help a charitable organization who have migrated their database from an Access 2000 MDB file to SQL Server Express via an Access ADP file. They have a main table (Mailing List) and a Main Form, and other tables relating to the Mailing List Table (such as Pledges, PhoneNumbers, etc, which have their own sub-forms inside the Main Form.

On the old MDB file, they have a field in the Pledges sub-form, inside the Main Form, that has the following SQL for the "Row Source":

SELECT Pledges.PledgeI D
FROM Pledges
WHERE (((Pledges.Mail ingListID)=[Forms]![Main Input Form]![MailingListID]))
ORDER BY Pledges.PledgeI D DESC;

It works fine in the MDB file, but in the new ADP file, made after moving the DB to SQL Server, that same field in that same form no longer accepts the same Row Source SQL. It doesn't like the "!" (exclamation points). Does anyone know how to refer to form data in a WHERE clause without using the exclamation points? What we're tring to do is to grab all the pledges from the Pledge table that match up for the person (MailingListID) that is currently showing in the Main Form (the Pledge Form is a subform inside the Main Form).

I'm in way over my head and would appreciate any help anyone can give me. Thanks.

Mike

[Jim Doherty]

Forgive me, I'm a newb at this, but I'm trying to help a charitable organization who have migrated their database from an Access 2000 MDB file to SQL Server Express via an Access ADP file. They have a main table (Mailing List) and a Main Form, and other tables relating to the Mailing List Table (such as Pledges, PhoneNumbers, etc, which have their own sub-forms inside the Main Form.

On the old MDB file, they have a field in the Pledges sub-form, inside the Main Form, that has the following SQL for the "Row Source":

SELECT Pledges.PledgeI D
FROM Pledges
WHERE (((Pledges.Mail ingListID)=[Forms]![Main Input Form]![MailingListID]))
ORDER BY Pledges.PledgeI D DESC;

It works fine in the MDB file, but in the new ADP file, made after moving the DB to SQL Server, that same field in that same form no longer accepts the same Row Source SQL. It doesn't like the "!" (exclamation points). Does anyone know how to refer to form data in a WHERE clause without using the exclamation points? What we're tring to do is to grab all the pledges from the Pledge table that match up for the person (MailingListID) that is currently showing in the Main Form (the Pledge Form is a subform inside the Main Form).

I'm in way over my head and would appreciate any help anyone can give me. Thanks.

Mike

Way over your head? ...nope... don't panic tis the proverbial learning curve we ALL go through that one. :)

Now that you have embarked with the ADP format (good choice by the way, use it all the time) you will quickly realise that in actuality you'll be rewriting that application as you become more conversant with learning SQL server language as it applies to an Access environment so a few tips.

Resist spaces and unwanted characters in field names. Mark out for yourself a good naming convention for your objects I recommend you look at the Leszynski/Reddick Guidelines for naming of objects and advise you follow that. You will benefit and so will other people having to advise on or read your code. (look it up on the web)

Don't go for creating too many subforms all over the place use ONE and alter its sourceobject property at runtime as and when needed OR if you can't do that mount them on tab pages and disengage their sourceobject until visible and when needed re-engage them again.

The object with correct database retrieval is this - you should only retrieve those records from the server that you actually intend to work with. That way your system will be swift and responsive.

In trying to establish comparisons VIEWS in an ADP are generally perceived as query equivalents for Access mdb files BUT you cannot reference a form in the query grid exactly as you would as if it were an MDB file.

Now this does not mean to say it cannot be achieved, you just get your head round the fact that you do it a different way. In fact theres a whole load of differences between them which is tooo big to go into in this thread. Suffice it to say, that you have decided to store your data outside the curtilage of Microsoft Access and into a robust well established database platform but still harness the excellent navigational and reporting capabilities of Access.

I'm slightly confused in that you speak of a subform... then a 'field' within it that has a 'ROW SOURCE' (this is usually associated with a combobox or listbox) so I'm slightly at odds as to actually what you are meaning? but anyway here goes:

If the object you are referring to is a combobox and it is the combobox that you wish to populate then my advice is to create a stored procedure (sproc) on the SQL Server like this:

CREATE PROCEDURE dbo.usp_DDPledgeList
(@MailingListID int)
AS
 SELECT TOP 100 PERCENT Pledges.PledgeID
 FROM dbo.Pledges
 WHERE (Pledges.MailingListID=@MailingListID)
 ORDER BY Pledges.PledgeID DESC

(Tip:- Personally I prefix my sprocs with DD as above where I know they provide a datasource for dropdowns that way they sort together in the objects list)

You populate the listbox at runtime by executing the sproc in the following manner

in the Got_Focus event of the combox enter the following code in code module between the Private Sub and End Sub Lines

On Error Resume Next  
Me!ComboBoxName.Rowsource="EXEC dbo.usp_DDPledgeList '" & Me.Parent!MailingListID &"'"
End Sub

Now the 'On Error Resume Next' first line is merely saying whatever happens in this flow of lines following must take place and if an error occurs disregard it and continue till the End Sub Line. The practical effect of this visually is that as soon as you put the cursor in the field it will populate the listbox (OR NOT if there is an error in the flow)

In the Lost_Focus event enter the following code again between the Private and End Sub Lines

On Error Resume Next  
Me!ComboBoxName.Rowsource=""

What this does is disengage the rowsource of the combo and the practical effect is that 'whilst so disengaged' you are speeding things up in essence because the form does NOT have to continually drag from the server a fresh list of values each time you move from one record to record and only does so when the cursor is placed in the combobox itself

If the object you are referring to is the subforms sourceobject recordsource it can be the VIEW name and you merely link the main and subform together with the Link MasterField and Link ChildField properties of the subform.

If you wish however to set the subforms sourceobject form to a stored procedure rather than a view then you would set its recordsource to the name of the stored procedure like this in that forms design view

Recordsource:= dbo.usp_DDPledg eList

But how does it know where to link up? where does the stored procedure get its MailingListID from in order to return records I hear you ask?

You type the following into the InputParameters Property of the form in design view

@MailingListID int=Forms![Main Input Form]![MailingListID]

This is a static property that can be amended at runtime but you have no need to do so because it will always look for the main forms Mailing List ID whichever main form record you are on you just have to tell the subordinate form to requery itself each time is sits on a different main form record in other words keep the forms datasets synchronised.

There is one important form property that you should look at and that is RESYNCH property look it up in help (as is your example is fairly basic but you will encounter having to use this command so have a read of the help file on it)

In this reply I referred to On Error Resume Next? I only do so to avoid error messages for the purposes of beta testing. In your production code I strongly suggest you take on board full error handling syntax as part of your procedural routines to do otherwise invites bad habit

The utopia is to work with SQL server using stored procedures (language = TRANSACT SQL or TSQL as it is known) as they are pre compiled working to an execution plan so are swift, basically you can do an immense amount with them.

Views are visible within the Access database windows and can be built using the grid but to be frank I think MS sold short on the GRID to the end user when considering useage in ADP,s MDBS are much better at using the GRID I feel.

Views are saved SQL statements that are compiled at runtime providing you with a tabular dataset hence the comparison to queries. Views and stored procedures can BOTH utilise UDF's (User defined functions) which are functions pretty much have the same principle idea as an Access function but in an SQL Server environment

I hope this helps you a little and wish you luck in your build

Regards

Jim

[Jim Doherty]

Way over your head? ...nope... don't panic tis the proverbial learning curve we ALL go through that one. :)

Now that you have embarked with the ADP format (good choice by the way, use it all the time) you will quickly realise that in actuality you'll be rewriting that application as you become more conversant with learning SQL server language as it applies to an Access environment so a few tips.

Resist spaces and unwanted characters in field names. Mark out for yourself a good naming convention for your objects I recommend you look at the Leszynski/Reddick Guidelines for naming of objects and advise you follow that. You will benefit and so will other people having to advise on or read your code. (look it up on the web)

Don't go for creating too many subforms all over the place use ONE and alter its sourceobject property at runtime as and when needed OR if you can't do that mount them on tab pages and disengage their sourceobject until visible and when needed re-engage them again.

The object with correct database retrieval is this - you should only retrieve those records from the server that you actually intend to work with. That way your system will be swift and responsive.

In trying to establish comparisons VIEWS in an ADP are generally perceived as query equivalents for Access mdb files BUT you cannot reference a form in the query grid exactly as you would as if it were an MDB file.

Now this does not mean to say it cannot be achieved, you just get your head round the fact that you do it a different way. In fact theres a whole load of differences between them which is tooo big to go into in this thread. Suffice it to say, that you have decided to store your data outside the curtilage of Microsoft Access and into a robust well established database platform but still harness the excellent navigational and reporting capabilities of Access.

I'm slightly confused in that you speak of a subform... then a 'field' within it that has a 'ROW SOURCE' (this is usually associated with a combobox or listbox) so I'm slightly at odds as to actually what you are meaning? but anyway here goes:

If the object you are referring to is a combobox and it is the combobox that you wish to populate then my advice is to create a stored procedure (sproc) on the SQL Server like this:

CREATE PROCEDURE dbo.usp_DDPledgeList
(@MailingListID int)
AS
SELECT TOP 100 PERCENT Pledges.PledgeID
FROM dbo.Pledges
WHERE (Pledges.MailingListID=@MailingListID)
ORDER BY Pledges.PledgeID DESC

(Tip:- Personally I prefix my sprocs with DD as above where I know they provide a datasource for dropdowns that way they sort together in the objects list)

You populate the listbox at runtime by executing the sproc in the following manner

in the Got_Focus event of the combox enter the following code in code module between the Private Sub and End Sub Lines

On Error Resume Next 
Me!ComboBoxName.Rowsource="EXEC dbo.usp_DDPledgeList '" & Me.Parent!MailingListID &"'"
End Sub

Now the 'On Error Resume Next' first line is merely saying whatever happens in this flow of lines following must take place and if an error occurs disregard it and continue till the End Sub Line. The practical effect of this visually is that as soon as you put the cursor in the field it will populate the listbox (OR NOT if there is an error in the flow)

In the Lost_Focus event enter the following code again between the Private and End Sub Lines

On Error Resume Next 
Me!ComboBoxName.Rowsource=""

What this does is disengage the rowsource of the combo and the practical effect is that 'whilst so disengaged' you are speeding things up in essence because the form does NOT have to continually drag from the server a fresh list of values each time you move from one record to record and only does so when the cursor is placed in the combobox itself

If the object you are referring to is the subforms sourceobject recordsource it can be the VIEW name and you merely link the main and subform together with the Link MasterField and Link ChildField properties of the subform.

If you wish however to set the subforms sourceobject form to a stored procedure rather than a view then you would set its recordsource to the name of the stored procedure like this in that forms design view

Recordsource:= dbo.usp_DDPledg eList

But how does it know where to link up? where does the stored procedure get its MailingListID from in order to return records I hear you ask?

You type the following into the InputParameters Property of the form in design view

@MailingListID int=Forms![Main Input Form]![MailingListID]

This is a static property that can be amended at runtime but you have no need to do so because it will always look for the main forms Mailing List ID whichever main form record you are on you just have to tell the subordinate form to requery itself each time is sits on a different main form record in other words keep the forms datasets synchronised.

There is one important form property that you should look at and that is RESYNCH property look it up in help (as is your example is fairly basic but you will encounter having to use this command so have a read of the help file on it)

In this reply I referred to On Error Resume Next? I only do so to avoid error messages for the purposes of beta testing. In your production code I strongly suggest you take on board full error handling syntax as part of your procedural routines to do otherwise invites bad habit

The utopia is to work with SQL server using stored procedures (language = TRANSACT SQL or TSQL as it is known) as they are pre compiled working to an execution plan so are swift, basically you can do an immense amount with them.

Views are visible within the Access database windows and can be built using the grid but to be frank I think MS sold short on the GRID to the end user when considering useage in ADP,s MDBS are much better at using the GRID I feel.

Views are saved SQL statements that are compiled at runtime providing you with a tabular dataset hence the comparison to queries. Views and stored procedures can BOTH utilise UDF's (User defined functions) which are functions pretty much have the same principle idea as an Access function but in an SQL Server environment

I hope this helps you a little and wish you luck in your build

Regards

Jim

Given there is no reply but just in case you do
As a PS I am on vacation as of today (off to sunny cyprus) so if you don't get a resolution you can PM me and I'll continue on return next week?

Jim

[MikeBreslin]

Given there is no reply but just in case you do
As a PS I am on vacation as of today (off to sunny cyprus) so if you don't get a resolution you can PM me and I'll continue on return next week?

Jim

Jim,

You were right, I was dealing with a combo box within a subform, hence the "Row Source".

I tried what you suggested, went into Studio Express and created a stored procedure, which looks like this:

-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- =============== =============== =============== ===
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
-- =============== =============== ===============
-- Author: Michael Breslin
-- Create date: 9/20/2007
-- Description: Create a SPROC for Pledges in the Donations sub-form
-- =============== =============== ===============
CREATE PROCEDURE dbo.usp_DDPledg eList
(@MailingListID int)
AS
SELECT Pledges.PledgeI D
FROM dbo.Pledges
WHERE (Pledges.Mailin gListID=@Mailin gListID)
ORDER BY Pledges.PledgeI D DESC
GO


I then created code for the Got_Focus event and the Lost_Focus event, which looks like this:

Option Compare Database

Private Sub PledgeID_GotFoc us()
On Error Resume Next
Me!ComboBoxName .RowSource = "EXEC dbo.usp_DDPledg eList '" & Me.Parent!Maili ngListID & "'"
End Sub
End Sub

Private Sub PledgeID_LostFo cus()
On Error Resume Next
Me!ComboBoxName .RowSource = ""
End Sub
End Sub

However, now when I click on that combo box (called "PledgeID", which I assumes triggers the Got_Focus event, I get this error message:

"Compile error: Only comments may appear after End Sub, End Function, or End Property", then the OK and Help buttons.

When I close that error window out with the "OK" button, it shows the code for Got_Focus with the first line high-lighted in yellow:

Private Sub PledgeID_GotFoc us()


Any idea where I screwed up? I'm a little concerned that when I use SQL Server Mgmt Studio Express, and look under the database, then under Programmability , then in Stored Procedures, I can't find the sproc I made. I have to do FILE --> OPEN to open a file, then it shows up underneath My Documents on my local hard drive. Shouldn't it be stored on the server with the SQL database? Maybe that's my screw-up.

Thanks for any help you can provide (when you get back from Cyprus).

Mike

[Jim Doherty]

Jim,

You were right, I was dealing with a combo box within a subform, hence the "Row Source".

I tried what you suggested, went into Studio Express and created a stored procedure, which looks like this:

-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- =============== =============== =============== ===
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
-- =============== =============== ===============
-- Author: Michael Breslin
-- Create date: 9/20/2007
-- Description: Create a SPROC for Pledges in the Donations sub-form
-- =============== =============== ===============
CREATE PROCEDURE dbo.usp_DDPledg eList
(@MailingListID int)
AS
SELECT Pledges.PledgeI D
FROM dbo.Pledges
WHERE (Pledges.Mailin gListID=@Mailin gListID)
ORDER BY Pledges.PledgeI D DESC
GO


I then created code for the Got_Focus event and the Lost_Focus event, which looks like this:

Option Compare Database

Private Sub PledgeID_GotFoc us()
On Error Resume Next
Me!ComboBoxName .RowSource = "EXEC dbo.usp_DDPledg eList '" & Me.Parent!Maili ngListID & "'"
End Sub
End Sub

Private Sub PledgeID_LostFo cus()
On Error Resume Next
Me!ComboBoxName .RowSource = ""
End Sub
End Sub

However, now when I click on that combo box (called "PledgeID", which I assumes triggers the Got_Focus event, I get this error message:

"Compile error: Only comments may appear after End Sub, End Function, or End Property", then the OK and Help buttons.

When I close that error window out with the "OK" button, it shows the code for Got_Focus with the first line high-lighted in yellow:

Private Sub PledgeID_GotFoc us()


Any idea where I screwed up? I'm a little concerned that when I use SQL Server Mgmt Studio Express, and look under the database, then under Programmability , then in Stored Procedures, I can't find the sproc I made. I have to do FILE --> OPEN to open a file, then it shows up underneath My Documents on my local hard drive. Shouldn't it be stored on the server with the SQL database? Maybe that's my screw-up.

Thanks for any help you can provide (when you get back from Cyprus).

Mike

Hi Mike,

The screw up hitting me in the face from your posting is a common typo (generally caused by cutting pasting w/o paying attention to whats already there do it myself all the time) .....

Lose one of the End Sub Lines you have two of them!! You only need one

End Sub
End Sub


Regards

Jim