Refreshing a subform

[aziz rasul]

On an unbound form, I have a list box and a subform whose record source is "qryContactinSlimwell". There are other controls on the main form. The list box has a list of names. The purpose of the subform is to give the details of the chosen name. Hence as each name is selected, the details in the subform should change accordingly.

When I select an item in the list box, the Click event of the list box is: -

Private Sub lstUnassignedWaitingList_Click()

    Dim strSQL As String
    Dim ctl As Control
    Dim x As Variant

    Set ctl = Me.lstUnassignedWaitingList

    Me.Refresh

    Me.frmcontactdetails.Visible = True
    Me.frmEnquirerSubformPrimary2.Visible = True
    Me.lblcontactdetails.Visible = True
    
    For Each x In ctl.ItemsSelected
        strSQL = "SELECT tblEnquirers.enquirerID, tblEnquirers.GPID, tblEnquirers.NHSNumber, tblEnquirers.Surname, tblEnquirers.firstname, tblEnquirers.postcode, tblEnquirers.housenumber, tblEnquirers.streetname, tblEnquirers.Area, tblEnquirers.County, tblEnquirers.towncity, tblEnquirers.telno, tblEnquirers.emailaddress, tblEnquirers.DOB, tblEnquirers.nationalethnicitycode, tblEnquirers.Disability, tblEnquirers.disabilitydetails, tblEnquirers.Gender, tblEnquirers.Height, tblEnquirers.emergencycontactname, tblEnquirers.emergencycontacttelno, tblEnquirers.foodallergy, tblEnquirers.foodallergydetails, tblEnquirers.Comments " & _
                 "FROM tblEnquirers INNER JOIN tblGroupWaitingList ON tblEnquirers.enquirerID = tblGroupWaitingList.enquirerID " & _
                 "GROUP BY tblEnquirers.enquirerID, tblEnquirers.GPID, tblEnquirers.NHSNumber, tblEnquirers.Surname, tblEnquirers.firstname, tblEnquirers.postcode, tblEnquirers.housenumber, tblEnquirers.streetname, tblEnquirers.Area, tblEnquirers.County, tblEnquirers.towncity, tblEnquirers.telno, tblEnquirers.emailaddress, tblEnquirers.DOB, tblEnquirers.nationalethnicitycode, tblEnquirers.Disability, tblEnquirers.disabilitydetails, tblEnquirers.Gender, tblEnquirers.Height, tblEnquirers.emergencycontactname, tblEnquirers.emergencycontacttelno, tblEnquirers.foodallergy, tblEnquirers.foodallergydetails, tblEnquirers.Comments, tblGroupWaitingList.groupprojectID " & _
                 "HAVING (((tblEnquirers.enquirerID)=" & Int(ctl.ItemData(x)) & ") AND ((tblGroupWaitingList.groupprojectID)=1));"
        Call CreateQuery("qryContactinSlimwell", strSQL)
        Me.frmEnquirerSubformPrimary2.Requery
        Me.Requery
    Next x

End Sub

Call CreateQuery("qryContactinSlimwell", strSQL) is a piece of code that creates the query based on strSQL.

The query is created successfully depending on the chosen name, but the details in the subform are not refreshing?

If I select a name, close the form and re-open it, then it works, but I don't want to do that as the other controls on the form are selected to certain values which I want to retain.

 

[boblarson]

To requery a subform, you need to

1. use the subform container name (not the subform name, although the two can be the same at times, but not neccessarily so) of the control that houses the subform on the main form.

2. Use .Form to tell Access you want to do something to the form within the subform container control.

So, like

Me.YourSubformContainerNameHere.Form.Requery

 

[aziz rasul]

I have tried

Me.frmEnquirerSubformPrimary2.Form.Requery

but still doesn't work. I have selected the subform to make sure I have the correct name.

I have attached the db. When opening the FE db, simply press on the NHS logo on the Splash screen to bring up the db window. The problem is with "frmSlimwell" (top left list box). The VBA code in question is the Click event of the list box.

 

[jal]

I have tried

Me.frmEnquirerSubformPrimary2.Form.Requery

but still doesn't work. I have selected the subform to make sure I have the correct name.

If your form were bound to a query object called queryGetData and queryGetData included a ref to the listbox and was thus sensitive to the LB's newly selected item, then this would be fine:

Me.frmEnquirerSubformPrimary2.Requery

Otherwise you'll probably need to feed the new listbox value to the Requery call manually, like this:

Me.frmEnquirerSubformPrimary2.RecordSource = strSql
Me.frmEnquirerSubformPrimary2.Requery

(Make sure filtering is turned off for the subform, if your strSQL has its own WHERE clause filter).

 

[aziz rasul]

Many thanks jal. Got it 2 work using your suggestion.

 

[boblarson]

Many thanks jal.

I say thanks jal as well! Glad to have you helping out around here. Keep up the great work!

 

[DAW]

I say thanks jal as well! Glad to have you helping out around here. Keep up the great work!

Me too. It was the reassigning the sourceobject that was causing my subform crosstab to screw up. Thanks for the help