Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    New York State

    Unanswered: ComboBox Problems on an Unbound Form

    Hi, Fellow Access Users,

    I now have my unbound form working, thanks to my friends on this forum, and I'm trying to use a ComboBox to do some fast record navigation. My CB's RowSource is
    SELECT tblModelInfo.ForeignKey, tblModelInfo.PartNum
    FROM tblModelInfo
    WHERE (((tblModelInfo.ForeignKey)=[forms]![frmAddModMInfo]![ForeignKey]))
    ORDER BY tblModelInfo.PartNum;
    This will return up to 10 records, depending on the value of ForeignKey. The problems:

    1 - The text portion of the ComboBox is always blank. How can I force the text in there?

    2 - When I do find a record I want, how do I calculate the record place value (Record X of Y, I need to extract the 'X') to put in my hand-made record navigation text box?

    Remember, I'm using an UNBOUND form!


  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    hi Sam,

    1: don't really uderstand the text problem you are having, but maybe go one more step unbound:

    dim strSQL as string
    strSQL = "SELECT tblModelInfo.ForeignKey, tblModelInfo.PartNum " _
    & "FROM tblModelInfo " _
    & "WHERE (((tblModelInfo.ForeignKey)= " & [forms]![frmAddModMInfo]![ForeignKey] & ")) " _
    & "ORDER BY tblModelInfo.PartNum;"
    myCombo.rowsource = strSQL

    or if FK is string
    = '" & [forms]![frmAddModMInfo]![ForeignKey] & "')) "

    then assuming (in design view) myCombo has
    .columncount: 2
    .boundcolumn: 2 ...i guess you want PN since you already know FK (why select FK at all??)
    .columnwidths: 0cm;3cm should see your PN text

    dim intIx as integer
    intIx = myCombo.listindex
    NB in designview most list/combo things are 1-based, but in code they are 0-based. code for .listindex returns 0,1,2,3,4....

    3: (i know you didn't ask 3)
    maybe counting thru the combo is the way to go, but you've done an ORDER BY so take care that things match up the way you expect.
    with designview .boundcolumn = 2 (the PN) you can get PN directly
    SELECT aa, bb, cc FROM PNdetail WHERE PN = " & myCombo & ";"

    another poss to consider if you don't have too much data in the PN detail:

    since you are already querying to fill the combo and you expect only a few (you say 10) records, why not stuff the other PN data you want into the combo with the same query:

    strSQL = "SELECT tblModelInfo.ForeignKey, tblModelInfo.PartNum, aa, bb, cc " _ ...etc as before
    .columnwidths = 0cm;3cm;0cm;0cm;0cm

    now you already have the PN data hidden away in the combo
    aaBox = myCombo.column(2) '0-based index in code)
    bbBox = myCombo.column(3) 'etc...
    you shouldn't see any difference in a local JET db, but if you were talking to a server you save a trip to the server to get the PN details (at the expense of a bigger download. the saved trip is often worth more than the bigger download - depends on how much PN-detail data there is, plus network congestion vs server congestion considerations)

    currently using SS 2008R2

  3. #3
    Join Date
    May 2004
    New York State

    Thumbs up

    Hi, Izy,

    and thanks for the time and effort to answer.

    1 - You're right, of course, who needs the FK, since I already have it. I now hid the field in the combo's rowsource. Also, for some cockeyed reason I had bound column 1, which of course I had hidden (0" columnwidth), which was why I kept losing the text part of my combo. I changed all that, and now I have my text.

    2 - Listindex I have to try out. I didn't think of it.

    3 - Adding all the details right in my combo's rowsource would be a good idea, but I already have very detailed assignment routines, because I'm using an unbound form with man-made record navigation tools. All I need to do in my combo's AfterUpdate() is to do the .FindFirst and then reference the variable assignment routine.

    Thanks so much, Izy,

Posting Permissions

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