one record into multiple records based on from and to date

[kwooten]

If I have a record that has a effective date of 1/1/2015 and expire date of 5/30/2015 then is it possible to get a record for each month based on these dates; so this 1 record would yield 5 records, one for each month active?

[June7]

With VBA code that creates new records into table.

[kwooten]

I am VBA illiterate... would you mind helping?

I have product number, effective date and to date.. can it be done off just these 3 pieces?

[June7]

Something like:

 Dim intMons As Integer, x As Integer, dteDate As Date
    intMons = DateDiff("m", Me.tbxEffectiveDate, Me.tbxExpirationDate) + 1
    dteDate = DateSerial(Year(Me.tbxEffectiveDate), Month(Me.tbxEffectiveDate), 1)
    For x = 1 To intMons
        CurrentDb.Execute "INSERT INTO sometable(ProductNum, NewDate) VALUES(" & Me.tbxProductNum & ", #" & dteDate & "#)"
        dteDate = DateAdd("m", 1, dteDate)
    Next

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[kwooten]

maybe I didnt create this correctly but when I hit run a Macros box opens up. I went to Create Module then copied your code in and changed to fit my tables.

[June7]

I revised my code significantly so it just works with values from a form.

You probably want this code behind a form in button Click event.

Create a button then select [Event Procedure] in its Click event property, click the ellipsis (...) to open VBA editor. Type code in the procedure.

[kwooten]

getting an error... Syntax error in INSERT INTO statement... will this code create the table for me or do I need to create it first?

CurrentDb.Execute "INSERT INTO EmpHistory(HSSN, YrMo) VALUES(" & rs!HSSN & ", 3" & DateAdd("m", 1, dteDate)

[June7]

Table must already exist.

Are you sticking with the Recordset code? The line you posted shows some errors I had not yet fixed.

CurrentDb.Execute "INSERT INTO EmpHistory(HSSN, YrMo) VALUES(" & rs!HSSN & ", #" & DateAdd("m", 1, dteDate) & "#"

Post your actual entire procedure if need more help.

Is this a process that you will have to do periodically or is this a one-time only occurrence?

[kwooten]

i updated based on the new code above and get Compile error: Method or data member not found.. Is this right?

 Dim intMons As Integer, x As Integer, dteDate As Date
    intMons = DateDiff("m", Me.tbxEFFDATE, Me.tbxEFFDATE) + 1
    dteDate = DateSerial(Year(Me.tbxEFFDATE), Month(Me.tbxEFFDATE), 1)
    For x = 1 To intMons
        CurrentDb.Execute "INSERT INTO EmpHistory(HSSN, NewDate) VALUES(" & Me.tbxHSSN & ", #" & dteDate & "#)"
        dteDate = DateAdd("m", 1, dteDate)
    Next

[kwooten]

Table must already exist.

Are you sticking with the Recordset code? The line you posted shows some errors I had not yet fixed.

CurrentDb.Execute "INSERT INTO EmpHistory(HSSN, YrMo) VALUES(" & rs!HSSN & ", #" & DateAdd("m", 1, dteDate) & "#"

Post your actual entire procedure if need more help.

Is this a process that you will have to do periodically or is this a one-time only occurrence?

would probably do once a month.. should I go back to the other code?

[kwooten]

correction...

Private Sub Command0_Click()

 Dim intMons As Integer, x As Integer, dteDate As Date
    intMons = DateDiff("m", Me.tbxEFFDATE, Me.tbxTODATE) + 1
    dteDate = DateSerial(Year(Me.tbxEFFDATE), Month(Me.tbxEFFDATE), 1)
    For x = 1 To intMons
        CurrentDb.Execute "INSERT INTO EmpHistory(HSSN, NewDate) VALUES(" & Me.tbxHSSN & ", #" & dteDate & "#)"
        dteDate = DateAdd("m", 1, dteDate)
    Next


End Sub

[June7]

The DateDiff() calculation is wrong. It needs to reference two date values, not the same one twice. Do some research on use of this function.

The compiler is probably not finding a textbox. Make sure names are correctly spelled.

Is HSSN a text or number type field? If it is text, will need apostrophe delimiters.

VALUES('" & Me.tbxHSSN & "', #" & dteDate & "#)"

[June7]

If you want to act on multiple records then yes, use Recordset version. However, will need to apply filter criteria in the recordset so don't repeat this process for prior month records.

[kwooten]

it is highlighting "Private Sub Command0_Click()"

[kwooten]

If you want to act on multiple records then yes, use Recordset version. However, will need to apply filter criteria in the recordset so don't repeat this process for prior month records.

Can you repost the recordset code? I will run for all the data I have currently then apply criteria to run only prior month going forward.. yes HSSN is text.