WITH Statement, An Easy One for Rep

[BrainExplodingFromCoffee]

Hi All,



I must be going nuts or have forgotten something. This is an easy one. I'm refactoring my code. This was the code before the refactoring

Dim list as String

list.Value = Form_Create.txtBox
list.Value = CStr(List)
list.Value = "[Deficiency Number] " & "IN (""" & list & """)"

and then when I refactor my code and apply some WITH Statements it looks like this:

Dim list as String

With list
     .Value = Form_Create.txtBox
     .Value = CStr(List)
     .Value = "[Deficiency Number] " & "IN (""" & list & """)"
End With

But, when I refactor my code I get a "Compile Error" message that says that the WITH can only be used with user-defined types, objects and variants...
And, I'm getting random errors with other bits of code that I refactored. But, those I will get to later. One thing at a time.

Can anyone help me out? Googling this messes up the searches because of all the common words.

Thanks,
BrainExplodingFromCoffee

[CJ_London]

try

with list.properties

but not sure what you are trying to do, only the last 'value' will be assigned, the others will be overwritten

and your original code could just have been written as

list= "[Deficiency Number] IN ('" & Form_Create.txtBox & "')"

or even

list= "[Deficiency Number] IN ('" & txtBox & "')"

if this code is in Form_Create


in line with your example, this assumes that txtbox has been entered correctly by the user and is alpha not numeric i.e.

abc','def','ghi

which concerns me since Deficiency Number implies numeric

If the code works at the moment you would be better spending your time removing spaces from field names

[BrainExplodingFromCoffee]

Hi Ajax,

I was trying to collapse my code for optimization. But, I made a mistake in my original post. the correct code is this:

Dim list as String

list = Form_Create.txtBox
list = CStr(List)
list = "[Deficiency Number] " & "IN (""" & list & """)"

when trying to block it within a WITH, I was used to using excel VBA. So, after some research it appears to be the object reference library that Access uses. I just can't do that in Access. I found this article:

http://www.cimaware.com/resources/article_43.html

Thanks for the help!
coffeemakesbrainexplode

[CJ_London]

Not sure if you read my post properly

I was trying to collapse my code for optimization

I suggested

and your original code could just have been written as

list= "[Deficiency Number] IN ('" & Form_Create.txtBox & "')"

or even

list= "[Deficiency Number] IN ('" & txtBox & "')"

if this code is in Form_Create

[BrainExplodingFromCoffee]

Hi Ajax,

Yep, I read your post. I just wanted to manipulate my string variable within a WITH block statement. The CStr is just to ensure that value is a text value. It might be redundant, but this was more about binding the commands with the WITH statement. :T

Thanks for your help.
Brain + Coffee = NormalStateOfLife