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

    Changing Calendar Value Via Code (2003)

    In the attached mdb, please put a breakpoint at the code line
    <pre> Me.Parent.Form.CGior = "#" & _
    Format(Me.Parent.Form.CGior - 1, "mm/dd/yyyy") & "#"</pre>


    in the Private Sub Form_Current() for the MG1Dez subform and then open the Dezzo main form.
    How come the value of the CGior calendar remains unchanged instead of going back one day when you execute the above statement?

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

    Re: Changing Calendar Value Via Code (2003)

    Some kind of timing problem? If you don't place a breakpoint, the code is executed correctly.

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

    Re: Changing Calendar Value Via Code (2003)

    Thank you Hans,
    Is there a way to avoid the RecordSource of the subform being incremented by one day every time you open the main Dezzo form?
    Try opening and closing the Dezzo form a few times; you'll see records being added to the g1dez and g2dez tables.

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

    Re: Changing Calendar Value Via Code (2003)

    As far as I can tell from the convoluted code, the On Load procedure of the main form sets CGior and hence Giorno to one day after the highest Giorno occurring in Portdez23. The subforms are linked to the main form by Giorno. The subforms are automatically opened to a new record, since there is no record yet for the value of Giorno set in the main form. The On Current procedure of the subforms then populates the new record by setting Giorno to the value of CGior. The value of CGior is decreased later in the same procedure.

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

    Re: Changing Calendar Value Via Code (2003)

    That's right.
    I'm using the following code in the On Current procedure of the subform to remove the record that's been added to the subform RecordSource

    <pre> Dim cmd1 As ADODB.Command

    Set cmd1 = New ADODB.Command
    With cmd1
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "DELETE FROM g2dez" & _
    " WHERE giorno = #" & _
    Format(Me.Parent.Form.CGior, "mm/dd/yyyy") & "#"
    .CommandType = adCmdText
    .Execute
    End With
    Set cmd1 = Nothing</pre>


    but why then does the RecordSource still retain that record?

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

    Re: Changing Calendar Value Via Code (2003)

    I don't know. Wouldn't it be more efficient not to add the record at all?

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

    Re: Changing Calendar Value Via Code (2003)

    Well, yes I can set
    Me.CGior = DMax("Giorno", "portdez23")
    instead of Me.CGior = DMax("Giorno", "portdez23") + 1
    in the Load procedure of the Dezzo main form
    but it would have been better the main form opening up already on the day to be filled out instead of having the user selecting it on the calendar.

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

    Re: Changing Calendar Value Via Code (2003)

    Can't you do something like this pseudo-code?

    If Giorno is null then
    If Giorno is not the same as the value of CGior* in the parent form then
    Populate Giorno
    ...
    End If
    End If

    * or CGior + 1 or CGior - 1 , I haven't checked exactly what you want.

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

    Re: Changing Calendar Value Via Code (2003)

    But...
    Looking at your pseudo-code, if Giorno is Null then it's obvious that Giorno is not the same as the value of CGior so why the second If condition?

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

    Re: Changing Calendar Value Via Code (2003)

    Oops, that's correct. Well, I don't know then, sorry.

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

    Re: Changing Calendar Value Via Code (2003)

    I've actually now realised that the statement <pre> .CommandText = "DELETE FROM g1dez" & _
    " WHERE giorno = #" & _
    Format(Me.Parent.Form.CGior, "mm/dd/yyyy") & "#" </pre>


    doesn't actually delete the record but I cannot see what's wrong with the syntax. Can you?

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

    Re: Changing Calendar Value Via Code (2003)

    That instruction doesn't, it's the .Execute further down that deletes (or should delete) the record. But your code is far too complicated for me to analyze.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Changing Calendar Value Via Code (2003)

    is it because the line:
    DELETE FROM g1dez
    should read
    DELETE * FROM g1dez

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

    Re: Changing Calendar Value Via Code (2003)

    Thanks Pat.
    I'm afraid even by making the change you suggested, when you open and close the Dezzo main form a few times you see the records still get added to the subforms record sources.

Posting Permissions

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