Prevent data entry of Date Field

I want to set up a table where I can enter dates that will prevent data
entry of Dates in the Main table.

I have done this in Approach by linking two tables and setting up a
validation formula IsBlank (Date.Date). After staff enter dates in the
detail table it will prevent dates in the Main table.

How can I do that in Access?

re: Prevent data entry of Date Field

Randy wrote:
[color=blue]
> I want to set up a table where I can enter dates that will prevent data
> entry of Dates in the Main table.
>
> I have done this in Approach by linking two tables and setting up a
> validation formula IsBlank (Date.Date). After staff enter dates in the
> detail table it will prevent dates in the Main table.
>
> How can I do that in Access?
>[/color]
In the AfterUpdate event of the subform (I'll assume subform) then enter
something like
If Not IsNull(Me.DateField) then
Forms!MainForm!MainDateField = Null
Forms!MainForm!MainDateField.Enabled = False
Endif
In the OnCurrent event of the main form you could enter something like
Dim rst As REcordset
set rst = Forms!MainForm!SubForm.Form.Recordsetclone
rst.findfirst "DateField Is Not Null"
Me.MainDateField.Enabled = (Not rst.NoMatch)
set rst = Nothing

re: Prevent data entry of Date Field

I can not seem to get the suggestion to work.

I get the following message when opening the form "Message or Data
Member not found" it highlights rst findfirst

I have one form. No subform. I have a table called Main that has all
the information. The field I want to prevent data entry is Main.Date.
I want to use a table called Date to enter data a list of fields into a
field called Date2 that will prevent data entry in Main.Date if the two
fields are equal. There is only one field in the Date table. The
Main Table and the Date table is linked by the common Date fields
Main.Date and Date.Date2. No enforce referential integrity is set.

Here is what I have coded on the Main Form.


Private Sub Form_AfterUpdate()
If Not IsNull(Me.DATE2) Then
Forms!MainForm!MainDate = Null
Forms!MainForm!MainDate.Enabled = False
End If
End Sub

Private Sub Form_Current()
Dim rst As Recordset
Set rst = Forms!MainForm!MainForm.Form.RecordsetClone
rst.findfirst "Date Is Not Null"
Me.MainDate.Enabled = (Not rst.NoMatch)
Set rst = Nothing

Appreciate any corrections to this or other suggestions.

re: Prevent data entry of Date Field

Randy wrote:
[color=blue]
> I can not seem to get the suggestion to work.
>
> I get the following message when opening the form "Message or Data
> Member not found" it highlights rst findfirst
>
> I have one form. No subform. I have a table called Main that has all
> the information. The field I want to prevent data entry is Main.Date.
> I want to use a table called Date to enter data a list of fields into a
> field called Date2 that will prevent data entry in Main.Date if the two
> fields are equal. There is only one field in the Date table. The
> Main Table and the Date table is linked by the common Date fields
> Main.Date and Date.Date2. No enforce referential integrity is set.
>
> Here is what I have coded on the Main Form.
>
>
> Private Sub Form_AfterUpdate()
> If Not IsNull(Me.DATE2) Then
> Forms!MainForm!MainDate = Null
> Forms!MainForm!MainDate.Enabled = False
> End If
> End Sub
>
> Private Sub Form_Current()
> Dim rst As Recordset
> Set rst = Forms!MainForm!MainForm.Form.RecordsetClone[/color]

I will assume the form name is called MainForm. Shouldn't it be
Set rst = Forms!MainForm.Form.RecordsetClone
You had !MainForm!MainForm. which appears incorrect.
[color=blue]
> rst.findfirst "Date Is Not Null"[/color]

I don't have a problem with that.
[color=blue]
> Me.MainDate.Enabled = (Not rst.NoMatch)
> Set rst = Nothing
>
> Appreciate any corrections to this or other suggestions.
>[/color]