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

    Using sql code as source parameter in a recorset o (2000)

    How come Query10 in the attached mdb works if run by itself while it doesn't if I include its sql code as the source parameter of a recordset Open method like I did in Private Sub Form_Load() of the startup form of the attached mdb?

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

    Re: Using sql code as source parameter in a recorset o (2000)

    Because the form opens to today, but there are no records for today, and you don't test for this.

    ...
    rst.Open ...
    <big>If Not rst.EOF Then</big>
    rst.MoveFirst
    i = rst!Giorno
    Do Until rst.EOF
    If Not rst!Giorno = i Then
    MsgBox "non

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

    Re: Using sql code as source parameter in a recorset o (2000)

    Thank you Hans,
    the problem is that by adding those two lines of code the Do Until...loop never gets entered into and so I cannot test if the T_COMUNI table has missing records. I've deleted the record for February 14 in the T_COMUNI table and as you can see in the attached mdb, that missing record is not detected when the startup form opens.

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

    Re: Using sql code as source parameter in a recorset o (2000)

    Giorgio,

    Could you compact your databases before zipping them? The current one went from 1084 MB to 444 MB after compacting. Thanks.

    OK, you can remove the If... End If. The error was in the way the date is included in the SQL statement. Use this:

    rst.Open "SELECT T_COMUNI.Giorno " & _
    "FROM T_COMUNI " & _
    "WHERE Year([giorno])= Year(#" & Format([Forms]![villadiserio]![CGior], "mm/dd/yyyy") & "#)", _
    cnn, adOpenKeyset, adLockOptimistic

    Note the use of # characters around the date, and the US date format.

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

    Re: Using sql code as source parameter in a recorset o (2000)

    Thank you my friend, it's just that making a copy of the original mdb in order to whittle it down to size caused those orphan code modules to be displayed in the Project Explorer and I remember you telling me to post uncorrupted databases so I created a new blank database and imported all objects into it forgetting that new databases don't have the "Compact on close" option checked by default.

Posting Permissions

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