Several Debugging Questions

[sparklegrrl]

My new db up and running but I have a couple of issues that have come up as well as a new report I am trying to program. I was wondering if anyone can help. The db was built in Access 2000 on a Win2000 machine. Three of the machines here are running XP with Access 2003. I asked one of these in another area of the forum but never got it resolved.

1) I have a form that merges and prints with word using the following event procedure:

Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err

Dim objWord As Word.Application

'Start Microsoft Word 97.
Set objWord = CreateObject("Word.Application")

With objWord
'Make the application visible.
.Visible = True

'Open the document.
.Documents.Open ("f:\Company\DatabaseFiles\GATXINSPECTIONFORMS.doc")

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("PurchaseOrder").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!PurchaseOrderID))
.ActiveDocument.Bookmarks("CarInitials").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!CarInitials))
.ActiveDocument.Bookmarks("CarNumber").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!CarNumberID))
.ActiveDocument.Bookmarks("CarInitial2").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!CarInitials))
.ActiveDocument.Bookmarks("CarNumber2").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!CarNumberID))
.ActiveDocument.Bookmarks("PurchaseOrder2").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!PurchaseOrderID))
.ActiveDocument.Bookmarks("Thickness").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!GATXINSPECTIONFORMsub!Thickness.Column(1)))
.ActiveDocument.Bookmarks("Rubber").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!Inventory))
.ActiveDocument.Bookmarks("Vendor").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!Vendor))
.ActiveDocument.Bookmarks("Pipe").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!GATXINSPECTIONFORMsub!Pipe.Column(1)))
.ActiveDocument.Bookmarks("LiningDate").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!GATXINSPECTIONFORMsub!GATXliningdate))
.ActiveDocument.Bookmarks("Employee1").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!GATXINSPECTIONFORMsub!GATXInspector.Column(3)))
.ActiveDocument.Bookmarks("Title").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!GATXINSPECTIONFORMsub!Title))
.ActiveDocument.Bookmarks("CarInitial3").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!CarInitials))
.ActiveDocument.Bookmarks("CarNumber3").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!CarNumberID))
.ActiveDocument.Bookmarks("CarInitial4").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!CarInitials))
.ActiveDocument.Bookmarks("CarNumber4").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!CarNumberID))
.ActiveDocument.Bookmarks("Employee2").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!GATXINSPECTIONFORMsub!GATXInspector.Column(3)))
.ActiveDocument.Bookmarks("CarInitial5").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!CarInitials))
.ActiveDocument.Bookmarks("CarNumber5").Select
.Selection.Text = (CStr(Forms!GATXINSPECTIONFORMMAINform!CarNumberID))
End With

'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=False

'Close the document without saving changes.
objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

'Quit Microsoft Word and release the object variable.
objWord.Quit
Set objWord = Nothing
Exit Sub

MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next



Exit Sub
End If
End Sub

This works GREAT on my pc and one other however, on the other 2 pcs (xp/2003), it simply opens word and nothing happens.

2) On the two new pc's you get a security warning when opening the database due to the macros I believe. Is there anyway to get rid of it?

3) I have a report that calculates the total company portion of 401K in an unbound textbox as follows: =IIf([FidelityPercentage]=0,0,[Text70]*0.03). This is Text117. I can't seem to get it to total at the bottom of the report. When I put an unbound box there that says =sum([Text117]) and I open the report it asks me the start date, end date and then asks me for Text117. Aarggh..I can't figure this one out.

And last but not least....

4) Our employee time is tracked on a daily basis for job costing purposes but on a weekly basis for payroll. For example. John Doe may work 14 hours in one day which is recorded as a cost for that job as 8 hours regular pay and 6 hours overtime, however, if he misses the next day at work and only has 32 regular hours and 14 overtime hours he would be paid for 40 regular and 6 overtime.

BUT, if there is a holiday or vacation time then he does in fact get the 32 regular, 14 overtime and 8 vacation which i paid at the regular rate.

Right now I have the report totalling the regular and overtime and splitting it as necessary.

For example...to calculate regular pay:
=IIf([Text91]>40,40,[Text91])

And for Overtime:
=IIf([Text91]-40<0,0,[Text97])

This works GREAT...BUT, I need it to look for the total vacation (text29) and holiday (text31) and if they are greater than 0 then it equals (text19), if they are less than zero then I need it to use the above calculation.

I have no clue how to write that expression.

I tried in several forms....
=IIf([Text29]or[Text31]>0,[Text19], [Text91]>40,40,[Text91])

5) I created a custom menu for the db. How do I open a form in add mode from the menubar?

Can anyone help? THANKS!

 

[ghudson]

They need to turn off [or lower] their macro security settings in Word. Not exactly sure but somewhere on the menu bar in Word, click... Tools / Options / [find the tab for macro security] and turn it off.

You can not use the OR within your IIF like that. You have to seperate the ORs like
[Text29] > 0 Or [Text31] > 0

HTH

 

[sparklegrrl]

Thanks, I'll go give that a TRY!

 

[sparklegrrl]

K - I tried..

=IIf([Text29]>0 or [Text31]>0,[Text19], [Text91]>40,40,[Text91])

But it didn't work. It said I had the wrong number of arguments in it.

 

[mission2java_78]

K - I tried..

=IIf([Text29]>0 or [Text31]>0,[Text19], [Text91]>40,40,[Text91])

But it didn't work. It said I had the wrong number of arguments in it.

=IIF([Text29] > 0 OR [Text31] > 0. [Text19], IIF([Text91>40, 40, [Text91]))

 

[sparklegrrl]

I tried to copy/paste that and it told me it contained an invalid syntax.

 

[mission2java_78]

IIF([Text29] > 0 OR [Text31] > 0. [Text19], IIF([Text91]>40, 40, [Text91]))

 

[mission2java_78]

Dope,

It keeps on pasting all wrong:

IIF(([Text29] > 0) OR ([Text31] > 0), [Text19], IIF([Text91]>40, 40, [Text91]))

jon

 

[sparklegrrl]

Now it's telling me there is a command without an operator. Uugh!

 

[mission2java_78]

Now it's telling me there is a command without an operator. Uugh!

Come on you got to do some of this on your own:

=IIF(([Text29] > 0) OR ([Text31] > 0), [Text19], IIF([Text91]>40, 40, [Text91]))

jon

 

[sparklegrrl]

Trust me, I would if I could. I am totally new to this with no background whatsoever and I can't find anywhere online with examples of long expressions like that.

Is there a list of those sorts of things somewhere that I just didn't find? I have no clue where to put the brackets, commas, exclamation points, etc.

Thanks so much for your help I will try that.

 

[mission2java_78]

Trust me, I would if I could. I am totally new to this with no background whatsoever and I can't find anywhere online with examples of long expressions like that.

Is there a list of those sorts of things somewhere that I just didn't find? I have no clue where to put the brackets, commas, exclamation points, etc.

Thanks so much for your help I will try that.

Take a look at help.

When you see something like this sort of if you have to make sense of it, so it does require some work on your part.

The expression is:

=IF([SOME EXPRESSION], TRUE cond1, FALSE cond2)

if [SOME EXPRESSION] is an expression that returns TRUE than you execute cond1, otherwise you execute cond2.

In your case you are actually iffing (not a word ) two things.

So you do something like:

=IIF([SOME EXpression1], TRUE, IIF([SomeExpre2], True, False))

That is the second IIF executes if Some EXpression1 returns false

I don't know of a place to learn this except help.

Ehh i was only kidding anyhow, I get a kick outta answers questions .

 

[sparklegrrl]

It works and I even modified it to include Bereavement! WOHOO!!!

Ya like to answer questions do ya? How about this one?

I have a form with a combo box and a button to preview a report based on the selection in the combo box. This is for customer sales reports.

The combo box is Combo0.

Under customer in the query I have Forms!CustomerSales!Combo0.

When I run the query in dataview & type the customer in, it works great, same thing when I run the report.

On the form I have a combo box with customer names and in the onchange event I have Me.Combo0.Requery.

When I select a customer on the form and click the preview report button it doesn't work although it works great running it from the query and from the report.

Any ideas?

 

[mission2java_78]

Try the after update event not on change.

Also make sure you arent closing the form after you click the reports button.

Your query should have that customer name or id

and in the critieria

=Forms!cmboForm!Combo0.Column(0)

Make sure your combo box has 2 columns with the id in column 0
and the name of the customer in column 1.

Jon

 

[sparklegrrl]

No workie! This is about to drive me CRAZY! I have 3 other forms that this works A-Ok on but for some reason..this one is givng me fits!

Still not working. That's when it's something really simple isn't it?

 

[mission2java_78]

No workie! This is about to drive me CRAZY! I have 3 other forms that this works A-Ok on but for some reason..this one is givng me fits!

Still not working. That's when it's something really simple isn't it?

post it

jon

 

[sparklegrrl]

Hang on...I'm gonna try scrapping the whole thing first and starting over. this is NOT that dang hard.

 

[mission2java_78]

here.

Hang on...I'm gonna try scrapping the whole thing first and starting over. this is NOT that dang hard.

Here is an example for you to use or for anyone including a sample query, report and form.

Jon

 

[mission2java_78]

Here is a 97 version for you old timers...

 

[sparklegrrl]

Thanks so much for your help!