Validation

[Guest]

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

 

[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

 

[Guest]

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?

 

[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.

 

[Guest]

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?

 

[Guest]

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?

 

[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?

 

[Guest]

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

 

[BruceM]

I see that way back in this thread I must have pressed the Shift key while
typing Me.txtNewInfo>SetFocus. The > needs to be a period. I repeat that
control names (as well as field names) should contain only alphanumeric
characters and underscores, but since you are using a name with a space you
will probably need to replace the space with an underscore in the code. If
you start typing in the code window, and if after you type Me. you see a
list from which you can choose, then I think you will see that Access has
replaced the space with an underscore.

 

[Guest]

Thats great ! It's worked !

One last thing - the message box appears and asks for the field to be
completed but i can still exit the record....but i dont want the user to be
allowed to exit the record until the other field is completed.

 

[BruceM]

You could use the form's Before Update event with something like this:

If IsNull(Me.Field1) Then
msgbox "You need to enter something in Field1"
Me.Field1.SetFocus
End If

If IsNull(Field2) Then
msgbox "You need to enter something in Field2"
Me.Field2.SetFocus
End If

You could also place the code (or similar code) in the Exit event of the
second text box (Report_Number, to which you set the focus), or an
appropriate event in any other control. For instance, if there is a command
button to print a report you could add the code to the button's Click event.
Or you could just make it a required field in table design view, except that
the default error message is rather enigmatic.

 

[Guest]

For info:

The 2 fields I am trying to update are

1) Reported to vo (date)

And

2) Report Number

I have already entered the following :

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

This tells users to update 'Report Number' if they have updated 'Reported to
vo (date)'. This only tells them to update the field, but I want to make
sure they can't exit the form without inserting a 'report number' (only
providing they have inserted a date in the field 'reported to vo (date)')
..If they have not entered a date in the 'reported to VO (date) field then it
doesn’t matter about 'Report Number' field. Its only when they do enter a
date in 'reported to vo (date)' that I need that message to appear.

So, what message in what field do I need to insert in order for this to work?