how do I go through a list of controls that need changing?

[galorin]

I am using the following function to turn a field red if a certain criteria is met:

Public Sub MakeRed(YesNo As Integer, Fieldname As Control)

If YesNo = 0 Then
With Fieldname
.SetFocus
.BackColor = 255
End With
End If

End Sub

It works fine if I call it as

Call Functions.MakeRed(0, Forms!People.Email)

but I am returning values from a database, and using this function to update multiple fields, as the code snippet below shows.

tick(1) = "Forms!VersionInfo.BBA99"
tick(2) = "Forms!VersionInfo.BBADetail"
tick(3) = "Forms!VersionInfo.BBS1"
tick(4) = "Forms!VersionInfo.BBS2"
tick(5) = "Forms!VersionInfo.BRE"
tick(6) = "Forms!VersionInfo.CullenB"

For X = 1 to 6

-----block of code to get resultset rst-----

Call Functions.MakeRed(rst.Fields(0), tick(X))
Next X

Calling it as such gives me an error of

Compile error:
ByRef argument type mismatch

which tells me that it's trying to pass a string to a control variable. I tried using Eval() but that function can only return string or numeric values. Not sure what to do next with it.

 

[Remou]

Try:

ticks=Split("BBA99,BBADetail,BBS1,BBS2,BRE,CullenB",",")

For i = 0 to Ubound(ticks)

-----block of code to get resultset rst-----

Call Functions.MakeRed(rst.Fields(0), Forms!VersionInfo(ticks(i)))
Next i

 

[galorin]

No luck. I either get a type mismatch with your code, or a statement that says

Access cannot find the field .BBA99 referred to in your expression

if I format it as so

Split(".BBA99,.BBADetail,.BBS1)

 

[ck1999]

Just for trouble shooting could you try

Call Functions.MakeRed(0, tick(X))

and let us know what you get

ck1999

 

[galorin]

Did that bit of troubleshooting before coming here. Trying Call Functions.MakeRed(0, tick(X))

gives me the

ByRef argument type mismatch

as well.

 

[ck1999]

You said you tried
Call Functions.MakeRed(0, Forms!People.Email) and this worked

have you tried
Call Functions.MakeRed(0, Forms!VersionInfo.BBA99)

if this works try them all down the list and see if one causes an error.

ck1999

 

[galorin]

The actual list is 33 items long, and will both change and grow over time. I've had to modify my MySQL Stored Procedure slightly, (needs more work though, returning wrong version numbers) as well as modify the MakeRed function, so it looks like this

Public Sub MakeRed(YesNo As Integer, version As String, Fieldname As Control)

If YesNo = 0 Then
With Fieldname
.SetFocus
.BackColor = 255
.Text = version
End With
Else
With Fieldname
.SetFocus
.Text = version
End With
End If

End Sub

There was a typing error somewhere in my ticks=split codethat made Access not happy.. at least that's what I'm assuming because it worked after re-typing things out. Now I just need top de-bug my code and get it to stop returning the last value for the first one. I either need to re-initialise a variable or muck about with my MySQL code.

Have you noticedthat some bits are 0-indexed and other bits in VBA are 1 indexed? Incredibly annoying.

 

[Remou]

Why are there stops in this statement?

Split(".BBA99,.BBADetail,.BBS1)

Did you test with the statement as I show it? That is:
ticks=Split("BBA99,BBADetail,BBS1,BBS2,BRE,CullenB",",")

In addition, I do not know why you have "Functions." , nor do I see any reference to a recordset in your code above. It would be as well to test with this statement:

[tt]Call MakeRed(0, Forms!VersionInfo(ticks(i)))[/tt]

 

[galorin]

The code for the resultset was not included because it is simple, and tested. All the really hard work is done in MySQL, not Access.

Well,I have got it working using the split() function. Not sure what was not working before. I do need to re-write the MySQL Stored Procedure, as it doesn't deal with NULL values yet, but if I run into something else not working, I'll post here.