ACC1x: Error Messages When Concatenating Variables or Controls |
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
When you are concatenating variables or controls in a function or
CreateDynaset method, you may receive one of the following error messages:
Can't bind name '<argument>'
-or-
Type Mismatch
-or-
1 parameter expected only 0 supplied
These error messages can result if one of the following is true:
- You have incorrectly declared the data type of the argument being
passed to the function.
- You are including a variable or control name in the function syntax
and Microsoft Access is unable to recognize the data type.
- You are using incorrect syntax to concatenate the variables.
This article presents examples in which these errors occur and explains
how to correct them.
MORE INFORMATION
"Can't bind name '<argument>'Error message
You receive the "Can't bind name" error message when you concatenate a
variable or control that has a String data type in a method or function as
a Numeric data type. For example, the following function produces the
"Can't bind name 'Davolio'" error message:
Call the function as follows: MyFunction ("Davolio")
Function MyFunction (DataToFind As String)
Dim MyDB As Database, myset As Dynaset
Set MyDB = CurrentDB()
Set myset = MyDB.CreateDynaset("Employees")
Myset.FindFirst "[Last Name]= " & DataToFind
End Function
The correct syntax for the last line of code above is as follows:
MySet.FindFirst "[Last Name] = '" & DataToFind & "'"
"Type Mismatch" Error Message
You receive the "Type Mismatch" error message when you concatenate a
variable or control that has a Numeric data type in a method or function as
a String data type. For example, the following function produces the "Type
Mismatch" error message:
Call the function as follows: MyFunction (3).
Function MyFunction (NumberToFind As integer)
Dim MyDB As Database, MySet As Dynaset
Set MyDB = CurrentDB()
Set MySet = MyDB.CreateDynaset("Employees")
MySet.FindFirst "[Employee ID] = '" & NumberToFind & "'"
The correct syntax for the last line of code above is as follows:
MySet.FindFirst "[Employee ID] = " & NumberToFind
Keep the following requirements in mind:
"1 Parameters Were Expected, But Only 0 Were Supplied" Error Message
You may receive this error message when you use the CreateDynaset method in
Access Basic on an existing query. If the query is a parameter query, you
need to explicitly declare the parameter and its data type and set the
parameter value for that query in the function. The following sample code
generates the error message when Query1 has the parameter "[Enter a Name]"
in the Criteria for the Last Name field:
Function TestQP ()
Dim MyDB As Database, MySet As Dynaset
Set MyDB = CurrentDB()
Set MySet = MyDB.CreateDynaset("Query1")
Debug.Print MySet![First Name]; Tab(10); MySet![Last Name]
End Function
When you refer to the parameter query, the correct syntax is as follows:
Function TestQP ()
Dim MyDB As Database, MySet As QueryDef, MyDyna As Dynaset
Set MyDB = CurrentDB()
Set MySet = MyDB.OpenQueryDef("Query1")
MySet![Enter a Name] = "Davolio"
Set MyDyna = MySet.CreateDynaset()
Debug.Print MyDyna![First Name]; Tab(10); MyDyna![Last Name]
MyDyna.Close
MySet.Close
End Function
The same error message may appear when you concatenate a variable in the
SQL SELECT statement of a CreateDynaset method. A syntactically correct
example is as follows.
NOTE: In the following example, an underscore (_) is used as a line-
continuation character. Remove the underscore from the end of the line when
re-creating this example.
Set MySet = MyDB.CreateDynaset("SELECT * FROM Employees WHERE _
[Employee ID] = " & Forms!Form1!Field0 & ";")
This SELECT statement points to a control on a form for the WHERE clause.
[Employee ID] is a numeric field type and the contents of the control are
numeric.
Additional query words:
parameters
Keywords : kberrmsg kbprg PgmObj
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbinfo