Results 1 to 6 of 6

Thread: combo box value

  1. #1
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Question Unanswered: combo box value

    During the monthly process that the user follows, they will use every value in a combo box (a list of part numbers for the selected account). I would like to provide them with a way to go to the next value in the list automatically. Something like a moveNext property. For example, if they are viewing the 4th part number in the list, I want to change it the 5th when they are finished. I can't find anything that will allow me to select a combo box value based on the index value. Is there a way to do this that I am missing?

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I would store the last used value in a control table and on open of the form, set the default value of the combo box to the next value.

  3. #3
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Well, that leads back to my original question. How do I get to the next value in the combo box? I don't know the values and I can't find any way to reference the value based on an index.

    And as a side note, the form is not being closed during the process, it is just changing part numbers and the related information.

    Steve

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    If the form is not closing, then my solution won't help; however, there is another problem. When should the value be changed if not on open? Once you decide when to do this, use code to open a recordset of the rowsource starting at the current value, then rs.movenext and assign the new value. You may want to open the top 2 for the recordset query so it remains small.

    You could use something like this:

    Private Sub cmd_Click()
    Dim rs As Recordset, qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs("lstFilesQy")
    Set rs = qdf.OpenRecordset
    rs.FindFirst ("YourField='" & TextBoxName & "' ")
    rs.MoveNext
    TextBoxName = rs!YourField
    rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    End Sub

    This works great if the query is a parameterized query because you can do something like this:

    qdf![Forms!ChqList!EndDate] = [Forms]![ChqList]![EndDate]

    Which is great if you do not want to use Find First.

  5. #5
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Ok, I can see the idea, just a few questions on implementing it.

    The rowsource for the combo box is currently

    Code:
     
    SELECT DISTINCT [RC-AP-Open].[PartNum] FROM [RC-AP-Open] WHERE 
    ((([RC-AP-Open].[ACCOUNT])=[Forms]![frmReconcile]![cboAccount])) 
    ORDER BY [RC-AP-Open].[PartNum];
    [RC-AP-Open] is a query that takes care of a lot of the details that are used at many places in the form such as formatting, data filtering and some calculations.

    How do I get this value for the row source into my query that I make when changing part numbers. I hardcoded it at the moment and it works fine (or will after some error handling) but I don't like the duplication this created.

    How are you gettting the row source and the recordset to run off of the same thing?

    Steve

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I do not use select queries for row sources. I usually save them as queries and put the name of the query in the row source. I would save the following as a parsed query called PartsQy:

    PARAMETERS [Forms]![frmReconcile]![cboAccount] Text ( 255 );
    SELECT DISTINCT [RC-AP-Open].[PartNum] FROM [RC-AP-Open] WHERE
    ((([RC-AP-Open].[ACCOUNT])=[Forms]![frmReconcile]![cboAccount]))
    ORDER BY [RC-AP-Open].[PartNum];

    Then using my code from the previous post:

    Set qdf = CurrentDb.QueryDefs("PartsQy")
    qdf![Forms!frmReconcile!cboAccount] >= [Forms]![frmReconcile]![cboAccount]
    Set rs = qdf.OpenRecordset

    Because of the greater than sign, you can use rs.movenext to get the next value.

Posting Permissions

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