Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
RC
Starting Member
8 Posts |
Posted - 2005-01-13 : 00:45:08
|
I just changed my Access 2002 database to a SQL Server ADP project. Ihad a form where the user entered a value into a text box and when acommand button on the form was clicked a Report was opened. The reportsrecord source is a query. The query uses the value from the form textbox to restrict the query.Table name = EggsTableone of the columns in the table is named: EggColorForm name = EggColorFormForm text box name = ColorTextBoxThis sql worked for the query before I converted to SQL:SELECT EggsTable.EggColorFROM dbo.EggsTableWHERE (((EggsTable.EggColor)=[Forms]![EggColorForm]![ColorTextBox]));This no longer works. Can I change the syntax somehow to get this towork? I tried dropping the brackets around the word "Forms", I trieddropping all the square brackets, etc., nothing worked.When I make the change in the design view the editor changes the code to a the letter N in front of the [Forms], like:WHERE "EggColor = N'[Forms]![EggColorForm]![ColorTextBox]'I also tried just opening the report with the report's Server Filterproperty set to:EggColor=N'Forms.EggColorForm.ColorTextBox'I tried using the Report's open event to pass the form value directlyto the report. I tried setting a variable from the text box value onthe form. So far, nothing works. Any ideas? |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-13 : 02:59:07
|
You can build RecordSource for the report on the fly:Private Sub Report_Open(Cancel As Integer)Me.RecordSource = "SELECT EggColor FROM dbo.EggsTable " & _"WHERE EggColor='" & Forms!EggColorForm!ColorTextBox & "'"End Suboryou can rewrite the query as a stored procedure and assign it to the report in its Prop Sheet(along with Input Parameters: @EggColor varchar(10) = Forms![EggColorForm]![ColorTextBox])create procedure mp_EggColors @EggColor varchar(10)asSELECT EggsTable.EggColorFROM dbo.EggsTableWHERE (((EggsTable.EggColor)=@EggColor)) |
 |
|
RC
Starting Member
8 Posts |
Posted - 2005-01-13 : 17:55:32
|
THANK YOU !!!!I cannot say how grateful I am for your help. Thank you for sharing your knowledge. I did not even know this Forum existed until I did a Google search for an answer to my question, now I'm a big fan of this site. I spent 14 hours (really) trying every which way to change the syntax around to make this work. I used the Report Open event and because my report shows Description and EggColor it ended up like this:Me.RecordSource = "SELECT , Description, EggColor FROM dbo.InventoryTable WHERE EggColor='" & Forms!EggColorForm!ColorTextBox & "'" |
 |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-14 : 06:29:09
|
You are welcome, RC! |
 |
|
|
|
|