MS Access - Form Validation Rule - referencing other forms or tables

[Larry1216]

I want to have a validation rule for a form reference another form or table in the same database.

I have a table [Reference 03 Data For Forms] with a field [RYEndDOS] in the Short Date Format. There is a form [Switchboard] where the user enters a date for the end of the year.

In the validation rule for a date field on a form, I have
<=[Forms]![Switchboard]![RYEndDOS] I have also tried referencing the table <=[Tables]![Reference 03 Data for Forms]![RYEndDOS]

However I recieve error messages when I try either of these. I used this technique successfully a few years ago (Access 97) but cannot duplicate it in Access 2000.

 

[M8KWR]

I have tested this within A97, and i got an error message when i was trying to reference the table, but when i referenced a form it worked fine.

All that i can say is to re-check you spelling of your form and field names...

Is the Switchboard form open when you are trying to validate???

 

[TheAceMan1]

How are ya Larry1216 . . . . .

I simulated this with [purple]no problem[/purple] (access 2k).

[ol][li][blue]Is the switchboard open[/blue] when you enter data is this other form?[/li]
[li]Is [blue]spelling[/blue] of names correct?[/li]
[li]Post the error you received![/li][/ol]

See Ya! . . . . . .

 

[Larry1216]

M8KWR and The ACEMAN1;

Thank you both for responding. I did not have the form open and that made the difference.

Is there a way I can use this type of validation rule without having the or table or form open?

Thanks, Larry1216

 

[TheAceMan1]

Larry1216 . . . . .

Yes . . . you can use the [purple]OpenArgs[/purple] arguement of the [purple]DoCmd.OpenForm[/purple] method.

In the calling method you use to open the form, copy/paste the following code:

[blue]   Dim Build As String
   
   If Trim(Me!RYEndDOS & "") = "" Then
      [green]'No Date message here[/green]
   Else
      Build = "<=#" & Me!RYEndDOS & "#"
      DoCmd.OpenForm "frmDates", acNormal, , , , , Build
   End If[/blue]

Then . . . in the OnOpen event of the opened form, copy/paste the following:

[blue]   Me![purple][b]DateControlName[/b][/purple].ValidationRule = Me.[b]OpenArgs[/b][/blue]

[purple]Thats it . . . give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .

 

[willir]

If you are referencing other tables (and forms), I have always found it better to reference the data in the table for current data, rather than referencing the form.

For example:

<=Dlookup("[RYEndDOS]", "[Reference 03 Data for Forms]", "YourWhereClause")

Your "where clause" is whatever condition you use to find the specific record.

Richard