Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    8

    Unanswered: Programing in VBA with conditional recordset???

    I am an intermediate programmer working on updating an Access 2000 database running on XP for our company that someone else has written. i have struggled with this for a week and cannot find any information anywhere on the internet so humbly I ask for you programming experts.

    Here is the existing code that works great and will print 1 check based on 1 Payee and 1 Loan Amount:

    'Add new record to tblChecks
    Dim rstChecks As Recordset
    Set rstChecks = dbs.OpenRecordset("tblChecks", dbOpenDynaset)
    With rstChecks
    .AddNew
    !CheckNo = (DLookup("MaxofCheckNo", "qryLastCheck")) + 1
    !Payee = [Forms]![frmLoanEntry]![Name]
    !Amount = -[Forms]![frmLoanEntry]![Amount]
    !Reason = DMax("[LoanNo]", "tblLoans", "[CustomerID] = " & Forms!frmcustomerentry!CustomerID)
    !TransType = "CHECK"
    .Update
    End With

    Now I have added a toggle button to this form called [TsplitValue] which will open up two more fields named [SplitCheckPayee] and [SplitCheckAmount] so that I can print more than one check if needed

    I want one of three things to happen based on certain criteria
    1. IIF [TSplitValue] is false then the original code above will fire and recorded in tblChecks (Prints 1 Check)
    2. IIF [TsplitValue] is true AND [SplitCheckAmount] = [Amount] then a [SplitcheckPayee] using [SplitCheckAmount] will be recorded in tblChecks(Prints 1 Check)
    3. IIF [TsplitValue] is true and [SplitCheckAmount] < [Amount] then [Name] will get a check for [amount]-[splitcheckamount] and get recorded in tblchecks AND [SplitCheckPayee] will get a check for [SplitcheckAmount] and recorded in tblChecks(Prints 2 Checks)

    The checks all get recorded later and that part of the code is fine. I have tried coding this 9 ways to Sunday but I cannot figure out how to conditionally format a recordset. Please any help would be great!

    I tried this and tested but it will not work whatsoever so I realize that I am on the wrong path! Help me please Access Geniuses!

    Dim rstChecks As Recordset
    Set rstChecks = dbs.OpenRecordset("tblChecks", dbOpenDynaset)
    With rstChecks 'Prints One check to Payee
    If [TSplitcheck] = False Then
    .AddNew
    !CheckNo = (DLookup("MaxofCheckNo", "qryLastCheck")) + 1
    !Payee = [Forms]![frmLoanEntry]![Name]
    !Amount = -[Forms]![frmLoanEntry]![Amount]
    !Reason = DMax("[LoanNo]", "tblLoans", "[CustomerID] = " & Forms!frmcustomerentry!CustomerID)
    !TransType = "Check"
    .Update
    Else
    If (-[Forms]![frmLoanEntry]![Amount]) = (-[Forms]![frmLoanEntry]![SplitCheckAmount]) Then
    'Prints One check to Split Check Payee
    .AddNew

    !CheckNo = (DLookup("MaxofCheckNo", "qryLastCheck")) + 1
    !Payee = [SplitCheckPayee]
    !Amount = [SplitCheckAmount]
    !Reason = DMax("[LoanNo]", "tblLoans", "[CustomerID] = " & Forms!frmcustomerentry!CustomerID)
    !TransType = "CHECK"
    .Update
    End With
    End If

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try something like:
    Code:
    'Add new record to tblChecks
        Dim rstChecks As DAO.Recordset
        Dim curAmount1 As Currency
        Dim curAmount2 As Currency
        Dim strPayee As String
    
        If [Forms]![frmLoanEntry]![TsplitValue] = True And [Forms]![frmLoanEntry]![SplitCheckAmount] < [Forms]![frmLoanEntry]![Amount] Then
            curAmount1 = [Forms]![frmLoanEntry]![SplitCheckAmount]
            curAmount2 = [Forms]![frmLoanEntry]![Amount] - curAmount1
            strPayee = [Forms]![frmLoanEntry]![SplitCheckPayee]
        Else
            curAmount1 = [Forms]![frmLoanEntry]![Amount]
            curAmount2 = 0
            strPayee = [Forms]![frmLoanEntry]![Name]
        End If
        Set rstChecks = dbs.OpenRecordset("tblChecks", dbOpenDynaset)
        With rstChecks
            .AddNew
            !CheckNo = (DLookup("MaxofCheckNo", "qryLastCheck")) + 1
            !Payee = strPayee
            !Amount = curAmount1
            !Reason = DMax("[LoanNo]", "tblLoans", "[CustomerID] = " & Forms!frmcustomerentry!CustomerID)
            !TransType = "CHECK"
            .Update
            If curAmount2 > 0 Then
                .AddNew
                !CheckNo = (DLookup("MaxofCheckNo", "qryLastCheck")) + 1
                !Payee = [Forms]![frmLoanEntry]![Name]
                !Amount = curAmount2
                !Reason = DMax("[LoanNo]", "tblLoans", "[CustomerID] = " & Forms!frmcustomerentry!CustomerID)
                !TransType = "CHECK"
                .Update
            End If
        End With
    Have a nice day!

  3. #3
    Join Date
    Sep 2010
    Posts
    8
    That was the ticket! Thank you so much! For some reason I just couldn't see it.
    Much appreciated!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Oct 2010
    Posts
    1

    Help with Conditional Macro Form Query

    Ok, I am in need of some assistance please. I have a Database that holds Pay Inquiries. I have 2 Forms. The main form has a button to search for existing record by SSN. If there is no record it stays on the form and it's blank. If a record does exists it pulls it up and it can be updated. My problem is, If there is no record for that SSN, I want it to return a Msg that says no record exist and then goes back to first form to add the record.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •