Combo box losing drop down list after first selection

[sohunter]

Well - it's a little more involved than that. It's no longer populating a column named "ListName" in drop down list after I open a query or report. And only happens on the machine that's running Office 2003. Not on machines running Office XP or 2000.

I've created an interface where user is choosing certain reports or queries from a table I've designed which lists report or query names, and description/comments. (In this case, only ones with "F" in the 4th character of their name show). The first field "tblReportsQueries.ReportQueryName" is the bound column, but doesn't show.

Here's the row source for "cboReportNameFUNDER":

SELECT tblReportsQueries.ReportQueryName, Mid([ReportQueryName],6) AS ListName, tblReportsQueries.ReportQueryDescription FROM tblReportsQueries WHERE (((tblReportsQueries.ReportQueryName) Like "rptF*" Or (tblReportsQueries.ReportQueryName) Like "qryF*") ORDER BY Mid([ReportQueryName],6);

And then they click the OPEN button, which is successfully opening the chosen report or query - code below:

Private Sub cmdPreviewChosenReportFUNDER_Click()

    Dim reportChoice As String

    reportChoice = Me!cboReportNameFUNDER
 
    If IsNull(Me!cboReportNameFUNDER) Then
       MsgBox "You must choose a report first", vbOKOnly
       Exit Sub
       End If
 
    
    If Left(Me!cboReportNameFUNDER, 3) = "rpt" Then
    
        DoCmd.OpenReport reportChoice, acViewPreview
        
    Else:
    
    DoCmd.OpenQuery reportChoice, acViewNormal
    
    End If
        
Exit_cmdPreviewChosenReportFUNDER_Click:
    Exit Sub
    
End Sub

The "fun" starts once they've opened a report or query for the first time. After they go back to the drop down list ...say they want to open a different report... the ListName column is empty. Other columns show, just not the one we're particularly interested in! I repeat, only in Office (and therefore Access?) 2003. Wierd. Any clues out there? As I only have access to this machine periodically, I'm trying to do my debugging offsite. On a machine where I don't have the problem. Great, eh? But that said, I can't remember if it matters whether I close the report or query I just opened, but I don't seem to remember it making a difference.

 

[KenReay]

Hi

Did the PC's with 2003 previously have earlier version of Access or Office?

I have seen thsi happend before and if has been a missing reference problem, or a reference pointing to the wrong version of the Office olb, this ten causes problems when using various functions in SQL statements (eg MID())

To check, open any code module in design view, then choose tools\refrences and see if any are missing

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TheAceMan1]

Have you tried fully qualifying [blue]ReportQueryName[/blue]?

[blue]SELECT tblReportsQueries.ReportQueryName, Mid([purple][b][tblReportsQueries].[/b][/purple][ReportQueryName],6) AS ListName, tblReportsQueries.ReportQueryDescription FROM tblReportsQueries WHERE (((tblReportsQueries.ReportQueryName) Like "rptF*" Or (tblReportsQueries.ReportQueryName) Like "qryF*") ORDER BY Mid([purple][b][tblReportsQueries].[/b][/purple][ReportQueryName],6);  [/blue]

See Ya! . . . . . .

 

[sohunter]

As always, thanks and thanks.

I'll try both of these things. As far as the references... I am making a screen shot of references that show on my own machine in code mode, and then will check against what I find when I am at that computer again, next week. Does that make sense? (Other wise I'd have no idea what to look for.) I'm a little confused though...I thought references live within a specific Access file.

 

[sohunter]

Although I must add, I have a sneaky feeling qualifying the ReportQueryName is going to do it, as that's the only field that's missing the qualification, and the only field that stops showing after opening a report.