By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Prevent data entry of Date Field

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.