For Loop for Managing Recordsets (2000)

**grovelli** · 2004-02-17 22:20

I'm trying to use the calendar active-x on the startup form of the attached mdb to make it so that, when I select the "giorno" text box on a blank record and click the "Insert Date" button, the "giorno" and "ora" text boxes are respectively filled with the day selected through the calendar and the hour progression from 1 to 24. The problem is the last record added has the value for the "ora" field set to zero. Why does that happen?

**HansV** · 2004-02-17 22:33

I can't run the code, I get some Italian error message that I don't understand, but I guess that what you mention is caused by the Default Value 0 for the Ora field. Try clearing the Default Value property.

**grovelli** · 2004-02-17 22:48

Hi Hans!
Those were not error messages, just message boxes in my VBA code, I have now commented them out and cleared the Default Value property as you advised but I still get the same behavior.

**HansV** · 2004-02-17 22:59

Well, you're on an empty record when you click the button. First, the code sets the CData text box (bound to the Giorno field) to the date selected in the calendar control. But the Ora text box/field stays empty. So now you have one record for the selected date, with a blank Ora. Then, the code adds 24 records for the same date, with Ora = 1, 2, ..., 24. So now you have 25 records for the date, of which one has an empty Ora field.

Note: you don't close the record set and you don't set the object variables to nothing. This may slowly eat up your resources. Here is revised code:

Private Sub Comando50_Click()
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim i As Integer
Set cnn = CurrentProject.Connection
If MsgBox("Attenzione stai modificando la data. Vuoi farlo?", _
vbQuestion + vbYesNo) = vbYes Then
Me!CData = Me!CGior
Me!Ora = 1
MsgBox ("Attenzione se vuoi tornare alla data originale premi ESC dopo la chiusura di questa maschera")
rst.Open "Previsioni", cnn, adOpenKeyset, adLockOptimistic
For i = 2 To 24
rst.AddNew
rst!Giorno = CGior.Value
rst!Ora = i
rst.Update
Next i
End If
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub

**grovelli** · 2004-02-17 23:40

Thank you Hans!
One thing I don't understand it the fact I have to use the QAMdati query (which is basically the Previsioni table sorted for "giorno" and "ora") as the form's Record Source because if I set it to the Previsioni table instead and set the Order By property in the form's property sheet to "giorno", "ora", the record containing value 1 for the ora field always shows below all the other records for the same day.

**HansV** · 2004-02-17 23:47

It is always better to use a query if you want the records in a form to be displayed in a certain order. You can't depend on the Order By property of the form.

**HansV** · 2004-02-17 23:58

Giorgio,

You can set it in the On Load event of the form:

Private Sub Form_Load()
Me.CGior.Value = Date
End Sub

**grovelli** · 2004-02-19 19:24

In the startup form of the attached mdb I'm trying to use the "Copy Selected Date" button to copy all the records that have the value of the field Giorno equal to the date selected in the calendar and append them to the form's record source with just the date changed. I can do that if I'm to copy the last day available(button "Copy Previous Day") but cannot with the above because there's something wrong with the update query
UPDATE Pgc INNER JOIN Previsioni ON ([Pgc].[Ora]=[Previsioni].[Ora]) AND ([Pgc].[Giorno]=[Previsioni].[Giorno]) SET Pgc.Giorno = Max([previsioni].[giorno])+1;
that I can't spot. <img src=/S/help.gif border=0 alt=help width=23 height=15>

**grovelli** · 2004-02-19 19:25

Thanks again, I don't know if it happens on your pc as well but on mine the calendar on the form always opens on July 15, 2003. Is there a way to make it open on the current date?

**HansV** · 2004-02-19 19:54

The query Query3A doesn't work because you are using an aggregation function (Max) in a query that is not a Totals query. But I don't understand what it is meant to do. Since you have joined Pgc and Previsioni on Giorno and Ora, taking a Max of Giorno is useless. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

**grovelli** · 2004-02-19 20:08

Ok. Real example: how can I select a date in the calendar, say Feb 1st, and copy all the data pertaining to that day to the newly created records for Feb 5th?

**HansV** · 2004-02-19 20:14

OK, so Feb 1st is the date selected in the calendar, but where does Feb 5th come from? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

**HansV** · 2004-02-19 20:54

Construct the SQL in code instead of having a fixed query: replace the line

DoCmd.OpenQuery stDocName1, acNormal, acEdit

by

Dim datNextGiorno As Date
Dim strSQL As String
datNextGiorno =DMax("Giorno", "Previsioni") + 1
strSQL = "UPDATE Pgc SET Giorno = #" & Format(datNextGiorno, "mm/dd/yyyy") & "#"
CurrentDb.Execute strSQL

**grovelli** · 2004-02-19 22:03

Your usual awesomeness... <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

**grovelli** · 2004-02-19 22:14

It's the last date on the form's recordsource(Previsioni table)+1. Please see how the "Copy Previous Day" button works. I want to achieve exactly the same only this time instead of copying the data from the previous day I want to copy data from a day selected through the calendar(obviously one must choose a day that is present in the form's recordsource).

Thread: For Loop for Managing Recordsets (2000)

Thread Tools