?
Solved

Validate subform value with Message box

Posted on 2011-03-25
15
Medium Priority
?
493 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

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!

Question has a verified solution.

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

Implementing simple internal controls in the Microsoft Access application.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

621 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