[AccessD] Populate a list box using the same criteria which opens the form

I have one form which is opened in several different ways by clicking
buttons on the menu form.  On the form is a list box that shows project
names.  Whenever the form is opened the list box shows only the user's
projects, which is just what I want most of the time.  However,  one of the
buttons on the menu allows the user to open the form and just see the user's
open projects.  The code below is in the onmouseup event of the button on
the menu:

 

Private Sub FindOpenProjectsOpen_MouseUp(Button As Integer, Shift As
Integer, X As Single, Y As Single)

    Dim stDocName As String

    Dim stLinkCriteria1 As String

    Dim stLinkCriteria2 As String

    Dim stLinkCriteria As String

    Dim StCriteria As String

 

Me.FindOpenProjectsOpen.Visible = True

Me.FindOpenProjectsClick.Visible = False

 

    stDocName = "frmProjects"

    stLinkCriteria = "[UserID]=" & "'" & Me![UsersName] & "'"

    

    StCriteria = IsNull(DLookup("[ProjectID]", "tblProject", "Complete"))

    stLinkCriteria2 = "[Complete]=" & StCriteria

    stLinkCriteria1 = stLinkCriteria & " and " & stLinkCriteria2

    

  If IsNull(DLookup("[ProjectID]", "tblProject", stLinkCriteria)) Then

    MsgBox "You have not recorded any projects yet."

  Else

    DoCmd.Close

    DoCmd.OpenForm stDocName, , , stLinkCriteria1

  End If

 

 

End Sub

 

The query which populates the list box is:

 

SELECT tblProject.ProjectID, tblProject.ProjectName, tblProject.Complete

FROM tblProject

WHERE (((tblProject.UserID)=Environ("username")))

ORDER BY tblProject.ProjectName;

 

Right now the list box always opens showing all of the user's projects
complete or open.

I want to send a parameter to the query that tells it to populate the
listbox with open user projects (isnull[complete] ) whenever the form opens
showing only open user projects.  Otherwise it needs to show all user
projects.

 

Any help would be greatly appreciated.

 

Susan Klos

Senior Database Analyst

Evaluation and Reporting

Florida Department of Education

850-245-0708 

sc 205-0708