Solved

Validate subform value with Message box

Posted on 2011-03-25
15
433 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
 

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now