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

Rabu, 25 November 2015

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

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