Solved

Best place to place code to check if a certain cell isNull or Equals Zero

Posted on 2013-06-13
5
275 Views
Last Modified: 2013-06-18
Drawing a blank on the best place to but the code to check the value of certain field.

the field is on 1 of many subforms of a main form (tabs).  I have tried it on Load of subform, ONcurrent etc.  the code does not seem to check the value of the field.  any ideas.

Thanks,

K

 If IsNull(Forms![frmProformaInv]![chdProformaWO].Form![PricePerUnit1]) Or _
     Forms![frmProformaInv]![chdProformaWO].Form![PricePerUnit1] = 0 Then
          Call _
              MsgBox("The Unit Price currently is missing or contains a Zero value.  Please review and enter the correct value.", _
              vbExclamation, "Unit Price Is Not Validate")
    Me.PricePerUnit.SetFocus
    Me.PricePerUnit.Enabled = True
 End If

Open in new window

0
Comment
Question by:Karen Schaefer
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:BAKADY
ID: 39245999
where is this code in your form or in a simple modul?

if the code is in your form, you should have a object name for your formular's objects, ... this mean, if you create a object names combo1 you can access to his value like this:

Dim strSomeString as String

strSomeString = combo1.text  ' or
strSomeString = me.combo1.text 'or
strSomeString = frmMyForm.combo1.text

in your case, i'm a little bit confused

Forms![frmProformaInv]![chdProformaWO].Form![PricePerUnit1]

frmProformaInv is your Form
chdProformaWO should be your object
PricePerUnit1 or it's this one

anyway
frmProformaInv.chdProformaWO.Value 'or .Text or .ListIndex or the property which you want to get the value

i'm glad to help you, if this doesn't work, please post more information about.
0
 

Author Comment

by:Karen Schaefer
ID: 39246009
I mean should if be in after update of the sub form, the mainform, etc.  or onFocus of subform

I need to check on focus of the subform to check if the value is null and also on exit of the subform or change of record on the subform for the null value.

Where should I place the code?

K
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39246079
has "PricePerUnit1" a datasource? i mean a table's field...

if a really unterstand your problem, your have a field - maybe numeric - names PricePerUnit1, this couldn't be NULL. right?

in this case nowhere in vba, into your table. In access under the field's properties you can define first a "Validation Rule" and second set a "validation message", this one will automatically displayed if the rull doesn't match, you don't need to write it in vba.

Validation Rule: > 0 and Is Not Null
Validation Message: The Unit Price currently is missing or contains a Zero value.  Please review and enter the correct value.
0
 

Accepted Solution

by:
Karen Schaefer earned 0 total points
ID: 39246251
yes it is numeric, however that is not the issue, I need the system to notify the user that the field for the active record contains a zero value or is null.

I added the code to the OnCurrent of the subform and this seems to have resolved the issue.

Thanks for your input.
0
 

Author Closing Comment

by:Karen Schaefer
ID: 39255637
Solve the issue on my own.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

707 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

19 Experts available now in Live!

Get 1:1 Help Now