473,320 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Event proc using vbYesNo with if then statement

I am rusty with my vba syntax. In order to preserve data integrity, I wanted to add some coding for an event procedure which would ask users to confirm their update to one y/n box (PGHOwned), and then update 3 other y/n boxes dependent upon the answer they choose.

Here is some info to help you better understand:
  • Controls (Y/N) are on FORM (frmVendor_DataEntry) in Access 2007
  • Form fed by query (qryVendors_Active)
  • 4 Y/N controls involved (COIRequired, ContractRequired, SafetyRequired, and PGHOwned)

If a vendor is PGHOwned, then the other requirements should always be "FALSE". Therefore, when someone changes the value of PGHOwned to TRUE, I want a msgbox to ask them to confirm the change.

If "Yes" is selected, I want the code to set the value of the other 3 Y/N controls to FALSE.

If end-user selects "NO", then it should UNDO the change and set the value of PGHOwned back to FALSE.

I started it something like this . . .but acknowledge how far off I am with some of the syntax. I appreicate any help.

Expand|Select|Wrap|Line Numbers
  1. Private Sub PGHOwned_Change()
  2. MsgBox("YOU HAVE CHANGED INFO FOR THIS VENDOR"&vbCr&"Continue",vbYesNo,"WARNING")
  3. If <msgboxname>.Value = True then
  4. Me!COIRequired.Value=False
  5. Me!ContractRequired.Value=False
  6. Me!SafetyRequired.Value=Fale
  7. else
  8. <statement about Docmd.undo???>
  9. end if
  10. End Sub
Sep 30 '11 #1
4 5576
Mihail
759 512MB
Not too far but... far.

As you designated, your MsgBox return either vbYes either vbNo (see VbMsgBoxResult in VBA help files) depending what button you click (Yes or No). So you can't check If <msgboxname>.Value = True. Even to use this syntax: If somwhat = True is a big mistake. Unfortunately my English is not enough to explain why.

On the other hand your code refuse to show the MsgBox window. To do that:
Expand|Select|Wrap|Line Numbers
  1. Dim Msg As VbMsgBoxResult
  2.     Msg = MsgBox("YOU HAVE CHANGED INFO FOR THIS VENDOR" & vbCrLf & "Continue",vbYesNo,"WARNING")
  3.     If Msg = VbYes Then
  4.         .......
  5.     Else
  6.         Me.Undo
  7.     End If
Or, without using Msg:
Expand|Select|Wrap|Line Numbers
  1. If MsgBox("YOU HAVE CHANGED INFO FOR THIS VENDOR" & vbCrLf & "Continue",vbYesNo,"WARNING") = VbYes then
  2.     .............
  3. Else
  4.     Me.Undo
  5. End If
Good luck !
Sep 30 '11 #2
NeoPa
32,556 Expert Mod 16PB
It's always nice when a member posts a question where the logic is clearly explained. Other than missing out the [ CODE ] tags (which now that it's been brought to your attention I'm sure you won't do again) this is perfectly asked. I wanted you to understand how well appreciated that is by those of us that try to help :-)

On to the question itself. I would start by using the BeforeUpdate event for the control. This allows, within its very design, the ability to cancel the update.

The MsgBox() function returns a value indicating what the user has chosen, so that needs tweaking, and the code should be designed to run only when the value of the control is changed to True (Not run in all cases).

Something like the following should work :
Expand|Select|Wrap|Line Numbers
  1. Private Sub PGHOwned_BeforeUpdate(Cancel As Integer)
  2.     Dim strMsg As String
  3.  
  4.     With Me
  5.         If Not .PGHOwned Then Exit Sub
  6.         strMsg = "You have changed info for this vendor" & vbNewLine & _
  7.                  "Would you like to continue"
  8.         If MsgBox(Prompt:=strMsg, _
  9.                   Buttons:=vbQuestion Or vbYesNo, _
  10.                   Title:="PGHOwned Update") = vbYes Then
  11.             .COIRequired = False
  12.             .ContractRequired = False
  13.             .SafetyRequired = False
  14.         Else
  15.             Cancel = True
  16.         End If
  17.     End With
  18. End Sub
Sep 30 '11 #3
NeoPa
32,556 Expert Mod 16PB
Mihail:
Even to use this syntax: If somewhat = True is a big mistake. Unfortunately my English is not enough to explain why.
What Mihail was alluding to (I suspect) when he advised not to compare a result to True or False was that if a result is already boolean then there is no point in doing a further comparison. What most people don't seem to get is that If statements don't require comparisons per se. They require a boolean value. The fact that comparisons return a boolean value is the reason most people get confused into thinking a comparison is required, when technically any boolean value (comparison; variable; even literal) is valid. When you already have a boolean result it really makes no sense to put it in a comparison to produce a boolean result.

There is also a technical glitch such that values which trigger a True response when checked (any non-zero value rather than just the value True == -1) will provide a False response when compared with the value True. This manages to confuse a lot of developers so is truly best avoided. On the plus side, this is only a problem when logic is implemented illogically (Easily avoided by using booleans sensibly) ;-)
Sep 30 '11 #4
Mihail
759 512MB
Yes, NeoPa.
Yours English is better then main. :)
Sep 30 '11 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Perion | last post by:
I have a simple form with, among other things, an ADODC data control (called "Adodc1"). The ADO connection to the database tests fine but when I try to add code to any of the Adodc1 control's event...
7
by: Bob | last post by:
Currently I am using this statement to translate 3 fields in my db thru Visual Basic. I import the data from one table to another then call the IFF statements and the NewDate to translate the...
0
by: Kamilche | last post by:
''' event.py An event manager using publish/subscribe, and weakrefs. Any function can publish any event without registering it first, and any object can register interest in any event, even...
2
by: bashanitwonline | last post by:
Hi All, I have DB2 8.1 on my system. I have created a procedure as follows. CREATE PROCEDURE DB2ER000.XYZ() LANGUAGE SQL BEGIN INSERT INTO DB2ER000.A ( C,D,E) VALUES (10,20,30); END It...
0
by: Lavs | last post by:
Hi Guys... Is there anyone who could help me on how to add an image file to a database using SQL Statement... I am using a database in MS ACCESS 2003, and my application was designed in VB 6.0. I...
0
by: brainfulkale | last post by:
My question is that "How to print on VDU without using printf statement in unix" In DOS i use far pointer as cls { char far *s; s=0xB8000000; *s='A'; } But if i wanted to print in unix jow...
1
by: =?Utf-8?B?bGlhbnF0bGl0?= | last post by:
Is using a jump statement more faster than using if statement with a jump statement example for(int i=0; i < 10; i++) { if(a == null) {
8
reginaldmerritt
by: reginaldmerritt | last post by:
Hi, I have two report layouts that are used to run 12 different reports using where statement. for example DoCmd.OpenReport "SomeReport", _ acViewPreview, _ ...
1
by: sakura | last post by:
Hi. I'm using asp.net and c# language. How can i display a GridView using select statement of two column in one table? This is my coding: if (sdr.Read()) { if(namaPembekal.Equals(sdr)...
0
by: beginerVB | last post by:
I am able to query/read the "Application" event log using System.Management classes in VB.net 2005 (win xp) But I have problem reading the "Message" / "description" of my custom event log. I am...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.