ACCESS FEED: Re: [MS_AccessPros] Before Update event not giving correct results

Hi Crystal and Bill,

Bill thanks to you and also Crystal for being helped me and telling me new ways and things to improve my skills & knowledge.

Crystal you taught me usage of

With

~~~~

End With

And Bill you taught me using Break Point.

I put a Break Point on the line:

If Not Nz([NoOfCartons]) > Nz([VarCartonsOfClient]) And Nz([SumOfCartons]) > Nz([VarCartonsOfClient]) Then

As earlier i said i was assuming something is wrong with the value of "SumOfCartons"

I observed on the Break Point hovering mouse on it that "SumOfCartons" was showing value of previous records, that clicked me. So I put one more Text Box on the Form Header "TotCartonsDelivered" and made its Control Source: =[SumOfCartons]+[NoOfCartons]

Made Visible Property to 'No' for both "SumOfCartons" & "TotCartonsDelivered"

Now the Event is working properly.

Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me.Parent.cmbClientCIN

If Not IsNull(.Value) Then

Me.ClientCIN.Value = Me.Parent.cmbClientCIN

End If

End With

Dim VarCartonsOfClient As Integer

VarCartonsOfClient = Parent.CartonsOfClient

If Nz([NoOfCartons]) > Nz([VarCartonsOfClient]) Then

MsgBox "Number of Cartons delivered to Client are: " & Nz([NoOfCartons]) & " " & vbCrLf & _

"You are delivering more cartons, than cartons supplied by Client: " & Nz([VarCartonsOfClient]) & "" _

, vbInformation, "PCTL Management - Information"

DeliveryToClient.SetFocus

Me.Undo

Else

If Not Nz([NoOfCartons]) > Nz([VarCartonsOfClient]) And Nz([TotCartonsDelivered]) > Nz([VarCartonsOfClient]) Then

MsgBox "Number of Cartons delivered to Client = " & Nz([TotCartonsDelivered]) & " " & vbCrLf & _

"You are delivering more cartons, than cartons supplied by Client = " & Nz([VarCartonsOfClient]) & "" _

, vbInformation, "PCTL Management - Information"

DeliveryToClient.SetFocus

Me.Undo

End If

End Sub

Once again I say Thank you to both of you Crystal & Bill.

I would also appreciate if you tell me that my method is correct OR its just fulfilling my requirement?

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :

Hi Bill,

I admit that the method you told me is the first experiment for me, therefore i might have not got it correctly.

I clicked on the left side of line With Me.Parent.cmbClient. I got at Red circle there. Then i opened the form and made one entry and Before Update event it opened the code highlighting that line in Yellow colour, I hovered mouse upon it, but could not observe any value displaying over it.

Is this you wanted for me to do? if so what to do next?

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

Khalid - Put a break point on the line With Me.Parent.cmbClientCIN

When the code breaks hover your mouse over Me.Parent.cmbClientCIN to see its value. Do the same for all the other controls/variables in this procedure. Check to see if they have expected values. BeforeUpdate events sometimes can't see what is in a control because what is in it is just text and not a committed value.

This debugging might shed some light on your problem.

-Bill

---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :

Hi Bill,

Thanks for your reply.

I assume that i am getting problem with the Text Box "SumOfCartons". I have changed sub form's Default view to Continuous Form, and put "SumOfCartons" on Form Header keeping its Visible property to "Yes" to observe how it is filled.

Also made little changes in Before Update event for checking basis as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me.Parent.cmbClientCIN

If Not IsNull(.Value) Then

Me.ClientCIN.Value = Me.Parent.cmbClientCIN

End If

End With

Dim VarCartonsOfClient As Integer

VarCartonsOfClient = Parent.CartonsOfClient

If Nz([NoOfCartons]) > Nz([VarCartonsOfClient]) Then

MsgBox "Number of Cartons delivered to Client are: " & Nz([NoOfCartons]) & " " & vbCrLf & _

"You are delivering more cartons, than cartons supplied by Client: " & Nz([VarCartonsOfClient]) & "" _

, vbInformation, "PCTL Management - Information"

DeliveryToClient.SetFocus

Me.Undo

Else

If Not Nz([NoOfCartons]) > Nz([VarCartonsOfClient]) And Nz([SumOfCartons]) > Nz([VarCartonsOfClient]) Then

MsgBox "Number of Cartons delivered to Client = " & Nz([SumOfCartons]) & " " & vbCrLf & _

"You are delivering more cartons, than cartons supplied by Client = " & Nz([VarCartonsOfClient]) & "" _

, vbInformation, "PCTL Management - Information"

DeliveryToClient.SetFocus

Me.Undo

End If

End Sub

Even then not getting msg box display at the right occasion.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

Khalid

Can you upload a zipped sample database for us to look at? I can't see where that error is coming from.

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :

Hi Crystal,

Ah! at last somebody replied to my question. Thank you very much Crystal for describing everything particularly use of NZ.

I did all as you said, but still my PROBLEM 1 & 2 is there.

Should it be some other event? Or Before Update is the right choice for this check?

Please look deeply into the matter, I am sure some where i myself is making some mistake.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :

wherever you are referencing a control, always wrap the reference with NZ ... in your query and also in the DCount.

"ConsignmentNo = '" & nz( [ConsignmentNo]) & "' AND ClientCIN = " & nz([cmbClientCIN])

and best to specify the optional second argument too

"ConsignmentNo = '" & nz( [ConsignmentNo],"") & "' AND ClientCIN = " & nz([cmbClientCIN])

[Forms]![Progress Of Consignment]![ConsignmentNo]
-->
nz([Forms]![Progress Of Consignment]![ConsignmentNo],"") 'assuming this is stored as text

~~~~~~~~~~~
before assigning the value of me.Parent.cmbClientCIN, make sure it is filled!
with me.Parent.cmbClientCIN
   if not isnull(.value) then
      Me.ClientCIN.Value = me.Parent.cmbClientCIN
   end if
end with
~~~
MsgBox "Number of Cartons entered = " & nz([SumOfCartons]) & " " & vbCrLf & _

"which are greater than cartons supplied by Client = " & nz([VarCartonsOfClient]) & "" _

, vbInformation, "PCTL Management - Information"

warm regards,
crystal

Microsoft Access Basics
https://www.goskills.com/Course/Access-Basics
40 lessons to empower you to build an application with Access.

~ have an awesome day ~

On 11/20/2015 2:16 PM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] wrote:

Hi All,
This is with reference to my previos post # 112780 dated 18 Nov.

Having got no answer on my question i've been trying different ways, now i am struggling on the following.

I put a Text Box control on the main form "CartonsOfClient" having Control Source:
=DCount("CartonNo","qryGroupCartonsPOC","ConsignmentNo = '" & [ConsignmentNo] & "' AND ClientCIN = " & [cmbClientCIN])

Sql of "qryGroupCartonsPOC" is:
SELECT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, IIf(IsNumeric(Left([CollectionVoucher].[CartonSuffix],1)),"",Left([CollectionVoucher].[CartonSuffix],1)) AS CartonSuffix, CollectionVoucher.CartonNo
FROM CollectionVoucher
WHERE (((CollectionVoucher.ConsignmentNo)=[Forms]![Progress Of Consignment]![ConsignmentNo]))
GROUP BY CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, IIf(IsNumeric(Left([CollectionVoucher].[CartonSuffix],1)),"",Left([CollectionVoucher].[CartonSuffix],1)), CollectionVoucher.CartonNo;

On opening Main Form "Progress Of Consignment" i get #Error in Text Box "CartonsOfClient", after selecting a value from drop down list of "cmbClientCIN"
I get correct value in Text Box "CartonsOfClient".

Second main issue i am facing is that on the sub form "ProgressOfConsignment subform" whose Record Source is:
SELECT ProgressOfConsignment.ConsignmentNo, ProgressOfConsignment.ClientCIN, ProgressOfConsignment.DeliveryToClient, ProgressOfConsignment.NoOfCartons, ProgressOfConsignment.DeliveredBy, ProgressOfConsignment.Receivedby FROM ProgressOfConsignment ORDER BY ProgressOfConsignment.DeliveredBy, ProgressOfConsignment.Receivedby;
It has Before Update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.ClientCIN.Value = Parent.cmbClientCIN

Dim VarCartonsOfClient As Integer
VarCartonsOfClient = Parent.CartonsOfClient
If SumOfCartons > VarCartonsOfClient Then
MsgBox "Number of Cartons entered = " & [SumOfCartons] & " " & vbCrLf & _
"which are greater than cartons supplied by Client = " & [VarCartonsOfClient] & "" _
, vbInformation, "PCTL Management - Information"
Me.Undo
DeliveryToClient.SetFocus
Me.Undo
End If
End Sub

I have put following controls on Sub form's Form Footer
ConsignmentNo -> Control Source -> ConsignmentNo
ClientCIN -> Control Source -> ClientCIN
SumOfCartons -> Control Source -> =Sum([NoOfCartons])

NoOfCartons is a Text Box on Detail section -> Control Source -> NoOfCartons

PROBLEM NO-1:
When i do a first entry for any Consignment and Client, and enter "NoOfCartons" more than "CartonsOfClient" msgbox does not triggers
and move on to second row, and after entering data on second row BeforeUpdate event triggers and in msgbox it shows
[SumOfCartons] of the first record.

PROBLEM NO-2:
When i do a first entry for any Consignment and Client, and enter "NoOfCartons" EQUAL to "CartonsOfClient" logically we should
not make more entries here, but by mistake we make second entry, and after entering data on second row BeforeUpdate event does not triggers,
then on the third entry msgbox opens and in msgbox it shows [SumOfCartons] of the first record + second record.

Help required please, where i am wrong and what i should do.

regards,
Khalid