Inserting a DATE from Recordset into Table

Getting a Syntax error when trying to insert a date from recordset into a table. If I take the date out of the insert is works without issue. Abbrevaited code looks like:

.

Set rsP = CurrentDb.OpenRecordset("SELECT RawData.name " & _
"FROM RawData " & _
"WHERE RawData.Date = #" & VDate & "# " & _
"AND RawData.Channel = """ & VChannel & """ " & _
"ORDER BY ColeTaylorRawData.name")

DoCmd.RunSQL "INSERT INTO tblRankings (Date, Channel) & _
"VALUES (#" & VDate & "#, """ & VChannel & """)"
.

If I remove the VDate from the DoCmd.RunSQL everything works perfectly. I can see in the debugger that my VDate is holding the correct date however the syntax is wrong on the insert. Why it is not the same as in the recordset is confusing to me.

Sponsored Links:

Related Topics

Move through recordset inserting new records
I have a table which appears as follows:

Store.Product.SalesDate.CumSales
A.Cans.20110101.100
A.Cans.20110103.250
A.Cans.20110104.300
A.Cans.20110106.500

CumSales = Cumulative Sales

In the table, I have missing dates. I want to add the missing dates. For example in the above, 2nd January is missing so the new record would be:

Store.Product.SalesDate.CumSales
A.Cans.20110102.100

When adding a missing date, I will use the cumulative sales from the last reported date before the new record being added.

I have a few hundred stores and about ten products for a period of two years so it's quite a lot of data to go through.

I will be adding data to the table in future (with missing dates) so I will need to run the update frequently to fill the gaps.

I wanted to do it in a query but couldn't figure it out.

Instead I am trying to do it in VBA. The code fails when a new record is inserted into the table. It causes the recordset to position itself at the end. I tried to reposition the recordset but I am getting an error which says, "Provider cannot determine the value.


Update Table - SQL or Recordset Edit
I'm relatively new to Databases and have been working on some projects. I have a doubt of what methods are more appropriate when updating or inserting data in a table. Here are the two methods that I use.

1. Open e snapshot recordset, calculate things and insert or update with sql the result in the table. Example:

Dim DataSet as DAO.Recordset Set DataSet = CurrentDb.OpenRecordset("Table", dbOpenSnapshot) CurrentDb.Execute "INSERT INTO Table VALUES (Example)"

2. Open a recordset and use the edit command to update it

Dim DataSet as DAO.Recordset Set DataSet = CurrentDb.OpenRecordset("Table") DataSet.Edit DataSet("Field") = Example DataSet.Update

I don't know which one is faster or more appropriate. any help is very appreciated.


Recordset get records from Recordset
trying to open a recordset from a select statement off of another recordset. access can't find the input table or query.

Is it not possible to open a recordset off the results of a prior opened recordset


Find first date in recordset to use for calculated control
I have an invoice report that has a calculated control for billing cycle. Currently it is set to

=[Enter start date] & "-" & [Enter end date]

This is because the start date depends on the date of the first charge and changes every month, so the user enters the dates simply for display purposes. What I was hoping to do is have the start date determined automatically based on the underlying recordset. Is there a way to find the first date in the recordset and use it as the start date in the calculated control


Problem with UpdateBatch statement - empty date fields no longer empty
I've created a form, data is pulled from a table into a recordset, and the form fields are populated from the recordset. I've got a little "next" and "previous" button which, upon being clicked, first update the recordset and the table to reflect any changes made on the form, and then move to the next or previous record.

I'm facing a problem with date/time fields in my table. If the date/time field in the table is blank, and I press the "next" button, it populates the field with a date from theprevious record, and pushes it into my table.

I tried to solve it in my UpdateRecords sub by pushing a blank field into the recordset if the form field is blank (as seen in the code below).

Here's some (shortened) code samples. I didn't show a sample of the FillData sub because I don't think the problem is there. All data is showing up correctly on my form.


DAO.Recordset Vs ADODB.Recordset Vs Recordset
So I know that there are two types of libraries for retrieving recordsets, either DAO or ADO.
I read somewhere that if you are using DAO, you should use DAO.recordset instead of just "recordset" to make sure it run properly.
E.g.
Dim rs As DAO.Recordset
instead of
Dim rs As Recordset

but if the default is DAO anyways (I think), would it really cause any problems. (I'm using Access 2003)

So now, which is better DAO or ADO if I'm only using a recordset to update a single field of a record from a table that may contain hundreds of records.

Which is better in general? What exceptions are there? What do other people use


Insert query inserting 2 same records in the table
My below sql inser statement is at first inserting one row in the table but after some time it automatically adds one more row in the table

strSQL = "INSERT INTO RELEASELIST ([Release Name],[Production Date],[Code Delivery Date],[Reg1 Completion Date],[Reg2 Completion Date],[Imp Test Completion Date],[Content Type],[Modified],[Created],[Created By],[Modified By] ) VALUES ('" & txtReleaseName & "','" & dateProd & "','" & codeDelDate & "','" & regression1date & "','" & regression2date & "','" & impcompdate & "','Item','" & currDateTime & "','" & currDateTime & "','" & strUsername & "','" & strUsername & "')"

DoCmd.RunSQL strSQL


Add query recordset results to Table
I am trying to add a recordset to an existing table using VBA/DAO from Excel. I'm stumbling on this because the recordset is not a table but a result of a query. The below does not work. Myrecordset is the recordset created from running a nested parameter query...


Recordset Update Issues
I have a button that filters a form filled with dates from two tables.

It filters the Season to Seasons and the DevCode to DevCodeA and pulls the marketingname from the current form for display.

The dates pulled from the first table are set and do not change (except from the table by a manager) the dates from the second table need to be updateable as they are "actual" dates.

Name | Set Date | Entered Date
Milestone | Mgr Set | Actual Date

Right now nothing is updateable. I am getting a recordset not updateable error.
I check both date tables and they both have primary key fields (autonumber) and all of the other fields are not indexed.

Is it something to do with the filtering?


inserting values in column based another column
I have a table having a column of Date of Birth. another column named Date of Retirement. Now I want to create an update query that fills the column date of retirement by adding 60 in year of Date of Birth.

I have 1200 record in my table so I want to fill it by query.


inserting values in column based another column
I have a table having a column of Date of Birth. another column named Date of Retirement. Now I want to create an update query that fills the column date of retirement by adding 60 in year of Date of Birth.

I have 1200 record in my table so I want to fill it by query


Date between in recordset maybe
I have to write a piece of code on a combobox onclick event. I know what it needs to do but I just need some advice about the best and most efficient way to do it.
What needs to happen is
If date of delegate already scheduled is 2 weeks before or after the date of the combobox then messagebox pops up.

I thought the best way would be to call a recordset that would bring up the eventstartdate of the event selected and the delegate but I don't know how to calculate 2 weeks before and after this date. Can anyone shed any light or show me where to look. All the examples i've seen can show before or after a specific date but this date is variable and I'm a bit lost to tell the truth.


Bound Date/Time field to automatically display the current date
I have a bound form which has a field called WorkDate which I would like to default to todays date.it also still needs to be able to be changed to a different date. I have tried inserting =Date() in the On Load event of the
Bound Worked Date field, but no luck.the picker is still just sitting there with nothing in the field.


Latest names: Me.frmLastUpdated.Form.Recordset.AddNew
Private Sub Form_AfterUpdate()

Me.frmLastUpdated.Form.Recordset.AddNew "Comp_Name", "Daniel"

End Sub

Im inserting new data to a subform named frmLastUpdated which gives me lastest name that was changed to the form


Update disconnected recordset field on lost focus
A textbox on a form has data from a different table than the form's recordset. It is populated with a dao recordset object for that field on the Form_Load event.
The customer does not like "update" buttons.

The objective is to detect if the data (either Null or a date) is changed. On the lost focus (or other event)

check that the new value is a valid date
on the lost focus event, messagebox the user that is is not valid, return the orginal value OR - are you sure to continue
If a date existed before, update the record and field
If the value was null, see if the table has a matching primay key and update the field OR if no primary key exist, append a new record with the primary key and the date
if the user starts a new date, and uses the Excape key (Esc) return the orginal value

Code, suggestions? Please contribute.


Dynamic Recordset & Array
I'd like to create a recordset that is NOT based on a table or query, but rather an array of values. I would love some assistance by way of code because I have always struggled to understand how to use arrays.

Here's what I need to do. User inputs a Begin Date and an End Date into a form. I'd like to

1. Create an array of the dates included between and including the user input begin and end dates.

2. Fill a dynamic recordset that is not based on a table or query, rather the values in the array that I created in step one


Get new ID from the table BEFORE insert the new row.
I'm programming with VB .NET 2010 and using a Microsoft Access Database file which is bound to my project as a Dataset. Now I want to know: BEFORE inserting a new row into my dataset tablewhat will be the new ID number,

Surely you know the autonumber ID of a table is different with row count number, for there may be deleted rows between the table rows. Like the following example:

Row 0: ID=1
Row 1: ID=2
Row 2: ID=4 (ID number 3 is deleted)
Row 3: ID=5
Row 4: ID=9 (ID numbers 6,7,8 are deleted)

Now how do we know what ID number of Row 5 will be before inserting the new row? Perhaps ID numbers 10 and 11 are either deleted or not, so it may be 10, may be 11 or 12 or whatever! Isthere a way to know it BEFORE adding or inserting the new row into the table?


Creating a recordset and inserting datausing it in Access 2007?
I have created a Access 2007 database, Test.mdb, created a table called "mytest" and created required fields in it. Now, using VBA(Visual Basic for Applications) I want to insert data into it using a Recordset from a Word document.

Can anyone please point me to a proper link where there is an example of how data is inserted using a Recordset into a Access 2007 table?

I created one using resources on Web, but it is not correct.

[code]

I get a compile time error for "Dim cnn1 As ADODB.Connection" as "User defined Type not defined" I want to choose Tools-References from the VBA Editor menu bar, but References is disabled and I cannot choose it.

I guess I have to set up the connection from Word 2007(where the above code is present as a Macro) to Access 2007(where the database is) which I have not done.


Reference a Recordset in a recordset
I mentioned this in another thread but the thread went a different way so I thought I'd ask just this question.

Is it possible to reference a previously opened recordset like a table in a subsequent recordset


Displaying a recordset in a datasheet view
I have a procedure which generates a recordset in code. Currently I copy the recordset to a temporary table and use docmd.opentable to display the table. Is there a better way of doing itrather than creating the temporary table? Can I just display a recordset in a datasheet view?