Recordset not updating

[butters27]

Hi,

I am running the following code which works ok the first time through the loop. When it gets to the next run through however, it stops at the text highlighted in red below. The value stored is still the old value. For some reason the sql does not update the recordset even though the sql is definitely run the 2nd time. Any ideas???

Thanks.


Private Sub AddDates_Click()
Dim retvalue As Integer, i As Variant, t As Variant, strSQL As String, rst As Recordset, k As Variant, db As Database, myQuery As QueryDef

For i = Int(CVDate(Me!StartDate)) To Int(CVDate(Me!EndDate))

If (DatePart("w", i, vbMonday) <> 6) And (DatePart("w", i, vbMonday) <> 7) Then
DoCmd.RunSQL "INSERT INTO [Date]([Date-Date], [Date-valid]) VALUES ('" & Format(i, "dd/mm/yyyy") & "', TRUE )"

Set db = CurrentDb

strSQL = "SELECT [Date-Key] FROM [Date] WHERE [Date-Date] = #" & i & "#"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
k = ![Date-key]
End With
rst.Close

For t = #9:00:00 AM# To #4:00:00 PM# Step #1:00:00 AM#
DoCmd.RunSQL "INSERT INTO [Timeslot]([Timeslot-date-key], [Timeslot-time]) VALUES ('" & k & "', '" & Format(t, "hh:mm") & "')"
Next t

Set rst = Nothing
Set k = Nothing

End If

db.Close
Next i

End Sub

[GolferGuy]

What is the error message when the execution stops at the line in red?
Vic

[butters27]

Run-time error '3021'
No current record.

Then it highlights that line I've highlighted in red.

[GolferGuy]

My only suggestion would be to loop through the code using breakpoints and the F8 key (Step mode). Then, by putting the SQL code that would be run with the "DoCmd.RunSQL" code, into the ClipBoard, then pasting it into an Access Query using the QBE grid, and running the query there, you will be able to see whatever the reason is the query is not running.
Hope this helps,
Vic

[butters27]

The query is running fine, I've tested it all with breakpoints already.
It's when it gets to the 2nd time round that the recordset is still holding the value from the first time the query runs. I can't seem to get the new value to be stored inside "k" within the "With RST" even though when I output the sql I can see that it's picked up the new value. I tried rst.requery, rst.movenext, etc but it still either shows the first value or that the recordset is empty.

[GolferGuy]

This code:

Set db = CurrentDb

strSQL = "SELECT [Date-Key] FROM [Date] WHERE [Date-Date] = #" & i & "#"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

will create a NEW recordset for each loop your code makes. Therefore, the second time through, this will be a completely NEW recordset. Obviously, on the second loop, this code is creating a recordset that is empty. So, you need to check this SQL during the second loop. I would guess that the record that this SQL is supposted to find is not there. Which means that the INSERT SQL in the code above is not working correctly. Again, I would suggest using the clipboard and a user-interface query to test the INSERT SQL during the second loop.

[butters27]

That's what I thought was happening but it IS finding the value the 2nd time through. It is inserting the 2nd date and it is pulling it back into the sql string, but it's then saying it's not there in the recordset.
I know it's there because when I get the error and DEBUG, if I hover over the strSQL I can see that it has pulled the correct value through.
I've also tested it using a msgbox so that I can see exactly what the strSQL is pulling through and the value is definitely there. ???

[GolferGuy]

So, if the INSERT SQL is putting the record there (with the date you want) and the second SQL has the correct date in it, then there is a discrepancy between the way the date is in the SQL (SELECT) statement and the date in the table. I would suggest checking to see if some Time is in the date field with the record, or whatever.
If you would like to send me a copy of this routine in a sample database, I would be more than happy to see what I can see. My email address can be found here .

[butters27]

Great thanks. Just sent it to you.

[GolferGuy]

Dom,
Here is your code after I have done a little bit of changes. I have highlighted my changes with bold. Some items, marked with green, are lines that I have moved to a "better" location.

Private Sub AddDates_Click()
Dim retvalue As Integer, i As Variant, t As Variant
Dim strSQL As String, rst As Recordset
Dim k As Variant, db As Database, myQuery As QueryDef

Set db = CurrentDb
For i = Int(CVDate(Me!StartDate)) To _
    Int(CVDate(Me!EndDate))

If (DatePart("w", i, vbMonday) <> 6) And _
        (DatePart("w", i, vbMonday) <> 7) Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO [Date]([Date-Date], " & _
         "[Date-valid]) " & _
         "VALUES ('" & Format(i, "dd/mmm/yyyy") & "', TRUE )"
    DoCmd.SetWarnings True
     
    strSQL = "SELECT [Date-Key] FROM [Date] " & _ 
         "WHERE [Date-Date] = #" & CVDate(i) & "#"
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
 
    With rst
        k = ![Date-key]
    End With
    rst.Close
    DoCmd.SetWarnings False
    For t = #9:00:00 AM# To #4:00:00 PM# _
               Step #1:00:00 AM#
        DoCmd.RunSQL "INSERT INTO " & _
          "[Timeslot]([Timeslot-date-key], " & _
          "[Timeslot-time]) VALUES ('" & k & _
                "', '" & Format(t, "hh:mm") & "')"
    Next t
    DoCmd.SetWarnings True
End If

Next i
Set rst = Nothing
set db = Nothing

End Sub

Basically what I did to get this to work is put in this format: "dd/mmm/yyyy" in your first INSERT query. Having never worked in a Day Month Year country, but only here in the US where we generally are a Month Day, Year formatted date. Anyway, I used Aug 1, 2007 to Aug 4, 2007 as my dates in your example. What was written into the "[Date]" table was 1/8/2007 to 1/10/2007. So, in order to get Access to understand what date I really wanted, I used the format function so when I put in Aug, Access would understand Aug, not the 8th day of some month. At least it worked for me. Let me know if this change works for you.
Additional suggestions:
Don't use Access type words for field or table names. "Date" is a word Access uses, therefore we shouldn't. You really don't need to use the table name as part of the field name. If you want to document your field names when writing something, you can always use the table name with the field name like this: tblName.fldName or tblName!fldName.
Also, best to use some form of prefix with your object names (tables, queries, forms, etc are objects). For example, "tbl" in front of table names. Therefore, tblDate would be a very acceptable table name. There is a form of naming things called CamelBack where there are no spaces or underscores (_), but each new word is capitalized, therefore "CamelBack" because of the "humps." This type of naming convention does not include special characters which makes it much easier to type. And, you don't need to put brackets "[]" around the field names. Again, easier to type. Because each new word is capitalized, the human eye can still read the words because we can see where each word begins.
Hope this helps,
Vic

[butters27]

Still doesn't work :0(
I'm now getting the error "Run-time error '3420' Object invalid or no longer set" . It's inserting the dates fine, eg. when I enter 01/01/07 to 10/01/07 it's inserting the first 2 dates as 01/01/2007 and 02/01/2007 but stopping at the rst value.

This time when I get the error and choose debug it takes me to the lin
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

[GolferGuy]

I'm going to assume you made changes to your code rather than copying and pasting the whole Sub from my post. Try doing the copy and paste of this whole Sub. Rename the Sub you currently have before doing the copy and paste.

[butters27]

Yes I did make the changes in my code. I've tried copying and pasting it though and when I do that I still get the "No current record" error message. Any more ideas???

[GolferGuy]

send me another sample like you did last time and I will give it another look. By the way, what version of Access are you using?

[butters27]

Sent it you again Vic. Just for info, I also tried changing my regional settings to US and running it and that didn't work either.
I'm using Access 97.

Thanks.