Cannot do data entry

[Ayiramala]

Hi,



I have been trying to create a db for the schools in our district, with moderate success. Everything is setup, but the problem is, when I try to enter data, the records do not update. When I try to add a new school in tblSchools it is not added. The DistrictID is not addedin the table tblSchools. Similarely SchoolID is missing in tblStaff.

Could you please take a look at the sample attached?

Many thanks for all your help.

[Ayiramala]

If I use a form - subform setup instead of opening the forms separately(in a new window), data can be entered without a problem. So that may be the problem? If that is the case, is there a way to keep the existing design? How do we connect the two forms?

Edit: So it is like how to make a continuous form the subform of another continuous form, right?

[June7]

Perhaps you want http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

[ssanfu]

I can not tell where you are headed with this db. It doesn't look like you have drawn out a plan on paper/whiteboard/etc before starting Access.

Here is your relationship window




So starting with the unbound form "frmSelectDistrict", you select a district, then click the select button.
The code that is executed is

Private Sub Command4_Click()
    DoCmd.OpenForm "frmSchools", acNormal, , "DistrictID = " & Me.Combo2 & " "     '  (1)
    DoCmd.GoToRecord , , acNewRec      ' (2)
    DoCmd.Close acForm, "frmSelectDistrict"     ' (3)
End Sub

1) this line opens form "frmSchools" and filters the records to match the value of Me.Combo2
2) this line goes to a new record
3) then a form is closed.

Nowhere has a value been added to any field for frmSchools.DistrictID. All that happens is the records that match the DistrictID are displayed.
You need to have a combo box on the form "frmSchools" to select the DistrictID.


For form "frmStaff", you need to add a combo box to select the "SchoolID".
Also, the field "StaffName" should be split into two fields: StaffFName and StaffLName.


-----------

I was wondering,
can a staff member work at more than one school at the same time?
can a staff member teach more than one subject?
If either is true/yes, you will need to change the structure - you will need to add a junction table (or 2).

[Ayiramala]

Thanks for the detailed information. Much appreciated.

I first set this up as a form - subform - sub-subform (to enter district, school and staff details). And it worked properly. But then I thought this continuous form may look better. I then changed it, and messed it up.

Other than this, did you notice any errors in design? Please let me know.

To answer your questions: no, a particular staff member works only in one school, and he teaches only one subject.

[ssanfu]

Other than this, did you notice any errors in design?

No errors jump out.....You don't have spaces in names, no reserved words, no look up fields (in tables)...
I use a different naming scheme, but that is me.

I would split the field "StaffName" into "StaffFirstName" (or "StaffFName") and "StaffLastname" (or ("StaffLName").
Instead of "PhoneNo1" I would use "PhoneNum1"
If "SchoolMailID" and "StaffMailID" are for E-Mail addresses, I would change the names to "School_eMail" and "Staff_eMail".



Here is an example with form/sub form design