Question Locked

MS Access VBA Sub Routine (Clueless)

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Was hoping someone could help me out a sec, doing some database work and basically trying to shorten some code. The example I'll use is one of the smaller bits to make it easy to explain what I'm trying to achieve.

Code:

Private Sub qst_11_Change()

If qst_11 = "Other" Then
Me.Form!qst_11_other.Visible = True
Else
Me.Form!qst_11_other.Visible = False
End If

End Sub

Essentially all this does is say that on qst_11 changing value (its a combo box), if "Other" is selected I want it to show qst_11_other (a text box), if its any other value I want qst_11_other to remain hidden.

That all works fine, but for every combo box I want to do this with, its a copy and paste and mistakes can be made. So what I wanted to do was turn it into a VBA Sub procedure. I came up with something along the following lines in a module but clearly my syntax is totally wrong and I don't know how to call the sub or pass the required variables to it:

Sub HideMe(varFieldNumber As String, varFieldCondition As String)

If qst_varFieldNumber$ = varFieldCondition$ Then
Me.Form!qst_varFieldNumber$_other.Visible = True
Else
Me.Form!qst_varFieldNumber$_other.Visible = False
End If

End Sub

I would then assume I would just be able to write the following in a form to call the routine and pass the variables:

call HideMe(11,other)

However as you might guess, it throws up nice errors, specifically with relation to the variable syntax, it doesn't like varFieldNumber$, I've tried $varFieldNumber and a number of other things but it doesn't like it. Any ideas would be most appreciated.

Cheers

4 replies

by tony hopkinson

Sub HideMe(varTestfield As ????,varOptField as ????, varFieldCondition As String)

If varfield.value = varFieldCondition$ Then
varOptField.Visible = True
Else
varOptField.Visible = False
End If

End Sub

Can't remember the datatype for ???
Then you'd call it with
HideMe(Me.Form!qst11,Me.Form!qst11_Other,"Other")

Your code is basically attempting to contruct a variable name.

If there is a findcontrolbyname method on the form you could do something like
Me.Form.ControlByName("qst" + varparam) = 'testvalue'
Me.Form.ControlByName("qst" + varparam + "_other").visible = false.

HTHs

by smithyguy

I understand what you've shown and have tried it. The only error that comes up is with:

HideMe(Me.Form!qst11,Me.Form!qst11_Other,"Other")

I have put this under a change event for a combo box, when I put that in a compile error displays saying "expected: ="

Clearly I'm a novice at this stuff, any ideas what might cause that error?

Cheers for your help, its appreciated!

by tony hopkinson

a bit of the code is syntactically incorrect.

So me too. :D

Is it just highlighting the HideMe line. Comment it out and see if anything else comes up, you get this sort of error when you've missed an 'end' out or that sort of thing.

This discussion is closed to new replies.