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
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!
DLookUp, validate form field, auto fill, autofill, required entry