Data Validation IF Statement

[svtguy02]

Hi there everyone,

I've got a little issue with a form that is able to be closed without it prompting me that it can't close it without required information!

What I have is a many to many relationship between the following tables

Contacts (TableName)
ContactID (PK)
Name
Title
Phone
Extension

Customers (TableName)
CustomerID (PK)
Customer_Name
Address
City
State

CustContact (TableName)
ContactID (PK)
CustomerID (PK)


I have forms for each of the tables mentioned above. Workflow starts by creating a contact, then linking that contact to a customer.
The form that opens, to link the two together, is bound to the CustContact table.

When you create a contact, I have a button with the following VB Code in it:


Private Sub BtnCustContact_Click()

DoCmd.OpenForm "CustContact", acNormal, , , , , Me.Contacts_ContactID

End Sub


So thats all good... It passes the ContactID through OpenArgs and all is good.

Upon opening the CustContact form it pre-populates the ContactID field based on what ID was passed from the first form although the CustomerID field needs to be selected from the combo box. I also have a 'confirm and close' button on this form.

So lets say you open the CustContact form and your name is there, but there is no Customer selected yet. you can click the confirm button and it will just close it. Here is the code for the form_load open args statement and the button click:


Option Compare Database

Private Sub BtnConfClose_Click()

Dim Msg, Style, Title, Response, MyString

Msg = "Do you want to link CustomerName and ContactName together?" 'Defined Message
Style = vbYesNo + vbQuestion + vbDefaultButton2 'Defined Buttons (Yes, No, as a question, 2nd button default)
Title = "Customer/Contact Confirmation" 'Defined Title of message box
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then 'If User Chose Yes
MyString = "Yes" 'Variable = Yes
DoCmd.Close acForm, "CustContact", acSaveYes 'Do Command Close 'VendContact' Form with Save
Else 'If User Chose No
MyString = "No" 'Variable = No
DoCmd.Close acForm, "CustContact", acSaveNo 'Do Command Close 'VendContact' Form without Save
End If

End Sub


Private Sub Form_Load()

DoCmd.GoToRecord , , acNewRec

Dim dbs As DAO.Database
Dim qrs As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT ContactID, Name FROM Contacts WHERE ContactID = " & Me.OpenArgs

Set qrs = dbs.OpenRecordset(strSQL)

Me.CustContact_ContactID.Value = qrs.Fields(0)
Me.CustContact_ContactID.ColumnCount = 2
Me.CustContact_ContactID.ColumnWidths = "0.25, 1"

qrs.Close

End Sub


I want to be able to click 'Confirm and Close' and if the CustContact_CustomerID field is NULL then it prompts you to fill it out IF you click YES on the prompt box that asks you if you want to save. If you click no, it just closes without saving anything. Any ideas?

I HAD this, but it didnt quite work:


Private Sub BtnConfClose_Click()

Dim Msg, Style, Title, Response, MyString
Dim ErMsg, ErStyle, ErTitle, ErResponse, ErrorMsg


Msg = "Do you want to link CustomerID and ContactName together?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Customer/Contact Confirmation"
Response = MsgBox(Msg, Style, Title)

ErMsg = "Please be sure to select a Customer before saving!"
ErStyle = vbOKOnly + vbExclamation
ErTitle = "Invalid Input - Required Field"
ErResponse = MsgBox(ErMsg, ErStyle, ErTitle)

If Response = vbYes And CustContact_CustomerID = Null Call ErResponse
ElseIf Response = vbYes And CustContact_CustomerID.Value = True
MyString = "Yes"
DoCmd.Close acForm, "CustContact", acSaveYes
Else
MyString = "No"
DoCmd.Close acForm, "CustContact", acSaveNo
End If


End Sub

ANY Help is appreciated! Thank you all for your time...

 

[missinglinq]

It has long been recommended that the code

If Me.Dirty Then Me.Dirty = False

be inserted before using

DoCmd.Close

to close a form because of a quirk in Access. When DoCmd.Close is used, Access closes the form regardless of whether a validation rule or required field rule has been violated! If one of these rules has been violated, Access will simply dump the record, close the form, and not tell the user that the record has been dumped!

If Me.Dirty Then Me.Dirty = False forces Access to attempt to save the record, and if a violation has occurred, will throw up a warning message allowing correction to be made before closing the form.

Linq