Help on functions and variables in VBA 1

[BotCow]

Okay you VBA experts can probably answer this one in a second and a half.
1)I have a private subroutine that basically takes whatever the user selects in a combo drop down menu and attaches "tbl" + cmbmenuselection. So I have a dropdown menu with "Books", "Trenches", "Dogs". If the user selects "Books" then the sub assigns a variable tblStr to tblBooks. I have another private subroutine that I want that value to go to, how does that work? If I use the variable tblStr does it get recognized? I'm thinking no because in C++/Java, you can only utilize instance or global variables that way.

2) Another question: I have a function SplitString(inString As String) and when I pass in a text box from a form (Me.txtBox) it prompts me to enter a parameter. Here's what I'm trying to use it for. I have a text box in a form and when I enter "dog cat mouse" I want it to process into an SQL statement that reads LIKE dog OR LIKE cat OR LIKE mouse and search for three distinct keywords. The function splitstring does all the formatting for me, but when I try the syntax SplitString(Me.txtBox), it doesn't work, what do I pass in for that? Or do I assign txtBox to a string and then pass that string in?

Sorry if it's a little confusing, but this code's been hammering at my head all day.. I really need to take a VBA class for this hehe. Thanks Tek-Tips people.

 

[markgrizzle]

1) You could make the original private subroutine public, and assign it to a variable in the 2nd routine.

2) I'm not sure which part isn't working. The sql? If so, you'll need to include quotes and asterisks.

"...Like '*dog*' Or Like '*cat*' Or Like '*mouse*'..."

If the function isn't returning a value, try setting a breakpoint in the function F9, and stepping though the code F8 to find out what's going on.

 

[willir]

BotCow

As per markgrizzle, you can make the variable globle to the form or database. Review life or scope of variables for this issue in help or in an Access or VB book.

Another way to resolve this is to pass the variable in the called statement.

Private first_func_afterupdate()
dim strTable as String

'logic already doen to create string
strTable = "tbl" & cmbmenuselection ' not + sign

'now call the second function - 2 ways to do this
second_func strTable

end sub

Private second_func (strTable)

'something magical happens to strTable

end sub

We all have our own styles, but I prefer to limit public variables to only when neccessary.

-------
Second issue...

This is much tougher than you may realize because you have to parse out each word. This will depend greatly on any rules you apply, i.e. separate words with commas or spaces, etc.

The latter part of the process may be fairly easy ... use the "IN (...)" option for your select clause.

Here is my crack at it from the seat of my pants -- there are other coders who may have much better solutions.

Private txtbox_after_updaet()

dim intLen as integer, intX as integer
dim strQ as string, strIN as string, strRemainder as string, strTemp as string, strParse as string

if not isnull(me.txtbox) then

'faster to work with a variable than item on form
strRemainder = me.txtbox

'get length of string
intLen = len(strOriginal)

'define parse variable -- using space for example
strParse = " "

'set up for loop
intX = 1
strIN = ""
strQ = chr$(34)

while intX > 0

intX = intstr(strRemainder, strParse)
if intX > 0 then

strIN = strQ & left(strRemainder, intX-1) & strQ & ", " & strIN
strRemainder = right(strRemainder, intLen - intX)
intLen = len(strRemainder)

end if

wend

if len(strIN) = 0 then
'one word
strIN = "where fldAnimal = " & strQ & strRemainder & strQ
else
'drop leading ", "
strIN = right(strIN, len(strIN)-2)
strIN = "where fldAnimal in (" & strIN & &quot"
end if


'now add logic for executing select clause


end sub

Note: since I typed the code from scratch without testing, you may have to do some debugging. As you can see, parsing is a tad complex. Hopefully you get the idea.

Richard