INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Create records based on form selections

Create records based on form selections

(OP)
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.

RE: Create records based on form selections

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

RE: Create records based on form selections

Just a guess here...

CODE

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 pipe

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.

RE: Create records based on form selections

(OP)
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.

CODE -->

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 

RE: Create records based on form selections

Try:
Docmd.RunSQL (strSQL, false)

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.

RE: Create records based on form selections

(OP)
I think we are getting closer.

I tried the line

CODE -->

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. :)

CODE -->

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 

RE: Create records based on form selections

Try this:

CODE

strSQL = "INSERT INTO tblPayments_PD_Amounts" _
        & " (strPD_Case, dtmPD_Date, curPD_Amount) VALUES " _
        & " " & stcase & ", #" & datDate & "#, " & intAmt & ")"

Debug.Print strSQL

     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.

RE: Create records based on form selections

(OP)
Thank you again for your help.

Here is what I get in the immediate window:

CODE -->

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.

RE: Create records based on form selections

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

And no other fields that are required.


Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Create records based on form selections

(OP)
There is also an auto number field. Here are the fields in the table:
  • aintPD_ID - AutoNumber
  • strPD_Case - ShortText
  • dtmPD_Date - Date/Time
  • curPD_Amount - Currency

RE: Create records based on form selections

Autonumber field is OK.

And since strPD_Case is Text and not a Number, try:
INSERT INTO tblPayments_PD_Amounts (strPD_Case, dtmPD_Date, curPD_Amount)
VALUES '1152547', #9/2/2016#, 25)

(See the red quotes around the value 1234567?)

That should work, so your code would look like:

CODE

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Create records based on form selections

(OP)
Same error. :(

Debug highlights the DoCmd.RunSQL (strSQL) line.

CODE -->

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:

CODE -->

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

RE: Create records based on form selections

Missing left (

CODE

strSQL = "INSERT INTO tblPayments_PD_Amounts" _
        & " (strPD_Case, dtmPD_Date, curPD_Amount) VALUES " _
        & " ('" & 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.

RE: Create records based on form selections

(OP)
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!!

RE: Create records based on form selections

Do this:

CODE


Debug.Print "Date is " & datDate
Debug.Print "Number of days to add is " & Forms!frmCaseInfo_Edit.frmPayments_SF_PD_Info.Form!intPDI_Frequency

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

What do you see in the Immediate Window?

Crazy long names.... sad

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Create records based on form selections

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

RE: Create records based on form selections

You can comment out:
'Debug.Print strSQL

Your datDate contains a Date:
Debug.Print "Date is " & datDate

But you didn't get anything out of:
Debug.Print "Number of days to add is " ...

Which suggests the statement:
Forms!frmCaseInfo_Edit.frmPayments_SF_PD_Info.Form!intPDI_Frequency

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.

RE: Create records based on form selections

(OP)
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

CODE -->

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!!!!!

RE: Create records based on form selections

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

CODE

Option Explicit
...
the rest of the code... 

If not - you should. smile

>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 smile

And welcome to Tek-Tips wavey3

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close