Dealing with Multiple forms 1

[Boris10]

Hi Everyone, i got a flexgrid with over a 1000 rows in my first form. This grid allows user to drill down into particular transactions by double clicking on a column/cell - which opens another window. When they are done viewing the second form they close and are presented with the 1st form.

My problem is: how can i prevent the 1st form from reloading everytime a user exits the 2nd form. This can save the user a lot of time waiting for the 1st form to reload.
To further clarify, i have placed my db code in USERFORM_ACTIVATE SUB.
Thank you for any hints or guidance.

 

[dhookom]

Can you add code to check to see if the flexgrid is already open so it doesn't run again?

Duane
Hook'D on Access
MS Access MVP

 

[combo]

The general idea:
1. standard module:
Declare public variable that will indicate that Form_2 is opened (bForm2Opened).
2. Form_2 code module:
Set the bForm2Opened to True in the form's Initialize event and to False in Terminate event.
3. Form_1 code module:
Set Cancel=bForm2Opened in the QueryClose event.

combo

 

[combo]

BTW, I assumed you use userform (MSForms library).

combo

 

[Boris10]

Thank you for quick response, the following is my code for the 1st form (which allows for the 2nd drill down window drill down)

Private Sub UserForm_Activate()
Dim i As Integer
Dim cn As ADODB.Connection
Dim rec As ADODB.Recordset
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
Set cn = New ADODB.Connection
Set rec = New ADODB.Recordset

AddMinimizeButton                      'Minimize Maximize Buttons
    AppTasklist Me                              'Appear in the Taskbar

'Read the current Form dimensions
Owidth = Me.width
OHeight = Me.height
OGridH = EmployeeGrid.height
OGridW = EmployeeGrid.width
pTop = ProgressBar1.Top
'db connection
cn.Open "Provider = sqloledb;" + "Data Source=MCLERPSTORE;" + "Initial Catalog=MCLDAT;" + "User ID = sa;" + "Password = p@ssword123"

cmd.ActiveConnection = cn
cmd.CommandText = "select  pohed.porhseq,podet.porcseq,pohed.PONumber,POHED.[DATE]as PODate,PODET.ITEMNO,podet.oqordered as QTYOrdered," & _
"SUBSTRING(COM.COMMENT,0,11) AS COMMENT,POHED.VDNAME,POHED.LASTRECEIP,POHED.RCPDATE,PODET.OQRECEIVED AS QtyReceived, " & _
"PODET.OQOUTSTAND AS POqtyOutstanding INTO #POREQ " & _
"from POPORH1 pohed inner join poporl podet on podet.porhseq = pohed.porhseq " & _
"INNER JOIN POPORC COM ON COM.PORHSEQ = PODET.PORHSEQ AND COM.PORCSEQ = PODET.PORCSEQ " & _
"WHERE COM.COMMENT LIKE 'RQN12%' AND COM.COMMENTTYP = 2 " & _
" SELECT I.ITEMNO,HIST.QUANTITY,HIST.HOMEEXTCST,HIST.TRANSDATE INTO #ITEM " & _
" FROM MCLDAT.DBO.ICHIST HIST RIGHT JOIN MCLDAT.DBO.ICITEM I ON I.ITEMNO = HIST.ITEMNO WHERE I.CNTLACCT = 'NON'" & _
" select  HED.RQNNUMBER,hed.REQDATE as PRDate,DET.FMTITEMNO AS ItemNumber,det.ItemDesc as Description, " & _
" det.REQQTY as PRQuantity,det.orderunit as UOM,HED.COSTCTR AS Department,HED.REQRNAME as Originator,HED.DESCRIPTIO AS Purpose, " & _
"PO.PONUMBER, PO.PODATE,PO.VDNAME,PO.QTYORDERED,(DET.REQQTY-ISNULL(PO.QTYORDERED,0)) AS PRQTYOutstanding, " & _
"PO.LASTRECEIP,PO.RCPDATE,po.QtyReceived,po.POqtyOutstanding,ISNULL(SUM(ITEM.QUANTITY),0) AS STORESQTY,ISNULL(SUM(HOMEEXTCST),0) AS TOTALCOST, det.Location INTO #RQ " & _
"from PTPRH HED inner join PTPRD DET ON DET.RQNNUMBER = HED.RQNNUMBER LEFT JOIN #POREQ PO ON PO.COMMENT = HED.RQNNUMBER AND PO.ITEMNO = DET.FMTITEMNO " & _
" LEFT JOIN #ITEM ITEM ON  DET.FMTITEMNO=ITEM.ITEMNO GROUP BY HED.RQNNUMBER,hed.REQDATE,DET.FMTITEMNO,det.ItemDesc,det.REQQTY,det.orderunit,HED.COSTCTR, " & _
" HED.REQRNAME,HED.DESCRIPTIO,PO.PONUMBER,PO.PODATE,PO.VDNAME,PO.QTYORDERED,PO.LASTRECEIP,PO.RCPDATE,po.QtyReceived,po.POqtyOutstanding,det.Location order by HED.RQNNUMBER"
cmd.Execute
With rec
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockBatchOptimistic
        Set .ActiveConnection = cn
        .source = "SELECT * FROM #rq"
        .Open
End With
'fill in the combobox
With EmployeeFinder
        .cmdEmployeeFinder.AddItem "RQNNUMBER"
        .cmdEmployeeFinder.AddItem "Originator"
        .cmdEmployeeFinder.AddItem "Department"
        .cmdEmployeeFinder.AddItem "Purpose"
        .cmdEmployeeFinder.AddItem "ItemNumber"
        .cmdEmployeeFinder.AddItem "Description"
        .cmdEmployeeFinder.AddItem "PONumber"
        .cmdEmployeeFinder.AddItem "Location"
        .cmdSearchType.AddItem "Starts With"
        .cmdSearchType.AddItem "Contains"
        .cmdEmployeeFinder.value = "Originator"
        .cmdSearchType.value = "Contains"
End With
Dim row As Integer

txtFilter.SetFocus

Call MSFlexColumnSetup(rec)                 'load the flex grid
Call FormatGrid(EmployeeGrid)               'format certain columns - number and date formating
rec.Close
Set rec = Nothing
cn.Close
Set cn = Nothing
End Sub

How will this statement prevent the 1st form from reloading:
Set Cancel=bForm2Opened in the QueryClose event.

 

[PHV]

Why not move the db code from _Activate to _Initialize ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Boris10]

Perfect!!! Works like a charm

Thank you everyone!!!

 

[Boris10]

However, what is the big difference between form activate and form initialize. In other words, what code would you put in which sub?

 

[combo]

Activate vs. Initialize:
Initialize event fires when the userform is created. You Activate it when the userform becomes visible. So the once created userform can fire multiple Activate events when in the sequence of making it visible and hiding actions.

Handle QueryClose event:
In the userform's code module in the left dropdown select UserForm, in the right one find QueryClose. You should get the procrdure template:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

End Sub

Setting Cancel (one of procedure arguments) to True you tell vba that you don't want to close the userform. The second argument (CloseMode) precises reason how the form is trying to be closed. You can block user action (CloseMode=vbFormControlMenu) or by code (CloseMode=vbFormCode), see VbQueryClose enumerated values in object browser, vba library.

combo