|
Help with Excel UserForm
Hi everyone,
In my code below, what I am trying to accomplish is to have a form popup that states "Please wait..." while my combo box is being populated. When I run my code, my popup box hangs until I click the close "X" button on my form, then a error message comes up and states: Error 402: Must close or hide topmost modal form first. Can anyone tell me what I am doing wrong? Private Sub cboProdLine_AfterUpdate() Dim Conn As New ADODB.Connection, rst As ADODB.Recordset, strSQL As String Dim Counter As Integer, RowMax As Integer, ColMax As Integer Dim r As Integer, c As Integer, PctDone As Single On Error GoTo cboProdLine_Change_Err Me.cboProductCode.Clear Conn.ConnectionString = "Provider=sqloledb;Data Source=myDatabaseServer;" _ & "Initial Catalog=myDatabase;User Id=myID;Password=myPWD;" Conn.Open 'Get the product line codes based on the plant selected Set rst = New ADODB.Recordset strSQL = "SELECT DISTINCT dbo.PlantProduct.ProductCode FROM dbo.PlantProduct INNER JOIN dbo.Plant" _ & " ON dbo.PlantProduct.PlantCode = dbo.Plant.Code INNER JOIN dbo.vwProductMasterWithVersioning" _ & " ON dbo.PlantProduct.VersionNumber = dbo.vwProductMasterWithVersioning.VersionNumber AND" _ & " dbo.PlantProduct.ProductCode = dbo.vwProductMasterWithVersioning.PRDNO INNER JOIN" _ & " dbo.vwProductVersionWithChildren ON dbo.PlantProduct.ProductCode =" _ & " dbo.vwProductVersionWithChildren.ProductCode AND dbo.vwProductMasterWithVersioning.PRDNO =" _ & " dbo.vwProductVersionWithChildren.ProductCode AND dbo.vwProductMasterWithVersioning.VersionNumber" _ & " = dbo.vwProductVersionWithChildren.VersionNumber INNER JOIN dbo.vwProductLineConversion ON" _ & " dbo.vwProductMasterWithVersioning.CLASS = dbo.vwProductLineConversion.CLASS AND" _ & " dbo.vwProductMasterWithVersioning.S2APH = dbo.vwProductLineConversion.S2APH WHERE" _ & " dbo.Plant.Name = '" & Me.cboPlantCode.Text & "' AND" _ & " dbo.vwProductMasterWithVersioning.S2APH = '" & Me.cboProdLine.Text & "'" WaitForm.Show rst.Open strSQL, Conn, adOpenKeyset, adLockOptimistic Me.cboProductCode.Clear If rst.BOF = True And rst.EOF = True Then MsgBox "There are no product lines associated with the plant " & Me.cboPlantCode.Text & "." Exit Sub End If rst.MoveFirst Do Until rst.EOF Me.cboProductCode.AddItem rst!ProductCode rst.MoveNext Loop rst.Close Conn.Close WaitForm.Hide cboProdLine_Change_Err_Exit: Set rst = Nothing Set Conn = Nothing Exit Sub cboProdLine_Change_Err: MsgBox Err.Description, , "Error: " & Err.Number Resume cboProdLine_Change_Err_Exit End Sub |
Help with Excel UserForm
Show the UserForm as Modeless, then your pop-up will not error out.
Mike F "EAB1977" wrote in message ... Hi everyone, In my code below, what I am trying to accomplish is to have a form popup that states "Please wait..." while my combo box is being populated. When I run my code, my popup box hangs until I click the close "X" button on my form, then a error message comes up and states: Error 402: Must close or hide topmost modal form first. Can anyone tell me what I am doing wrong? Private Sub cboProdLine_AfterUpdate() Dim Conn As New ADODB.Connection, rst As ADODB.Recordset, strSQL As String Dim Counter As Integer, RowMax As Integer, ColMax As Integer Dim r As Integer, c As Integer, PctDone As Single On Error GoTo cboProdLine_Change_Err Me.cboProductCode.Clear Conn.ConnectionString = "Provider=sqloledb;Data Source=myDatabaseServer;" _ & "Initial Catalog=myDatabase;User Id=myID;Password=myPWD;" Conn.Open 'Get the product line codes based on the plant selected Set rst = New ADODB.Recordset strSQL = "SELECT DISTINCT dbo.PlantProduct.ProductCode FROM dbo.PlantProduct INNER JOIN dbo.Plant" _ & " ON dbo.PlantProduct.PlantCode = dbo.Plant.Code INNER JOIN dbo.vwProductMasterWithVersioning" _ & " ON dbo.PlantProduct.VersionNumber = dbo.vwProductMasterWithVersioning.VersionNumber AND" _ & " dbo.PlantProduct.ProductCode = dbo.vwProductMasterWithVersioning.PRDNO INNER JOIN" _ & " dbo.vwProductVersionWithChildren ON dbo.PlantProduct.ProductCode =" _ & " dbo.vwProductVersionWithChildren.ProductCode AND dbo.vwProductMasterWithVersioning.PRDNO =" _ & " dbo.vwProductVersionWithChildren.ProductCode AND dbo.vwProductMasterWithVersioning.VersionNumber" _ & " = dbo.vwProductVersionWithChildren.VersionNumber INNER JOIN dbo.vwProductLineConversion ON" _ & " dbo.vwProductMasterWithVersioning.CLASS = dbo.vwProductLineConversion.CLASS AND" _ & " dbo.vwProductMasterWithVersioning.S2APH = dbo.vwProductLineConversion.S2APH WHERE" _ & " dbo.Plant.Name = '" & Me.cboPlantCode.Text & "' AND" _ & " dbo.vwProductMasterWithVersioning.S2APH = '" & Me.cboProdLine.Text & "'" WaitForm.Show rst.Open strSQL, Conn, adOpenKeyset, adLockOptimistic Me.cboProductCode.Clear If rst.BOF = True And rst.EOF = True Then MsgBox "There are no product lines associated with the plant " & Me.cboPlantCode.Text & "." Exit Sub End If rst.MoveFirst Do Until rst.EOF Me.cboProductCode.AddItem rst!ProductCode rst.MoveNext Loop rst.Close Conn.Close WaitForm.Hide cboProdLine_Change_Err_Exit: Set rst = Nothing Set Conn = Nothing Exit Sub cboProdLine_Change_Err: MsgBox Err.Description, , "Error: " & Err.Number Resume cboProdLine_Change_Err_Exit End Sub |
Help with Excel UserForm
Try this:
WaitForm.Show vbModeless This shows the form but does not halt ongoing execution while waiting for the form to be closed. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "EAB1977" wrote in message ... Hi everyone, In my code below, what I am trying to accomplish is to have a form popup that states "Please wait..." while my combo box is being populated. When I run my code, my popup box hangs until I click the close "X" button on my form, then a error message comes up and states: Error 402: Must close or hide topmost modal form first. Can anyone tell me what I am doing wrong? Private Sub cboProdLine_AfterUpdate() Dim Conn As New ADODB.Connection, rst As ADODB.Recordset, strSQL As String Dim Counter As Integer, RowMax As Integer, ColMax As Integer Dim r As Integer, c As Integer, PctDone As Single On Error GoTo cboProdLine_Change_Err Me.cboProductCode.Clear Conn.ConnectionString = "Provider=sqloledb;Data Source=myDatabaseServer;" _ & "Initial Catalog=myDatabase;User Id=myID;Password=myPWD;" Conn.Open 'Get the product line codes based on the plant selected Set rst = New ADODB.Recordset strSQL = "SELECT DISTINCT dbo.PlantProduct.ProductCode FROM dbo.PlantProduct INNER JOIN dbo.Plant" _ & " ON dbo.PlantProduct.PlantCode = dbo.Plant.Code INNER JOIN dbo.vwProductMasterWithVersioning" _ & " ON dbo.PlantProduct.VersionNumber = dbo.vwProductMasterWithVersioning.VersionNumber AND" _ & " dbo.PlantProduct.ProductCode = dbo.vwProductMasterWithVersioning.PRDNO INNER JOIN" _ & " dbo.vwProductVersionWithChildren ON dbo.PlantProduct.ProductCode =" _ & " dbo.vwProductVersionWithChildren.ProductCode AND dbo.vwProductMasterWithVersioning.PRDNO =" _ & " dbo.vwProductVersionWithChildren.ProductCode AND dbo.vwProductMasterWithVersioning.VersionNumber" _ & " = dbo.vwProductVersionWithChildren.VersionNumber INNER JOIN dbo.vwProductLineConversion ON" _ & " dbo.vwProductMasterWithVersioning.CLASS = dbo.vwProductLineConversion.CLASS AND" _ & " dbo.vwProductMasterWithVersioning.S2APH = dbo.vwProductLineConversion.S2APH WHERE" _ & " dbo.Plant.Name = '" & Me.cboPlantCode.Text & "' AND" _ & " dbo.vwProductMasterWithVersioning.S2APH = '" & Me.cboProdLine.Text & "'" WaitForm.Show rst.Open strSQL, Conn, adOpenKeyset, adLockOptimistic Me.cboProductCode.Clear If rst.BOF = True And rst.EOF = True Then MsgBox "There are no product lines associated with the plant " & Me.cboPlantCode.Text & "." Exit Sub End If rst.MoveFirst Do Until rst.EOF Me.cboProductCode.AddItem rst!ProductCode rst.MoveNext Loop rst.Close Conn.Close WaitForm.Hide cboProdLine_Change_Err_Exit: Set rst = Nothing Set Conn = Nothing Exit Sub cboProdLine_Change_Err: MsgBox Err.Description, , "Error: " & Err.Number Resume cboProdLine_Change_Err_Exit End Sub |
| All times are GMT +1. The time now is 04:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com