Replies

I wrote the following code for Access 97 and have since upgraded the database to 2K. It has always worked fine, though it can be slow, and you have to be careful with mapped network drives as they can cause another Access to start.
The letters themselves are mail merge linked from Word to a standard query in the database, and all have a AutoOpen macro which specifies the document name. Is this a good way to do it? Would another method be quicker?

This first procedure sets the path for Word and the letters to be used.

Public Sub WordandDocPaths()
WordLocation = “C:Program FilesMicrosoft OfficeOfficeWINWORD.EXE”
DocLocation = “server1group datadatabasesDCDATA”

End Sub

This procedure determines whether or not Word is already running. If No, word is started and the appropriate letter loaded. If yes, the current instance of Word is used.

Sub openletter()

Dim wrd As Object

On Error Resume Next

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Set wrd = GetObject(, “Word.Application”)
If Err.Number 0 Then
Call Shell(WordLocation & ” “”” & DocLocation & StdLet, 1)

Else: wrd.Documents.Open DocLocation & StdLet

wrd.wdWindowStateNormal = 0
End If

AppActivate (“Microsoft Word”)

Set wrd = Nothing

End Sub

Each button on the form has the following code to set the value of stdlet so the correct letter is loaded:

Private Sub CommandGeneralLetter_Click()
On Error GoTo CommandGeneralLetter_Click_Err

StdLet = “ack0.doc”

Call WordandDocPaths

Call openletter

CommandGeneralLetter_Click_Exit:
Exit Sub

CommandGeneralLetter_Click_Err:
MsgBox Error$
Resume CommandGeneralLetter_Click_Exit

End Sub

Charlotte
Once again you are spot on. Many thanks

Hi
Go to http://www.fmsinc.com/free/utilities/index.html and download the Jet4Admin utility.

I am getting the same problem trying to put a Word 2000 doc into an Access 2000 form. The same message appears on all 4 of my office computers. In another company where I am currently working, there is no problem so, acting on a complete hunch, I have replaced the contents of C:Program FilesCommon FilesSystemOle DB with the files from a machine where OLE works. Hey presto! My OLE now works fine
Woops! In the time it took me to type the above, OLE has gone belly up again

Hi
There are two reasons why controls do not shrink when you tell them to:
1 The controls are overlapping – check all the “Top” and “Height” properties and make sure there is a small gap between (It only needs to be 0.5mm);
2 There are other controls on the same lines as these. Since the Can Shrink property operates across the entire width of the report, these other controls will prevent your address fields from shrinking. The solution here is to put your address fields into a sub-report with Can Shrink set to yes on all the controls and the section. They can then shrink within their own report and will not be affected by other controls in the main report.

Ok So you should have 3 tables: Inventory, Employee and Register. The Inventory table holds details of the stock items (ID, Description etc), the Employee table has ID, Name, Position, Department and stuff like that. The Register table records the logging out and in of inventory items by employees so will include Inventory ID, Employee ID, Date/Time Out, Date/Time Returned.
The many to many relationship is actually between the Inventory and the Employee (An inventory item is used by many employees, an employee uses many inventory items). The Register is your link table. It has a many to one relationship with both Inventory and Employee so an Inventory item can be logged out and in many times but each time by only one employee. Similarly, each employee can log out many inventory items but each log out can only be to one employee (you can’t have two people using the brush at the same time). So you should have three tables (Inventory, Register and Employee) and the one-to-many relationships are Inventory/Register and Employee/Register.
I sincerely hope that helps. You could also benefit from reading info about normalisation of data structures. I do have a couple of Knowledge Base documents on this if you think it might be helpful.

More information is needed. You talk about material and tools and yet your tables are inventory and register. What’s the connection?
Many to many relationships are resolved exactly as MarkJ has illustrated but more specific information would yield a better solution for your problem

Somehow I just knew that was going to be the answer. Thanks Charlotte

What serious problems would they be? I use non-automatic keys quite often and if I’m heading for trouble I’d like to know about it.

To all on this particular thread:
Charlotte is right about the compacting issue and potential holes in the autonumber sequence so here’s some ideas:
1 Compact before you archive. This may be impractical so;
2 Don’t take the Autonumber key with the record when you archive it (or even return it to live). This way, Access will automatically generate a new, unique key everytime a record is added to a table. This does mean that the record changes key when you archive it but since the autonumber’s purpose is purely to maintain uniqueness, this shouldn’t be a problem. If it is;
3 Don’t use Autonumbers for your key fields. Use a manually entered number or text field. You can write a bit of VBA to automatically generate the next number in a data entry form to save having to constantly try to remember what the last number was but you can change it to any unique number. This last method means your key fields are kept intact no matter how many times the record changes tables.

Thanks for your input Charlotte. It’s nice to know somebody cares! I’m finding a few Win 2000 issues but the bios I’d forgotten about.

Thanks for the reply Charlotte. The more I think about this, the more I think it’s a Windows 2000 issue rather than Access since everything was fine under Win 98. However, just to clarify, I know it’s crazy but today all the text boxes are doing it. If I enter a day of 12 or less, the date gets screwed i.e 12/1/2001 becomes 1/12/2001. Not only does the format apparently change, but 1st December gets passed to the query when it should be 12th January. It’s as if my typing is assumed to be in American format (mm/dd/yy) which is then converted to dd/mm/yy when the text box updates.
I’ve just tried another test. If the first number I type is 13 or more, the date remains correct i.e. I type 13/12/2000, that is what is displayed and 13 Dec is (correctly) passed to the query. If the first number I type is 12 or less, the day and month are transposed i.e. 12/1/2000 becomes 1/12/2000, 1/31/2000 becomes 31/1/2000 etc. Am I making sense here? Re. one of your questions, these are unbound text boxes so there is no underlying data. My Windows Regional Settings are English (UK) with Short Date format dd/MM/yy