Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Validate subform value with Message box

Posted on 2011-03-25
15
Medium Priority
?
489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 19

Expert Comment

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

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 19

Expert Comment

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

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 400 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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