Validate Fields, DLookUp, and Dummy-proofing

BrokenBiker · May 7, 2006

Well, many people use the mousetrap database for field validation, but it wasn't quite working for me.
http://www.access-programmers.co.uk/forums/showthread.php?t=38364&highlight=mousetrap

The Assessment form has an OnCurrent event and AfterUpdate events for DLookUp that messes up the BeforeUpdate event in the mousetrap db. This db uses a separate function (Validate) to check the fields. It's called by various commands.

Form Current Sample:

Code:

Private Sub Form_Current()

Dim TECInfo As String
TECInfo = Nz(DLookup("[MAINTENANCE ACTION]", "[qryShowTECInfo]"), "")
[TECDescription] = TECInfo

Dim TECBaseline As String
TECBaseline = Nz(DLookup("[Baseline]", "[qryShowTECInfo]"), "")
[Baseline] = TECBaseline

End Sub

AfterUpdate Sample:

Code:

Private Sub Inspection_Type_AfterUpdate()
On Error GoTo Err_Inspection_Type_AfterUpdate

    If (Forms![Assessment Form]![Inspection Type] = "SI") Then
        Forms![Assessment Form]![Main Assessee] = "9999A"
    End If
    
    If (Forms![Assessment Form]![Inspection Type] = "UCR") Then
        Forms![Assessment Form]![Main Assessee] = "9999B"
        Forms![Assessment Form]![TEC] = "802"
        Forms![Assessment Form]![Rating] = "Non Rated"
    End If

Dim MainAssesseeRank As String
MainAssesseeRank = Nz(DLookup("[Employee RANK]", "[qryShowMainAssesseeInfo]"), "")
[AssesseeRank] = MainAssesseeRank

Dim MainAssesseeName As String
MainAssesseeName = Nz(DLookup("[Employee NAME]", "[qryShowMainAssesseeInfo]"), "")
[AssesseeName] = MainAssesseeName

Dim MainAssesseeAFSC As String
MainAssesseeAFSC = Nz(DLookup("[AFSC]", "[qryShowMainAssesseeInfo]"), "")
[AssesseeAFSC] = MainAssesseeAFSC

Exit_Inspection_Type_AfterUpdate:
    Exit Sub
    
Err_Inspection_Type_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Inspection_Type_AfterUpdate
    
End Sub

Validate Function:

Code:

Function Validate() As Boolean

    If IsNull(Me.Rating) Then
    'MsgBox removed for testing
    Cancel = True
    DoCmd.GoToControl "Rating"
    Exit Function
    Else
    End If

'etc for all fields

Validate = True

End Function

It's designed to allow you to close an 'empty' or full record, but not a partially filled record. You are prompted to save (--continue filling in info--) or not(--delete the record--). Also, you can't use the navigation buttons to move out of an empty or partially filled record, but you can view 'open' or 'all' assessment w/ the commands (filters) in the upper left corner of the form

Thanks goes out to SamDeMan for helping me along.
http://www.access-programmers.co.uk/forums/showthread.php?t=106218

BrokenBiker · May 7, 2006

File attached.

PattyS · Jun 20, 2006

Hi Broken Biker, (I hope the name dosent imply a debilitating motorcycle accident?) I ride 03 Heratage Softail.
Anyway....first off, I am not a programmer. I took a few extra Access 97 courses 6 years ago and never applied the knowledge till now (what I could remember, yea right!) So Boss asked if I could do this database and I said I'd try (because database operator is not in my job discription) I have made alot of mistakes and a guy on another forum said that its all wrong but its working so I am leaving it. At this point, with 1498 records entered, I am "fine tuning" it. Now that the records are cought up to date, I am finding duplicate records. So I have a code that works finding and alerting me to these records as soon as I enter the first field which is [CASE NO]
Ok so now I want the msgbox YesNo to give me the choise to Yes > go to the existing record or No > return to a blank record to enter a new case number.
This is my code that works finding the duplicate and brings up the MsgBox. When you click Yes OR No the same thing happens, the message box closes and the record with the duplicate case number is the active window with the cursor blinking in the CASE NO field. So I just erase that number and put in a new one.
Private Sub CASE_NO_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[CASE NO]","[CRASH DATA TABLE]","[CASE NO]='" & Forms![CRASH DATA FORM]![CASE NO] & "'")) Then
Cancel = True
If Me.NewRecord Then
MsgBox "THIS RECORD IS ALREADY ENTERED." & vbNewLine &
"WOULD YOU LIKE TO VIEW THIS RECORD?", vbExclamation + vbYesNo
Else
Me![CASE NO].Undo
End If
End If
End Sub

I know I need to put in where I want to go if the message box is Yes but I cannot seem to find the right code.
I've tried:
If vbYes Then GoTo [CRASH DATA FORM].Me![CASE NO]
Else
If vbNo Then [CASE NO].Undo
Dosent work
tried other variations of If vbyes then....like DoCmd.GoToRecord acGoTo, (area to put in the criteria but I dont know my criteria!) so that was a lost cause, might work if i knew what to put in the my criteria part.

this is getting long, i hope you can help, please!

gold007eye · Jun 21, 2006

Patty,

This problem used to stump me as well, but I did some research and got it figured out. Here is what I use for code using Yes/No button. (In my case I use it for the "Delete" function, but you should be able to adapt this code to fit your needs. Hope this helps:

Code:

Private Sub Delete_Click()
On Error GoTo Err_Delete_Click
'Code Start
Dim Message As String

Beep

Select Case MsgBox("Are you sure you want to remove " & [Employee Name] & "'s Security Access", vbYesNo + vbQuestion, "Remove the Associate " & [Employee Name] & "?")

Case vbYes: 'Delete the record
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True
    MsgBox "This associate's security access has been removed!" & vbCrLf & vbCrLf & "Click OK to continue...", vbCritical, "Associate Removed!"

Case vbNo: 'Do NOT delte the record
    DoCmd.CancelEvent
    MsgBox "Associate Security Removal Cancelled!", vbInformation, "Associate Security Removal Cancelled!"

Case Else: 'Trap any other errors that could occur
    'Do Nothing
End Select
'Code End

Validate Fields, DLookUp, and Dummy-proofing

BrokenBiker

ManicMechanic

BrokenBiker

ManicMechanic

Attachments

PattyS

Registered User.

gold007eye

Registered User.

Similar threads

Users who are viewing this thread