Validation

S

Shep99

I have a table which contains many fields.

One of the fields is a 'date' field and this remains blank until someone
decides to use that field. I would like a validation rule 'if a date is
entered into the date field, a different field must be updated with
information before exiting the record (this information will be different
each time).

I also have queries and forms that run from this table.

Any ideas on how I can achieve this?

Thanks
 
B

BruceM

In a form based on the table, bind a text box to the Date field. By the
way, don't call the field Date. StartDate, EndDate, etc., are good, but not
Date by itself. I will call the text box txtStartDate, and the text box
bound to the field that needs to change txtNewInfo. In the After Update
event for txtStartDate, you could add something like the following between
Private Sub and End Sub:

msgbox "Text of message"
Me.txtNewInfo>SetFocus
 
S

Shep99

For info:

The other field name is 'Report number'

I have gone into the start date text box and in the 'after update' field i
have entered:

msgbox "enter Report number"
Me.Report Number>SetFocus

But I'm getting the following message:

Menu can't find the macro 'msgbox" Enter Report Number"Me.'

Any ideas?
 
B

BruceM

It sounds as if you opened the form's property sheet (five tabs, one of
which is Event). If so, you are in the right place, but are not quite
following the necessary procedure. Click into After Update, then click the
three dots at the right side of that line. Click Code Builder, then OK.
You should see a window with the lines:

Private Sub txtStartDate_AfterUpdate()

End Sub

and the cursor blinking between them. Add the two lines of code.

A few points. See help for msgbox (or message box) for more information.
You can add a title, for one thing. As it is the user will see a generic
text box with the message "enter Report number".
When you create a text box by dragging a field from the field list, Access
automatically uses the field name for the text box name. I prefer to
identify text boxes with the prefix txt followed by the field name, just so
I can be clear when I need to refer to one or the other in code. If you
create a text box, then bind it to a field, Access will give it a generic
name such as Text 8. This can be very inconvenient as you try to remember
which text boxes go with which fields.
I recommend that field names contain only alphanumeric characters and
underscores. Access can work with spaces, but when you are writing code
field names with spaces will need to be enclosed in square brackets. I
don't know if this is invariably true, but it is certainly true often enough
that it can cause inconvenience. Consistent naming conventions will save
you a lot of grief as your designs become more complex.
 
S

Shep99

Thanks for your help

I have done what you said but now get the message:

compile error

syntax error

The error line =

If (rs.EOF) Then
"There was an error reading the Switchboard Items table."
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Function
End If


any ideas?
 
S

Shep99

Shep99 said:
Thanks for your help

I have done what you said but now get the message:

compile error

syntax error

The error line =

If (rs.EOF) Then
"There was an error reading the Switchboard Items table."
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Function
End If


any ideas?
Click to expand...
 
B

BruceM

What is that code? It just showed up in this discussion. My one
observation is that the second line of the code: "There was an error
reading the Switchboard Items table" appears to be a message box item, in
which case it would need to be preceded by msgbox, but the If line makes no
real sense, so I don't know what to make of the whole thing. If you put an
apostrophe in front of each line of code it won't be run, but that doesn't
explain where the code came from in the first place. Under what
circumstances did you receive the error message?
 
S

Shep99

Bruce,

The message i get is:

Compile Error - Expected function or variable

Private Sub Reported_to_VO__date__AfterUpdate()
msgbox "Enter Report number"
Me.Report Number > SetFocus
End Sub

The 'SetFocus' field is highlighted

Any idea's?

Thanks

Richard
 
Top