Create records based on form selections

[Denae]

I have a form that has fields that I need to drive creating records on another table when the values are provided.

Value table: tblPayments_PayrollDeduction_Info
Destination table: tblPayments_PayrollDeduction_Amounts

tblPayments_PayrollDeduction_Info has the following fields:

intPayrollDeductionInfo_Count - this is the number of deductions being taken​

dtmPayrollDeductionInfo_Start - this is the date of the first deduction​

intPayrollDeductionInfo_Frequency - this is the number of days between each deduction​

intPayrollDeductionInfo_Amount - this is the total amount once all deductions are taken​

tblPayments_PayrollDeduction_Amounts has the following fields:

dtmPayrollDeduction_Date - this is the date for each deduction​

curPayrollDeduction_Amount - this is the amount of each deduction​

I need to have the database create corresponding records in tblPayments_PayrollDeduction_Amounts based on the values provided in tblPayments_PayrollDeduction_Info.

For example

intPayrollDeductionInfo_Count = 4​

dtmPayrollDeductionInfo_Start = 6/3/16​

intPayrollDeductionInfo_Frequency = 14​

curPayrollDeductionInfo_Total = $400.00​

I need to have 4 records created in tblPayments_PayrollDeduction_Amounts

dtmPayrollDeduction_Date | curPayrollDeduction_Amount
6/3/16 | $100.00
6/17/16 | $100.00
7/1/16 | $100.00
7/15/16 | $100.00


I am not sure where to start with something like this, any help anyone can give would be greatly appreciated.

 

[LaurieHamlin]

I must be missing something. Why would you create 4 records instead of 1? What if they change their deductions before 7/1?

 

[Andrzejek]

Just a guess here...

Dim intC As Integer
dim datDate As Date
Dim intAmt As Integer

datDate = tblPayments_PayrollDeduction_Info.dtmPayrollDeductionInfo_Start
intAmt = curPayrollDeductionInfo_Total / intPayrollDeductionInfo_Count 

For intC = 1 To intPayrollDeductionInfo_Count 
    strSQL = "INSERT INTO tblPayments_PayrollDeduction_Amounts " _
        & " (dtmPayrollDeduction_Date, curPayrollDeduction_Amount) VALUES " _
        & " #" & datDate & "#, " & intAmt & ")"
    DB.Execute strSQL
    datDate  = DatAdd("d", intPayrollDeductionInfo_Frequency, datDate)
Next intC

Crazy loooong names for tables and ever creazier looong names for fields. Nightmare

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Denae]

LaurieHamlin - we need to track when each payment was received, so we need each payment logged so we know how much and when we expect it.

Andy - thank you for the feedback.

I tried pulling this into my form, but I keep getting an error when I run the code.

I have a form with subforms, so I made a few changes to accommodate and added it to a button the user clicks to create the records.

I am getting Run-time error '424': Object Required. When I click debug, it highlights the DB.Execute strSQL row. I am guessing I messed something up when I tried to make tweaks.

Private Sub Command311_Click()
Dim intC As Integer
Dim datDate As Date
Dim intAmt As Integer
Dim stcase As String



'Forms.frmCaseInfo_Edit.sfrmPayments_PayrollDeduction_Info.dtmPayrollDeductionInfo_Start

stcase = Forms!frmCaseInfo_Edit!sfrmPayments_PayrollDeduction_Info.Form!strPayrollDeductionInfo_Case

datDate = Forms!frmCaseInfo_Edit!sfrmPayments_PayrollDeduction_Info.Form!dtmPayrollDeductionInfo_Start

intAmt = Forms!frmCaseInfo_Edit!curCaseInfoAdditional_ActualGrossOverpayment / Forms!frmCaseInfo_Edit!sfrmPayments_PayrollDeduction_Info.Form!intPayrollDeductionInfo_Count

For intC = 1 To Forms!frmCaseInfo_Edit.sfrmPayments_PayrollDeduction_Info.Form!intPayrollDeductionInfo_Count
    
    strSQL = "INSERT INTO tblPayments_PayrollDeduction_Amounts" _
        & " (strPayrollDeduction_Case, dtmPayrollDeduction_Date, curPayrollDeduction_Amount) VALUES " _
        & " " & stcase & ", #" & datDate & "#, " & intAmt & ")"
    
    DB.Execute strSQL
    datDate = DateAdd("d", Forms!frmCaseInfo_Edit.sfrmPayments_PayrollDeduction_Info.Form!intPayrollDeductionInfo_Frequency, datDate)

Next intC

End Sub

 

[Andrzejek]

Try: [tt]
Docmd.RunSQL (strSQL, false) [/tt]
instead.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Denae]

I think we are getting closer.

I tried the line

DoCmd.RunSQL (strSQL,false)

But I was getting Compile error: Expected =
So after some googling, I changed it to what you see below, but I am now getting Run-time error 3134 - Syntax error in INSERT INTO statement.

Also, based on your suggestion I shortened some of the naming.

Option Compare Database

Private Sub cmdAddRecords_Click()

Dim intC As Integer
Dim datDate As Date
Dim intAmt As Integer
Dim stcase As String


stcase = Forms!frmCaseInfo_Edit!frmCaseInfo_SF_PMT_PD_Info.Form!strPDI_Case

datDate = Forms!frmCaseInfo_Edit!frmCaseInfo_SF_PMT_PD_Info.Form!dtmPDI_Start

intAmt = Forms!frmCaseInfo_Edit!curCIA_ActualGrossOverpayment / Forms!frmCaseInfo_Edit!frmCaseInfo_SF_PMT_PD_Info.Form!intPDI_Count

For intC = 1 To Forms!frmCaseInfo_Edit.frmCaseInfo_SF_PMT_PD_Info.Form!intPDI_Count
    
    strSQL = "INSERT INTO tblPayments_PD_Amounts" _
        & " (strPD_Case, dtmPD_Date, curPD_Amount) VALUES " _
        & " " & stcase & ", #" & datDate & "#, " & intAmt & ")"
    
     DoCmd.RunSQL (strSQL)
            
        
    datDate = DateAdd("d", Forms!frmCaseInfo_Edit.frmPayments_SF_PD_Info.Form!intPDI_Frequency, datDate)

Next intC

End Sub

 

[Andrzejek]

Try this:

    strSQL = "INSERT INTO tblPayments_PD_Amounts" _
        & " (strPD_Case, dtmPD_Date, curPD_Amount) VALUES " _
        & " " & stcase & ", #" & datDate & "#, " & intAmt & ")"
[blue]
Debug.Print strSQL
[/blue]
     DoCmd.RunSQL (strSQL)

And see what you get in the Immediate Window.

You should be able to copy that statement from your IDE and run it in Access.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Denae]

Thank you again for your help.

Here is what I get in the immediate window:

INSERT INTO tblPayments_PD_Amounts (strPD_Case, dtmPD_Date, curPD_Amount) VALUES  1152547, #9/2/2016#, 25)

Now I am getting a Run-time error '3134': Syntax error in INSERT INTO statement.

 

[Andrzejek]

So you have a table named [tt]tblPayments_PD_Amounts[/tt] and 3 fields in this table:
[tt]strPD_Case[/tt] defined as Number
[tt]dtmPD_Date[/tt] defined as Date
[tt]curPD_Amount[/tt] defined as Number?

And no other fields that are required.




Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Denae]

There is also an auto number field. Here are the fields in the table:
[ul]
[li]aintPD_ID - AutoNumber[/li]
[li]strPD_Case - ShortText[/li]
[li]dtmPD_Date - Date/Time[/li]
[li]curPD_Amount - Currency[/li]
[/ul]

 

[Andrzejek]

Autonumber field is OK.

And since strPD_Case is Text and not a Number, try:
[tt]INSERT INTO tblPayments_PD_Amounts (strPD_Case, dtmPD_Date, curPD_Amount)
VALUES [red]'[/red]1152547[red]'[/red], #9/2/2016#, 25)
[/tt]
(See the red quotes around the value 1234567?)

That should work, so your code would look like:

strSQL = "INSERT INTO tblPayments_PD_Amounts" _
        & " (strPD_Case, dtmPD_Date, curPD_Amount) VALUES " _
        & " [red]'[/red]" & stcase & "[red]'[/red], #" & datDate & "#, " & intAmt & ")"

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Denae]

Same error.

Debug highlights the DoCmd.RunSQL (strSQL) line.

Private Sub cmdAddRecords_Click()

Dim intC As Integer
Dim datDate As Date
Dim intAmt As Integer
Dim stcase As String

stcase = Forms!frmCaseInfo_Edit!frmCaseInfo_SF_PMT_PD_Info.Form!strPDI_Case
datDate = Forms!frmCaseInfo_Edit!frmCaseInfo_SF_PMT_PD_Info.Form!dtmPDI_Start
intAmt = Forms!frmCaseInfo_Edit!curCIA_ActualGrossOverpayment / Forms!frmCaseInfo_Edit!frmCaseInfo_SF_PMT_PD_Info.Form!intPDI_Count

For intC = 1 To Forms!frmCaseInfo_Edit.frmCaseInfo_SF_PMT_PD_Info.Form!intPDI_Count
    
 strSQL = "INSERT INTO tblPayments_PD_Amounts" _
        & " (strPD_Case, dtmPD_Date, curPD_Amount) VALUES " _
        & " '" & stcase & "', #" & datDate & "#, " & intAmt & ")"

Debug.Print strSQL

    DoCmd.RunSQL (strSQL)
       
    datDate = DateAdd("d", Forms!frmCaseInfo_Edit.frmPayments_SF_PD_Info.Form!intPDI_Frequency, datDate)

Next intC

End Sub

Here is what is in the Immediate window:

INSERT INTO tblPayments_PD_Amounts (strPD_Case, dtmPD_Date, curPD_Amount) VALUES  '1033107', #6/17/2016#, 162)

 

[Andrzejek]

Missing left (

 strSQL = "INSERT INTO tblPayments_PD_Amounts" _
        & " (strPD_Case, dtmPD_Date, curPD_Amount) VALUES " _
        & " [red]([/red]'" & stcase & "', #" & datDate & "#, " & intAmt & ")"

If it still does not work -
So what happens if you take your Insert statement:

INSERT INTO tblPayments_PD_Amounts (strPD_Case, dtmPD_Date, curPD_Amount)
VALUES ('1033107', #6/17/2016#, 162)

and run it directly in Access (not thru VBA)?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Denae]

Well, that gave me a message, you are about to append 1 row(s), when I click OK it adds the record (yay!), but then I get a new error:

Run-time error '2465': Application defined or object-defined error

When I click Debug it highlights this row of code:

datDate = DateAdd("d", Forms!frmCaseInfo_Edit.frmPayments_SF_PD_Info.Form!intPDI_Frequency, datDate)

I think we are getting closer!!

 

[Andrzejek]

Do this:

[blue]
Debug.Print "Date is " & datDate
Debug.Print "Number of days to add is " & Forms!frmCaseInfo_Edit.frmPayments_SF_PD_Info.Form!intPDI_Frequency
[/blue]
datDate = DateAdd("d", [red]Forms![/red]frmCaseInfo_Edit.frmPayments_SF_PD_Info.[red]Form![/red]intPDI_Frequency, datDate)

What do you see in the Immediate Window?

Crazy long names.... :-(

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Denae]

INSERT INTO tblPayments_PD_Amounts (strPD_Case, dtmPD_Date, curPD_Amount) VALUES ('00000000', #6/24/2016#, 100)
Date is 6/24/2016

 

[Andrzejek]

You can comment out:[tt]
'Debug.Print strSQL[/tt]
Your datDate contains a Date:[tt]
Debug.Print "Date is " & datDate[/tt]
But you didn't get anything out of:[tt]
Debug.Print "Number of days to add is " ...[/tt]
Which suggests the statement:[tt]
Forms!frmCaseInfo_Edit.frmPayments_SF_PD_Info.Form!intPDI_Frequency[/tt]
is wrong.

What field on which Form do you want to use (as a Number) to add these many days to datDate?



Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Denae]

I did have a typo in the reference, so simple, but so annoying!!!

I fixed the string and now the macro is running! Thank you, thank you, thank you!!!

One question, it is asking me to append each row, then when it is done the records aren't there until I refresh the view.

Is there a way to automatically say yes to the errors and refresh the subform?

Here is the working VBA

Private Sub cmdAddRecords_Click()

'Dim RS As DAO.Recordset

Dim intC As Integer
Dim datDate As Date
Dim intAmt As Integer
Dim stcase As String

stcase = Forms!frmCaseInfo_Edit!frmCaseInfo_SF_PMT_PD_Info.Form!strPDI_Case
datDate = Forms!frmCaseInfo_Edit!frmCaseInfo_SF_PMT_PD_Info.Form!dtmPDI_Start
intAmt = Forms!frmCaseInfo_Edit!curCIA_ActualGrossOverpayment / Forms!frmCaseInfo_Edit!frmCaseInfo_SF_PMT_PD_Info.Form!intPDI_Count

For intC = 1 To Forms!frmCaseInfo_Edit.frmCaseInfo_SF_PMT_PD_Info.Form!intPDI_Count
    
 strSQL = "INSERT INTO tblPayments_PD_Amounts" _
        & " (strPD_Case, dtmPD_Date, curPD_Amount) VALUES " _
        & " ('" & stcase & "', #" & datDate & "#, " & intAmt & ")"

'Debug.Print strSQL

    DoCmd.RunSQL (strSQL)
 
Debug.Print "Date is " & datDate
Debug.Print "Number of days to add is " & Forms!frmCaseInfo_Edit!frmCaseInfo_SF_PMT_PD_Info!intPDF_Days
       
    datDate = DateAdd("d", Forms!frmCaseInfo_Edit!frmCaseInfo_SF_PMT_PD_Info!intPDF_Days, datDate)

Next intC

End Sub

Again, did I say thank you!!!!!

 

[Andrzejek]

>I did have a typo in the reference, so simple, but so annoying!!!
I hope you have [blue]this line[/blue] at the top of your code:

[blue]Option Explicit[/blue]
...
the rest of the code...

If not - you should.

>Is there a way to automatically say yes to the errors [...]?
I don't think those are the error messages ( I hope they are not). You do not want to turn off the error messages, you do want to know when and where the errors happen.

But read this about Turn action query confirmation messages on or off

>and refresh the subform?
How about this?

Oh, yeah... You are welcome

And welcome to Tek-Tips

Have fun.

---- Andy

There is a great need for a sarcasm font.