pass arguments to function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am passing values for search criteria, query and sort order for the query
via this code:

Public Sub QuickSearch(strSearch As String, qry As String, ord As String)

When I try to pass values to the function using this line, I get a syntax
error.

QuickSearch("Me.ID", "vwSMKPatientLookUp", "nm")

The message says there is a "=" sign missing. I've tried it with and without
the quote marks. Me.ID is a string value the user types in.

Thanks for looking at this.

sam
 
Without seeing the details of the QuickSearch routine, it's difficult to
know what your problem is.

The use of "Me.ID" looks suspect to me: that's going to pass the literal
string "Me.ID" to the function, rather than whatever value that field
contains.

It's entirely possible that qry is supposed to be an actual SQL statement,
not the name of a query.

Are you sure that QuickSearch actually works?
 
QuickSearch was working fine until I added the last two arguments. It used to
be QuickSearch(Me.Id) and that works fine. I wanted to vary the underlying
query used and here is where those new arguments appear:

If sWhereClause <> "" Then
strSQL = "SELECT v.* FROM " & qry & " v WHERE " & sWhereClause & _
" ORDER BY " & nm & ";"
CurrentDb.QueryDefs("pqPCriteria").SQL = strSQL

End If

I could abort the whole thing and just write individual SELECT statements if
that is better in the long run (maintenance, performance, etc).
Thanks for the help.

Sam
 
How are you assigning a value to sWhereClause? Also, do you assign the SQL
of the query if sWhereClause is blank?

FWIW, while it's not what's causing your problems, there's no real reason
for the v. in v.*, the v before the word WHERE nor the terminating
semicolon. Also, just in case you ever use the (poor, in my opinion)
possibility of having embedded blanks in your query or field names, you
might put square brackets, just in case:

If Len(sWhereClause) > 0 Then
strSQL = "SELECT * FROM [" & qry & "] WHERE " & sWhereClause & _
" ORDER BY [" & nm & "]"
CurrentDb.QueryDefs("pqPCriteria").SQL = strSQL
End If
 
Believe it or not, it finally worked when I went with:

QuickSearch Me.ID, "qry", "nm"

Basically took away the parentheses. I must have had a typo somewhere. I
want to say thanks for what a big difference you all make, MVPs and others
that contribute. After over 6 months of banging my head against seemingly
insurmountable obstacles, I now have a working application. The significant
irritations have finally been resolved. Although others will inevitably
appear, I think the biggies are done. You guys have made my day.
Thanks!!!
Sam

Douglas J. Steele said:
How are you assigning a value to sWhereClause? Also, do you assign the SQL
of the query if sWhereClause is blank?

FWIW, while it's not what's causing your problems, there's no real reason
for the v. in v.*, the v before the word WHERE nor the terminating
semicolon. Also, just in case you ever use the (poor, in my opinion)
possibility of having embedded blanks in your query or field names, you
might put square brackets, just in case:

If Len(sWhereClause) > 0 Then
strSQL = "SELECT * FROM [" & qry & "] WHERE " & sWhereClause & _
" ORDER BY [" & nm & "]"
CurrentDb.QueryDefs("pqPCriteria").SQL = strSQL
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



smk23 said:
QuickSearch was working fine until I added the last two arguments. It used
to
be QuickSearch(Me.Id) and that works fine. I wanted to vary the underlying
query used and here is where those new arguments appear:

If sWhereClause <> "" Then
strSQL = "SELECT v.* FROM " & qry & " v WHERE " & sWhereClause & _
" ORDER BY " & nm & ";"
CurrentDb.QueryDefs("pqPCriteria").SQL = strSQL

End If

I could abort the whole thing and just write individual SELECT statements
if
that is better in the long run (maintenance, performance, etc).
Thanks for the help.

Sam
Click to expand...
Click to expand...
 
VB syntax is funny. Normally you mustn't use parentheses round the
argument list of a sub procedure. Try

QuickSearch Me.ID, "vwSMKPatientLookUp", "nm"

This asssumes Me.ID is a control or field on the form and the other two
arguments are string literals. If they're variables, omit the quotes.
 
Aargh. Caught up on one of the basics!

Yeah, you either don't use parentheses when calling a Sub with more than one
parameter, or you use the Call statement:

Call QuickSearch (Me.ID, "qry", "nm")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



smk23 said:
Believe it or not, it finally worked when I went with:

QuickSearch Me.ID, "qry", "nm"

Basically took away the parentheses. I must have had a typo somewhere. I
want to say thanks for what a big difference you all make, MVPs and others
that contribute. After over 6 months of banging my head against seemingly
insurmountable obstacles, I now have a working application. The
significant
irritations have finally been resolved. Although others will inevitably
appear, I think the biggies are done. You guys have made my day.
Thanks!!!
Sam

Douglas J. Steele said:
How are you assigning a value to sWhereClause? Also, do you assign the
SQL
of the query if sWhereClause is blank?

FWIW, while it's not what's causing your problems, there's no real reason
for the v. in v.*, the v before the word WHERE nor the terminating
semicolon. Also, just in case you ever use the (poor, in my opinion)
possibility of having embedded blanks in your query or field names, you
might put square brackets, just in case:

If Len(sWhereClause) > 0 Then
strSQL = "SELECT * FROM [" & qry & "] WHERE " & sWhereClause & _
" ORDER BY [" & nm & "]"
CurrentDb.QueryDefs("pqPCriteria").SQL = strSQL
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



smk23 said:
QuickSearch was working fine until I added the last two arguments. It
used
to
be QuickSearch(Me.Id) and that works fine. I wanted to vary the
underlying
query used and here is where those new arguments appear:

If sWhereClause <> "" Then
strSQL = "SELECT v.* FROM " & qry & " v WHERE " & sWhereClause &
_
" ORDER BY " & nm & ";"
CurrentDb.QueryDefs("pqPCriteria").SQL = strSQL

End If

I could abort the whole thing and just write individual SELECT
statements
if
that is better in the long run (maintenance, performance, etc).
Thanks for the help.

Sam

:

Without seeing the details of the QuickSearch routine, it's difficult
to
know what your problem is.

The use of "Me.ID" looks suspect to me: that's going to pass the
literal
string "Me.ID" to the function, rather than whatever value that field
contains.

It's entirely possible that qry is supposed to be an actual SQL
statement,
not the name of a query.

Are you sure that QuickSearch actually works?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am passing values for search criteria, query and sort order for the
query
via this code:

Public Sub QuickSearch(strSearch As String, qry As String, ord As
String)

When I try to pass values to the function using this line, I get a
syntax
error.

QuickSearch("Me.ID", "vwSMKPatientLookUp", "nm")

The message says there is a "=" sign missing. I've tried it with and
without
the quote marks. Me.ID is a string value the user types in.

Thanks for looking at this.

sam
Click to expand...
Click to expand...
Click to expand...
 
Just as a matter of practice to make it all clear to me and to those who are
unfortunate enough to have to work with my code at a later date, I always use
the Call statement to differentiate between a call to a sub and a function.
 
Back
Top