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 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


 

__._,_.___

Posted by: khalidtanweerburrah@yahoo.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

.

__,_._,___

Tidak ada komentar:

Posting Komentar