Results 1 to 3 of 3
  1. #1
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Blank column in Combobox (Access 2002/SQL 2000)

    We have a client installation where certain machines running Windows 2000 are seeing an odd behavior in a combobox that displays two columns, a report number and a medium formatted date. We have not been able to reproduce the behavior here even on Win2k, so I need some ideas. The back end is SQL Server 2000 and the combobox has this for a rowsource:

    <code>SELECT ReportNo, ReportNo as [Number], Format(ReportDate, "dd-mmm-yy")
    FROM DailyDrillHdr
    WHERE ((DailyDrillHdr.WellID=[Forms]![frmDailyRpt]![cboWellID]))
    Order by ReportNo desc;</code>

    This works fine when the form opens and a WellID is selected. It continues to work when a well is selected from the Well combobox. It breaks when the user brings up a popup form that displays additional information about the well and allows them to select a different WellID and hand that back to the initial form. The initial form obligingly changes the WellID in its Well combobox, which repopulates the subforms, and it specifically requeries the report number combobox in the AfterUpdate event of the WellID combobox. I must stress that this works on the rest of the machines, Win2k or not, and the code is actually executing as it should.

    At this point, however, on those problem machines, the date column of the report number combo is blank and it doesn't show dates again until the form is closed and reopened. The combobox is being requeried, and the correct report numbers do show up in it, but not the dates associated with them. The really odd part is that if you take the formatting out of the combobox rowsource, the dates show up every time. Since we have international clients, we use the medium format for dates to eliminate any potential date confusion with regional formats.

    The client is using a fully up to date runtime version of Access 2002, which works fine everywhere else and doesn't throw any errors on the problem machines, so I suspect that would be a red herring to follow. The form is shared between our Access and SQL Server application versions, so we can't replace the rowsource with an sproc. We can't reproduce it in our office and I'm going nuts!

    Has anyone else seen something like this? I know I have seen something similar, but it's been years ago and I can't remember what caused it! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Charlotte

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Blank column in Combobox (Access 2002/SQL 2000)

    A couple of ideas to check (I presume these are linked tables, and you aren't working in an ADP):<UL><LI>Check and see if all of the Win2000 PCs have the same MDAC files, and in particular the ODBC SQL Driver.<LI>You could try turning on the ODBC trace function and see if that's where things are going south - but you would probably need to trace both a faulty PC and one that works. Those traces can be voluminous.<LI>You might also try doing a pass-thru query as the data source for your query, assuming that you have the front-end loaded on the workstation, and you have the ability to dynamically update the pass-thru query. That would let SQL Server do the formatting work for you.[/list]
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Blank column in Combobox (Access 2002/SQL 2000)

    Thanks, Wendell, but we are not currently in the market for a passthrough query. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> We share the forms with the Access version, so eliminate that from your thinking. I know the MDAC can cause wierd behavior but we don't use ADO and do not have a reference to it, and the app was running under our runtime installs. We have no control over what the client has installed, so I don't know about the ODBC SQL driver. That's certainly something we can check into.
    Charlotte

Posting Permissions

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