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 6063
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,579
Recognized Expert Moderator MVP
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,579
Recognized Expert Moderator MVP
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,579
Recognized Expert Moderator MVP
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,
UPDATE, DELETE) which are controlled by a web frontend and the table
records are manipulated to control the permissions.
Example:
The Press Release section record would look like this:
Username: John Doe
Function Name: Press Release
|
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 sub-directory and for each file listed insert a record into a temporary table.
Can anyone help?
My code is as follows....
SQL Stored Procedure
dbo.sp_bmwrInsSpool @username VarChar(8), @spoolName VarChar(12), @spoolDate VarChar(6) AS
DECLARE...
|
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 to use sub-queries and sub-reports to
filter and display the data in the unrelated tables in my report. The
common information is a user-inputed date range.
I want to avoid having the user prompted for the and
variables repeatedly. Somehow I...
|
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 names, date they
first became a customer, and about 3 fields of addional info.
I.e.
|
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, weight2. There
are 30-40 records per day and I would like to be able to have the query
find like dates (without having to enter a date) and give me an average
for all of the weight1 and weight2 records. Is this possible? Thanks!
| |
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 getting any records inserted. For
troubleshooting, I cut the form down to 1 textbox and when submitted it
populated 5 rows of the same data. So I know I'm connected, but not getting
the LOOP and NEXT correct? How do I set this up so many records can be...
|
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 tables are all linked with the field 'member_id', which is an auto-increment field in the parent table ('members').
I've been able to input multiple records into the other three tables 'specialty_groups', 'committee_interest' and 'committee_member'...
|
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, Event, LocationCity, and LocationState. Everything works perfectly, except for the fact that the dates do not correctly enter into the table. For example, if I enter a date range of March 3, 2008 - March 5, 2008 and click OK, 3 records are generated. ...
|
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")
connect.open "DSN=OPTUMETL;Driver= Oracle in OraHome92;Server=urnts1.uhc.com;UID=OPTUMETL;PWD=OPTUMETL"
Reporter.ReportEvent 0, "Database connection", "Successfully connected to URNTS1"
Set objRecordset = CreateObject("ADODB.Recordset")
' Stmt to execute...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |