Inserting a DATE from Recordset into Table
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.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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:
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.
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.
Is it not possible to open a recordset off the results of a prior opened recordset
=[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
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.
I read somewhere that if you are using DAO, you should use DAO.recordset instead of just "recordset" to make sure it run properly.
Dim rs As DAO.Recordset
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
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 & "')"
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?
I have 1200 record in my table so I want to fill it by query.