Synchronizing records of multi-tier tab controls and subforms

[macftm]

Hello,



I have created a main form which has a number of tabbed sub forms linked. I have used following code to filter users so only their details appear on the forms:

Private Sub Find_Pupil_Click()
Dim sSurname As String
Dim sRegClass As String

sUser = Me.txtSurname
sPassword = Me.txtRegClass

If DCount("*", "T_Pupil_Information", "Username = '" & sUser & "' And Password = '" & sPassword & "'") = 0 Then
   MsgBox "Incorrect User Details! Please re-enter your details", vbExclamation, ""
Else
   DoCmd.OpenReport "F_Pupil_Profile", , , "[Username] = '" & Me.txtUserName & "'"
End If

End Sub

All the fields on the form F_Pupil_Profile update appropriately. However, the sub forms do not update, they just hold the details of the first record located in table T_Pupil_Information.

Does anyone know how I can also update the sub form details so they are linked to the appropriate user?

Regards

Macftm

[June7]

Don't understand the issue. Are Master/Child links properties of the subform container set?

[macftm]

I'm not too sure sorry, I'am new to this level of database design. How do I check?

Thanks for your help once again June 7

Regards

Macftm

[June7]

Click the subform and should see edge highligted. This has selected the subform container control. View its property sheet, data tab, Master/Child links. Any thing in there? Click subform again and now the form should be selected and can view its properties. Click on any control of the form and view its properties.

Do you want to provide db for analysis? Follow instructions at bottom of my post.

[macftm]

Hi June7, Thanks for your comments. I did what your said and found the Master/Child link. However, when I click on either of the properties I get the message "object variable or with block not set" ???

I have attached the db. If you wouldn't mind looking at the db and provide me with some feedback that would be great.

Regards


Macftm

[June7]

Too much scrolling on form! Annoying and confusing for users.

This is the first time I've seen nested tab controls. This requires a 'middle-man' subform for the second tab control. The middle-man has no RecordSource so the subform on the second tab control has no parent for the subform container links. I set RecordSource and links for the middle-man but when I try to set the links on PE get the error you encountered. I've never run into that before in this context.

Since you want the profile form to open to a single pupil's records, the quick fix to synchronize the records may be to have the RecordSource of form F_S1_PE include a parameter that references the username on another open form.
SELECT T_Pupil_Information.*, T_S1_PE.*
FROM T_Pupil_Information INNER JOIN T_S1_PE ON T_Pupil_Information.Username = T_S1_PE.Username
WHERE (((T_S1_PE.Username)=[forms]![F_Pupil_Profile]![UserName]));

However, this will not allow display of username, first name, surname on form F_S1_PE unless record already exists in T_S1_PE. Personally, I would not repeat display of this info on the subforms, only on the primary. Will need code to set the value of the Username in F_S1_PE record if creating a new PE record.

Decided I better take a closer look at the data structure. It appears the tables are not normalized. Multiple similar name fields is one indicator. What are the data relationships? Will each student have more than one PE? Also, do you intend to have a separate table for each subject each semester? RED FLAG HERE. If these tables will all have the same fields then should be one table. I suggest you step back from form and report building and better define the data entities and how they should be related. Review: http://forums.aspfree.com/microsoft-...es-208217.html

It is a balancing act between normalization and ease of data entry/output and possibly some violation of convention will best suit your situation (I am guilty of that) but really need to have the data structure fleshed out before proceeding.

Note that I renamed the thread to something that seems more accurate for the issue and placed code in code tags.

[macftm]

Hi June 7,


thanks for looking over my db and providing me with some extremely useful feedback. Sorry about the vertical scroll bars on each form. I do not intend to have all these in place and the final version. I am just using them whilst designing the forms.


Thank you for the link regarding Rational Database Principles. I have had a look over it has helped a great deal. I have revised each table in my db and I think the relationships in the attached db work better (comments would be gratefully accepted).

The only concern I have is regarding the subject tables. Each subject will have to have its own table as they will require different fields as they will want to ask pupils different questions and collect different evidence from each pupil. Do you have any suggestions how this could be made more manageable? At the moment I am thinking that I will need to create a separate table for each subject and year group (which is going to be very inefficient).

Regards

Macftm