Putting in safegaurd. Is it the best way.

[Abacus1234]

I have a process which involves entering data on 5 forms. Some of the later forms are dependent on data in the earlier forms.

Ideally, I would like them to be able to open each form independently, but I get messages about Query connections because some records have not yet been created



As each form is opened, I set a value equivalent to the ID field for the new record on the Main Screen.
As form 2, 3, 4, 5 are opened, I use a macro to determine if the value is there, if not I provide a message saying please complete the forms in order, as the data flows better, and stop them opening the form.

My concern is that there may be a circumstance, where they have completed the earlier form, but the value I am looking for did not get posted to the Main Screen. So I want to be able to override the condition I have set up. Or find a better way of doing it.

Any ideas would be appreciated.
.

[Micron]

I don't know what this means I set a value equivalent to the ID field for the new record on the Main Screen.
If the value is in the record created by the parent form, and that field is a required field via the table design, it cannot be omitted. If there is a field in the parent form/record that the child form will be looking for, you look for it and cancel the Open event if not found. If as I said, it was a required field, 99.9% certain it would be there. If that's not enough for you, then you could institute an over-ride, but why open the form if there is no linking data? Any record created on this child form would be what I call an orphan record, unless I'm not understanding the situation.

[Abacus1234]

I'm using the Main Screen as a dashboard, and the values are foreign keys in the dominant table. There is no parent or subforms. I'm trying to offer a fast way of entering and retrieving data. So a user might want to open and print one page out of the five whether it had data or not. I think some of the issue must be in the query itself, and I should look at how I connect the data. I think I can use your idea of canceling the open event or providing a message about data that should have been entered, and still allow them to open the form. I really appreciate the input. Thanks.

[Abacus1234]

I'm looking for a sanity check on how I am making one form work. This form pulls together some highly summarized data, as well as first level data entry. When I open it I get 17 invalid use of Null messages. I have done everything I know to ensure that there are no Nulls, but I still get 17 occurrences of the message. So I am suppressing the messages with the following event procedures
[QUOTE]Private Sub Form_AfterUpdate()
'Because we use forms pulling data from different but commontables
'software supposes that the data in the
'database changed and is now different to the data on thescreen, you have to do ‘something to make the bound
'fields on your screen also match
'when we open different forms you get data has changed -solution is Me.Requery on ‘after update
'of each form or subform?
On Error GoTo Err_Close_Requery
'If Me.Recordset.RecordCount > 0 Then
'On Error Resume Next
Me.Recordset.Requery
'End If
Exit_Close_Requery:
Exit Sub
Err_Close_Requery:
'MsgBoxErr.Description
ResumeExit_Close_Requery
End Sub
[/QUOTE]
[QUOTE]Private Sub Form_Error(DataErr As Integer, Response AsInteger)
If DataErr = 7787 Then
Response = acDataErrContinue
'Me.Recordset.MovePrevious
'Me.Recordset.MoveNext
End If
If DataErr = 94 Then
Response = acDataErrContinue
End If
End Sub
[/QUOTE]

I have some empty date fields in this form, and use a datediff calculated control to determine the number of days between 2 dates. If this form is opened before those dates are entered, I believe this could cause an Invalid use of Null. I have tried testing the datediff with Not IsNull, and wrapping it in a date format, but this did not reduce the number of invalid null errors.

Using these event procedures has caused the software to work the way I want it to, but ........

[Micron]

You cannot pass a null value to a function that requires valid dates in order to calculate the difference. Suppressing the message is not a good idea.
You will either have to ensure there are two dates by not allowing Null values, or use an expression to supply a default date, even if the supplied date is the same as the date you do have. In that case, the difference would be 0, which would be a valid result. One way is to use the IIF function but the Nz function is probably more efficient for your needs.

[Abacus1234]

We tracked down what was causing the 17 Invalid Use of Null messages. We did this be going into Page 1 and Page 2 and entering certain bits of data that effect page 3. It turned out to be data in a subform on Page 2, and it seemed to be focused on a total derived from this data which is used on Page 3.


We decided in the end, to not allow the user to open Page 3 until Page 1 and Page 2 data have been completed.

Thanks very much for your input which helped us work through the problem and arrive at a well thought through solution.