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
8 6035
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 32,556
Expert Mod 16PB
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.
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?
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sans Spam |
last post by:
Greetings! I have a table that contains all of the function
permissions within a given application. These functions are different
sections of a site and each has its own permissions (READ, WRITE,...
|
by: Ian Macey |
last post by:
I am wanting to Insert multiple records into a table, and although I have some working code I feel that there must be a better way. The purpose of the code is to do a directory listing of a...
|
by: BlackFireNova |
last post by:
Using Access 2002
I am writing a report which draws data from several different tables. I
can't link all the tables in a query, as some can not be related without
truncating the data. I plan...
|
by: Adam |
last post by:
Hi All,
This may be a really obvious thing that I'm missing ... but if anyone can
help, I'd appreciate it.
I have MS Access 2000:
I'm using it for a CRM type database. I have a table with...
|
by: edhead |
last post by:
Access newbie here, I am trying to figure out how to set up a query
that will return an average from multiple records all created on the
same date. Table has the following fields date, weight1,...
|
by: shank |
last post by:
I'm trying to use online samples for submitting multiple records from ASP
into a stored procedure. Failing! Through the below form, a user could be
submitting many records at a time. I'm not...
|
by: chumlyumly |
last post by:
Hello scripters -
OS: Mac OSX
Language: PHP w/ MySQL database
I've created an insert page where a user inputs his info, which then goes to four different tables in a MySQL database. The...
|
by: jrodcody |
last post by:
Hello:
I am trying to create multiple records in a table named tblTravel based on the input of a date range in two form controls. The inputs for the form are LastName, TravelDate, EndDate,...
|
by: uma9 |
last post by:
hi,
the code below is used to insert a single record....i want to know how to insert multiple records using a "for" loop...please help
Set connect = CreateObject ("ADODB.Connection")...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |