Qualify a field that can come from a number of Forms

Posted on 2011-04-28
Medium Priority
Last Modified: 2013-12-05
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
Question by:Adlerm
  • 2
LVL 46

Accepted Solution

aikimark earned 500 total points
ID: 35488772
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.

Author Comment

ID: 35489485
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
LVL 46

Expert Comment

ID: 35490584

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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question