Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Insert the Code

Posted on 2009-04-27
9
Medium Priority
?
134 Views
Last Modified: 2013-11-25
How can i insert this code; base on the code given below
If Amount > !Amount then
msgbox "123"
else
msgbox "321"

My purpose is to identify the amount of the field !Amount before doing so...
thanks
Public Sub save_Collections(cn As ADODB.Connection, lpMyDoc As String, ACCTID As String, _
                                Amount As Double, UsrName As String, CustName As String)
Call OPEN_CON(cn, "DBSALES", "Winpos")
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "select * from Collect where Docno='" & lpMyDoc & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
        If .BOF = True And _
            .EOF = True Then
            .AddNew
            !docno = lpMyDoc
        End If
        !ACCTID = !ACCTID
        !Amount = !Amount - Amount
        !uname = !uname
        !Cust_Name = !Cust_Name
        .Update
        .Close
    End With
    Set rs = Nothing
    Set cn = Nothing
End Sub
Public Sub SaveCollections()
Dim cn As ADODB.Connection
Call OPEN_CON(cn, "dbsales", "Winpos")
Call save_Collections(cn, CustomerID.text, 0, txtAmount.text, 0, 0)
    End If
Set cn = Nothing
MsgBox "Done !!!", vbconfirm, "confirm"
End Sub

Open in new window

0
Comment
Question by:Whing Dela Cruz
  • 5
  • 4
9 Comments
 
LVL 13

Expert Comment

by:game-master
ID: 24247305

good morning!

sumthing like this...

Public Sub save_Collections(cn As ADODB.Connection, lpMyDoc As String, ACCTID As String, _
                                Amount As Double, UsrName As String, CustName As String)
Call OPEN_CON(cn, "DBSALES", "Winpos")
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "select * from Collect where Docno='" & lpMyDoc & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
        If .BOF = True And _
            .EOF = True Then
            .AddNew
            !docno = lpMyDoc
        End If
        !ACCTID = !ACCTID

    ''''if here u want to put the code...
        If Amount > !Amount then
            msgbox "123"
         else
            msgbox "321"
        end if
    '''''''''  
        !Amount = !Amount - Amount
        !uname = !uname
        !Cust_Name = !Cust_Name
        .Update
        .Close
    End With
    Set rs = Nothing
    Set cn = Nothing
End Sub



FYI: can u tell where do u wnat to put the code....


game-master
0
 

Author Comment

by:Whing Dela Cruz
ID: 24247374
yes thanks, and then i want to exit the sub once Amount > !Amount
and must be no transaction happen if  Amount > !Amount.
Thanks! Can you add some to make this...


0
 
LVL 13

Expert Comment

by:game-master
ID: 24247777

just put "exit sub" after the "msgbox "123"

then it will exit on the sub procedure...


game-master
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Whing Dela Cruz
ID: 24247792
Hi! Sorry, but I did it but it doesn't work...
0
 
LVL 13

Expert Comment

by:game-master
ID: 24247921

can u post ur code here?
0
 

Author Comment

by:Whing Dela Cruz
ID: 24247940
yes, thanks!

Private Sub Command1_Click()
 
SaveCollections
 
If Len(Trim(txtID.text)) = 0 Then
    MsgBox "Invalid ID.", vbCritical, "Confirm"
    txtID.SetFocus
ElseIf Not IsNumeric(txtAmount.text) Then
    MsgBox "Invalid amount.", vbCritical, "Confirm"
    txtAmount.SetFocus
Else
    Expens.Name = lblName.Caption
    Expens.ID = txtID.text
    Expens.Amount = txtAmount.text
    Expens.Remark = txtRemark.text
    Expens.YN = "Y"
    Unload Me
End If
 
End Sub
 
 
Public Sub SaveCollections()
Dim cn As ADODB.Connection
Call OPEN_CON(cn, "dbsales", "Winpos")
Call save_Collections(cn, CustomerID.text, 0, txtAmount.text, 0, 0)
    
Set cn = Nothing
MsgBox "Done !!!", vbconfirm, "confirm"
End Sub
Public Sub OPEN_CON(cn As ADODB.Connection, db As String, SERVERNAME As String)
Set cn = New ADODB.Connection
cn.Provider = "sqloledb"
cn.ConnectionTimeout = 25
cn.Properties("Data source").Value = Trim(SERVERNAME)
cn.Properties("Initial Catalog").Value = Trim(db)
cn.Properties("User ID").Value = "sa"
cn.Properties("Password").Value = ""
cn.CursorLocation = adUseClient
cn.Open , "sa", ""
End Sub
 
Public Sub save_Collections(cn As ADODB.Connection, lpMyDoc As String, ACCTID As String, _
                                Amount As Double, UsrName As String, CustName As String)
Call OPEN_CON(cn, "DBSALES", "Winpos")
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "select * from Collect where Docno='" & lpMyDoc & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
        If .BOF = True And _
            .EOF = True Then
            .AddNew
            !docno = lpMyDoc
        End If
        
        If Amount > !Amount Then
            MsgBox "123"
            Exit Sub
         Else
            MsgBox "The Amount you enetered is bigger than the balance"
        End If
        !ACCTID = !ACCTID
        !Amount = !Amount - Amount
        !uname = !uname
        !Cust_Name = !Cust_Name
        .Update
        .Close
    End With
    Set rs = Nothing
    Set cn = Nothing
End Sub

Open in new window

0
 
LVL 13

Accepted Solution

by:
game-master earned 2000 total points
ID: 24248004


the "exit sub" will exit on the SaveCollections only!

what u wanted is that it also exit on the command1_click...
is that what ur trying to do?

if that so, try sumthing like this...

declare a public variable... Public exitNow as Boolean

add this on your code...


Private Sub Command1_Click()
 exitNow = False
SaveCollections
 
if exitNow = true then exit sub
If Len(Trim(txtID.text)) = 0 Then
    MsgBox "Invalid ID.", vbCritical, "Confirm"
    txtID.SetFocus
ElseIf Not IsNumeric(txtAmount.text) Then
    MsgBox "Invalid amount.", vbCritical, "Confirm"
    txtAmount.SetFocus
Else
    Expens.Name = lblName.Caption
    Expens.ID = txtID.text
    Expens.Amount = txtAmount.text
    Expens.Remark = txtRemark.text
    Expens.YN = "Y"
    Unload Me
End If
 
End Sub


Public Sub SaveCollections()
Dim cn As ADODB.Connection
Call OPEN_CON(cn, "dbsales", "Winpos")
Call save_Collections(cn, CustomerID.text, 0, txtAmount.text, 0, 0)
   
Set cn = Nothing
MsgBox "Done !!!", vbconfirm, "confirm"
End Sub
Public Sub OPEN_CON(cn As ADODB.Connection, db As String, SERVERNAME As String)
Set cn = New ADODB.Connection
cn.Provider = "sqloledb"
cn.ConnectionTimeout = 25
cn.Properties("Data source").Value = Trim(SERVERNAME)
cn.Properties("Initial Catalog").Value = Trim(db)
cn.Properties("User ID").Value = "sa"
cn.Properties("Password").Value = ""
cn.CursorLocation = adUseClient
cn.Open , "sa", ""
End Sub
 
Public Sub save_Collections(cn As ADODB.Connection, lpMyDoc As String, ACCTID As String, _
                                Amount As Double, UsrName As String, CustName As String)
Call OPEN_CON(cn, "DBSALES", "Winpos")
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "select * from Collect where Docno='" & lpMyDoc & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
        If .BOF = True And _
            .EOF = True Then
            .AddNew
            !docno = lpMyDoc
        End If
       
        If Amount > !Amount Then
            MsgBox "123"
            exitNow = true
            Exit Sub
         Else
            MsgBox "The Amount you enetered is bigger than the balance"
        End If
        !ACCTID = !ACCTID
        !Amount = !Amount - Amount
        !uname = !uname
        !Cust_Name = !Cust_Name
        .Update
        .Close
    End With
    Set rs = Nothing
    Set cn = Nothing
End Sub


with this code, once the amount > to !amount it will exit on the
sub procedure SaveCollections and make the exitNow = True...

Finally on the Command1_Click, the code will check
the value of exitNow, if it is true then it will exit to procedure...


i hope i give u some idea..

game-master


0
 

Author Comment

by:Whing Dela Cruz
ID: 24248209
Thank you so much, I've got it.. Is now working.
Thanks! More Power to you!
0
 
LVL 13

Expert Comment

by:game-master
ID: 24248314


I'm glad i could help! :-)


game-master
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question