MS Access Front End To SQL Server Problem Passing Form Value To Report

Jul 23, 2005

I just changed my Access 2002 database to a SQL Server ADP project. I
had a form where the user entered a value into a text box and when a
command button on the form was clicked a Report was opened. The reports
record source is a query. The query uses the value from the form text
box to restrict the query.
Table name = EggsTable
one of the columns in the table is named: EggColor
Form name = EggColorForm
Form text box name = ColorTextBox

This sql worked for the query before I converted to SQL:

SELECT EggsTable.EggColor
FROM EggsTable
WHERE (((EggsTable.EggColor)=[Forms]![EggColorForm]![ColorTextBox]));

This no longer works. Can I change the syntax somehow to get this to
work? I tried dropping the brackets around the word "Forms", I tried
dropping all the square brackets, etc., nothing worked.

I also tried just opening the report with the report's Server Filter
property set to:
EggColor=N'Forms.EggColorForm.ColorTextBox'

I tried using the Report's open event to pass the form value directly
to the report. I tried setting a variable from the text box value on
the form. So far, nothing works. Any ideas?

View 7 Replies

Passing Null Value From Front End Access To SQL Back End

Dec 19, 2007

Hi,

I'm not sure if this question should be in this thread but anyway...

I have a form (Mricosoft Access front end) in which the user enter a Pending Date in one of the fields. The date is populated into my backend (SQL Server) withouht problems.
Whenever the issue which had the pending date in the field is resolved I'd like to delete this date from this field and also from the back end. At the moment I'm not being able to pass the "value" Null to my back end (probably because it's a datetime type). I've searched the net throughout and I couldn't find a way around it. Only found solution to when the data is not date but numbers/letters, etc... Any ideas on how can I sort this out?

Many thanks
Emanuel

View 3 Replies View Related

Passing Value From A Access Form To A Query

Jun 23, 2006

Frustration has gotten the best of me on this one. Can anyone help?
I need to pass the current value in an Access Data Project (back-end is MS SQL) text field to the where condition in an SQL using VBA. This is what I have, but does not work.

Private Sub Command44_Click()
Me.SS.SetFocus
Dim strSQL As String
Dim strSSecurity As String
strSSecurity = Me.SS
strSQL = "Update Employees Set employees.PositionID = '',employees.jobcode = '' Where employees.ss = strSSecurity"
DoCmd.RunSQL strSQL
End Sub

It is supposed to take the current Social Security number from the form and match it against the employees table. Once it finds the matching record it should update the PositionID and JobCode fields to '' (empty string)
But it doesn't
Anyone with any ideas?
Thanks
Dan

View 2 Replies View Related

Passing Date Fields From Form To Access ADP Views

Jul 20, 2005

I have a whole bunch of forms that have an unbound StartDate and anEndDate field that I have used in MSAccess MDB databases as parametersin queries (ie tblEvent.StartDate > Forms!myFormName.StartDate.)So, now I'm migrating this beast over to and ADP/SQL Server projectusing Views and Procedures.How do I pass the value in Forms!myFormName.StartDate to a Procedureand get something that looks like:If tblEvent.StartDate > Forms!myFormName.StartDate then ...Any help is GREATLY appreciated. This is a major problem before I canmove ahead with this beast!lq