Retyping reference to formfield in module

M

Maarten

I've got this annoying thing where the reference to formfields are not
recognised in the VBA module if I refer to them by their name only. I
need to include the reference to the form. Is there a workaround,
would save me a lot of typing?
Example:
I've got a form (frm_AAA) with two text fields str_Greet and Result.
Apart from the coding in the object area of the form I created a
separate module to keep things clear. In this module there a sub which
calculates the color of the field Result (Calculation)

The code I in the object area:
Private Sub str_Greet_AfterUpdate()
Calculation
End Sub

And the module needs to be coded as follow:
Sub Calculation()
If str_Greet = Hello Then frm_AAA.Resultaat.BackColor = RBG(0,255,0)
Else If str_Greet = Cheers Then frm_AAA.Resultaat.BackColor =
RGB(255,0,0)
Else: MsgBox("Sorry, I can't hear you")
End If
End Sub

While I rather type:
Sub Calculation()
If str_Greet = Hello Then Resultaat.BackColor = RBG(0,255,0)
Else If str_Greet = Cheers Then Resultaat.BackColor = RGB(255,0,0)
Else: MsgBox("DSorry, I can't hear you")
End If
End Sub

Althoug the difference is minimal for this code, it definately makes
sense on 500+ lines of code.

Any help welcome, thanks.
Maarten
 
R

roger

If the code in IN the Form, the you can use the Me keyword

Me.controlname

if not: then call the Forms! collection:

Forms!MyForm.MyControl.backcolor

hth
 
J

Jon Lewis

You could try passing the Form as a parameter to your Sub like this: (not
tested)

Sub Calculation(oForm As Form)
If str_Greet = "Hello" Then oForm.Resultaat.BackColor = RBG(0,255,0)
Else If str_Greet = "Cheers" Then oForm.Resultaat.BackColor =
RGB(255,0,0)
Else: MsgBox("Sorry, I can't hear you")
End If
End Sub

Then when you call the sub:
(Assuming that Resultaat is on the same form)

Private Sub str_Greet_AfterUpdate()
Calculation (Me)
End Sub

Also how does Sub Calculation() know what str_Greet is? Are you storing
this in a global variable? You might want to pass this value in the same way
too, e.g.:

Sub Calculation(strGreet As String, oForm As Form)
If strGreet = "Hello" Then oForm.Resultaat.BackColor = RBG(0,255,0)
Else If strGreet = "Cheers" Then oForm.Resultaat.BackColor =
RGB(255,0,0)
Else: MsgBox("Sorry, I can't hear you")
End If
End Sub

Private Sub str_Greet_AfterUpdate()
Calculation (str_Greet, Me)
End Sub
 
M

Maarten

Hi Jon, thanks for your help.

sorry for the errors in my start post, i wrote it directly into the
newsgroup window, without checking it in VBA.
Where I said 'Resultaat' it should have been 'Result' (Same word,
different language)
str_Greet is also a text field in the form. So the original
programming should have been 'frm_AAA.str_Greet' in stead of just
'str_Greet'
Thanks for your careful reading!

Unfortunately I can't get this to work. I tried the first option (with
only oForm as Form). It returns a type mismatch on "Calculation (Me)"
I gave it a try on using different types (Forms, Variant, ..., either
if it made sense to me or not) and replaced Me in "Calculation (Me)"
by frm_AAA, Form_frm_AAA and Me!.

Any clue?
 
M

Maarten

Hi Jon, thanks for your help.

sorry for the errors in my start post, i wrote it directly into the
newsgroup window, without checking it in VBA.
Where I said 'Resultaat' it should have been 'Result' (Same word,
different language)
str_Greet is also a text field in the form. So the original
programming should have been 'frm_AAA.str_Greet' in stead of just
'str_Greet'
Thanks for your careful reading!

Unfortunately I can't get this to work. I tried the first option (with
only oForm as Form). It returns a type mismatch on "Calculation (Me)"
I gave it a try on using different types (Forms, Variant, ..., either
if it made sense to me or not) and replaced Me in "Calculation (Me)"
by frm_AAA, Form_frm_AAA and Me!.

Any clue?
 
M

Maarten

Hi Jon, thanks for your help.

sorry for the errors in my start post, i wrote it directly into the
newsgroup window, without checking it in VBA.
Where I said 'Resultaat' it should have been 'Result' (Same word,
different language)
str_Greet is also a text field in the form. So the original
programming should have been 'frm_AAA.str_Greet' in stead of just
'str_Greet'
Thanks for your careful reading!

Unfortunately I can't get this to work. I tried the first option (with
only oForm as Form). It returns a type mismatch on "Calculation (Me)"
I gave it a try on using different types (Forms, Variant, ..., either
if it made sense to me or not) and replaced Me in "Calculation (Me)"
by frm_AAA, Form_frm_AAA and Me!.

Any clue?
 
M

Maarten

Hi Jon, thanks for your help.

sorry for the errors in my start post, i wrote it directly into the
newsgroup window, without checking it in VBA.
Where I said 'Resultaat' it should have been 'Result' (Same word,
different language)
str_Greet is also a text field in the form. So the original
programming should have been 'frm_AAA.str_Greet' in stead of just
'str_Greet'
Thanks for your careful reading!

Unfortunately I can't get this to work. I tried the first option (with
only oForm as Form). It returns a type mismatch on "Calculation (Me)"
I gave it a try on using different types (Forms, Variant, ..., either
if it made sense to me or not) and replaced Me in "Calculation (Me)"
by frm_AAA, Form_frm_AAA and Me!.

Any clue?
 
M

Maarten

Hi Jon, thanks for your help.

sorry for the errors in my start post, i wrote it directly into the
newsgroup window, without checking it in VBA.
Where I said 'Resultaat' it should have been 'Result' (Same word,
different language)
str_Greet is also a text field in the form. So the original
programming should have been 'frm_AAA.str_Greet' in stead of just
'str_Greet'
Thanks for your careful reading!

Unfortunately I can't get this to work. I tried the first option (with
only oForm as Form). It returns a type mismatch on "Calculation (Me)"
I gave it a try on using different types (Forms, Variant, ..., either
if it made sense to me or not) and replaced Me in "Calculation (Me)"
by frm_AAA, Form_frm_AAA and Me!.

Any clue?
 
M

Maarten

Hi Jon, thanks for your help.

sorry for the errors in my start post, i wrote it directly into the
newsgroup window, without checking it in VBA.
Where I said 'Resultaat' it should have been 'Result' (Same word,
different language)
str_Greet is also a text field in the form. So the original
programming should have been 'frm_AAA.str_Greet' in stead of just
'str_Greet'
Thanks for your careful reading!

Unfortunately I can't get this to work. I tried the first option (with
only oForm as Form). It returns a type mismatch on "Calculation (Me)"
I gave it a try on using different types (Forms, Variant, ..., either
if it made sense to me or not) and replaced Me in "Calculation (Me)"
by frm_AAA, Form_frm_AAA and Me!.

Any clue?
 
J

Jon Lewis

Sorry - my mistake! You don't need brackets.

Calculation Me
or
Calculation str_Greet, Me

HTH
Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Providing user message when RED X clicked & properly closing application. 2
Refering to Form controls in a general module 3
Subform Reference 6
Boolean variable not holding state 5
Multiple Fxns in single sheet module 0
Cancel = True "variable not defined" 3
Open form relative to another form 1
Unable to call Module? 4

Top