473,695 Members | 2,777 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Referencing Form data in an SQL Query

2 New Member
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
Sep 7 '07 #1
4 8839
Jim Doherty
897 Recognized Expert Contributor
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:

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.usp_DDPledgeList
  2. (@MailingListID int)
  3. AS
  4.  SELECT TOP 100 PERCENT Pledges.PledgeID
  5.  FROM dbo.Pledges
  6.  WHERE (Pledges.MailingListID=@MailingListID)
  7.  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

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next  
  2. Me!ComboBoxName.Rowsource="EXEC dbo.usp_DDPledgeList '" & Me.Parent!MailingListID &"'"
  3. 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

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next  
  2. 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

Expand|Select|Wrap|Line Numbers
  1. @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
Sep 8 '07 #2
Jim Doherty
897 Recognized Expert Contributor
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:

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.usp_DDPledgeList
  2. (@MailingListID int)
  3. AS
  4. SELECT TOP 100 PERCENT Pledges.PledgeID
  5. FROM dbo.Pledges
  6. WHERE (Pledges.MailingListID=@MailingListID)
  7. 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

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next 
  2. Me!ComboBoxName.Rowsource="EXEC dbo.usp_DDPledgeList '" & Me.Parent!MailingListID &"'"
  3. 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

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next 
  2. 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

Expand|Select|Wrap|Line Numbers
  1. @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
Sep 11 '07 #3
MikeBreslin
2 New Member
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
Sep 20 '07 #4
Jim Doherty
897 Recognized Expert Contributor
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
Sep 21 '07 #5

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

Similar topics

1
1825
by: sinthreck | last post by:
have a query which gets data from two tables. There's about 20 fields worth of data. In VB code, I run this query: strPOItems = "SELECT * FROM LEFT JOIN OutstandingInvoices ON (.PartNumber = OutstandingInvoices.PartN) AND (.OrderNo = OutstandingInvoices.PONumber) AND (.ItemNo = OutstandingInvoices.ItemNo) AND (.SubOrder =
6
2463
by: jstaggs39 | last post by:
I want to create a Dcount and an If...Then...Else statement to count the number of records in a table based on the date that is entered to run the form. The If....Else statment comes in because if the amount of records for a particular date is positive, i want the form to stop running, if there are no records that contain the date in question, then it should continue to run the form. The problem i'm having is referencing the date that is...
2
4070
by: Axel | last post by:
Hi, a question about something that seems very simple at first glance: is it possible to reference other controls of a subform in a query window without referencing through the parent form? I want to do this as I want to use same subform on two different parent forms. Main problem is that the subform is not part of the forms collection. The only workaround I found was setting the query in code in the
2
8169
by: Robert | last post by:
I am trying to give the user dynamic search capabilities to select almost any record in the database from criteria they select. Everything seems to work except when I open the display form to display the data to the user. If that form is already open, how do I make it refresh its data source and display the new data. Here is what I am doing. >From a text entry form, I create an SQL query dymanically and alter the sql statement of an...
8
36127
by: hoofbeats95 | last post by:
I don't think this should be this complicated, but I can't figure it out. I've worked with C# for several years now, but in a web environment, not with windows form. I have a form with a query button on it. If the query returns multiple results, a new window is opened with a grid containing the results. When the user double clicks on the desired row in the grid, I want the first form to populate with the correct data. I don't know how...
2
2226
by: ccsnavy | last post by:
For some reason referencing an unbound control on an active form from a query has ceased to work correctly. While other previously existing references to unbound controls in the same form seem to work fine new ones just return null or if referencing a combo box some random character (usually a box). In fact any subsequent queries created that reference controls on active forms don't seem to work properly. What could be causing this problem? ...
11
3520
by: ozTinker | last post by:
I'm sure this shouldn't be too difficult, but I lack familiarity with the MS object model. Suppose I have a table "Purchase_Orders" and a form "TEMP" which I am using to look up a customer's orders. The form has a text field named LastName for typing in new orders, but first let's look at the previous orders. Elsewhere on the form is a Office Web Components Spreadsheet version 11.0, which will run a query and show the result list. The...
7
6242
patjones
by: patjones | last post by:
Hi: I have two tables in my Access database, tblBase and tblRIP. Both of these tables have field names in common. The fields contain different data, but simply have the same names. For example, both tables have a field called fldAsstCoordinator. It's just that the numbers under this field name in tblBase are different from the numbers under the same field name in tblRIP... My question is this. When referencing these tables in Visual Basic,...
8
2181
by: angi35 | last post by:
Hi all - I've been working on this from various angles for days, and I'm pretty well stumped at this point. Hope someone has some ideas. Working in Access 2000... I’m trying to write code to assign a 3-part identifier to each new record. The 3 parts are: BidNumPt1: two-digit current year (BidNumPt1 = Format(Date, “yy”)) BidNumPt2: three-digit incremental number based on the year (Pt1) and the salesperson (Pt3) BidNumPt3: one-digit...
0
8631
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9115
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8984
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8847
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7661
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6491
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4579
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2272
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1976
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.