Subform requery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Have been trying unsuccessfully to get a requery of a subform to work now
need guidance from some kindly soul.

Main Form has a 'Select Products' button that instigates a pop up form to
display all products (criteria can be used to narrow list). Before the Call
to the form is invoked I force a save to create a new IssueId(not an
autonumber) using Me.Dirty.

For each product where a quantity > 0 is recorded, a record is written to
the details table (on use of a 'Close' action button. On return from this pop
up form the data is not displayed in the subform. The subform is based on a
query using the details table.

After the DoCmd.OpenForm in the 'Select Products' button, I have the comand :-
Me![fsubIssueDetails].Requery

If I use the 'Select Products' button again the required details records
appear - confused!!!!!!!

I have tried forcing another Save but this does not bring the details up
either, have also tried this line without any success :-
Me.fsubIssueDetails.Form.RecordSource = "Select * From qryIssueDetails WHERE
IssueId = " & Me.IssueId

Any help gratefully received.
Cheers,
Steve
 
Lets see if I've got this right. This is what you want to happen?
1) MainForm: OpenForm "ProductList"
2) ProductList popup: opens
3) ProductList popup: Creates Detail records on Demand (command button)
4) ProductList popup: Closes
5) MainForm: Requery subform to show Detail records

If the above is true then I think you need a "WaitUntilClosed" routine after
#1 because as it stands your execution order is 1, 2, 5, i.e., 5 is going
to happen right after the popup has opened. That requery won't reflect any
changes you make to Details while the popup is open since they haven't
happened yet. You need to delay the requery until the popup has been closed.

Add the following 2 routines to a general module and then change your code
to something like:
DoCmd.OpenForm "frmProductList"
WaitUntilClosed "frmProductList"
Me![fsubIssueDetails].Requery

Public Function IsLoaded(strObjName As String, Optional lngObjType As
acObjecttype = acForm) As Boolean
' Returns True if strObjName is Open (non-zero), False(0) otherwise.
Default Object is Form
' Should return 0, not an error, if the object doesn't exist. *Forms
used as subforms will always return False*.
On Error Resume Next
IsLoaded = (SysCmd(acSysCmdGetObjectState, lngObjType, strObjName) <> 0)
End Function

Public Sub WaitUntilClosed(strObjName As String, Optional lngObjType As
acObjecttype = acForm)
' Suspends code execution while object is open. Default object is a
Form.
On Error Resume Next
Do While IsLoaded(strObjName, lngObjType)
DoEvents
Loop
End Sub

HTH,
 
George,

Many thanks for the solution provided and the time spent in providing it.
Code works a treat, and I understand a bit more of how forms transfer focus
between themselves.

I have had to adapt the code due to a 3rd form (products) called from the
pop-up form (criteria selection to filter for required products) to display
the selection made by adding another 2 calls to the WaitUntilClosed routine
(1 for products form, 2nd for popup).

Cheers,
Steve
George Nicholson said:
Lets see if I've got this right. This is what you want to happen?
1) MainForm: OpenForm "ProductList"
2) ProductList popup: opens
3) ProductList popup: Creates Detail records on Demand (command button)
4) ProductList popup: Closes
5) MainForm: Requery subform to show Detail records

If the above is true then I think you need a "WaitUntilClosed" routine after
#1 because as it stands your execution order is 1, 2, 5, i.e., 5 is going
to happen right after the popup has opened. That requery won't reflect any
changes you make to Details while the popup is open since they haven't
happened yet. You need to delay the requery until the popup has been closed.

Add the following 2 routines to a general module and then change your code
to something like:
DoCmd.OpenForm "frmProductList"
WaitUntilClosed "frmProductList"
Me![fsubIssueDetails].Requery

Public Function IsLoaded(strObjName As String, Optional lngObjType As
acObjecttype = acForm) As Boolean
' Returns True if strObjName is Open (non-zero), False(0) otherwise.
Default Object is Form
' Should return 0, not an error, if the object doesn't exist. *Forms
used as subforms will always return False*.
On Error Resume Next
IsLoaded = (SysCmd(acSysCmdGetObjectState, lngObjType, strObjName) <> 0)
End Function

Public Sub WaitUntilClosed(strObjName As String, Optional lngObjType As
acObjecttype = acForm)
' Suspends code execution while object is open. Default object is a
Form.
On Error Resume Next
Do While IsLoaded(strObjName, lngObjType)
DoEvents
Loop
End Sub

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Ace9x said:
Have been trying unsuccessfully to get a requery of a subform to work now
need guidance from some kindly soul.

Main Form has a 'Select Products' button that instigates a pop up form to
display all products (criteria can be used to narrow list). Before the
Call
to the form is invoked I force a save to create a new IssueId(not an
autonumber) using Me.Dirty.

For each product where a quantity > 0 is recorded, a record is written to
the details table (on use of a 'Close' action button. On return from this
pop
up form the data is not displayed in the subform. The subform is based on
a
query using the details table.

After the DoCmd.OpenForm in the 'Select Products' button, I have the
comand :-
Me![fsubIssueDetails].Requery

If I use the 'Select Products' button again the required details records
appear - confused!!!!!!!

I have tried forcing another Save but this does not bring the details up
either, have also tried this line without any success :-
Me.fsubIssueDetails.Form.RecordSource = "Select * From qryIssueDetails
WHERE
IssueId = " & Me.IssueId

Any help gratefully received.
Cheers,
Steve
Click to expand...
Click to expand...
 
Back
Top