Very Urgent

[Joselekas]

I have a table which I called Leave Entitlement (each record is unique). Their in the table there is a field called Total Entilement (which stores number of days of Leave each staff is entitled to).
I also have a report ( NOT based on the above Leave Entitlement Table) that is generated based on a criteria (Staff Number). In this report I have a text box in the Report Header that store the summation of a field in the report { =sum([LEAVE DURATION]) } for each criteria.
The challenge I have is that I want to subtract the summation from the Leave Entitlement for every criteria specify in the report.

 

[DrSnuggles]

You will need link both records.
You could use a DLookup on the Open Event of the Report, defining the criteria specifically.
Save this in a variable, do the calculation and then display this in the 'text box'.

 

[Joselekas]

Please DrSnuggles, can u be more specific with the reply.

 

[Joselekas]

Private Sub Report_Open(Cancel As Integer)
Dim Leave_Entitlement As Integer
Leave_Entitlement = 0
Leave_Entitlement = DLookup("[TOTAL_ENTITLE]", "[tblLeave_Entitlement]", "[STAFF NUMBER]=[Enter Staff Number]")
Me.Text41 = Leave_Entitlement
End Sub
I try this above line of code error at the DLookup level. Please help.

 

[Joselekas]

Private Sub Report_Open(Cancel As Integer)
Dim Leave_Entitlement As Integer
Leave_Entitlement = 0
Leave_Entitlement = DLookup("[TOTAL_ENTITLE]", "[tblLeave_Entitlement]", "[STAFF NUMBER]=[Enter Staff Number]")
Me.Text41 = Leave_Entitlement
End Sub
I try this above line of code but it gives error at the DLookup level. Please help.

 

[Tracey Rylatt]

Hi

Can somebody please help me. I have the following query and cannot find why it is stopping me pressing a tab button to release records.

DoCmd.SendObject acSendNoObject, , , Me.RepEmail, , , "Order Confirmation", vbCrLf & vbCrLf & vbCrLf & "Please find attached Order Acknowledgement for Oracles Sales Order number:" & vbCrLf & "Your Customer Purchase Order number:" & Me.Customer PO_& vbCrLf & "The relating reference is ISO:" & vbCrLf & "Your order will be sourced from:" & vbCrLf & vbCrLf & "Please check the configuration summary attached and advise within 24 hours should there be any problems.", True

It highlights the red part but cannot see anything wrong with this. Can anyone help?

Thanks.

Tracey

 

[Rabbie]

As there seems to be a space character in Customer PO_ you need to enclose it in square brackets like Me.[Customer PO_]

 

[Tracey Rylatt]

Hi Rabbie

I have done this and it still brings up the debug menu. Will not allow me to move out of this code.

Any other ideas?

Tracey

 

[Rabbie]

If it is failing at the same place I suspect you should not have Me.[Customer PO_] but Me.[Customer PO]. The underscore looks like it may have crept in by mistake. Make sure you have the name of Customer PO the same as it is on your form

 

[Tracey Rylatt]

Hi Rabbi,

This has now worked but it is still highlighting the whole line in Yellow and then showing an error message which brings up the debug and says that the Send Option was cancelled, if I end through it it is fine. Can I stop it bringing up the message.

 

[Rabbie]

Hi Tracey,

Is there by any chance a brown dot in the left hand margin beside this line. if so just click on the line and the dot should disappear.

 

[Tracey Rylatt]

Hi Rabbie

No, no brown dot but still highlighting the whole of the bottom line in yellow.
This is the whole code.
Private Sub Released_to_MFG_Click()
Me.Booked_Date = Date
DoCmd.SetWarnings no
DoCmd.OpenQuery "TJR MFG Status Update", acViewNormal, acEdit
DoCmd.SendObject acSendNoObject, , , Me.RepEmail, , , "Order Confirmation", vbCrLf & vbCrLf & vbCrLf & "Please find attached Order Acknowledgement for Oracles Sales Order number:" & vbCrLf & "Your Customer Purchase Order number:" & Me.[Customer PO#] & vbCrLf & "The relating reference is ISO:" & vbCrLf & "Your order will be sourced from:" & vbCrLf & vbCrLf & "Please check the configuration summary attached and advise within 24 hours should there be any problems.", True
End Sub

Tracey

Can anybody help with this.

 

[jarico75@yahoo.]

Is it giving you a runtime error? If not then you may want to save your work and close out of access and reopen. I changed all of you variable and ran the code fine. If you continue to have problems, you will need to look at the form fields you are referencing as possible errors.


This is how I ran the code.

Public Sub Released_to_MFG_Click()
Dim Booked_Date As Date
Dim CustomerPO As String
Dim RepEmail As String
Booked_Date = Date
CustomerPO = "12345po"
RepEmail = "jarico75@yahoo.com"
DoCmd.SetWarnings no
'DoCmd.OpenQuery "TJR MFG Status Update", acViewNormal, acEdit
DoCmd.SendObject acSendNoObject, , , RepEmail, , , "Order Confirmation", _
vbCrLf & vbCrLf & vbCrLf & _
"Please find attached Order Acknowledgement for Oracles Sales Order number:" & vbCrLf & _
"Your Customer Purchase Order number:" & CustomerPO & vbCrLf & "The relating reference is ISO:" & vbCrLf & _
"Your order will be sourced from:" & vbCrLf & vbCrLf & _
"Please check the configuration summary attached and advise within 24 hours should there be any problems.", True
End Sub

 

[Tracey Rylatt]

Thanks, I'll have another look at it today.

Tracey

 

[Joselekas]

Private Sub Report_Open(Cancel As Integer)
Dim Leave_Entitlement As Integer
Leave_Entitlement = 0
Leave_Entitlement = DLookup("[TOTAL_ENTITLE]", "[tblLeave_Entitlement]", "[STAFF NUMBER]=[Enter Staff Number]")
Me.Text41 = Leave_Entitlement
End Sub

The Administrator,
Mr, Jericho was not replying to my post.

I'm yet to solve the problem. Please gurus in the house help.