Full Version: Subform Not Refreshing
UtterAccess Forums > Microsoft® Access > Access Forms
koclea
Hi
I have a form/subform. The form has 2 unbound combo boxes which "lookup" the selection critera. The subform underlying query has statements in the criteria like =[Forms]![frmTimesheets]![StaffNo]. When I make my selections in the combo boxes nothing happens, the subform remains unpopulated. However, if I flip back to design view and then back to form view, the data in the subform appears. I tried puting a "Refresh Form data" button on the form but it just displays a message "The command or action 'refresh' isnt available now".
Any ideas anyone?
thanks in advance, koclea
JayNoelOlimpo
Hi;
Have youre tried .ReQuery ?
HTH.
sredworb
Hello,
On the AfterUpdate of Combobox try
Me.NameofSubform.Requery
See what happens.
jerry
koclea
thank you. I put Me.frmTimesheetRosterDataSubform.Form.Requery in a "change" event and it worked.
sredworb
Great!
JayNoelOlimpo
Glad we could be of assistance. thumbup.gif
GOOD LUCK!
AccDB4Free
Hi Jay!
I have the same issue, but mine is my subform is residing in my tabctl page, how would I reference the .requery event.
thank you in advance,
ca
cheekybuddha
Hi ca,
ou'd do it exactly the same - the tab control should have no bearing.
Me.NameOfSubformControl.Form.Requery
Note that the NameOfSubformControl may not necessarily be the same as the name of your subform.Often it is called something like 'Child1'. To find out its name you have to click on the very edge of the subform in design view.
hth,
d
AccDB4Free
Hi Cheeky,
Thanks for the quick reply, it works perfectly, but why is it that the tabctrl has no bearing,what I was struggling to
do is I reference the name of my tabctl then the name of my subform,anyway thank very much.
I have another things to ask you, the below code is working fine when the selected student has a record, it did open a form and display the correct student on double click of my listform, but if the student has no record, it always give/show the first record (i.e. student 000001).
How can I fix this.
code:
-----
Private Sub lstStudent_DblClick(Cancel As Integer)
On Error GoTo Err_lstStudent_DblClick
Dim db As DAO.Database
Dim rst As DAO.Recordset
DoCmd.OpenForm "frmEditStudentRatings"
Set rst = Forms!frmEditStudentRatings.Recordset.Clone
rst.FindFirst "[StudentID] = " & Me.LstStudent
Forms!frmEditStudentRatings.Bookmark = rst.Bookmark
'Forms!frmSales![Print Client Equip Details].SetFocus
'DoCmd.Close acForm, Me.Name
Exit_lstStudent_DblClick:
Exit Sub
Err_lstStudent_DblClick:
MsgBox Err.Description
Resume Exit_lstStudent_DblClick
End Sub
----
Can I add additional feature, suppose the student selected has no record(s), hide the subform, and I want to display a msgbox "this student has no record, would you like to add record", (YesNo), if Yes pop a form to add, if No close/clear the msg box and back to listbox. NOTE: the listbox is my mainform, and 1 subform to display the related records of selected student (link by studentid).
Thanks again cheeky,
ca
cheekybuddha
Hi ca,
Yes it is strange that a tab control has no bearing, but that is the way it is - it is probably easier in the long run! grin.gif
Please explain the steps you want to go through again.
What is the name of your mainform?
Also, what is the RecordSource of frmEditStudentRatings?
d
AccDB4Free
Thanks cheeky,
Yes I agree with you.
Ok here it is,
My mainform is "frmSearchStudent", with a listbox showing names of students (bound to tblStudent) link with StudentID.
My RecordSource of frmEditStudentRatings:
SELECT tblStudentsAndSchoolYear.StudentID, tblStudents.YearLevel, tblStudents.Gender, tblStudents.SFirstName, tblStudents.SMiddleName, tblStudents.SLastName, tblStudentsAndSchoolYear.SYID, tblStudentsAndSchoolYear.GrdgID, tblStudentsAndSchoolYear.SectionID, tblStudentsAndSchoolYear.SubjectID
FROM tblStudents INNER JOIN tblStudentsAndSchoolYear ON tblStudents.StudentID = tblStudentsAndSchoolYear.StudentID;
Also my frmEditStudentRatings, has a subforms links with student ID.
STEPS:
1. Open mainform
2. Select/double click name/student from the listbox display
3. frmEditStudentRatings form poping up (displaying related records to the student selected)
(which is the current code is doing), PROBLEM is, if the student has no record, it is showing always the student 000001 and its related records.
THere is the thing I wanted to happen.
1. I want to display a msgbox saying "selected student has no related records, would you like to add/update?!" (YesNo), if Yes poping up a form to enter record, if No, clear/close msgbox and setfocus to listbox in mainform.

I hope my explanation is clear and inorder.
Thanks cheeky,
ca
cheekybuddha
OK, the best thing to do is check whether the Edit form will contain related records first before opening it.
!--c1-->
CODE
Private Sub lstStudent_DblClick(Cancel As Integer)
On Error GoTo Err_lstStudent_DblClick
    Dim rst As DAO.Recordset
[color="green"]'   Check something was actually clicked in the listbox[/color]
    If Not IsNull(Me.lstStudent) Then
[color="green"]'       Use a DCount() to see if there are any related records
'       in tblStudentsAndSchoolYear[/color]
        If DCount("*", "tblStudentsAndSchoolYear", "StudentID = " & Me.lstStudent) > 0 Then
[color="green"]'           Records Found![/color]
            DoCmd.OpenForm "frmEditStudentRatings"
[color="green"]'           Find the first record for this student[/color]
            Set rst = Forms!frmEditStudentRatings.RecordsetClone
            rst.FindFirst "[StudentID] = " & Me.LstStudent
[color="green"]'           Navigate to the record[/color]
            Forms!frmEditStudentRatings.Bookmark = rst.Bookmark
[color="green"]'           Clean-up, free up resources - Important![/color]
            Set rst = Nothing
        Else
[color="green"]'           No related records! Ask if user wants to add one[/color]
            If MsgBox("selected student has no related records, would you like to add/update?!", _
                      vbYesNoCancel) = vbYes Then
[color="green"]                ' Open your add form here[/color]
            End If
        End If
    End If
End Sub

The DCount() checks how many records there are in tblStudentAndYear that have the StudentID selected in the listbox. If it's zero then there arre no related records, otherwise there are.
So your edit form is not opened if there are no related records.
hth,
d
AccDB4Free
Hi cheeky,
Thank you very very much for the code, I love the comments you included, (how i wish all the gurus out there will do the same) with these things, I know exactly what the codes will do.
I will try the code, and let you know.
Agan many many thanks,
ca
AccDB4Free
Hi Cheeky,
The code is worked perfect, thank you thank you thank you very much, as always you are my savoir!.
One thing more, the empty subform within that mainform is look bad when no data to display, how can I hide the subform (this subform is on the mainform) if listbox selected name is no record.
Right now, I am experimenting some code to hide the subform when selected name has no records..
Thank you again cheeky,
ca
cheekybuddha
Hi ca,
Well this is fairly simple now. To hide the subform you can hide the subform control.
CODE
    Me.SubFormControl.Visible = False

To show it again you just substitute True for False.
In the code above, place the respective line of code in each bit where you have found records and where you have not found records of the 'If' statement.
hth,
d
AccDB4Free
Hi cheeky,
How is this all!?..
Am trying without success the "me.name of mainform.form.name of my subform".
Thanks again,
ca
cheekybuddha
'Me' is a 'shorthand' andrefers to the mainform, so no need to refer to it again.
You could have used:
Forms!NameOfMainForm.SubFormControl.Visible = False
hth,
d
AccDB4Free
Hello cheeky,
Thanks for the explanation, I would not know that..
thanks,
ca
AccDB4Free
Hi cheeky,
nother problem comes up.
On the product of my listbox double click, I want to print the record which come up with the form. I have made my filter query "[Form]![frmEditStudentRatings]![StudentID]", it works manually, when I click the report button within
the header of my form (frmEditStudentRatings) it pops up a small query parameter box to fill of the student id,then it will print the record.
I want the report open based on the student record display on the form (frmEditStudentRatings), without having
to input the parameter, because it is already filtered by the form.
FOr is it maybe that may student id on this form is locked?..
Thanks again for the help,
ca
hfreaves
Going back to the problem of 'if there is no matching student record, the subform displays the first student's record' problem - First , this is just an alternative; the solution you have (dcoutn matching records and branch accordingly) is certainly a correct one. (I'm sure there are many alternatives). An alternative would be to staty with your initial strategy using the 'Findfirst' method to look for a matching record, but add the conditional based on the 'findfirst' result. The '.Nomatch' property is the one i am suggesting:
Code
...
rst.Findfirst ....(as you had it )
'here's the conditional branch
'If rst.Nomatch is true, then no matching record was found
If rst.Nomatch then
actions here to make subform visible and populate it as you had before
Else '.nomatch false means a matching record was indeed found
'actions here to advise user of lack of Student record and allow coice to bring up form for record entry
Endif
Just a suggection which uses a few less lines of code
cheekybuddha
Hi ca,
heck the criteria in your query. I think it should be:
[Forms]![frmEditStudentRatings]![StudentID]
You are referencing the 'Forms' collection, not the 'Form' collection.
@hfreaves - one of us has misunderstood the original prAs I understood it , the problem was that the form that was opened ('frmEditStudentRatings') went to the first record if there were no related records, and not the subform on the calling form.
rst is the RecordsetClone of the form being opened and not of the subform. In order to access the RecordsetClone in the code above to perform the 'If Not .NoMatch ...' test the form must be opened, but I think the idea was not to have to open the form if there were no related records. As far as I can see ca has given no indication that the subform and the form 'frmEditStudentRatings' have the same RcordSource.
However, I do have a habit of misreading the situation sometimes but I hope ca would have corrected me by this stage!
d
AccDB4Free
Hello cheeky,
onderful it works perfect with the addition of "s".
Thanks again cheeky,
ca
PS.
Ohfreaves, thank you for having look of my post, cheeky has given all the solution, the pop up form of the related records, and his follow up post to hide my subform. Guys thank you thank you very much for all your times.
ca
cheekybuddha
You're welcome ca. I'm glad you got it working. thumbup.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.