Help with Excel UserForm

[EAB1977]

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

 

[Mike Fogleman]

Show the UserForm as Modeless, then your pop-up will not error out.

Mike F

 

[Jon Peltier]

Try this:

WaitForm.Show vbModeless

This shows the form but does not halt ongoing execution while waiting for
the form to be closed.

- Jon