Multiple Record Entry Form - Test for any null fields

[mgmirvine]

Hello Experts,

I have a form (DataEntry) and subform (DataEntrySubform) that allows the user to enter multiple rows of data to TableTemp, upon clicking Submit, VBA code is used to append TableAll with any rows in TableTemp, then delete * TableTemp.

My problem is this. I would like to test to make sure the user has filled out certain columns (some are optional, some are not). I don't want to set the requirement in TableTemp, because users need the freedom to enter data in multiple rows column by column.

I prepared an if statement like this

If IsNull(DLookup("[ColumnA]", "TableTemp")) Or IsNull(DLookup("ColumnB", "TableTemp")) THEN MsgBox "Whoooops!"

But this will not work if there are multiple rows, where perhaps one is completed satisfactorily while another row is not.



Any help you can provide is greatly appreciated!

[NTC]

One approach is to put the logic in the subform - this presumes the user will be completing a row, and then moving to the next row. in which case you can have the logic of checking as they leave exit the last field in the row.

Or you can inspect the temp table, find the row in error, report to the user which row is in error - abort the append function - and leave the form open. This is trickier but do-able.

Hope it helps.

[mgmirvine]

NTC, Thank you very much for the tip. I can't perform the check row by row, as the user must be able to enter data column by column (Input Column 1, record 1, 2, 3, then input data in Column 2, then 3 and so on)...

At any rate, I've been at this all day and have found a solution, albeit likely not the most elegant solution.

I created a new query Verify that sums the count of each column, then divides by the total number of columns, then divides again by a control column. I then create an if statement in the VBA that tests to see if this is <> 1, in which case the error message is displayed.

So... Verify Query:

Test: (Count(A)+Count(B)+Count(C))/3/Count(ControlColumn)

And Form VBA on click of submit button:

If DLookup("Test", "Verify") <> 1 Then
MsgBox "Whoooops!"