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.PledgeID
FROM Pledges
WHERE (((Pledges.MailingListID)=[Forms]![Main Input Form]![MailingListID]))
ORDER BY Pledges.PledgeID 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_DDPledgeList
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