Function input is name of string variable needing evaluating (1 Viewer)

[WJB]

Hi. So this is probably an easy one for a lot of you but it's killing me! I've found lots of similar-ish questions but can't get any answers to work :banghead:

SV = InStr(IdStrPart1, intID)
works for my variables. Both terms within InStr are names of variables - in this case they are both the number 5. (If it makes any difference, IdStrPart1 is actually a string and intID is an integer)

I want to make SV a function where I pass it both of the terms (x, intID). The second term is fine but I'm having pblms with the first. The issue I believe is because I'm passing it the string of a name of a variable and it can't cope. I suspect I need eval() or similar, but I'm stuck.

Using the immediate window I have found that:
"InStr(" & x & ", intID)" gives me InStr(IdStrPart1, intID)
and
InStr(IdStrPart1,intID) gives me the answer 1 (which I want )
but,
eval("InStr(" & x & ", intID)") gives me nothing as it "can't find the name IdStrPart1"

What am I missing? (sorry for the rambling question, this has fried my brain!)

 

[jdraw]

More info on InStr here.
I recommend you give us some context, and perhaps a description and example, so we clearly understand your issue.

 

[Cronk]

function sv(x as string, intID as integer) as integer
sv= instr(x, intID)
end function

? sv("00123456",4)
6

 

[WJB]

That doesn't work Cronk, it gives 0 but should be 5.
Will try jdraw, trying not to confuse myself even more!

So, this all started because I have a (continuous) form that displays product data. A lot of the info is repeated over each row, ie brand name. I am trying to make my form look like you could in a report - have just the first occurrence of each brand showing etc.

On my form I have the text box that has the brand in it (Part1) and another box, (at the mo called text0) that calls my 'to be corrected function'. Part1 is conditionally formatted so that if text0 > 0 it can be seen but otherwise it can't.
When the form loads up, it calls a function that finds the ID numbers of the records with the first occurrence of each brand and remembers this in IdStrPart1 (so for eg IdStrPart1 could be = "5,7,9")
Once access has loaded the form it tries to fill in the text boxes and so it sees text0 that calls my to-be-function.
In text0 I have the control source as
=SV("IdStrPart1",[Product_ID_3])
where Product_ID_3 is the primary key of my records.
The idea is that each record with a matching ID of the ones in IdStrPart1 will be formatted to be shown.

If I just have =SV([Product_ID_3]) I can use
Function SV(intID)
SV = InStr(IdStrPart1, intID)
End Function

and it all works fine. What I am wanting to do is to be able to pass the function the word IdStrPart1 so that I can repeat this exercise for other repeated data, such as colour etc. ie, when the Form loads it will find IdStrPart1 and IdStrPart2 (can do that) and then I have text0 and text1 both calling the same function but with different arguments depending upon which IdStr I want it to use.

Does that make any more sense?

 

[jdraw]

??? Did not clarify for me (but others may understand). I think you're telling us how you're doing something. We really need the what(something) in simple business terms.

You can post your database if you use zip format.

 

[WJB]

Bugger! Will try again!!

How do I make

Function SV(x, intID)
SV = InStr(x, intID)
End Function

work? where x is a string which is the name of another string eg x ="IdStrPart1" and IdStrPart1 = "5, 7, 9" (I can't just pass "5, 7, 9" in, I have to use the name)
and intID is a simple number

Thx

 

[jdraw]

What exactly is IdStrPart1?
Where does it get its value from?
Is it a field in your table/query?

 

[WJB]

when the form loads, it creates IdStrPart1. It's made by going through the recordset and pulling out the ID numbers of certain records. Idea is that I'll do that for IdStrPart1, IdStrPart2, IdStrPart3 etc where each set of ID numbers remembered is different.

Private Sub Form_Load()

' find when the part1 (brand - description) changes
IdStrPart1 = find_change(Me.Recordset.Clone, "part1")
IdStrPart1 = Mid(IdStrPart1, 2)
End Sub


Function find_change(rs, x As String)

rs.MoveFirst
Do While Not rs.EOF
VarStr = rs(x)
IdStr = IdStr & "," & rs!Product_ID_3
rs.FindNext x & "> '" & VarStr & "'"
If rs.NoMatch Then Exit Do
Loop
find_change = IdStr

End Function


On the form there's a text box that then wants the result of the SV function. There'll be one text box for each of the IdStrPart1, IdStrPart2, IdStrPart3 created... rather than create a new SV function for each of these, I wanted to be able to pass the term "IdStrPart1" to it.

So it's created by a function and remembered (have declared it outside of the functions and that seems to do it) and I want to use it in another function. But I wanted to be able to call it dynamically in this second function.

 

[jdraw]

Sounds to me that if you're processing the recordset, you could use the logic to select appropriate records or parts thereof and do your processing.

I'm not sure why you need IdStrPart1, IdStrPart2, IdStrPart3, but I don't know the specific of your manipulations.
If you're processing a recordset, you could store the required ID's in an array, and use a loop to process the array???

 

[WJB]

Ha, solved it
So your last reply got me thinking - I'm clearly crap at this explaining game!

Did some thinking (after all, what is going to bed for!) and realised that my question pretty much boiled down to "how do I dynamically reference a variable created in VBA"

Seeing as that was what I wanted to do I figured I'd try it with a table as I know I can do that. So I ended up with:

Private Sub Form_Load()
DoCmd.DeleteObject acTable, "Name_Changes"
Call find_change(Me.Recordset.Clone, "part1", "part2_final")
End Sub

Where texts at the end are what I was calling IdStrPart1 IdStrPart2 etc

and

Public Sub find_change(rs, ParamArray intVars())
DoCmd.SetWarnings False
DoCmd.RunSQL "CREATE TABLE Name_Changes (PK CHAR);"

For Each x In intVars
DoCmd.RunSQL "ALTER TABLE Name_Changes ADD COLUMN " & x & " Double;"

rs.MoveFirst

Do While Not rs.EOF
VarStr = rs(x)

DoCmd.RunSQL "INSERT INTO Name_changes(" & x & ") SELECT " & rs!Product_ID_3 & " AS Expr1;"

rs.FindNext x & "> '" & VarStr & "'"
If rs.NoMatch Then Exit Do
Loop
Next x
DoCmd.SetWarnings True
End Sub


So I deleted a table, (I might actually move that to the 'on close' part and then passed my strings to the other sub. It created a table and used my strings to create somewhere to store my variables.

My textbox on my form no longer has to call a function that uses InStr to see if the record identifier exists in the 'chosen record' list (ie IdStrPart1) but instead simply has

=DLookUp("part1","Name_changes","part1=" & [Product_ID_3])

as its control source. (with one box per "part1" as before)
So now if I add another IdStrPart3 for eg, I just add it to the strings in the Form Load box and add my extra textbox and the rest is done dynamically.

Clear as mud ?!

Thanks for sticking with me and helping me think my way out of this