Validation question?

[Randy]

I want to set up a Date table to validate a Date field in my main
table. When ever a staff person enters a date in the main form I want
it to check the Date table to see if a date matches. If the date
matches I want the staff person to get an error message not to enter
the date, if it doesn't match I do not want the worker to get an error.

How can I do that? Please be specific as I am new to Access. I would
rather have a validation formula if that can be used rather than long
code.

 

[Lynn Trapp]

Randy,
I have a couple of questions for you.

1. Since you are new to Access, are you also new to Visual Basic for
Applications programming? This is the only way you are going to be able to
handle this kind of validation.

2. How do you propose populating this Date table? Do you want any new
entered into the main table to also be entered in the Date table? If so, you
may not need the Date table at all, because you can just write code to
verify that the date already exists in the main table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Randy]

Lynn

I do not know how to come up with code, but can follow instructions on
how to put code into an application. Right now I am trying to convert
a number of Lotus Approach programs to Microsoft Access and you only
needed to know how to do macros with the Lotus Programs I set up. I
have been able to convert most of the programs but there are a few
tasks that require coding in some of the programs and I was hoping I
could get some help with those.

As far as the Validation Date Table. Staff each month would enter the
dates in the Validation Date Table to prevent incorrect dates being
entered by other staff in the Main Table. These dates would be things
such as holidays or days they would not get appointments.

 

[Guest]

Why not use a ListBox based on a query of your ValidDate table. Set criteria
for the query to >=Date() so only future dates will appear.

 

[Lynn Trapp]

Randy,
This is untested code, but you can try this in the BeforeUpdate event of
your date combobox.

Dim v_Num_Dates As Integer
Dim rsCheckDate As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rsCheckDate = db.OpenRecordset("Select InvalidDate from InvalidDates
WHERE InvalidDate = " & Forms!Payments!txtPaymentDate)

v_Num_Dates = rsCheckDate.RecordCount
If v_Num_Dates > 0 Then
MsgBox "You have entered an invalid date."
Cancel = True
End If

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html