Replies

They really seem to have hidden this in the menus, but the menu path is Format –> Sheet –> Rename, so you can do Alt-O to open the Format menu, then just type h, then r.

I think that if you set the width and height in the Load event of the child form to match what was specified in the property window it will appear the way you want it.

Dale, do the users need the dialog actually displayed at all times or just available at the click of a button? Maybe you could load the userform at startup, put a button on it to hide it without unloading it, and provide a button on the toolbar to show it as needed.

You might also want to consider using an MDI form as a container for your form. While the standard form in VB does not have scroll bars, the MDI form can. You might have to experiment some with sizing your parent MDI form and your regular form, which would be the MDI child, to get the scroll bars to appear and to work the way you want.

Use the ISNULL function instead of testing for a Null String. IIF(ISNULL(TDBDateDx……

Using the visual data tools in Visual Basic is a great way to get someone else (namely, VB!) to build the connection string for you. Go to the Data View Window, Add a Data Link. Then just fill in the Provider and Connection dialog pages, click on the “All” tab, locate the name JET OLE DB: System Database in the list, edit the value to point to the mdw file. Now save that connection and when you right click it in the Data View Window and choose Properties you can see the string created. It will have tons of extra properties too including many that are the defaults. If you want just the “bare bones” properties, try a similar trick using an ADO control instead and just look at its connection string properties after setting it up.

Neville,
You’ve probably already done this but just in case … When you checked that the references were the same in both databases did you also check that the Location: paths at the bottom of the references window point to the identical paths and file names? e.g. OLE automation on my machine points to C:WINNTSystem32stdole2.tlb

Hi Dale,

Actually, the visibility is controlled by a parameter in the open method:
Set MyWordFile = Documents.Open(strDocName, , , , , , , , , , , False).
However, when I tried this out the invisible documents behaved just as badly as the ones obtained through the GetObject method, so it might still wreak havoc with your references.

Oops! I probably spoke too soon when suggesting using an outright Open. It works, even in the UserForm, if you don’t mind the document being visible, but if you open it with the visible parameter set to False you get the same unpleasant behavior as with the GetObject. Hmm.., so does this mean GetObject does an invisible open? Well, I guess since you didn’t want the documents Open in the first place you won’t want them to be visible, so this isn’t going to be a help. Sorry.

Hi Dale,
Those references are really behaving strangely! After reading your comments, I tried running the code again in the document and saw just what you are describing. Besides seeing the pile of projects in the editor, when I looked at Tools –> References, each of the documents I’d used the GetObject method on showed as references named “Project” in the list but without checkmarks! If I tried to check them “it” complained that it would be a conflict with an existing reference. Iterating through the Documents collection revealed that each of the referenced documents was there as though they had been opened. Adding a MyWordFile.Close before the Set MyWordFile = Nothing seems to resolve the issue; no pile of projects listed in the editor. However, if the documents are sort of getting opened anyway (I’m guessing they are ’cause they are in the Documents collection), maybe you’d be just as well off using a Documents.Open instead of the misbehaving GetObject.

Mike,
Looks good! You might want to change the cursor type to adOpenStatic in case any of your updates will be done to records out of sequence. You’ll also need to trap for errors when you try to run the UpdateBatch method after reconnecting the recordset; if there are any conflicts during the update an error will be thrown. You’ll want to determine a strategy for handling conflicts. In case you haven’t already been to the section of MSDN’s ADO Programmer’s Guide dealing with batch updates, here is a link:
http://msdn.microsoft.com/library/default….onbatchmode.asp

Thanks, Wendell, for all the additional suggestions, and for your kind words. Moving to SQL Server sounds like a great idea as we do have a lot of users. Not sure that it is an option right now, but we’ll keep tracking the number of logins and hopefully the numbers will back up our request for a more robust back-end platform. Will check on Monday to see if we have SR1 installed. Thanks again.

There is a Knowledge Base article that might help:
http://support.microsoft.com/support/kb/ar…s/Q244/2/64.ASPQ244264- TroubleShooting Error 429 When Automating Office Applications

Hi Mike,
For just “diving into” ADO you certainly have accomplished a lot, and I think you really have already identified the cause of one of your problems being the connection object itself getting used in multiple places with the updates, opens, and closes, not quite in sync. I have just gone through your code and have some ideas that I hope will help you get past some of the remaining sticking points.
I’m pretty sure that the lock method is what is preventing the update from occurring at all. adLockBatchOptimistic is used in conjunction with the UpdateBatch method; this is used for handling disconnected recordsets, so in your future studies you will definitely want to read about why and how disconnected recordsets are used. You want adLockOptimistic for your current situation; that will work with the Update method. BTW: You are right about ADO automatically running the Update method, but this happens when you move off the record. Closing the recordset should have the same result as navigating to another record, but I just thought it was worth explicitly using Update to see if it shed any light on your troubles. As for using the same connection object in multiple places: leaving it as in your posted code, will cause the statement ” If LoadFormData(tdbMRN.Text) Then” to always give your “Error Updating File” message even when the update succeeds after your locking is fixed. It seems the two “cn” objects are out of sync. A couple of approaches to fix this are: 1) close connection, recordset, set both to nothing BEFORE invoking your LoadFormData method. OR 2) Use one connection object declared in the form’s general declaration area. Close it and set it to nothing in the form’s terminate event; you will have to decide the best spot for opening it. When using one connection object in several places, you’ll also have to check the state as in If cn.State = adStateOpen then ….. before attempting to close it as well as to avoid getting an error for trying to open one that is already open. There are lots of considerations for determining the best way of handling connections. I am glad that you will followup on Charlotte’s suggestion about the Errors collection too; looking at those errors will help you learn more about what is happening from the database’s point of view. Good luck!

Hi Dale,
Having had little experience with UserForms in Word, I have only just discovered that GetObject behaves differently in a UserForm than it does in a Word Document. Originally, in testing your code, I had just added the list box, command button, and code directly to a Word document, and was unable to recreate the error; GetObject worked every time. Today, out of curiosity I moved it to a UserForm, and, lo and behold, got the exact error you’ve been fighting. I am glad to hear that you have developed a workaround, but, just in case you’re still looking for alternatives, you might try moving your stuff to a document. Also, if anyone out there knows why GetObject acts differently between Document and UserForm, an explanation would be greatly appreciated. Thanks.