Replies

Can you send me a picture of you so I can set up a shrine to worship you! Man – You are good.

The answer is – No – I didn’t do that. It never ocurred to me. But it worked perfectly!

This is a clear case of looking for a complicated solution when a double-click will do!

Thank you so much!!!

Best Regards…

Rich P

This is a split database, and all the tables are linked to a backend file. All users have the front-end file on their own machines. Nothing trickey here.

One point that may be worth noting is that many of us connect to this database through Citrix. We are spread out over the East Coast and mid-Atlantic states while the files reside in Florida. (But this has worked fine in the past!)

The query SQL view looks like this…

SELECT DISTINCTROW tblALL_Owners.Client_ID, tblALL_Clients.Client_Name, tblALL_Facilities.Fac_ID, tblALL_Facilities.OWN_ID, tblALL_Facilities.Fac_Name, tblALL_Facilities.FAC_ADD, tblALL_Facilities.Fac_City, tblALL_Facilities.Fac_St, tblALL_Submittals.Sub_Num, tblALL_Submittals.Sub_Amt_Pending, tblALL_Submittals.Sub_Status, tblALL_Submittals.Sub_Date_Due, tblALL_Submittals.DateReviewed, tblALL_Submittals.Sub_Work_Per, tblALL_Submittals.Sub_Comment, tblALL_Submittals.ESS_Job, ProgMgrs.Staff_Long AS PM, ClaimAnalysts.Staff_Long AS CA, [PM] & “/” & [CA] AS [PM/CA], tblALL_Submittals.Sub_Res_Staff, qryALL_Claims_Pending_Totals.SumOfInv_Paid_Amt, tblALL_Submittals.DateSubmitted_Client, tblALL_Submittals.Sub_CA
FROM (tblALL_Clients INNER JOIN tblALL_Owners ON tblALL_Clients.Client_ID = tblALL_Owners.Client_ID) INNER JOIN (tblALL_Facilities INNER JOIN ((tblALL_Staff AS ProgMgrs RIGHT JOIN (tblALL_Submittals LEFT JOIN qryALL_Claims_Pending_Totals ON tblALL_Submittals.ESS_SUB_Key = qryALL_Claims_Pending_Totals.ESS_SUB_Key) ON ProgMgrs.Staff = tblALL_Submittals.Sub_Res_Staff) LEFT JOIN tblALL_Staff AS ClaimAnalysts ON tblALL_Submittals.Sub_CA = ClaimAnalysts.Staff) ON tblALL_Facilities.Fac_ID = tblALL_Submittals.Fac_ID) ON tblALL_Owners.Own_ID = tblALL_Facilities.OWN_ID
WHERE (((tblALL_Submittals.Sub_Status)=”AF-In Prog” Or (tblALL_Submittals.Sub_Status)=”DP-in prog” Or (tblALL_Submittals.Sub_Status)=”In Progress”) AND (Not (tblALL_Submittals.Sub_Date_Due) Is Null));

I should mention that I did get this to work yesterday. But I am the first to admit that I can’t explain WHY it works. I literally created an empty report object, then copied and pasted the contents of the original report into the new report and it worked fine! Could be that there was some corruption in the report object. Of there was a setting that I missed that should have been different.

I would love to know what went wrong. But I was able to at least get the customer going again.

If you can see anything wrong with the query, please let me know.

Thanks for the reply.

Rich P.

You mentioned that there are 4 computers involved. Are they all sharing the same application? If so, how is it constructed?
Is the application split into a front-end and back-end?
If so, have you deployed the front-end to each of the 4 computers, so no one is sharing that file?
You may run into resource problems if everyone is sharing the same single file on one computer.

Another thing to look at might be any code behind the form. If there is any code, are variables being released properly?

Just adding my 2 cents…

RIch P.

(Edited by WendellB on 05-Mar-03 03:57. activate link)

In case this issue is not closed for you, have you ever tried some of the merging option presented by our resident Access Newsletter editor, Helen Feddema? I have used her methoed of pumping recordsets into custom Word Doc properties rather than the old Mail Merge standard practice and found it to be very quick and fairly easy to implement.

Check out http://www.helenfeddema.com/CodeSamples.htm%5B/url%5D and look specifically at Download 24; 4 Mail Merge Examples.

Rich P

First of all, thanks so much for the timely response. It is exaclty what I needed. Though I know I would not have found this anywhere else!
For anyone interested, I used Hans’ solution with a slight modification. I have a few listboxes to clear, so I created a function that takes a Listbox control as an argument. Also, in order to clear the selection where the first item in the list is selected, I changed the counter in the loop to begin at ‘0’ instead of ‘1’.

Private Function ResetList(lst As Access.ListBox)
Dim i As Integer

For i = 0 To lst.ListCount – 1
lst.Selected(i) = False
Next i

End Function

Then I can call this function from a command button placed next to a listbox control on the form:

Private Sub cmdResetPgmMgr_Click()
Call ResetList(Me.lstStaff)
End Sub

Thanks again for the answer. I should have asked sooner, rather than waste all that time.

Regards,

Rich P.

Thanks to all who have responded. I did get to the bottom of this (these) problem(s). It turned out to be a combination of things. Not only was I handling the variables in the wrong order, but after finding a reference to MSKB article Q178510 and applying its ‘knowledge’ I was able to resolve the problem.

For anyone who is having trouble getting automation code to run, I highly recommend reviewing that article.

Thanks again for the input.

Rich P.

Better late than never…But I recall having that problem, or some like it.

I have had much better luck by grouping the whole drawing, or page, before copying it. It seems to be less overhead for the apps to keep track of. Try it out…

Rich P.

Is it possible that you might be better off going back to the original source in Word and trying something there? I have had to do something like this for a client, but I was able to do my own export from Word. That made a huge difference.

Is the Word source still available?

I was frustrated the same way. What I finally found the problem to be was the form that was calling the report neded the error handling. And rather than display any message as a result of this error, I chose to simply Resume processing. The code snipet below would be on the form’s button…

Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click

(do what ever here…)

Err_cmdReport_Click:

Resume Exit_cmdReport_Click
End Sub

I always provide a simple message to the user when no data are available. But I do it in the On No Data event of the form…

Private Sub Report_NoData(Cancel As Integer)
MsgBox “There is no data to print in this report.@ Please change your settings and try again.@Printing will be cancelled.”, , “No Data to Display…”
Cancel = True
End Sub

Hope this helps!

Regards,

Rich P.

I was able to figure it out eventually. And I came up with exactly the same answer. It was so long ago, I can’t even remember how I came across it, but I no longer need to count how many times the Graph has been updated. It works fine.

Funny how little info there is out there in the world about controlling graphs programatically!

Thanks again,

Rich P.

Hello there ‘ThomasW’…

You may want to check out Article ID: Q169968 on the MS Knowledge base. If your custom application can handle automatically handle registry changes, that is, entering a new key with our custom label settings, then I think we are in business.

I ams till interested what the other experts in this forum will say about this approach. And I am especially interested in any other means of accomplishing this.

Talk with you soon…

Rich P.

This may be what you need…

When you select the Data Source for the merge, before selecting the filename, check the option on the right, in the Dialog box that reads, “Select Option”.

If I recall, this works to prevent the other instances of Access from opening. Let me know if it doesn’t do the trick. I can dig deeper…

Regards,

Rich P.

Don’t know if you ever solved this or not, but one thought I had was to recommend creating the tables in Access and then using the Transfer action to place them in Word. I believe if you run an Append query that writes all the table values to an Access table, you can pre-set the Field properties and they should be retained in Word. That way all you would have to do is the visual formatting in Word.

If you found another faster way, I would love to see it.

Thanks,

Rich P.

Thanks for the post. But I need to have the approprate title within the graph because the users want to be able to copy it from the Access form and paste it into other applications as they see fit.

I have come up with a workaround, but it still doesn’t tell me why I need one. What I have done is modified the code so that when the user hits the ‘Apply’ button, the code checks to see if this is the first time the graph is being updated. If it is, then the update code runs twice with the ‘On Error Resume Next’ statement in between each pass.

If it is not the forst pass at the graph, it worked fine anyway! It appears that the graph must initialize, or get drawn, or something, before I can modify some properties.

Still looking for the answer…

Thanks,

Rich P.

Project 2000 offers a whole bunch of events for you to use. The items below were pasted directly from the help file within Project 2000. Take a look there. You should be able to do most of what you are looking for.

Good luck.

Rich P.

Application object events

Application events occur when a project is created. To write event procedures for the Application object, you must create a new object using the WithEvents keyword in a class module. For more information, see Using events with the Application object.

NewProject ProjectBeforeResourceDelete
pjevtProjectBeforeAssignmentChange ProjectBeforeResourceNew
ProjectBeforeAssignmentDelete ProjectBeforeSave
ProjectBeforeAssignmentNew ProjectBeforeTaskChange
ProjectBeforeClose ProjectBeforeTaskDelete
ProjectBeforePrint ProjectBeforeTaskNew
ProjectBeforeResourceChange ProjectCalculate