asked on

A very complicated VBA challenge

I really hope someone can help me with this one.

On a form I have a start date and an end date. I also have a field named txtDailyInvAmount on the form and I need to do the following:

Write records to a table named tblBuyerInvoiceDetails.

If the start date is 1/1/2016 and the end date is 3/31/2016 then I need 3 records written to the table, one for each 1st of the month following the start date. The first record needs to be written to have a field named InvoiceAmount be equal to the number of days elapsed between 1/1/2016 and 1/31/2016, in other words, the entire month of January (31 days) times another field value I have on the form named txtDailyInvAmount and have a field in the table being written to be 2/1/2016 (the 1st of the month following the start date.

Then I also need another record written to the table with the number of days in February (29 this year) times the txtDailyInvAmount and have a field in the table being written to be 3/1/2016 (the 1st of the month following the previous date of 2/1/2016.

And then since the end date is 3/31/2016, I need a 3rd record written to the table with the number of days in March (31 days) times the txtDailyInvAmount and have a field in the table being written to be 4/1/2016 (the 1st of the month following the previous date of 3/1/2016.

The value of txtDailyInvAmount in the following example is $52.5556 (4 decimals intentionaly)

Using the above example here is an snapshot of what I need in the table:

But if for example the start date was 1/15/2016 and the end date was 3/25/2016 then the result would have to be:

Is this possible? Somehow the function has to loop and create the records in the table.

Scott McDaniel (EE MVE )

Sounds like a project - what have you got so far?

SteveL13

ASKER

Nothing. I don't even know where to begin.

This function is to write values to a table using a specific criteria which I can handle. I have other code in the form already to handle another situation where just one record is being written with this code where the buyer option is 1. In this case the buyer option is 3 and I need more than one record written:

    If Me.cboBuyerInvoiceOption = 1 Then
       Set RS = CurrentDb.OpenRecordset("Select * from tblBuyerInvoiceDetails where DealID = " & Me.txtDealID.Value)
    
       With RS
       If .RecordCount = 0 Then
            MsgBox "This appears to be a new invoice detail record which does not already exist in the program.  A new record is being created."
            .AddNew
            !DealID = Me.txtDealID
            !CompanyID = Me.cboBuyerCompanyID
            !InvoiceDate = DateSerial(Year([StartDate]), Month([StartDate]) + 1, 1)
            !InvoiceAmount = Me.txtBuyerBrokerCommissionTotal
            !BuyerOrSeller = "Buyer"
            !CounterpartyCompanyID = Me.cboSellerCompanyID
            !Trader = Me.cboBuyerTrader
            .Update
        Else
            .Edit
            !DealID = Me.txtDealID
            !CompanyID = Me.cboBuyerCompanyID
            !InvoiceDate = DateSerial(Year([StartDate]), Month([StartDate]) + 1, 1)
            !InvoiceAmount = Me.txtBuyerBrokerCommissionTotal
            !BuyerOrSeller = "Buyer"
            !CounterpartyCompanyID = Me.cboSellerCompanyID
            !Trader = Me.cboBuyerTrader
            .Update
         End If
       End With
    End If

Open in new window

SteveL13

ASKER

And that code I just posted is working fine.

Scott McDaniel (EE MVE )

Okay - you can determine the number of records you need to write by using DateDiff with your two values:

Dim NumRecords As Integer
NumRecords = DateDiff("m", Me.StartDate, Me.EndDate)

Now you would loop through those to create your records:

Dim i As Integer
Dim NumDays As Integer
Dim InvDate As Date

For i =0 to NumRecords
InvDate = DateSerial(Year(Me.StartDate), Month(Me.StartDate) + i, 1)
NumDays= DateSerial(Year(InvDate), Month(InvDate) + i, 1) - DateSerial(Year(InvDate), Month(Me.InvDate), 1)
Currentdb.Execute "INSERT INTO SomeTable(Field1, Field2) VALUES(#" & InvDate &"#," & Me.YourInvAmount * NumDays & ")"
Next i

You may have to adjust the various DateDiff and DateSerial calls to get exactly what you need, but this should get you started.

Gustav Brock

You can loop like this:

Public Function InvoiceRecords(ByVal StartDate As Date, ByVal Enddate As Date, ByVal Amount As Currency)

    Dim MonthDate   As Date
    Dim Days        As Integer
    Dim TotalAmount As Currency
    
    MonthDate = StartDate
    
    While DateDiff("d", MonthDate, Enddate) > 0
        MonthDate = DateSerial(Year(StartDate), Month(StartDate) + 1, 1)
        If DateDiff("d", MonthDate, Enddate) > 0 Then
            Days = DateDiff("d", StartDate, MonthDate)
        Else
            Days = DateDiff("d", StartDate, Enddate) + 1
        End If
        TotalAmount = Days * Amount
        StartDate = MonthDate
        Debug.Print MonthDate, Days, TotalAmount
        ' Insert in recordset:
        ' rs.AddNew
        '     rs!InvoiceDate.Value = MonthDate
        '     rs!InvoiceAmount.Value = TotalAmount
        ' -- etc.
        ' rs.Update
    Wend

End Function

Open in new window

Results will be:

? InvoiceRecords(#1/15/2016#, #3/25/2016#, 52.5556 )
2016-02-01     17            893.4452 
2016-03-01     29            1524.1124 
2016-04-01     25            1313.89

? InvoiceRecords(#1/1/2016#, #3/31/2016#, 52.5556 )
2016-02-01     31            1629.2236 
2016-03-01     29            1524.1124 
2016-04-01     31            1629.2236

Open in new window

which reveals that your first example day count is not correct.

/gustav

SteveL13

ASKER

Gustav,

How do I use your suggested code with the onclick event of a command button?

SteveL13

ASKER

This is the code I have working if Me.cboSellerInvoiceOption on the form is 2: I Need to say "If Me.cboSellerInvoiceOption = 3 Then ..... and so forth. And loop through but can't figure out how to write that code..

    If Me.cboSellerInvoiceOption = 2 Then
       Set RS = CurrentDb.OpenRecordset("Select * from tblSellerInvoiceDetails where DealID = " & Me.txtDealID.Value)
    
       With RS
       If .RecordCount = 0 Then
            MsgBox "This appears to be a new invoice detail record which does not already exist in the program.  A new record is being created."
            .AddNew
            !DealID = Me.txtDealID
            !CompanyID = Me.cboSellerCompanyID
            !InvoiceDate = DateSerial(Year([EndDate]), Month([EndDate]) + 1, 1)
            !InvoiceAmount = Me.txtSellerBrokerCommissionTotal
            !BuyerOrSeller = "Seller"
            !CounterpartyCompanyID = Me.cboBuyerCompanyID
            !Trader = Me.cboSellerTrader
            .Update
        Else
            .Edit
            !DealID = Me.txtDealID
            !CompanyID = Me.cboSellerCompanyID
            !InvoiceDate = DateSerial(Year([EndDate]), Month([EndDate]) + 1, 1)
            !InvoiceAmount = Me.txtSellerBrokerCommissionTotal
            !BuyerOrSeller = "Seller"
            !CounterpartyCompanyID = Me.cboBuyerCompanyID
            !Trader = Me.cboSellerTrader
            .Update
         End If
       End With
    End If

Open in new window

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer