ReQuery a Form

[TedMosby]

I have this code below which loads up a list box, how can I get the query requeryed everytime I load up the form?



Private Sub Form_Load()
Dim sQRY As String
sQRY = _
"SELECT jez_SWM_Visits.VisitID, jez_SWM_Visits.NHSNo, jez_SWM_Visits.Surname, jez_SWM_Visits.Forename, jez_SWM_Visits.VisitDate AS [Visit Date] " & _
"FROM jez_SWM_Visits " & _
"WHERE jez_SWM_Visits.NHSNo Is Not Null AND jez_SWM_Visits.VisitID <> 1 " & _
"GROUP BY jez_SWM_Visits.VisitID, jez_SWM_Visits.NHSNo, jez_SWM_Visits.Surname, jez_SWM_Visits.Forename, jez_SWM_Visits.VisitDate " & _
"ORDER BY jez_SWM_Visits.NHSNo "
Me.RecordSource = sQRY
Me.cboSearchOn.RowSource = sQRY
Me.lstSearch.RowSource = sQRY

End Sub


How can I do this?

[CreganTur]

Try either of these:
Me.Requery
or
Me.ListBoxName.Requery
Where ListBoxName is the name of your actual listbox object's name.

[FrymanTCU]

Randy,

I have Frm2 that is set to pop up after a button on Frm1 is selected. Once the user hits Submit on Frm2, how do I requery Frm1.SubFrm? I have tried Frm1.SubFrm.Forms.Requery but have been unsuccessful.

Thanks,

Rich

[CreganTur]

Your syntax is wrong. Try:
Forms!Frm1.SubFrm.requery
I think that's the right syntax... not gauranteed though; written off the top of my head.

[FrymanTCU]

Thanks Randy that was the correct Syntax to Requery a seperate form.

[FrymanTCU]

Okay, I have another Syntax question so I figured I would put it here. I have a button on SubFrm1. SubFrm1 is located in multiple forms, Frm1, Frm2 and Frm3. When I select the button on SubFrm1, how do I call the name of the parent form?

I was hoping it was me.parent.form.name but no dice, Syntax error. I'm sure Randy or one of the other Access Gurus on here can help, thanks in advance.

-Rich

[CreganTur]

I think there's a way to pull the name of open forms...but I can't remember where I saw this code. I've been looking for it, but so far haven't had any luck finding it.

Have you considered creating a global variable? When the required forms open, their form name is loaded into this variable. Then you can call the value from your subform.

[FrymanTCU]

I did another search for view open forms and found the below code... It's probably not the most efficient script but it is working so I can't complain.:cool:

Sub RefreshForms()
On Error GoTo RefreshForms_Err

Dim x As Integer
For x = 0 To Forms.Count - 1
Select Case Forms(x).Name
Case "PA_FollowUp"
Forms!PA_FollowUp.SubFormNewClosure.Requery
Case "FR_FollowUp"
Forms!FR_FollowUp.SubFormNewClosure.Requery
Case "NewClosure"
Forms!NewClosure.SubFormNewClosure.Requery
Case "NewFeatureRemoval"
Forms!NewFeatureRemoval.SubFormNewClosure.Requery
End Select
Next
RefreshForms_Exit:
Exit Sub
RefreshForms_Err:
If Err.Number <> 0 Then
MsgBox "Error #" & Err.Number & ": " & Err.Description
End If
Resume RefreshForms_Exit
End Sub