By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

How to insert multiple records based on a date range

P: 5
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_addRecord_Click()
  2. On Error GoTo Err_cmd_addRecord_Click
  3.  
  4. Dim i
  5. For i = Int(CVDate(Me!txt_beginDate)) To Int(CVDate(Me!txt_EndDate))
  6.  
  7.     If (DatePart("w", i, vbMonday) <> 6) And (DatePart("w", i, vbMonday) <> 7) Then
  8.     DoCmd.RunSQL "Insert INTO schedule([contact ID], [Event ID], Date) Values('" + Me!txt_ContactID + "', " + Format(i, "m/d/yyyy") + ",8);"
  9.     End If
  10. Next i
  11.  
  12. Exit_cmd_addRecord_Click:
  13.     Exit Sub
  14.  
  15. Err_cmd_addRecord_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_cmd_addRecord_Click
  18.  
  19. End Sub 
This results in a Type Mismatch error.

Option 2
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_addRecord_Click()
  2. On Error GoTo Err_cmd_addRecord_Click
  3.  
  4. Dim RepeatEvent
  5.  
  6. For RepeatEvent = Int(CDate(Me!txt_beginDate)) To Int(CDate(Me!txt_EndDate))
  7.  
  8. DoCmd.RunSQL "INSERT INTO schedule ([contact ID], [Event ID], Date) " & _
  9.             "SELECT Forms![Form2]!txt_ContactID AS Expr1," & _
  10.             [Forms]![Form2]![txt_EventID] AS Expr2,#" & _
  11.             Format$(RepeatEvent, "m/d/yyyy")"#;"
  12.  
  13.  
  14. Next RepeatEvent
  15.  
  16. Exit_cmd_addRecord_Click:
  17.     Exit Sub
  18.  
  19. Err_cmd_addRecord_Click:
  20.     MsgBox Err.Description
  21.     Resume Exit_cmd_addRecord_Click
  22.  
  23. End Sub
  24.  
This results in a Compile Error. Syntax Error
May 7 '10 #1
Share this Question
Share on Google+
8 Replies


P: 53
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

Expand|Select|Wrap|Line Numbers
  1. docmd.runsql "INSERT INTO tbl1 (Col1, Col2, Col3) Values( '05/07/2010', 12345, 'Hi World')"
  2.  
any references (whether on screen or variables) must be "outside" the statement text.

Expand|Select|Wrap|Line Numbers
  1. Dim i as string
  2.  
  3. i = dlookup("col1","tbl2","[tbl2id]=" & me.txtbox34)
  4.  
  5. docmd.runsql "INSERT INTO tbl1 (Col1, Col2, Col3) Values('" & me.txtboxdate & "', " & me.ident & ", " & i & ")"
  6.  
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
May 7 '10 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 7 '10 #3

P: 5
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?
May 9 '10 #4

P: 5
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.
May 9 '10 #5

P: 5
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.
May 10 '10 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 10 '10 #7

P: 5
Here is what I have currently:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_addRecord_Click()
  2. On Error GoTo Err_cmd_addRecord_Click
  3.  
  4. Dim i
  5. For i = Int(CVDate(Me!txt_beginDate)) To Int(CVDate(Me!txt_EndDate))
  6.  
  7.     If (DatePart("w", i, vbMonday) <> 6) And (DatePart("w", i, vbMonday) <> 7) Then
  8.     DoCmd.RunSQL "Insert INTO schedule(contactID, EventID, [Date]) Values('" & Me!txt_ContactID & "', '" & Me!txt_EventID & "', #" & Format(i, "m/d/yyyy") & "#);"
  9.     End If
  10.  
  11. Next i
  12.  
  13. Exit_cmd_addRecord_Click:
  14.     Exit Sub
  15.  
  16. Err_cmd_addRecord_Click:
  17.     MsgBox Err.Description
  18.     Resume Exit_cmd_addRecord_Click
  19.  
  20. 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:

Expand|Select|Wrap|Line Numbers
  1.     Dim stDocName As String
  2.     Dim stLinkCriteria As String
  3.  
  4.     stDocName = "frm_addMemSched"
  5.  
  6.     DoCmd.OpenForm stDocName, acNormal, , "event_id = " & Forms.events.EventID
May 10 '10 #8

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 10 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.