Solved

Validate subform value with Message box

Posted on 2011-03-25
15
451 Views
Last Modified: 2012-06-27
I have a form [Order Entry] and subform [Order Details Subform] and fields on the subform [Quanity] and [Product Description].
I want to run a before update that checks the quanity. If it is greater than 10 I want to prompt the user to verify amount.
If the amount is okay set focus on product description if not set focus on quantity to make correction.
0
Comment
Question by:mbbrice
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 35219388
What are the names of the controls containing Quantity And Product Description.  We can only set focus on a control, not a field.
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 35219547
Also, we should assume that this check is in the subform record?
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 35219553
You can make a macro in the fields subform to check for quantity,  pop a msg box if greater than 10.   No need to move to description if not greater than 10 if that is the next tab stop of the form.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:mbbrice
ID: 35219560
Control Name and field name are the same
0
 

Author Comment

by:mbbrice
ID: 35219567
Yes the validation check is in the subform. Forgot to include that.
0
 

Author Comment

by:mbbrice
ID: 35219586
DoDahD,

It is possible for the quantity value to be greater than 10, but would not happen often. this VBA validation would be a more for verifying user's data enter accuracy.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 35219591
Use

Private Sub Quantity_BeforeUpdate()

If Me.Quantity>10 then

Dim i as integer

i=msgbox("Are you sure this is the Correct value",vbYesNo)

If i=vbNo


Cancel=True
Undo

End If
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 35219598
What version of access are you using.  
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 35219613
sb9 has provided a VBA code for the Before Update event in the Quantity field of the subform.    This will provide the logic you requested.
0
 

Author Comment

by:mbbrice
ID: 35219678
I am getting a syntax error and it is pointing to the if i=vbno portion

 
Private Sub Quantity_BeforeUpdate(Cancel As Integer)

    If Me.Quantity > 10 Then

Dim i As Integer

i = MsgBox("Quantity is 10 or more. Is this correct", vbYesNo, "Verify Quantity")

If i=vbNo


Cancel = True
Undo

End If
End Sub

Open in new window

0
 
LVL 16

Accepted Solution

by:
Sheils earned 100 total points
ID: 35219704
Line 9 should be

If i=vbNo then
0
 
LVL 16

Expert Comment

by:Sheils
ID: 35219712
Sorry that what a mistake in my original post
0
 

Author Closing Comment

by:mbbrice
ID: 35219721
Thanks for the help.
0
 
LVL 1

Expert Comment

by:DesertBreeze
ID: 35219751
Hi,

I think you need a "Then" after the IF i=vbNo Statement:

If i=vbNo Then
Cancel = True

0
 
LVL 44

Expert Comment

by:GRayL
ID: 35220139
I hope the other thing you learned is to never accept the default control names for controls on an Access form.  They will always be identical to the field name to which they are bound.  Headaches will abound when troubleshooting.  Have a good one!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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.

776 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