Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    110
    Thanks
    25
    Thanked 2 Times in 2 Posts

    Question Access 2010: User Function displays #TYPE! on Form - How to Hide?

    I have an Access 2010 Form that is used for inserting data into a Table. The Form also shows calculated values (from User Defined Functions) in Text Boxes, these calculated values are not saved in the Table. When I move to a New Record with no data inserted on the Form, my Text Box calculated values show "#TYPE!", obviously because no input variables have been inserted in the displayed New Record.

    How do I stop the display of #TYPE! and have the Text Boxes remain simply blank until appropriate data is inserted into the New Record.

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    There are various ways you can go about this. Probably the simplest is to modify the source for the Text Boxes containing the user defined functions, so that the function only runs if there are data in the supporting field(s). It would look something like:

    =IIF(IsNull([SupportingField]),null,UserDefinedFunction)

    Cheers

    Alison C

  3. The Following User Says Thank You to alifrog For This Useful Post:

    RetiredGeek (2011-05-16)

  4. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    110
    Thanks
    25
    Thanked 2 Times in 2 Posts

    The VBA Solution I Used...

    Thanks Alifrog for the suggestion. That works just fine in a simple situation, but... my situation uses the result in the first filled Text Box as input parameters to another User Function and the propagation of "null" results in the problem also being propogated, as the User Function formula variables are all defined as numbers or strings and the required result must be a number.

    Also, I'm doing all my calculations and data validation in VBA for Access and my User Functions have up to 6 or more parameters , so my VBA solution looks like this:

    ' Test if any parameters being used by the User Functions (a maths calculation and
    ' an imbeded formating procedure) are "null"

    If IsNull(Me.A_Price) = True Or IsNull(Me.A_Price) = True Or IsNull(Me.A_Price) = True _
    Or IsNull(Me.A_Price) = True Or IsNull(Me.Display_Currency_Symbol) = True _
    Or IsNull(Me.Instrument_Decimal_Places) = True Then
    ' If a "Null" is found then insert a default value in the Text Box on the Form

    Me.Tb_25_Milestone = 0
    Else
    ' If no input parameters report "Null" proceed with calculation and insert in Text Box on the Form

    Me.Tb_25_Milestone = Format(CalcMilestone(0.25, Me.A_Price, Me.B_Price, Me.C_Price, _ Me.Long_Short), DecimalDisplayFormat(Me.Display_Currency_Symbol, Me.Instrument_Decimal_Places))
    End If

    Thanks for the clue as to how to solve my problem.

  5. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Hi BygAuldByrd -

    It looks as though you have things under control now, but just a couple of extra points.

    You could define the input/output parameters for your functions as variants where a null is possible, to avoid errors associated with nulls. That would allow you to pass the potentially null values directly to the function if you want, rather than referencing the cells using Me. (Without my knowing what DecimalDisplayFormat actually does, you might be better to address the form directly for that one.) I should point out that variants take up more space than strings or numbers, so whether this is helpful depends on how important it is to optimise the functions.

    Just another tip for readability: =True is assumed to be the case in an If, so you could get away with "If IsNull(Me.A_Price) Or IsNull(Me.A_Price) Or IsNull(Me.A_Price) Or IsNull(Me.A_Price) Or IsNull(Me.Display_Currency_Symbol) Or IsNull(Me.Instrument_Decimal_Places) Then..." But I was born lazy, so anything to cut down on typing!

    Cheers

    AlisonC

  6. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    110
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi Alison,

    Thanks for the additional points - I've noted them for future reference and use.

    I'm new at this Access & VBA stuff so learning the hard way, trial and error(ssssss) with the help of Google and Windows Secrets Lounge. For that reason I tend to be fairly verbose with all my variants names, logic detail and comments - so when I go back and read it in a few months/years time when I next want to do something more exotic with my Access database I can work out what on earth I was trying to do. Makes for verbose coding, but at least I can understand it. Some of the cryptic code I see as examples on the web leave me totally lost.

    Anyway, thanks again for your prompt assistance.

    Cheers

    BygAuldByrd

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,379
    Thanks
    207
    Thanked 829 Times in 762 Posts
    Quote Originally Posted by BygAuldByrd View Post
    Makes for verbose coding, but at least I can understand it. Some of the cryptic code I see as examples on the web leave me totally lost.
    BygAuldByrd
    I don't think verbose is the correct term...the correct term is maintainable! That is something most code I've seen in my career is definitely NOT!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Hi BygAuldByrd (and RetiredGeek) -

    I agree about making code readable for future reference - good grief, sometimes I come back next week and wonder what I was on about. So if expanding your statements or breaking them down into extra lines helps with that, then you should go for it. Like you, I'm also fairly AR about object naming and comments, as well as creating additional subroutines and functions for repetitive sections or calculations, and including white space and indenting - anything to help group concepts together and make it easier to come back to after a gazillion different jobs in the meantime. (And to help the poor geek who has to maintain my code after I get run over by a bus/win the lottery/decide it's all too hard.)

    And let's face it, breaking everything down like this rarely requires the computer to actually do any more, and it can read this stuff much faster than we can!

    Keep going with the VBA and Access - it's lots of fun; and I love this Lounge, as there's always someone around to help.

    Cheers

    Alison C

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •