Replies

Rather than a passive trap, perhaps try an active one with a “Create Case” command button situated just to the right of the two combo boxes. You’ll know when an OnClick events happens there! You can use the OnClick event to run any validation or error checking you’d like on the two combo boxes, and assuming an “all’s clear” from that, create the case number and unlock/reveal the remainder of the form.

Well isn’t that the darndest thing?! For completeness’ sake, the ultimate criteria I ended up with is:

CLng([Forms]![frm_Main]![frm_TicketViewer]![fk_StatusCodeID])

That is, ignore the fact that the subform is on a tab control, and cast the value in the referenced control as the necessary data type using the appropriate conversion function.

Thanks, all, for your help and suggestions. Cheers, Charlotte!

Thanks, all, for the replies. I’m glad I’m not alone is not understanding what underlies this issue. To make it more confusing yet, I placed the following in the OnOpen event of frm_Main, and it works properly by displaying the value in the text box fk_StatusCodeID. But, the same syntax, with or without referencing the Value property, in a query as criteria doesn’t. Hmmmmm.

MsgBox Forms!frm_Main!frm_TicketView!fk_StatusCodeID.Value

I’m not far from just fetching the value in the field and changing the QueryDef behind the scenes, but for a number of reasons, would really rather not. Any more thoughts?

Thanks for the quick reply, Pat, but still no dice.

Something along these lines should do the trick for you. Good luck!

Dim ctl As Control

For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Then
        ctl.RowSource = ""
    End If
Next

I’m not sure how you’re exporting your report, but if you’re using the OutputTo method, check out the AutoStart argument. The below line of code exports my report named “Rpt” to a Rich Text Format document named “MyReport.rtf” at the root of my D partition. The True value as the 5th argument is what tells the OS to fire up the application associated with .rtf files. Best of luck!

DoCmd.OutputTo acReport, "rpt", "RichTextFormat(*.rtf)", "D:MyReport.rtf", True, "", 0

There are so many variables, network speed, processor/memory load on the SQL server, etc., that it’s tough to say. You may want to increase the ODBC timeout for your query, though, as it is set to 60 seconds by default. Right-click in the grey area of your query while in Design view, choose Properties, and you’ll see the ODBC Timout property. Good luck!

Edit: It should go without saying, though, that making a user wait for more than a minute for a query to return is a bit, er, excessive. You may want to check out the beauty of pass-through queries, and place the processing load on the SQL server.

I get the same error, Mike. You might try posting the issue in the MS newsgroup microsoft.public.adsi.general; I lurk there and bug the nice people with questions from time to time, almost always getting a more educated response than what I’m giving you now. Good luck!

You’re close, Ronald. To add a reference to the appropriate library, choose Add Reference from the Project menu. Click on the COM tab, and scroll down to the Microsoft Excel 10.0 Object Library. After you’ve added it, you should stop seeing the Blue Squiggle of Terror when you type something like:

Dim oExcel as New Excel.Application()

I believe you have to have Excel 2002 installed on the machine running VS.NET, though. You can check KB Q328912 for some info on downloading the Primary Interop Assemblies, another acronym for the dyslexicon, to work with Office XP COM libraries. Good luck!

You’re a life saver — literally! Great hordes of nasty people were about to descend upon me! Thanks again!

“…and what setting would that be?”, asks the man who just received the same message after having changed from V4R4 to V5R1.

Eureka!! Thanks, Steve, for staying with me! It makes good sense to me now, in practice if not in understanding why MS chose to implement the functionality that way, and has been put to good use. If you find yourself in Minneapolis, the pints are on me! Cheers!

If you can get that to work, Steve, you’re a better man than I. The below code doesn’t fire the AfterRefresh event for the QueryTable named LastRunDate.

Private Sub CommandButton1_Click()
    Sheets("Detail").QueryTables("LastRunDate").Refresh BackgroundQuery:=False
End Sub

Private Sub LastRunDate_AfterRefresh(Success As Boolean)
    MsgBox "Oh...I updated!"
End Sub

All of Microsoft’s literature suggests that you first must code a seperate class module to declare a QueryTable object, then dance the Hokey-Pokey to get the nasty @#$!% to work. Has anybody, anywhere in the history of clever developers actually used the AfterRefresh event of a QueryTable? I’d trade a multitude of refreshments to see one working example pulling data from the Northwind mdb…

Right. Now if you can help me make sense of the example provided by MS on how to actually use the AfterRefresh event of a QueryTable, we’d be on to something. Check the Help topic “Using Events with the QueryTable Object” to see the source of my confusion.

Sure. I have a worksheet with two seperate QueryTables. After both have refreshed, running queries against an Access mdb, I’d like to compare two cells, one from each query table. My code, as it stands in a single Sub, calls the ActiveWorkbook.RefreshAll method and then starts to compare the cells. The problem is that the comparison runs well before the RefreshAll finishes and so yields erroneous results.

So, the AfterRefresh event springs to mind as a good event to trap and use to compare the cells. However, the MS team is as helpful as ever in the Help file and I’m having a bit of trouble following the example they give on how to use the AfterRefresh event of a QueryTable.