Search results

The sequence of events is: Form BeforeInsert Form Dirty Form BeforeUpdate Form AfterUpdate Form AfterInsert If you're updating an existing row, BeforeInsert and AfterInsert do not occur. BeforeInsert occurs on a new row the instant the user types any character or chooses a value from a bound...

Here are the real rules: 1) You can create Referential Integrity only between two tables in the same database. The reason is simple: RI uses indexes to do the enforcement, and an index can reference only tables in the same physical file. 2) If you link tables, Access brings forward the...

Thanks for the recommendation, Ions. My sample is a "robust" app, but I must admit that I skirted the issue of costing of incoming inventory by not allowing it to be posted until the vendor invoice is posted. This probably wouldn't work in "real life," as you want the warehouse folks to be...

Galaxium is exactly correct. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France)

David- Close, but no cigar: Dim sText as String Dim rIndex As Long Open TextFile for Input As#1 Do Until EOF(1) Line Input #1, sText rIndex = rIndex + 1 If rIndex = 330656 Then Debug.Print sText Exit Do End If Loop Close #1 John Viescas, author Microsoft...

FWIW, this MVP agrees that "ADPs are dead." John Viescas

First, you have to open the Report in Design view. You can browse the Application.Printers collection until you find the one you want by looking at DeviceName. Let's say the one you want is Application.Printers(5). Change the report printer by doing: Set Reports!MyReport.Printer =...

I switch back and forth between 2000, 2003, and 2007 all the time - I have clients who use the different versions. Custom ribbons are much more flexible than the old menus / toolbars and not hard to build if you get a tool like this: http://pschmid.net/office2007/ribbonx/index.php After...

No, that is not correct. The index pages are a B-Tree, so Access fetches the root page(s) of the index, then figures out which lower level index pages are applicable to the problem and fetches those. It does not fetch all the index pages for most requests. From the index pages, Access decides...

Banana- FWIW, I agree with you. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France)

If we're talking about an Access database back end (which I assume is the case since you mention the JET engine), I have to disagree. First, JET runs on the workstation and acts like a smart flat file manager. It *must* fetch entire pages from the database. However, if you ask for only a few...

That is correct. But keep in mind that Access has to fetch the index pages first. Sometimes it'll decide to do a full scan anyway if the index has low granularity - which is why I said that an index on the yes/no field or the character field with only two values probably wouldn't help. No...

Ions- You mention "network." What is the back end database? If SQL Server, then yes, eliminating fields that you don't need except for filters can make the amount of data transmitted less. If the back end is an mdb file, then it doesn't matter. When Access is the front end to a shared mdb...

I would like to think that the database engine optimizer is smart enough to think of that, but it would be worth a try in an attempt to influence it. John Viescas

Because there's probably not much benefit. For the boolean field, there's only a benefit if most of the values are, for example, True, and you're looking for False. Same thing with the "B" "G" field that has only two values. Also, your suggestion to rearrange the predicate probably won't...

I would put a single index on Subinfo.BrokerID and Subinfo.GeneratorID and see if that helps. Don't bother indexing the text field, the boolean field, or the "B" "G" field. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office...

Although the PARAMETERS statement is "optional," there are cases where you *must* use it. Crosstab queries is another example where it's required. I always declare my parameters to avoid this problem and to take advantage of data type validation. For example, if you declare a parameter...

Ions- It works for me in Northwind 2007. First, I created this query: OrderSummaryParam: PARAMETERS [Enter Order Date:] DateTime; SELECT Orders.[Order ID], Orders.[Employee ID], Orders.[Customer ID], Orders.[Order Date], Orders.[Shipped Date], [Order Price Totals].[Price Total] AS [Sub...

Just for the record, I agree with everything Banana has said in this thread. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France)

Banana- Excellent reply. You laid out the limits more succinctly than I. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France)