Conditional Programming within a recordset

[Bmw]

I an an intermediate programmer working on updating a database 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

[ajetrumpet]

I know you are new here, so if you could, please check out this link and format your code for readability.

[NTC]

Very generally speaking; you are taking a programmatic approach to a database. Meaning that database designers work primarily with record sets and action queries. A db designer will not use vb to Add new record to tblChecks - we would use either an AppendQuery or rely upon form entry. All of this is an aside and really doesn't help you but thought I would point this out for other novices that may read.

More specifically - is the task you are attempting occuring as a user is entering live data into a form? This is a key element to understand because one can then implement some aspects of the task into events that occur on the form/screen. ... the alternative would be a batch run that occurs started by an administrator that combs thru all records and updates/changes info.

The record set approach is oriented to summarizing that the table has fields x, y, z - - with values 1,2,3 ; and a brief sample of the needed changes/results. Because in the end a db is tables. So knowing what you have to start and what you need to end is all that is really key in db design.

Hope this helps a little.