A lot going on and I'm lost.......DLookUp and Validate field entries

BrokenBiker · Apr 23, 2006

Here's the deal. The Assessment Form is where all the data is loaded, but for some reason a couple of queries don't include records w/ blank fields while other queries will include them. This skews the numbers so bad that different reports and pivot tables come up w/ different numbers. I can't find anything in the queries to cause this.

To overcome this I loaded some code to ensure that all the fields are filled in, even if it's 'n/a.' This works fine, but it can be a little cumbersome when deleting a blank entry, i.e. someone accidentally starts an assessment then for whatever reason it needs to be deleted. Message boxes pop up one after the other telling the user to enter various data. It's irritable, but livable.

I would like to use one grouping of code as opposed to a dozen for all the fields. Right now this is the code on the Form AfterUpdate function I use for close to a dozen fields:
============
If IsNull(Me.Rating) Then
MsgBox "All inspections require a rating. Please select from the list provided."
Cancel = True
DoCmd.GoToControl "Rating"
Else
End If
=============

I've tried using a few other bits of code that are designed to loop through all the fields and then give you one message if a field is blank, but no luck in making it work.

Also, this doesn't actually force an entry, it only gives you a message. After scrolling through the messages you can still move to the subforms or move to another record.

Also, I had been using subforms to show related info, i.e. the user selects the Main Assessee via a combo box and the subform shows name, rank, AFSC. I removed those subforms and started using DLookup. I had the DLookUp loaded under the AfterUpdate event which worked fine unless you were scrolling through the records. Then the DLookUp didn't update for the current record, so I added the DLookUp to the Form_Current event.

This works fine, except that it opens in Add mode and because of the Form_Current event a record is created before you actually type anything. The problem comes up when you try to click the View Open Assessments or Show All buttons. Then the validating code runs and you have to run through a bunch of message boxes before you can view those records.

I can probably work around this by having the form open showing the open assessments (records w/o a Chief Inspector Review) as opposed to opening in Add mode.

So.......to sum things up. I need to be able to incorporate both the validating functions and the DLookUp functions w/o running into these errors.

Desparately seeking help!

-----Keywords-----------

DLookUp, validate form field, auto fill, autofill, required entry

BrokenBiker · Apr 24, 2006

Too much? Plenty of people have checked this out, but there's no feedback.

I'd take almost any kind of input. This is pretty serious, and I'm seriously stuck.

BrokenBiker · Apr 26, 2006

Bueller?...

SamDeMan · Apr 26, 2006

couldn't read your file.

BrokenBiker · Apr 26, 2006

I opened it up and it worked fine.

It might've been a problem w/ compression; it was set to Max.

This one is normally compressed.

SamDeMan · Apr 27, 2006

first of all i would like to tell you that you did an amazing job.
right off the bat i see a few things.
here is a few pointers:
1. i don't know why you type in "Else" if there is nothing to be done for the else condition. (not so important)
2. what i think you are trying to do is that if the user doesn't enter a required field the user shouldn't be able to proceed ?? if this is true you want to do the following: condion 1: If IsNull(RequeredField) Then msgbox... Exit sub End IF condition 2: if .... exit sub end if condition 3: if .... exit sub end if and so on.
(i usually use setfocus before the exit sub)
3. for the Inspection_Type_AfterUpdate you can use a case statement (again, this is not important just a lot less code)
4. for you last problem of dlookup, i can think of many solution, some of them i don't actually know how to do (i am also amature) but i think you can use a condition like if primary key is null then exit sub else dlookup....

sam

BrokenBiker · Apr 27, 2006

Thanks. I've been picking up a lot on this board. I included the 'Else' because I didn't know not to. No biggie.

I've been looking at Select Case statements, but there are a few places where I don't think they'd be so helpful, but I think it may work for the inspection type.

The field validation worked well before, not perfect, but well. THEN...I added the DLookup function. In order for it to work I had to include that in certain fields' BeforeUpdate function and/or the Form's OnCurrent function to have it reflect the current info.

This created the problem. Because of the OnCurrent and/or BeforeUpdate events, the fields are considered updated....then comes all that code for the field validation. The biggest problem is that the code runs through even when you press the ShowAll or Open Assessments button.

I did change some of the code to avoid message after message poppin' up. I changed the If...End If statements to

ElseIf IsNull(Me.Location) Then
MsgBox "This is a required field."
Cancel = True
DoCmd.GoToControl "Location"

ElseIf IsNull(Me.Time) Then
MsgBox "What time was the inspection conducted?"
Cancel = True
DoCmd.GoToControl "Time"

....End If

This basically runs through the code one step at a time.

Back to the problem at hand....If I could find a way to cancel the field validation (so you can use the filter buttons) my problem would be solved.

SamDeMan · Apr 27, 2006

you didn't add "Exit Sub"

BrokenBiker · Apr 27, 2006

The 'end sub' is in the code still, I just posted a sample of the change in the code. I'm thinking I might create an unboudn, invisible field w/ a small tied function to it so I can use that value for a select case.

I.e. I'm thinking about adding a bit more code to one of the AfterUpdate functions so that when it's fill out, the invisible field is filled also filled.

If (Forms![Assessment Form]![Inspection Type] = "someinput") Then
Forms![Assessment Form]![invisiblefield] = "1"

I could then build the Select Case from the invisiblefield value and use that to bypass the form validate functions so that people could still use the filter buttons.

SamDeMan · Apr 27, 2006

you didn't read my code: IF IsNull Then "EXIT SUB" End IF
please my post

BrokenBiker · Apr 27, 2006

Oh. I see that now.

BrokenBiker · May 2, 2006

OK...I got caught up in other issues. I'm back and included the Exit Sub portion to the code. That works fine to stop the code from running which avoids the problem of running through all of the message boxes.

There's a way (at least I'm looking for it) to cancel a sub in another sub. For instance, there should be a way to cancel the Form_AfterUpdate sub in the Open_Assessments_Click sub. This would solve my problems.

More to come...

BrokenBiker · May 2, 2006

I think I have it fixed. It's late so I'll finish it up tomorrow. But here it is...

In the form's code I entered a new sub

Code:

Sub Validate

If IsNull(Me.TEC) Then
MsgBox "The TEC is a required field."
Cancel = True
DoCmd.GoToControl "TEC"
Exit Sub
Else
End If

(etc...for all fields)

End Sub

I added a navigation button from the toolbar and added a call to the Validate sub. Pretty easy, but I didn't know how to create a sub w/o it being tide to an event or how to call it.

Code:

Private Sub Command104_Click()
On Error GoTo Err_Command104_Click

[B]Validate[/B]

    DoCmd.GoToRecord , , acNext

Exit_Command104_Click:
    Exit Sub

Err_Command104_Click:
    MsgBox Err.Description
    Resume Exit_Command104_Click
    
End Sub

Ahhh...the learning curve is starting to even out a little...

SamDeMan · May 3, 2006

I didn't know how to create a sub w/o it being tide to an event or how to call it.

Code:

Private Sub thisIsMyCode()

    DoCmd.GoToRecord , , acNext

End Sub

you can call this sub anyway you want. try it. you can either put in the name in another sub just type in the name thisIsMyCode or on the event of an object. so instead of the words EventProcedure in the event pull down menu you can type in the name of the sub. just remember it most be part of the same form, and a sub can't be called from a different form.

good luck,

try it. also try to figure out how to get feeback like: debug.print or to know how to place a breakpoint in your code.

sam

BrokenBiker · May 3, 2006

I'll mess with the debug.print at another time--One thing at a time. I was able to call the function and overcome some problems. I'm slowly but surely weeding out any issues.

Here's what I have now. I added navigation buttons and a close-form button, both of which call the function 'Validate.' Things are working pretty good, except I get two message boxes (one right after the other) when the Validate function runs.

Also, I'm looking for a way to skip the Validate function if all fields are empty (except the Report Number field--it's not an issue in any of this). I tried this:

Code:

Function Validate() As Boolean

[B]Dim AllCtrls As String

AllCtrls = Me.date & Me.TEC &...etc

If AllCtrls = "" Then
Exit Function
Else
End If[/B]

If IsNull(Me.TEC) Then
MsgBox "The TEC is a required field."
Cancel = True
DoCmd.GoToControl "TEC"
Exit Function
Else
End If

'-----etc. for all the different fields

Validate = True

End Function

The code for the various navigation buttons works fine, except the duplication of the MsgBox's.

Code:

Private Sub btnGoToLast_Click()
On Error GoTo Err_btnGoToLast_Click

Call Validate

If Validate <> True Then
Cancel = True
Exit Sub
Else
DoCmd.GoToRecord , , acLast
End If

Exit_btnGoToLast_Click:
    Exit Sub

Err_btnGoToLast_Click:
    Resume Exit_btnGoToLast_Click
    
End Sub

The bigger problem is trying to skip the Validate function when all fields are empty. If someone opens the form, then decides to work on the assessment later, they should be able to just close out w/o getting prompted first to enter info, then getting asked if they want to load an assessment.

The intended process for the close button has three things. First, if all fields are filled then close. Second, if not all fields are filled then call Validate. Third, if all fields are empty, then MsgBox vbYesNo, "Do you wanna load an assessment?" vbYes Then call Validate, Else close form.

Code:

Private Sub btnClose_Click()
On Error GoTo Err_btnClose_Click

Call Validate

If Validate <> True Then
    If MsgBox("Do you want to load an assessment?", vbYesNo, "Validate") = vbYes Then
    Cancel = True
    Exit Sub
    Else
    DoCmd.Close
    End If
Else
DoCmd.Close
End If

Exit_btnClose_Click:
    Exit Sub

Err_btnClose_Click:
    Resume Exit_btnClose_Click
    
End Sub

BUT...the AllCtrls string doesn't work, so before the vbYesNo MsgBox runs I always get the MsgBox from the Validate function. I really need to skip the Validate function when all fields are empty.

Am I going about this the right way? It sounds right to me, I just don't wanna spin my wheels over nothing.

BrokenBiker · May 7, 2006

Alright...Finally got it figured out.

http://www.access-programmers.co.uk/forums/showthread.php?t=106940

raskew · May 7, 2006

Hi -

What exactly is your understanding of data?

Here's the deal. The Assessment Form is where all the data is loaded,

Forms don't contain data, they display data coming from tables or, perhaps, queries of multiple tables

Bob

A lot going on and I'm lost.......DLookUp and Validate field entries

BrokenBiker

ManicMechanic

Attachments

BrokenBiker

ManicMechanic

BrokenBiker

ManicMechanic

SamDeMan

Registered User.

BrokenBiker

ManicMechanic

Attachments

SamDeMan

Registered User.

BrokenBiker

ManicMechanic

SamDeMan

Registered User.

BrokenBiker

ManicMechanic

SamDeMan

Registered User.

BrokenBiker

ManicMechanic

BrokenBiker

ManicMechanic

BrokenBiker

ManicMechanic

SamDeMan

Registered User.

BrokenBiker

ManicMechanic

Attachments

BrokenBiker

ManicMechanic

raskew

AWF VIP

Similar threads

Users who are viewing this thread