Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 2013

Posted on 2016-10-02
15
Medium Priority
?
44 Views
Last Modified: 2016-10-23
In writing a macro expression for my access database I can't figure out wrong.
[Forms]![FrmTracker]![NumberOf Remaining Units]<100 Then MessageBox
0
Comment
Question by:Isaiah Noell
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 22
ID: 41825703
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
 

Author Comment

by:Isaiah Noell
ID: 41825710
[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
 
LVL 22
ID: 41825713
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Isaiah Noell
ID: 41825745
Its a calculated field.
0
 
LVL 22
ID: 41825752
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
 

Author Comment

by:Isaiah Noell
ID: 41825762
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
 
LVL 22
ID: 41825773
you're welcome

is the form open and on a record?

what is the equation for the calculated field?
0
 
LVL 75
ID: 41825779
Crystal ... what make you think the OP is using a subform ?
0
 
LVL 19

Expert Comment

by:John Tsioumpris
ID: 41825864
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41825900
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
 

Author Comment

by:Isaiah Noell
ID: 41828619
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
 
LVL 75
ID: 41828925
So what error are you getting now and where ?
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points (awarded by participants)
ID: 41829371
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
 
LVL 22
ID: 41850190
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41855858
Most likely the answer.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

963 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