10-31-14, 13:26 #1Registered User
- Join Date
- Oct 2014
Unanswered: Multiple Records based on combo box selection
So I have two tables, one being Payments and another being Sales.
In the sales table, I have fields called Sales ID(Primary), Total Due, Date of Sale and Payment Plan (Combo-box with options: Full, 1 Year Installments, 2 Year Installments).
In the Payments table, I have fields called Invoice number(Primary key(autonumber)), Sales ID(Foreign), Amount Due and Due Date.
Now heres my issue. I created a form that will input the details into the Sales table (Total due, date of sale and payment plan). Is there any way, based on the field values, to generate records in the payment table?
1)Input Total due - $10000, Date of sale - 1/1/2000 and Full Payment:
Generates 1 record in Payments table that auto-fills in due date = date of sale and Amount Due = $10000
2)Input Total due - $12000, Date of sale - 1/1/2000 and 1 Year Installments:
Generates 12 records. Each record with due date that is 1 month after the Date of sale and 1 month after each payment (E.g 1/2/2000, 1/3/2000, 1/3/2000 etc...). Amount Due is Total due divided by 12.
This will replace keying in invoices one by one for different payment plans. So is there any code that will do that? Or perhaps I can create a command button that will do that but I do not know the codes needed.
Many Thanks in advance!
10-31-14, 16:04 #2Registered User
- Join Date
- Jan 2003
How much VBA do you know? With what you describe, I would probably create a command button with a procedure on the OnClick event to generate records in your Payments table based on the selected plan in the combo box. A couple of things to note:
1. I would be to have the event do nothing if a payment plan is not selected.
2. Include a validation to ensure that you are not creating duplicate invoice records in your Payments table (just in case someone hits the create payments button more than once) based on Sales ID.
Question: Your Invoice Number field is set as an autonumber. Is your intention for each payment of a 1 year installment plan to be a separate invoice number?
11-01-14, 06:45 #3Registered User
- Join Date
- Oct 2014
I don't have much VBA coding language knowledge.
Thats a fantastic direction to point me to! I would definitely look into ways to code a command button to do what you've suggested. Any suggestion on where I can pick up those coding skills?
For Point number 1: Absolutely my intention.
For Point number 2: That is interesting. Any suggestion on how that would be done? Perhaps a validation that allows no more than 12 records for a 1 year selection and 1 record for a full payment? Might that work?
As for Invoice number. Yes each payment for the installment program would be a seperate invoice number.
Many thanks for your input!
11-03-14, 14:25 #4Registered User
- Join Date
- Jan 2003
It is alright. We all have to start somewhere. I have provided something I put together pretty quickly which should give you an idea of some of the approaches to breaking down a problem like yours and developing your final solution.
The validation piece would be a check before any sort of append action where you query the Payments table for any records matching the SalesID on the form. This can be done through a domain count (or DCOUNT). If there is one, the procedure just needs to end (Exit Sub).
Once you have estblished that it is a new Sales ID, you have logic based on the selection in the combo box. A CASE statement is usually the best method for this since it allows you to provide specific logic for each scenario. For "Full", it is a simple append to to the Payments table with the current information in the form. For the others, it is a loop to append each installment. While the combo box has 2 selections for 12 and 24 months, they would operate the same way, which is why I developed a separate sub to run that logic and just pass the variables to it.
The installations logic then loops through however many installments where selected and appends records to the Payments table. DateAdd is used to add months (and a day) using the loop counter itself in the calculation. The amount due is also rounded to 2 digits.
You will notice that this loop leaves out the last installment. This is because you cannot assume the amount due will divide equally between 12 or 24 months. So, I employed a recordset to identify the sum of the previous records appended and then subtract that from the total amount due. This value is then appended to the Payments table for the last installment.
Private Sub CreatePayments_Click() 'This is your validation to ensure that you are not trying to create another series of payments for an existing SalesID If DCount("[SalesID]", "Payments", "[SalesID] = '" & Me.SalesID & "'") > 0 Then MsgBox ("Invoices already exist for this Sales ID") Exit Sub End If 'This actually creates the records within the Payments table based on the combo box selection. Select Case Me.ComboPayMeth Case "Full" Dim dbs As Database Set dbs = CurrentDb dbs.Execute "INSERT INTO Payments (SalesID,AmountDue,DueDate) VALUES (" & Me.SalesID & "," & Me.TotalDue & ",#" & Me.DateOfSale & "#);" dbs.Close Case "1 Year Installments" Call Installments(Me.SalesID, Me.TotalDue, Me.DateOfSale, 12) Case "2 Year Installments" Call Installments(Me.SalesID, Me.TotalDue, Me.DateOfSale, 24) Case Else MsgBox "Invalid Payment Method" End Select End Sub Private Sub Installments(SalesID, Due, DueDate, Installment) 'Defines the objects variables you will need Dim dbs As Database Dim rs As Recordset Dim lastinstallment As Currency Set dbs = CurrentDb 'Beginning of a loop to iterate appends for the number of installments except the last one. For x = 1 To (Installment - 1) dbs.Execute "INSERT INTO Payments (SalesID,AmountDue,DueDate) VALUES (" & SalesID & "," & Round(Due / Installment, 2) & ",#" & DateAdd("d", 1, DateAdd("m", x, datetemp)) & "#);" Next x 'Since you have to assume that your number will not divide equally over 12/24 months, this will determine the value of the last installment. Set rs = dbs.OpenRecordset("SELECT SUM(AmountDue) AS [Total] FROM Payments WHERE SalesID = '" & SalesID & "'") lastinstallment = Due - rs!Total dbs.Execute "INSERT INTO Payments (SalesID,AmountDue,DueDate) VALUES (" & SalesID & "," & lastinstallment & ",#" & DateAdd("d", 1, DateAdd("m", x + 1, datetemp)) & "#);" rs.Close dbs.Close End Sub