CODEWITHSUNDEEP
sql-serverms-accessvba
toofaced
toofaced

Reputation: 151

How to pass a parameter with space in its name to store procedure in vba access?

I am using vba access and SQL server. I have a form in which there is a field named Material ID with a space. Now, I am trying to pass this in the Store Procedure but I am not sure how do I actually pass it. Code I have been working on is: 

If frm.Tag Then
    strFilter = "usp_MovePending '" & UserStr & "'"
    strFilter = strFilter & "," & frm!MaterialID  

    DB.QueryDefs("qryMovePending").Connect = SQLConnectString
    DB.QueryDefs("qryMovePending").ReturnsRecords = False
    DB.QueryDefs("qryMovePending").sql = strFilter
    DB.QueryDefs("qryMovePending").Execute dbFailOnError
End If

In above code, I used frm!MaterialID without space which obviously gave me an error saying Microsoft access can't find the field 'MaterialID' referred to in your expression. And when I keep [frm!Material ID] with space, it doesn't return anything. I also tried keeping single quote around it but it returned single quote with no value. Please help.

Upvotes: 1

Views: 1944

Answers (3)

Minty
Minty

Reputation: 1626

It's almost certainly added a underscore to it - try using the Me.Material_ID or Me.[Material ID] to refer to the actual forms control name. Value is the default property

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

I'm not very familiar with the Access object model, but in VBA/VB6 the bang notation foo!bar is shorthand for the explicit notation:

foo("bar").Value

In other words, frm!Foobar is shorthand for this:

frm("Foobar").Value

So you would do:

frm("Material ID").Value

Upvotes: 0

Arockia Nirmal
Arockia Nirmal

Reputation: 777

Try with the square bracket as delimiter ...

frm![Material ID]

Upvotes: 1

Related Questions