passing parameter in query without promp

[spieters via AccessMonster.com]

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

 

[Stefan Hoffmann]

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 <--

 

[J_Goddard via AccessMonster.com]

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

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

 

[spieters via AccessMonster.com]

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] ;"

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 <--

 

[Stefan Hoffmann]

hi,

"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 <--

 

[spieters via AccessMonster.com]

Hi Stefan,

Thank you very much. It's works just the way I wanted it to work. Thanks
again.

hi,

"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 <--