Bound Items - Database Schema/Relationship question

[Austin]

Okay this seems like it should be easy but I can't figure it out.

I have a fortm that lets the user select a university then it uses a sync'd combo box to select the academic area(math, business, etc).

I want the users to be able to edit the course data but when they select a institution thats not associated with that class it lets me enter that data. It's causing the data in my institutions table to get messed up.

How do I lock it where if the class doesn't exist for the specific institution then it won't let you save the form.

I know this is the most complicated description but it's all I have.

Maybe this will help

Three tables affected are

History_Course_Lines
-Student_ID
-Semester
-Grade
-ClassID <--this is the problem child

Classes
-ClassID
-Acad_Discipline
-Hours
-InstitutionID
-Course_Number

Institutions
-InstitutionID
-Institution(name)
-School_type

The form that lets you add courses is a subform for the main student_id form.

The subform lets you select first Institution -> Course -> Course Number

This works great if I just bind course_number to ClassID in the History_Course_Lines table, but if I want people to be able to edit the other two fields then I bind them to the respective fields Acad_Discipline and Institution then it starts messing up my tables, it'll let you go back in and go to records and change stuff that shouldnt' be possible. Such as putting a class with a institution that doesn't exist. Say Institution 2 doesn't have class 3, well it lets you do this and then renames the institution.

Totally messed up but I can't figure out how to lock it down.

[ibgreat]

Upon initial reading your post, I think the issue is that you are actually using bound forms. When you use a bound form and make a change to it, the change is automatically made in the underlying table. You may be better off unbinding your form or at least the two fields you want to be editable. If you did this you would have to get rid of the navigation buttons and manually code some navigation and command buttons.

I am going to reread and draw it out to figure out exactly what you are trying to accomplish.

[ibgreat]

I want the users to be able to edit the course data but when they select a institution thats not associated with that class it lets me enter that data.

This would be done through a VBA conditional statement (If...Then) in afterupdate event. You will be setting your combo boxes RowSource manually so get rid of what is in there now.

I don't know your object names but it would look something like:


Dim strInstitutionAdd As String
Dim strInstitutionType As String
Dim strSQL As String

If cboAcadArea.column(<Enter the column that the InstitutionID is in on your query - 1>) <> cmbEmployment Then

strInstitutionAdd = InputBox("Enter the name of the Institution " & _
"you would like to create.")

strInstitutionType = InputBox ("Enter the type of Institution.")

strSQL = "INSERT INTO (Institutions.Institution, Institutions.School_Type) " & _
"VALUES (strInstitutionAdd, strInstitutionType)"

DoCmd.RunSQL (strSQL)

Me.cboInstitution.Requery
Me.cboInstitution.SetFocus


End If




You would also need a conditional statement on your OnClick Event to ensure that an institution has been selected.


If cboInstitution = -1 Then
MsgBox ("You need to select an Instution first.")
End If



I didn't bother to run this through a compiler, but it should give you the general idea to achieve what you want. There are some additional things you will likely want to do (e.g., reset the course combo box when the form changes. If you need additional help post the SQL statement from your combo box to select the course and the names of your combo boxes so we can use your exact info.

[CreganTur]

I want the users to be able to edit the course data but when they select a institution thats not associated with that class it lets me enter that data. It's causing the data in my institutions table to get messed up.


The simple answer is that you do not want to display classes that are not associated with the chosen institution.

For the combobox where they choose the University I would suggest using either the combobox's Change event of AfterUpdate event to fire some code. Create a series of Select Case statements that will set the 2nd combobox's rowsoure via VBA so that it only shows the classes that exist for that university.

You can use queries to accomplish this, or if queries aren't a good option, then you can create lists- lists would be more time comsuming... I'm just offering both examples since I don't know how your tables are related.

[Austin]

I've actually already done that but you can still type data into the box and it also leaves whatever class was selected. I guess if I just set the value = "" that would fix the problem.