?
Solved

Validate subform value with Message box

Posted on 2011-03-25
15
Medium Priority
?
481 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
Independent Software Vendors: 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: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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

777 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