Code Modification

[dr223]

Hallo,

I have this code which moves from one record to another. Is there a way I can modify it so that it can only naviagte from a field in a subform when clicked. The field name is called Session (which is built up of many Sessions 2003/04, 2004/05, 2005/06 etc). Also incase there are two identical Sessions displayed for example 2005/06 then 2005/06, I want it only to display one (2005/06) and go to 2006/07.

The code moves from one record to another and is:

On Error GoTo Err_Command91_Click


DoCmd.GoToRecord , , acNext

Exit_Command91_Click:
Exit Sub

Err_Command91_Click:
MsgBox Err.Description
Resume Exit_Command91_Click



Thank you

 

[ajetrumpet]

Is there a way I can modify it so that it can only naviagte from a field in a subform when clicked.

From a field in a subform, SURE! But what do you want to navigate TO?? The next field? The code you have right now will navigate you to the next record on the same field.

The field name is called Session (which is built up of many Sessions 2003/04, 2004/05, 2005/06 etc). Also incase there are two identical Sessions displayed for example 2005/06 then 2005/06, I want it only to display one (2005/06) and go to 2006/07.

I'm not sure I understand this. Please elaborate on it...

BTW, where are you from?? Isn't "Hallo" the greeting used in Holland?

 

[dr223]

I have a subform built with 5 fields one of the field is called Session (this field has a drop list of the study year) therefore 2006/07, 2007/08, 2008/09 etc.

I just want to navigate on this Session, so that when I click the button it takes me from one field of the Session to another. Therefore, for example a student has studied on 2006/07 and 2008/09, when I press the button I want it to display 2006/07, when I click it again displays 2008/09 etc. i have a text box for the field to be displayed on the main form.

The code there is for a whole record, I just want for the specific Session field.

Any ideas!!!!

I am from UK, and Hallo is a common greeting here....

 

[ajetrumpet]

I have a subform built with 5 fields one of the field is called Session (this field has a drop list of the study year) therefore 2006/07, 2007/08, 2008/09 etc.

If it has a dropdown, then it's getting its values from somewhere else (another "session" table maybe?). So, answer these...

** Does the subform display an entire record from some table when it is visible(i.e. do all of the 5 fields on the sub represent a complete record)??
** Where does the Session list values come from??
** What's the relationship bw/the main form and subform??

I'm sure this is possible, but there hasn't been enough explanation of the relevant facts to give a comprehensive answer here. (sorry!)

 

[dr223]

Adam , please see a sample attached.

Note: The button is just below the Upload and the Session on this database are in a different format 02/03, 03/04, 05/06 etc

Thank you!!!

 

[ajetrumpet]

OK Dr,

Tell me what we're after here. Here are the facts that I recognize...

**The subform is for data entry (because you have combos AND controlsources set).
**The "Play" button (as of right now) is for moving through the records on the subform, if there is more than one record present.

I am guessing that you are wanting to: display the records that correspond to the Session (year) that is entered in the Main Form's "Session" text box.

Does that sound about right?

 

[dr223]

Nop, what I want is display the record as normal,

Then use the "Play" button to display Session i.e., 03/04, 04/05, 05/06 etc depending on what is on the subform, at that time for that particular record.

For Example for Student_Id: HUL555
I want when you click the button it gives 05/06, then when you click again 03/04, when clicked again 02/03 (note it doesn't give 03/04 again as it has already been displayed before), then 01/02. Thats all!! There are no more sessions to display for this record (HUL555).

After this task I want to SUM the Amount per session per record. So that, I can just click the "Play" button show the year such as 03/04 on this example and give the total as £ 9252.00 on another field.

But first things first. When I can easily move on the session field then the next task will be try to sum each session and display the total_amount. On HUL555 example the SUM will ONLY be applicable to 03/04 but the rest will display one amount only.

I hope am clearer now.

Thank you

 

[ajetrumpet]

If you want to do any of this first, you have to get the data in your tables that would be located in your joining field!!

E.G. - your tables "FeeDetails" and "StudentDetails" contain an intermediate relationship on StudentID, whereas it SHOULD be a one-to-many relationship. Either that, or you can join the two by the "HESA" field, because it obviously serves the same purpose (with regard to identifying data).

You can't query the data you want without a "connector" between the two tables. You have it now, but you don't have any data in the studentID field of the "Fees" table, and that's just like not having any at all.

 

[dr223]

I connected the table using the HESA, because it is unique for each student and a Student might have many HESA values not one, at any one time.

The Student_ID is also unique, but I never also set it as Primary key on the StudentDetails because there is a possibility that a student will have more than one HESA value and hence student_Id duplicated. The form and subform are linked by the HESA number.

What it does is check the HESA value in the StudentDetails then check the HESA value on the subform and display the data on the respective fields. Thats the link between the Form and subform.

I dont want to store the Session value nor the Total_Amount when displayed on the main form, it's just for viewing purposes. Its like when you look at it and the fees total is correct, you upload the data by checking the check box besides it.

Thats all what I want to do.

 

[ajetrumpet]

I am going to give you example of what should happen here, but questions first...

** when you push the play button, you want the subform to change records, based on the studentID you are querying, right? I mean, display the next "FeeDetails" record that is in the table, right?

** How do you want to specify which studentID to look at?

** Or are you going to be looking at Fee records based on the SESSION, and NOT the studentID that is associated?

 

[dr223]

** Or are you going to be looking at Fee records based on the SESSION, and NOT the studentID that is associated?

Yes thats what I want to do.

What I have done is create a text box on the subform footer and called it txtsession1
in the control source added =[Session]

Then on the text box near session on the main form added this source

=FeeDetails.Form!txtsession1

So, what happens now is that it reads the session whenever you move your coursor to the drop down list as recorded on the Session, so when you go like 03/04, it changes, when you go 07/08 it changes to reflect the cursor selection.

However, what i want to do is have a code on the "play" button and be able to move from 03/04, 07/08 and whenever there are 2 payers per year then it just brings one year 02/03 for instance and when you click the "play" button it moves to the following year.

Any ideas on this now, we are getting there....

Many thanks

 

[ajetrumpet]

One question...

why does the subform not load all the records when the main form opens?

The sub is not specified as data entry!

 

[dr223]

The subform is not a dat entry but can act as one when needed. Some fields act as data entry fields.

Secondly, I set the subform as blank when the main form opens so that you can either find a record or start with the first record.

Please find the abopve attached specimen, for clarity many thanks