How to insert multiple records based on a date range

[cehlinger]

I am trying to add multiple records based on a date range. I found some examples and have tried them but am getting errors and I can't seem to figure out what the problem is. I am not a VBA programmer so am a little lost. Any help would be appreciated.

As an example, the user would enter in the following:

4
522
6/5/2010
6/10/2010

Once the user selected OK it would enter 6 records into the schedule table as the following:

4, 522, 6/5/2010
4, 522, 6/6/2010
4, 522, 6/7/2010
4, 522, 6/8/2010
4, 522, 6/9/2010
4, 522, 6/10/2010

Following are the examples I am using and the error messages I am getting when I run them.

Option 1

Private Sub cmd_addRecord_Click()
On Error GoTo Err_cmd_addRecord_Click

Dim i
For i = Int(CVDate(Me!txt_beginDate)) To Int(CVDate(Me!txt_EndDate))
 
    If (DatePart("w", i, vbMonday) <> 6) And (DatePart("w", i, vbMonday) <> 7) Then
    DoCmd.RunSQL "Insert INTO schedule([contact ID], [Event ID], Date) Values('" + Me!txt_ContactID + "', " + Format(i, "m/d/yyyy") + ",8);"
    End If
Next i

Exit_cmd_addRecord_Click:
    Exit Sub

Err_cmd_addRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmd_addRecord_Click
    
End Sub

This results in a Type Mismatch error.

Option 2

Private Sub cmd_addRecord_Click()
On Error GoTo Err_cmd_addRecord_Click

Dim RepeatEvent
  
For RepeatEvent = Int(CDate(Me!txt_beginDate)) To Int(CDate(Me!txt_EndDate))
  
DoCmd.RunSQL "INSERT INTO schedule ([contact ID], [Event ID], Date) " & _
            "SELECT Forms![Form2]!txt_ContactID AS Expr1," & _
            [Forms]![Form2]![txt_EventID] AS Expr2,#" & _
            Format$(RepeatEvent, "m/d/yyyy")"#;"
 
  
Next RepeatEvent

Exit_cmd_addRecord_Click:
    Exit Sub

Err_cmd_addRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmd_addRecord_Click
    
End Sub

This results in a Compile Error. Syntax Error

[Echidna]

Hi,

I will take you through option 1
The SQL Statement needs to be rewritten slightly.

The Columns you have selected need to be in the same order and of the same data type within the values clause as the column list within the INSERT INTO statement.

example

Col1 is Datetime datatype
Col2 is Integer datatype
Col3 = char datatype

docmd.runsql "INSERT INTO tbl1 (Col1, Col2, Col3) Values( '05/07/2010', 12345, 'Hi World')"

any references (whether on screen or variables) must be "outside" the statement text.

Dim i as string

i = dlookup("col1","tbl2","[tbl2id]=" & me.txtbox34)

docmd.runsql "INSERT INTO tbl1 (Col1, Col2, Col3) Values('" & me.txtboxdate & "', " & me.ident & ", " & i & ")"

There is a column within your table "schedule" which uses an access reserved word (Date) you should rename this, or delimit this with square brackets [].

Hope this helps and sets you on the right track (and welcome).

cheers

Leon

[NeoPa]

Decent answer Leon, but your handling of dates is not quite right. See Literal DateTimes and Their Delimiters (#) for more on this. Your handling of string delimiters is better than most mind.

As for the question, this is nicely formatted with CODE tags and it has relevant error messages. What would be helpful though, is for the line that the error message occurs on to be posted too.

Other than the dates being wrong, the answer supplied is reliable.

[cehlinger]

Seems like this has gotten me further. I no longer get the Type Mismatch error but now it is telling me it can't add a record to the table due to key violations. There is a key field in the Schedule table that I didn't mention but it is an autonumber field. Do I need to do something special with that when inserting through vba? I just figured it would add the record in and generate the autonumber for the field. What am I missing?

[cehlinger]

Further info. I had it working (was missing a field) but it was putting in 12/30/1899. I formated the date correctly and it then put it into the table correctly. However, after that I am now back to getting the key violation error. I changed nothing in the code and when I put in a break point and check the values being passed to the insert statement they are correct.

Not sure why it worked twice with the wrong date and then once correctly when I fixed it and now it's back to the same error. I'll keep looking.

[cehlinger]

I have it working now. Was using some bad data to test and was violating a constraint. Duh! Thanks for all your help. Now just to tie it all together with the forms to get it working.

[NeoPa]

Why don't you post your current code. It's hard to be sure, but from your comments I think you may still be treating the date value as a string, which would be a bad idea.

[cehlinger]

Here is what I have currently:

Private Sub cmd_addRecord_Click()
On Error GoTo Err_cmd_addRecord_Click

Dim i
For i = Int(CVDate(Me!txt_beginDate)) To Int(CVDate(Me!txt_EndDate))
    
    If (DatePart("w", i, vbMonday) <> 6) And (DatePart("w", i, vbMonday) <> 7) Then
    DoCmd.RunSQL "Insert INTO schedule(contactID, EventID, [Date]) Values('" & Me!txt_ContactID & "', '" & Me!txt_EventID & "', #" & Format(i, "m/d/yyyy") & "#);"
    End If

Next i

Exit_cmd_addRecord_Click:
    Exit Sub

Err_cmd_addRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmd_addRecord_Click
    
End Sub

My next obstical is to open the dialog that I am using this on from a sub from on a form. I want it to pass the primary id from the main form. Here is what I have now but it's not working. I am sure I am missing something simple:

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm_addMemSched"

    DoCmd.OpenForm stDocName, acNormal, , "event_id = " & Forms.events.EventID

[NeoPa]

From line #8 of your first set of code EventID is a string value, however on line #6 of your second set of code you're trying to treat it as a numeric value. Clearly one will fail. Whichever doesn't match the actual design.

BTW, I was clearly wrong about your possibly treating date values as strings. I very rarely see date literals done absolutely properly on here, but yours is.