Changing input parameters in VBA

[papernate]

I have a form that dislays payroll information, based on date. The form is based on a stored procedure that displays all records, sorted by date (which is a variable). In the Form Properties...Data...Input Parameters field I have this entered in:

@varDate datetime = Date()

This way only the current date's records will show by default. I want the user to be able to type in a new date in a text field, and then only have those records available to be viewed. So I put this code in:

Private Sub txtDate_AfterUpdate()

Me.InputParameters = CDate(Me!txtDate) 'problem location
Me.Requery

End Sub

I can't find the right syntax to make this work. I keep getting a "Error converting from nvarchar to datetime" message. The CDate is what was in the help file.

Any help will be appreciated!

 

[Rolliee]

Show yuour SQL for the form so we can show you where to make changes.

rollie@bwsys.net

 

[papernate]

SQL for my stored procedure:

CREATE PROCEDURE dbo.frmFullTimePayrollSource(@varDate datetime)
AS SELECT [Department_Name] AS Expr1, Date AS Expr2, dbo.[Payroll_Info].*
FROM dbo.[Payroll_Info]
WHERE ([Department_Name] = SUSER_SNAME()) AND (Date = @varDate)
GO


My form's Input parameters property is set to:

@varDate datetime = Date()

My idea was to use the after update event of a text box to change the Input parameters property to the value stored in that text box, and then requery.

 

[Rolliee]

Send your email address and I will send you a sample of how this could be done.

rollie@bwsys.net

 

[papernate]

I actually got it working. I save various examples of code I find/use, and one worked.

Private Sub Date_AfterUpdate()

Me.InputParameters = "#" & Format$(Me!Date, "mm/dd/yyyy&quot & "#"
Me.Requery

End Sub

Thank for your help.