passing parameter in query without promp
in [Forms]
|
From: spieters via AccessMonster.com on 20 Jan 2010 12:26 Dear All, I'm trying to create a query using as parameter a field on the form named me. [Naam Vennoot]. I'm getting , however, a prompt screen to enter the parameter {Naam Vennoot]. How can I create/display a query without showing the prompt screen? Here follows the coding. Private Sub Soortmij_BeforeUpdate(Cancel As Integer) If Me.Soortmij = "liquidatie" Then Dim dbs As Database Dim strSQL As String Dim strQueryName As String Dim qryDef As QueryDef Set dbs = CurrentDb strQueryName = "Qryupdateliquid" dbs.QueryDefs.Delete strQueryName 'qryDef.Delete ("Qryupdateliquid") 'dbs.QueryDefs.Delete ("Qryupdateliquid") strSQL = "SELECT vervolgdoosnr.doosnummer, vervolgdoosnr.[Naam vennoot], vervolgdoosnr.Soortmij,vervolgdoosnr.[tijdsduur archivering], vervolgdoosnr. [destroy datum] " & _ "FROM vervolgdoosnr " & _ "WHERE (((vervolgdoosnr.[Naam vennoot]) = '" & Me.Naam_vennoot & " ')) " & _ "ORDER BY vervolgdoosnr.doosnummer, vervolgdoosnr.Soortmij, Vervolgdoosnummer. [Naam vennoot] ;" Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL) DoCmd.OpenQuery "Qryupdateliquid" dbs.Close qryDef.Close Set qryDef = Nothing End If End Sub Thanks in advance for your help Stella -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1
From: Stefan Hoffmann on 20 Jan 2010 12:43 On 20.01.2010 18:26, spieters via AccessMonster.com wrote: > I'm trying to create a query using as parameter a field on the form named me. > [Naam Vennoot]. I'm getting , however, a prompt screen to enter the parameter > {Naam Vennoot]. It may be a typo or the parentheses in the WHERE clause, try "WHERE [vervolgdoosnr].[Naam vennoot] = '" & _ Replace (Me.Naam_vennoot, "'", "''") & "' " & _ instead of yours. btw, there was an extra space in your condition. mfG --> stefan <--
From: J_Goddard via AccessMonster.com on 20 Jan 2010 13:16 Hi - You are actually not creating a parameter query; you are building the SQL for the query, and including the value of the form field Me.Naam_vennoot in the SQL. From the SQL you have shown, the probable cause for the prompt to enter a parameter is that the table vervolgdoosnr does not contain a field called [Naam vennoot]. HTH John spieters wrote: >Dear All, > >I'm trying to create a query using as parameter a field on the form named me. >[Naam Vennoot]. I'm getting , however, a prompt screen to enter the parameter >{Naam Vennoot]. > >How can I create/display a query without showing the prompt screen? Here >follows the coding. > >Private Sub Soortmij_BeforeUpdate(Cancel As Integer) > >If Me.Soortmij = "liquidatie" Then > >Dim dbs As Database >Dim strSQL As String >Dim strQueryName As String >Dim qryDef As QueryDef > >Set dbs = CurrentDb >strQueryName = "Qryupdateliquid" > >dbs.QueryDefs.Delete strQueryName > >'qryDef.Delete ("Qryupdateliquid") >'dbs.QueryDefs.Delete ("Qryupdateliquid") > >strSQL = "SELECT vervolgdoosnr.doosnummer, vervolgdoosnr.[Naam vennoot], >vervolgdoosnr.Soortmij,vervolgdoosnr.[tijdsduur archivering], vervolgdoosnr. >[destroy datum] " & _ >"FROM vervolgdoosnr " & _ >"WHERE (((vervolgdoosnr.[Naam vennoot]) = '" & Me.Naam_vennoot & " ')) " & _ >"ORDER BY vervolgdoosnr.doosnummer, vervolgdoosnr.Soortmij, Vervolgdoosnummer. >[Naam vennoot] ;" > >Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL) >DoCmd.OpenQuery "Qryupdateliquid" > > dbs.Close > >qryDef.Close > >Set qryDef = Nothing > >End If > >End Sub > >Thanks in advance for your help > >Stella -- John Goddard Ottawa, ON Canada jrgoddard at cyberus dot ca Message posted via http://www.accessmonster.com
From: spieters via AccessMonster.com on 20 Jan 2010 16:52 Halo Stefan, If tried it out but I keep on getting a syntax error. This is the coding I'm getting the error in. strSQL = "SELECT vervolgdoosnr.doosnummer, vervolgdoosnr.[Naam vennoot], vervolgdoosnr.Soortmij,vervolgdoosnr.[tijdsduur archivering], vervolgdoosnr. [destroy datum] " & _ "FROM vervolgdoosnr " & _ "WHERE ((([vervolgdoosnr].[Naam vennoot]) = '" & (Me.Naam_vennoot & "') "'") & "' " & _ "ORDER BY vervolgdoosnr.doosnummer, vervolgdoosnr.Soortmij, Vervolgdoosnummer. [Naam vennoot] ;" Stefan Hoffmann wrote: >> I'm trying to create a query using as parameter a field on the form named me. >> [Naam Vennoot]. I'm getting , however, a prompt screen to enter the parameter >> {Naam Vennoot]. >It may be a typo or the parentheses in the WHERE clause, try > >"WHERE [vervolgdoosnr].[Naam vennoot] = '" & _ > Replace (Me.Naam_vennoot, "'", "''") & "' " & _ > >instead of yours. > >btw, there was an extra space in your condition. > >mfG >--> stefan <-- -- Message posted via http://www.accessmonster.com
From: Stefan Hoffmann on 21 Jan 2010 04:59
hi, On 20.01.2010 22:52, spieters via AccessMonster.com wrote: > "WHERE ((([vervolgdoosnr].[Naam vennoot]) = '"& (Me.Naam_vennoot& "') "'") > & "' "& _ First of all, drop the parentheses as they are not necessary in this simple condition. Also use an table alias to write a shorter SQL statements and use a better formatting style. strNaamVennoot = "'" & Replace(Me.Naam_vennoot, "'", "''") & "'" strSQL = "SELECT V.doosnummer, V.[Naam vennoot], " & _ "V.Soortmij, V.[tijdsduur archivering], " & _ "V.[destroy datum] " & _ "FROM vervolgdoosnr V " & _ "WHERE V.[Naam vennoot] = " & strNaamVennoot & " " & _ "ORDER BY V.doosnummer, V.Soortmij, V.[Naam vennoot];" mfG --> stefan <-- |