467,182 Members | 1,048 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,182 developers. It's quick & easy.

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?

Nov 13 '05 #1
  • viewed: 1944
Share:
3 Replies
Randy wrote:
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?

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
Nov 13 '05 #2
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.

Nov 13 '05 #3
Randy wrote:
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
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.
rst.findfirst "Date Is Not Null"
I don't have a problem with that.
Me.MainDate.Enabled = (Not rst.NoMatch)
Set rst = Nothing

Appreciate any corrections to this or other suggestions.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Randy | last post: by
15 posts views Thread by PC Datasheet | last post: by
17 posts views Thread by Timothy.Rybak@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.