Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    For Loop for Managing Recordsets (2000)

    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?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Loop for Managing Recordsets (2000)

    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.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Loop for Managing Recordsets (2000)

    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.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Loop for Managing Recordsets (2000)

    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

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Loop for Managing Recordsets (2000)

    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.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Loop for Managing Recordsets (2000)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Loop for Managing Recordsets (2000)

    Giorgio,

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

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

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Loop for Managing Recordsets (2000)

    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>
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Loop for Managing Recordsets (2000)

    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?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Loop for Managing Recordsets (2000)

    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>

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Loop for Managing Recordsets (2000)

    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?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Loop for Managing Recordsets (2000)

    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>

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Loop for Managing Recordsets (2000)

    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

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Loop for Managing Recordsets (2000)

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

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Loop for Managing Recordsets (2000)

    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).

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •