Qualify a field that can come from a number of Forms

Posted on 2011-04-28
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
    LVL 44

    Accepted Solution

    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

    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 44

    Expert Comment


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

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now