Hide Form until it is completely open 1

[puforee]

I have added code to turn a list box into a multi-selection list box. Part of the code includes Echo False and Echo True. This does not seem to be completly working. The form shows too soon when the queries behind the Open form button are not complete. As a general note: using the multi-select functionality takes a while to work...not overly long but I wish it could be faster. Here is the code I am using to open the form using the data from a multi-select list box:

Main question: How can I keep the Form from showing until the code is completely complete?
Secondary question: Is there better (faster) code to use for a multi-select list box?

Thanks,

 

[puforee]

For some reason my code did not show....

Private Sub EmployeeSelectOpenFrmButton_Click()
On Error GoTo Err_EmployeeSelectOpenFrmButton_Click
    Echo False
    Dim strWhere As String
    strWhere = "1=1 "
    strWhere = strWhere & BuildIn(Me.List14, "[Name (Last, First, MI)]", "'")
    DoCmd.OpenForm "Employee Frm", acNormal, , strWhere, acEdit, acNormal
    DoCmd.Close acForm, "Employee Selection", acSaveNo
    Echo True

Exit_EmployeeSelectOpenFrmButton_Click:
    Exit Sub

Err_EmployeeSelectOpenFrmButton_Click:
    MsgBox Err.Description
    Resume Exit_EmployeeSelectOpenFrmButton_Click
'....
End Sub

 

[MajP]

I would be curious why this would take so long. Can you provide your code for "BuildIn"? There may be a more efficient way to query.
There is no "fully loaded" event, and no easy way to test this. I would play with the form's timer event, but not sure of the condition to check. My guess is the form's recordset is nothing until it loads so you could check that. Once it is no longer "nothing" make the form visible.

But I would focus on speeding this up. There should be no reason a multiselect is dragging this down.

 

[puforee]

OK... BuildIn code.

Function BuildIn(lboListBox As ListBox, _
        strFieldName As String, strDelim As String) As String
    'send in a list box control object
    Dim strIn As String
    Dim varItem As Variant
        
    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " AND " & strFieldName & " In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
        Next
        'remove the last ", " and add the ")"
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn

End Function

 

[MajP]

Try adding an index to the name field if you do not have one already. Also is there a PK field in your employee table besides the name fields. It would probably run faster searching for a number field instead of a "Last, First MI".

 

[puforee]

OK. So I should return to the list box the PK and Name fields. Setting the PK as the bound column...which I will set to 0 width. Then in the first set of code above I would just change the 2nde strWhere statement (strWhere = strWhere & BuildIn(Me.List14, "[Name (Last, First, MI)]", "'")

to strWhere = strWhere & BuildIn(Me.List14, "[the PK name here]", "'"). Would this do it?

Thanks,

 

[MajP]

I think it could speed things up especially if that field is indexed.
Normally this would be faster

where EmployeedID in (1,5,7)
vs
where EmployeeFullName in ("Smith, John A.", "Smith, John X.", "Brown, Matt")

I am guessing because, I would not think there would be much of a problem anyways.
The only way I think you can check that it is fully loaded would be to use the forms timer event and check if the recordset exists. Never done it so do not know. You may want to look at setting application.echo to false so at least you are not seeing the screen repaint slowly.