Results 1 to 9 of 9

Thread: Listbox help

  1. #1
    Join Date
    Apr 2004
    Posts
    42

    Question Unanswered: Listbox help

    I have a listbox in a form that I use to select some data to populate my table.

    The list box is getting its information from a table. The table has 3 fields: project code, title and active. The primary key in the table is the project code. In the listbox I am storing the project code and displaying the title.

    So here is my problem. I want to change my list box to display only active projects. When I do this I cannot display the deactivated projects in my previous record on my form.

    So I thought I could just change the limit to list to no. I can’t do that because I want to display the Title on the form.

    So I wrote a sql to change what I display in the list box.

    This really slowed down the performance of this form.

    I was wondering if there is a better way to do what I am requesting.

    I don’t know very much about using a recordset could this speed things up?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    What was your original SQL and qhat is your new SQL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2004
    Posts
    42
    Here is what I have for my SQL:

    Private Sub Project_GotFocus()

    Dim mySql As String

    If IsNull(Forms![Primary]![Time Data Query subform].Form![Project]) Or Forms![Primary]![Time Data Query subform].Form![Project] = "" Then

    mySql = "SELECT DISTINCTROW [Projects and Time Codes].[Project Title], [Projects and Time Codes].[Time Code], [Projects and Time Codes].Active " & _
    "FROM [Projects and Time Codes] " & _
    "WHERE ((([Projects and Time Codes].Active) = true)) " & _
    "ORDER BY [Projects and Time Codes].[Project Title];"
    Forms![Primary]![Time Data Query subform].Form![Project].RowSource = mySql
    Else
    mySql = "SELECT DISTINCTROW [Projects and Time Codes].[Project Title], [Projects and Time Codes].[Time Code], [Projects and Time Codes].Active " & _
    "FROM [Projects and Time Codes] " & _
    "ORDER BY [Projects and Time Codes].[Project Title];"
    Forms![Primary]![Time Data Query subform].Form![Project].RowSource = mySql
    End If
    End Sub


    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    A few Qs:
    What is project? Are you sure you want to set the row source of a control on a subform every time project gets the focus?

    minor but your code could be more succint:
    Code:
    Private Sub Project_GotFocus()
    
    Dim mySql As String
     
    mySql = "SELECT DISTINCTROW [Projects and Time Codes].[Project Title], [Projects and Time Codes].[Time Code], [Projects and Time Codes].Active " & _
    "FROM [Projects and Time Codes] " 
     
    If Nz(Forms![Primary]![Time Data Query subform].Form![Project], "") = "" Then
    mySql = mySQL & "WHERE [Projects and Time Codes].Active = true "
    End If
     
    mySql = mySql & "ORDER BY [Projects and Time Codes].[Project Title];"
    
     
    Forms![Primary]![Time Data Query subform].Form![Project].RowSource = mySql
    
    End Sub
    (Sorry - I started tidying it up for my own ease of reading and carried on....)

    Does the code run slowly irrespective of the value of [Project]?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2004
    Posts
    42
    Project is a listbox.

    Thanks for the help on the SQL that make sense to me.

    The slowness issue is when I getfocus on the listbox project. Access pauses for aprox 1 to 2 seconds (while the sql is rewritten?)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - then he code can be ever so slightly more efficient:
    Code:
    Private Sub Project_GotFocus() Dim mySql As String mySql = "SELECT DISTINCTROW [Projects and Time Codes].[Project Title], [Projects and Time Codes].[Time Code], [Projects and Time Codes].Active " & _ "FROM [Projects and Time Codes] " If Nz(Me.[Project], "") = "" Then mySql = mySQL & "WHERE [Projects and Time Codes].Active = true " End If mySql = mySql & "ORDER BY [Projects and Time Codes].[Project Title];" Me.[Project].RowSource = mySql End Sub
    Access can refer to a control in a forms controls collection more quickly if you use Me rather than search the forms collection for the Primary form and then find the subform within that and finally the listbox control in the subfoms controls collection.

    However - the gist of this code now appears to be:
    if project gets the focus and is empty then populate it with active projects only. If it is not empty populate it with all the projects - each and every time the listbox gets focus. Is this correct?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2004
    Posts
    42
    You are correct 'the gist of this code now appears to be:
    if project gets the focus and is empty then populate it with active projects only. If it is not empty populate it with all the projects - each and every time the listbox gets focus.'

    The project listbox is in a subform.

    I didn't think that I could do a 'me' call on a subform.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by BRIANGAMER
    I didn't think that I could do a 'me' call on a subform.
    Yes you can

    Remember the control is on the subform so as far as the control is concerned - it is not a subform. To put it another way - whether or not a form is a subform or not is relative. From the perspective of the parent form it is a subform. From it's own perspective it is merely a form. The fact it is contained in a parent is not relevant to it.

    Most of the 1-2 seconds is retrieving the data - not changing the SQL. I find listboxs a bit of a dog to populate if there is a lot of data. What sort of volume of data are you returning?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2004
    Posts
    42
    20-30 Active projects - Eventually there will be a lot of deactivated projects.

    I tried your code and that seemed to speed things up.

    Thanks a bunch for all your help.

    Brian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •