Link to home
Start Free TrialLog in
Avatar of Sbovino
SbovinoFlag for United States of America

asked on

Building a form for multiple table entries

Experts:


I have a table that contains data for schools.  THe table structure is as follows:

buildingid
gradeid
teacher_count
student_enroll
year

My question concerns the form used to add data.  Right now I have a continuous form that requires the user to populate data for each grade using a combo box.  For example, a school may have 20 grade classifications that the user has to pull from a combo to add the data.  Is is possible to build a form that would have the grades listed on the form?  Example when the user opens the form for School A all the possible grades are listed and the user enters the counts for each grade.  I know the forms are not structured that way but it would sure make it easier for users to see a complete form and just tab to each entry.
Avatar of Markus Fischer
Markus Fischer
Flag of Switzerland image

I suppose you are working with a three-table structure, materializing a many-to-many relationship. Something like this:

   Buildings <1-n> YourData <n-1> Grades

Let's further imagine that you have a main form: Buildings, on which you display a continuous form to enter data relevant for each grade. When you start your data entry for a building, the sub form is empty, and you wish it to be pre-filled with the grades.

Is that correct, or does that resemble what you have in any way?

If so, there are basically two solutions: a) working with a left-joined dataset, which is a bit technical, and b) using an append query to pre-fill your table on request.

Please tell me if this is the right direction.

(°v°)
Avatar of Sbovino

ASKER

Yes, you are going in the right direction.  I do have a subform that you add to as you have described.

Steve
Let's try the simple case: the append query.

On the main form, create a button with the event in the snippet. As you see, it's a simple append query (which we could also create in the query grid) that creates all missing records. The query is run, and the subform is requeried.

It assumes that buildingid is a numeric key, and that the main form has a field of the same name to use as filter. You will need to adjust for your table names, naturally.

Good luck!
(°v°)
Private Sub cmdFillGrades_Click()
 
    Dim strSQL As String
    
    strSQL _
        = " INSERT INTO YourData ( buildingid, gradeid )" _
        & " SELECT " & Me.buildingid & ", gradeid" _
        & " FROM Grades" _
        & " WHERE gradeid Not In (" _
        & "   Select gradeid From YourData" _
        & "   Where buildingid = " & Me.buildingid _
        & "   );"
    CurrentDb.Execute strSQL
    subYourSubform.Requery
 
End Sub

Open in new window

Avatar of Sbovino

ASKER

This looks great - there is one more need however.  When I select grades I use a numeric field that indicates what type of school(elemetary, middle, high) - hence what grades. The grade table has a schoollevel for each grade.  For example, if the schoollevel fiedl = 3 only thoses gradid which are equal to 3 should be  selected.   This field is located in the main form so how would I adjust your code to select gradeid  but limit gradeid to schjoollevel = the vale in my form.  thanks so much for the assist.

Steve
ASKER CERTIFIED SOLUTION
Avatar of Markus Fischer
Markus Fischer
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sbovino

ASKER

Worked just great!!