Access 2013

In writing a macro expression for my access database I can't figure out wrong.
[Forms]![FrmTracker]![NumberOf Remaining Units]<100 Then MessageBox
Isaiah NoellAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Gustav BrockConnect With a Mentor CIOCommented:
The code must be inside the function:
Private Sub NumberOf_Remaining_Units_AfterUpdate()

    If Nz(Me![NumberOf Remaining Units].Value, 0) < 100 Then
        MsgBox "Units are low, contact System Administrator", vbInformation + vbOKOnly, "Remaining Units"
    End If

End Sub

Open in new window

/gustav
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
since you are referring to a subform, you must use .form after the subform controlname to go inside the form and reference what it contains

the subform itself is where the subform control is ... its position, size, etc

assuming your subform control Name IS the same as the SourceObject it contains:
[Forms]![FrmTracker].form![NumberOf Remaining Units]

Open in new window

0
 
Isaiah NoellAuthor Commented:
[Forms]![FrmTracker].[Form]![NumberOf Remaining Units]<100

I'm not really versed in access. I haven't really used it since Access 2003. I'm getting a error message Type mismatch.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
try Forms!Formname.form.controlname

if names have spaces, substitute underscore --

however a type mismatch generally means a different data type was expected.  Is this a bound control? If so, what is the underlying data type?

If not, then Access assumes it is text and the value must be converted to a number to compare with a number
0
 
Isaiah NoellAuthor Commented:
Its a calculated field.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
then in the equation to calculate, wrap with a conversion function such as cLng, cInt, cDbl to specify the data type.  You can also wrap the control reference.  If you wrap the reference, be sure to use NZ inside since a conversion function can't convert null.  For example, for Long Integer:
       cLng(NZ([Forms]![FrmTracker].form![NumberOf Remaining Units],0))

Open in new window

0
 
Isaiah NoellAuthor Commented:
First of all I want to say thank you for all your help.
 I tried  cLng(NZ([Forms]![FrmTracker].form![NumberOf Remaining Units],0)) and I got Error: subscript out of range.

I want a MessageBox to appear when NumberOf Remaining Units get below 100
It's doing the calculations correctly.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome

is the form open and on a record?

what is the equation for the calculated field?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Crystal ... what make you think the OP is using a subform ?
0
 
John TsioumprisSoftware & Systems EngineerCommented:
IF your subform has some code behind it you can use this :
Form_FrmTracker.Remaining 

Open in new window

If you don't have code behind just press the button of code...just to enter the VBE environment and leave...that would be enough
0
 
Gustav BrockCIOCommented:
Write a piece of code as an EventProcedure in the AfterUpdate event of the textbox bound to [NumberOf Remaining Units]:
If Nz(Me![NumberOf Remaining Units].Value, 0) < 100 Then 
    MsgBox "Number of units must be larger than 100", vbInformation + vbOkOnly, "Remaining Units"
End If

Open in new window

/gustav
0
 
Isaiah NoellAuthor Commented:
This is what my event procedure looks like, what am I doing wrong?


Private Sub Command30_Click()

Call Auditchanges("PeerID1", "Delete")



DoCmd.GoToRecord , , acNext


End Sub

Private Sub Command48_Click()

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Call Auditchanges("PeerID1", "New")
Else
    Call Auditchanges("PeerID1", "Edit")
    End If
   
    End Sub

Private Sub NumberOf_Remaining_Units_AfterUpdate()

End Sub
If Nz(Me![NumberOf Remaining Units].Value, 0) < 100 Then
    MsgBox "Units are low contact System Administrator", vbInformation + vbOKOnly, "Remaining Units"
End If

Private Sub NumberOf_Remaining_Units_BeforeUpdate(Cancel As Integer)

End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
So what error are you getting now and where ?
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Joe, "Crystal ... what make you think the OP is using a subform ?" -- gee, I don't know! Too much multi-tasking and not paying enough attention -- sorry!
0
 
Gustav BrockCIOCommented:
Most likely the answer.
0
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.