Module question

[leon_hi]

I have a database that has 3 main tables:

tbl_StudInfo
tbl_Core
tbl_major

tbl_StudInfo is related to both tbl_Core and tbl_major:

tbl_StudInfo is related to tbl_Core with the Unique Primary Key "SID" from tbl_StudInfo and the Unique Primary Key "SID" from tbl_Core.

tbl_StudInfo is related to tbl_major with the Unique Primary Key "SID" from tbl_StudInfo and the Unique Primary Key "SID" from tbl_major.

Each table has a lot of information so I created three separate forms to view the information.

If I create a fourth form with tab control, I am still running into the issue of too many fields on one form which deletes the autonomy of the forms.

To keep the functionality in addition to the autonomy of the forms, I bound the individual forms in a Navigation Form. I am using the navigation form with the tabs across the top, but have included unique identifying information as a basis for the navigation form (SID, FirstName, LastName, Cohort) and then added the frm_StudInfo, frm_Core, and frm_major on tabs below the unique identifying information.

With this, I have two issues:

Issue 1:
How do I get these tables to "talk" to each other so that the records on one form relate to the other two forms? For example, records in tbl_StudInfo have general information. Each record has a coinciding record (related by the SID field) in the tbl_Core and tbl_major. If I choose record 3 in the first form (frm_StudInfo) in the Navigation form, it will not choose record 3 in the tbl_Core form when I navigate to the second form (frm_Core) - it goes back to the first record.

I tried to solve this issue with a module and I thought it worked. Here's my coding:

Public Sub Command31_Click()
On Error GoTo Err_Command31_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "tbl_Core"

stLinkCriteria = "[tbl_Core]=" & Me![tbl_Core]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click

End Sub


but it didn't work...

Issue 2:
I don't know if this was caused by creating the module, but I cannot add new records to the database using the forms. I can only update. I receive the error that states the (new) record cannot be found in tbl_StudInfo with matching SID.

Thank you in advance.

 

[jdraw]

Please tell us in plain English what these tables represent. Also, describe a scenario of the interaction between these tables.

 

[arnelgp]

since there is no Master Link Fields/Child Link Fields in navigationsubform, the solution i found is to save the SID on text box on the main form (navigation form). and filter the other forms with it.

now on adding records on the other form (info and major), set the SID to the SID of the main form.

these will explain much better.

 

[leon_hi]

Please tell us in plain English what these tables represent. Also, describe a scenario of the interaction between these tables.

Sorry for being so vague. This is the first time I have ever posted to a forum.

tbl_StudInfo is general information about students (name, major, phone, email, enroll date, graduation date, etc)

tbl_Core is core curriculum all students must take (English, Math, Science, etc). This includes the date taken, whether the student successfully completed the course or whether the course was accepted as transfer credit from another institution.

tbl_major is the major curriculum students in a particular major must take. Same principle as tbl_Core, with the exception that only students in that particular major will take these courses. (i.e., nursing students only take nursing requirements)

Does this help?

Thanks!
leon_hi

 

[leon_hi]

since there is no Master Link Fields/Child Link Fields in navigationsubform, the solution i found is to save the SID on text box on the main form (navigation form). and filter the other forms with it.

now on adding records on the other form (info and major), set the SID to the SID of the main form.

these will explain much better.

Thank you for the attachment and suggestions. I have the SID on the main nav form above the subforms, but the subform does not seem to recognize the SID and keep the information in the subform consistent with the selected student. Does that make sense?

Thanks!

 

[sneuberg]

I think arnelnp is suggesting that you also need to filter the forms in the tabs. Since the forms in the tabs are opened each time you switch tabs you could probably do the filtering in the form open. If the text box in the main nav form is named txtSID for example then the code in the form open would be something like"

Me.Filter = "[SID] = " & Me.Parent.txtSID
Me.FilterOn = True

 

[arnelgp]

is there anyway you can have your db uploaded, so we can see why it is not working?

 

[leon_hi]

I think arnelnp is suggesting that you also need to filter the forms in the tabs. Since the forms in the tabs are opened each time you switch tabs you could probably do the filtering in the form open. If the text box in the main nav form is named txtSID for example then the code in the form open would be something like"

Me.Filter = "[SID] = " & Me.Parent.txtSID
Me.FilterOn = True

Thank you so much! That solved the problem. I appreciate everyone's help!

Leon_hi