Display Records then Add Longitudinal Data

[watembo]

I have a student database and I want to update class manually as the students graduate each year. In my form I want to search the name of the student and retrieve records from last update from a query I have created. To find the record, I search from a combo box with a code:

Me.Recordset.FindLast "StudentID = " & Me.SName

The fields i want displayed for that are LUpdate, LClass and LStatus

I am stuck from there. At least I get no error message. Please kindly help.

Regards,
William

 

[vbaInet]

William, do a search on the forum for "search forms", it's been covered many times.

By the way, what do you mean by "last update"?

 

[watembo]

Thanks vbaInet. I would appreciate something more specific since what I have searched doesnt quite answer my dilema. "last update" is simply the last record that was updated.

 

[vbaInet]

So what have you found and tried that didn't work?

 

[watembo]

Thanks vbaInet.

After some literature this is what I finally did and it worked:

Private Sub SName_AfterUpdate()
DoCmd.ShowAllRecords
Me.Recordset.FindLast "StudentID = " & Me.SName
Forms!frmStudLong!SName.SetFocus
End Sub

Only problem that if it doesnt find the record it shows data for the very last record in the list instead of showing blanks.

Regards,
William

 

[vbaInet]

I need to see how your records are ordered and the SQL of your query. Upload some fictitious sample records in a spreadsheet and paste the SQL of your form's Record Source here.

To upload, click the "Go Advanced" button below and scroll mid way down.

 

[watembo]

Thanks,

First Query

SELECT tblStudLong.StudentID, Last(tblStudLong.DateUpdate) AS LUpdate, Last(tlkClass.Class) AS LClass, tlkStatus.Status, tlkClass.ClassID
FROM (tblStudLong LEFT JOIN tlkClass ON tblStudLong.CurrentClass = tlkClass.ClassID) LEFT JOIN tlkStatus ON tblStudLong.Status = tlkStatus.StatusID
GROUP BY tblStudLong.StudentID, tlkStatus.Status, tlkClass.ClassID;

RecordSource

SELECT qrystudlong.StudName, qrystudlong.StudentID, qrystudlong.LUpdate, qrystudlong.LClass, qrystudlong.Status
FROM qrystudlong;

The longitudinal records are updated in a subform linked on StudentID
and has similar fields.

Data:

StudName StudentID LUpdate LClass Status ClassID Student1 11052 27-May-15 Nursery (Pre Unit) Active 3 Student2 2 07-Jan-15 7 Active 10 Student3 3.2 07-Jan-15 3 Active 6 Student4 4 07-Jan-15 8 Active 11 Student5 6.1 07-Jan-15 7 Active 10 Student6 5 27-May-15 Baby Class Active 1 Student7 7.1 18-May-15 Baby Class Active 1 Student8 0 27-May-15 8 Active 11 Student9 6 18-May-15 Active
Student10 9.1 07-Jan-15 6 Active 9 Student11 9 23-May-15 6 Active 9 Student12 10 18-May-15 3 Active 6 Student13 12.1 23-May-15 3 Active 6 Student14 11 18-Jun-15 5 Active 8
Hope this helps

 

[vbaInet]

Upload the data in a spreadsheet as requested. What you pasted isn't legible.

 

[watembo]

Sorry about the data. I have attached

 

[vbaInet]

Tell me the fields you want to search by and also tell me names of the unbound search textboxes.

By the way, you do realise that your IT department/Admissions Officer/Data Coordinator should already have the updated classes after the roll-over and they will be able to provide this data?

 

[watembo]

Sorry for the late reply. The name field is in a combo box unbound name=SName. Afterupdate it should display the corresponding <LUpdate>, <LClass>, <Status> and <StudentID> from qryStudLong.

This works well but I need to update data in tblStudLong from where the above query was drawn based on when the class was last updated. I appreciate your comment on updates yearly when the class is rolled over - the data is available for each student. There are some, however, who need to be updated mid-year.

 

[vbaInet]

Ok. Here are some steps:

1. Create a form based on tblStudLong and include the relevant fields (including StudentID)
2. Drop a subform control onto your original form (i.e. the form bound to qryStudLong)... place it wherever you see fit
3. Follow the wizard and use the form created in step (1) as its Source Object and link it to your parent form via the StudentID field.

NB: This will only work if the original form's Default View is set as a Single Form. If you have it set as a Continuous Form or Datasheet let me know and I'll tell you what to do.