Form Validation Rules

Question

I am trying to create a form to register students that restricts new entries based on a set of rules.

A student cannot register for more than 10 courses and

A student can register for a class provided it is not in the same course

I would like to know if I can use queries like course count and find course duplicates as validation rules when entering data into the form?

Answer 1

1.  I would like to know if I can use queries like course count and find course duplicates as validation rules when entering data into the form?

2.  In my form there are 3 fields

1. Course ID
2. Section ID
3. Student ID

I would like to limit the list in the second field (section ID ) based on entry in the first field (course ID)

1.  You cannot call a query as the ValidationRule property of a control or column (field), but you can do so as a CHECK CONSTRAINT on the table.  This cannot be done in table design view, but by executing code.  For an example see  this earlier thread.

2.  The Section ID combo box can be correlated with the Course ID combo box by giving it a RowSource property which references the Course ID control as a parameter, e.g.

SELECT [Course ID], [Course]

FROM [Courses]

WHERE [Section ID] = Form![Section ID]

ORDER BY [Course];

The Section ID combo box is then set to Null requeried in the AfterUpdate event procedure of the Course ID combo box with:

Me.[Course ID] = Null

Me.[Course ID].Requery

and requeried in the form's Current event procedure with:

Me.[Course ID].Requery

However, this raises a modelling issue.  By having both Course ID and Section ID columns in the table to which your form is bound you are introducing redundancy, because Course ID is functionally determined by Section ID.  The table is consequently not normalized to Third Normal Form (3NF) which requires all non-key columns to be functionally determined solely by the whole of the table's primary key.  Otherwise the table is open to the risk of update anomalies. The table should include only the Section ID column and the Course ID combo box in the form should be an unbound control.  You'll find an example in ComboDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.  In the demo the form illustrating the use of correlated combo boxes in single form view (first button on the opening menu form)  is the appropriate example in your case.

Answer 2

First, I said nothing about macros. Access has two programming languages, VBA and macros. These are totally different and separate. What I gave you was a VBA code snippet. VBA is much more powerful and flexible than the macro language.

Second, everything in Access is triggered by events. There may be several events attached to an object in Access. 

Third, what you are talking about is a standard technique called Cascading or Synchronized comboboxes. If you search on that, you will find several articles that explain how to do it. Basically, the second combobox is filtered by the selection in the first combo. However, to do this properly your tables need to be setup properly. 

This begs the question of whether the section list is unique to the course or can a section apply to multiple courses. 

By the way you don't have fields on a form, you have controls that may or may not be bound to a field in a table. This is a subtle but important distinction.

Answer 3

.......

Answer 4

Thank You, 

I really am quite new to access so this is my first time hearing of macros and before/after update events. Before I can implement the dcount I do have another problem.

In my form there are 3 fields

1. Course ID
2. Section ID
3. Student ID

I would like to limit the list in the second field (section ID ) based on entry in the first field (course ID)

Pseudocode:

after event (select form Section ID)

run Find Sections Query (return list of related Sections) 

Where (selected form entry for Course ID) == (Course_ID used in the query)

on 2nd field Section ID

How can I to use a macro to implement this?

Answer 5

Yes and no. The Validation Rules property is generally for simple rules that can be easily stated in an expression. 

However, you can certainly use the Before/After Update events and VBA code to test values:

For example. 

If Dcount("*","tblRegisterdCourses","[StudentID] = " & Me.StudentID) >= 10 Then

      MsgBox "You are only allowed to register for 10 courses!"

     Me.CourseID = Null

End If