ADP Form to show one record, or all records

[Beau Sharbrough]

Thanks in advance for answering a question that I'm sure you've had
before. I'm in the middle of my first MDB to ADP conversion and every
day I run into something that I never saw before. My current hangup is
making a form (frmAccounts) that will allow the user to page through
all of the accounts, and I built the stored procedure to duplicate the
old qryAccounts and it worked great.

Then I went to frmClaims, and there is a control that, in the old MDB,
linked to frmAccounts, but it set a filter to show only the account
that went with the current record on the Claims form. When I click
this control in the ADP, I set a message that I can't set a server
filter on a stored procedure.

I can change frmAccounts and qryAccounts to use a parameter / input
parameter and make the link from frmClaims work, but as you might
suspect, the original use of the form as a file maintenance form for
tblAccounts was broken. The main menu just loads the form, and there
are no records since the Claims form isn't open.

A buddy of mine says that there is a way to define the parameter in
the stored procedure so that the default is all records, but I have
been to Books On Line and I've googled the web and the groups and I'm
not finding anything I want.

At this point, if I click on the Main Menu option to open frmAccounts,
I get a prompt for the value from frmClaims - and if I type a % sign I
can see all the records. It's a kludge and I'd rather see it just open
right up with all 3,000 records. I'm not inclined to make two separate
forms and two separate stored procedures. Surely this trick has been
worked out by someone already.

Can anyone tell me what keywords to search on, where to find examples,
or what I need to do?

Thanks again, in advance,

Beau Sharbrough

 

[Kevin3NF]

Create Procedure SelectAccounts
@AccountID nvarchar(20) = '%'
AS

select * from tblAccounts
where [Acct no] like @AccountID

/* Test scripts */
exec SelectAccounts -- Should return all records
exec SelectAccounts '0001362283' --should return one account


For those that wonder how I knew what his fields were named....I am the
aforementioned buddy....LOL