Form parameter to a stored procedure in an Access Project 1

[traveller4]

I am attempting to pass a date parameter to a stored procedure through a form in an Access project. My problem is when I execute the procedure it brings up the parameter value box and tells me to enter a value. I would like it to pick up the form value from the text boxs.

The stored procedure is as follows:

CREATE PROCEDURE dbo.qapdMoveGahs
@Begins AS datetime,
@Ends AS datetime
AS
SELECT
dbo.vMoveGahsInvoices.FileID,
dbo.vtblFileMain.NameFile,
dbo.vtblDesc.Description,
dbo.vMoveGahsInvoices.GahsInvID,
dbo.vMoveGahsInvoices.GahsRetID, dbo.vMoveGahsInvoices.BeginDate, dbo.vMoveGahsInvoices.EndDate, dbo.vMoveGahsInvoices.InvoiceNum,
dbo.vMoveGahsInvoices.Hours, dbo.vMoveGahsInvoices.InvoiceFees, dbo.vMoveGahsInvoices.InvoiceDisbursement,
dbo.vMoveGahsInvoices.LSBCost, dbo.vMoveGahsInvoices.ClientCost, dbo.vMoveGahsInvoices.JVNum,
dbo.vMoveGahsInvoices.JVPaid,
dbo.vMoveGahsInvoices.ClientID, dbo.vMoveGahsInvoices.InvoicePST, dbo.vMoveGahsInvoices.InvoiceGST, dbo.vMoveGahsInvoices.InvoiceFiscal,
dbo.vMoveGahsInvoices.BatchNum, dbo.vMoveGahsInvoices.InvoiceTotal, dbo.vMoveGahsInvoices.Invoiced,
dbo.vMoveGahsInvoices.InvoicePaidBy
FROM dbo.vMoveGahsInvoices
INNER JOIN dbo.vtblFileMain ON bo.vMoveGahsInvoices.FileID = dbo.vtblFileMain.FileID
INNER JOIN dbo.vtblDesc ON dbo.vtblFileMain.LocID = dbo.vtblDesc.XTableId
WHERE dbo.vMoveGahsInvoices.BeginDate BETWEEN @Begins AND @Ends)

The form is called: frmMoveslips
and the text boxes are:
txtBeginDate
txtEndDate


Thanks in advance

Micheal

 

[chrissie1]

its quite easy

on your form you put the name of the stored procedure as recordsource. In the data tab you have a field called parameters (or something like that) their you can set the parameter you want. something like this

@begindate date=forms![formname].form![textboxname],other parameters

and if you set me.requery in your afterupdate of the two textboxes you will see it work whenever you change something "What a wonderfull world" - Louis armstrong

 

[traveller4]

Thanks you for your suggestion, It still brings up the parameter box asking for input.

Here are my parameters on the

@Begins date =forms![frmMoveslips_old].form![txtBeginDate], @Ends date =forms![frmMoveslips_old].form![txtEndDate]

The @Begins and the @Ends are being passed in the Stored Procedure. Is this why the parameter box is aksing for input.

Thanks for your input I have spent all day trying to resolve this and I am a little closer with your help

Micheal

 

[traveller4]

Thank you for pointing me in the right direction, I finally got it. One form contains the text box for input and another form when opened executes the stored procedure with the parameters. you are right, in the end it was simple, just a little brain freeze I guess.

Your tip was great

Thanks again

Micheal

 

[chrissie1]

if you put these textboxes on the same form as the one you want to show the records then in the beginning the textboxes will be empty. i would try it on a second form. with the textboxes on another form. I looked here how i did it and i saw that there is a small error in the input parameters

@Begins date =forms![frmMoveslips_old].form.[txtBeginDate], @Ends date =forms![frmMoveslips_old].form.[txtEndDate]

and the sql statement

CREATE PROCEDURE dbo.qapdMoveGahs
(
@Begins AS datetime,
@Ends AS datetime
)
AS
SELECT
dbo.vMoveGahsInvoices.FileID,
dbo.vtblFileMain.NameFile,
dbo.vtblDesc.Description,
dbo.vMoveGahsInvoices.GahsInvID,
dbo.vMoveGahsInvoices.GahsRetID, dbo.vMoveGahsInvoices.BeginDate, dbo.vMoveGahsInvoices.EndDate, dbo.vMoveGahsInvoices.InvoiceNum,
dbo.vMoveGahsInvoices.Hours, dbo.vMoveGahsInvoices.InvoiceFees, dbo.vMoveGahsInvoices.InvoiceDisbursement,
dbo.vMoveGahsInvoices.LSBCost, dbo.vMoveGahsInvoices.ClientCost, dbo.vMoveGahsInvoices.JVNum,
dbo.vMoveGahsInvoices.JVPaid,
dbo.vMoveGahsInvoices.ClientID, dbo.vMoveGahsInvoices.InvoicePST, dbo.vMoveGahsInvoices.InvoiceGST, dbo.vMoveGahsInvoices.InvoiceFiscal,
dbo.vMoveGahsInvoices.BatchNum, dbo.vMoveGahsInvoices.InvoiceTotal, dbo.vMoveGahsInvoices.Invoiced,
dbo.vMoveGahsInvoices.InvoicePaidBy
FROM dbo.vMoveGahsInvoices
INNER JOIN dbo.vtblFileMain ON bo.vMoveGahsInvoices.FileID = dbo.vtblFileMain.FileID
INNER JOIN dbo.vtblDesc ON dbo.vtblFileMain.LocID = dbo.vtblDesc.XTableId
WHERE dbo.vMoveGahsInvoices.BeginDate BETWEEN @Begins AND @Ends)
RETURN

perhaps it is good on your side but just checking.
But it works over here, so keep trying
"What a wonderfull world" - Louis armstrong

 

[traveller4]

Thank you for your time.

You have been very helpful. Your solution works good for displaying the records. I am using the dual form as this is an append query and I need to move records from multiple databases into one for invoicing, so in the beginning the dates are picked from forms with ccotaining calenders when the application loads

PS: The Microsoft Access Project (.adp) is by far the best upgrade to Access. This is my first project using the .adp

Thanks again

Micheal