Qualify a field that can come from a number of Forms

I'm trying to write a function that will pick up a Customers Email Address which inturn is pass it to another function that will sent an Email to the Customer. Before sending the Email I need to ensure the Customer has a valid Email address (ie not blank). However, the Customers Email field name may come from a number of forms so I need to qualify the Email field with the correct Form name  so it has the format :- Forms!frmName!fieldname. The following code is what I have developed but I keep getting the following error when I attempt to execute it
                                          Runtime error '424'
                                         Object Required

Set frmCurrentForm = Screen.ActiveForm
sFormName = "Forms!" & frmCurrentForm.Name & "!"
 If IsNull(sFormName & fieldname)   <================ this is the statement where the error is

I've tried a number of syntax scenarios but I can't seem to crack the right combinations.
Any suggestion greatfull accepted.

Note: If I qualify the field longhand - Forms!frmName!fieldName then it works,  however, frmName can keep on changing
Who is Participating?
aikimarkConnect With a Mentor Commented:
use a form variable instead of a string variable.  Iterate through the various forms, assigning them to the form variable.  Then you should be able to use

If IsNull(frmThing.fieldname) Then

Open in new window

There are other ways to reference a field on a form, but the above should be the easiest to implement, given the code you're starting with.
AdlermAuthor Commented:
Hi aikimark
Thank you so much for your reply. I actually managed to get the code to work (see below).
************** Code*******************************************************
Set frmCurrentForm = Screen.ActiveForm
sFormName =  frmCurrentForm.Name
 If IsNull(Forms(sFormName)!fieldname)  
***************End Code************************************************

I'm going to award the points to you - at  least you tried and for allI know your suggestion may work but I'm going to leave well enough alone

Cheers: MLA

You are using one of the "other ways" to reference a control on a form.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.