Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Feb 2011
    Posts
    23

    Unanswered: Vba settings help please

    problem with vba settings

    please help me, i need you to see why the values in my report will not appear when i enter parameters from form like [Forms]![ypobolesfrm]![year] on my crosstab query, while
    report works when i put the parameters like "2011" (numbers) in the same query..
    code for my report is:

    Private Sub Report_Open(Cancel As Integer)
    Dim db As Database, Qrydef As QueryDef, fldcount As Integer
    Dim i As Integer
    Dim rpt As Report
    Dim fldname As String, ctrl As Control, ctrl2 As Control


    On Error GoTo Report_Open_Err

    Set db = CurrentDb
    Set Qrydef = db.QueryDefs("Qrsavvataepilogicross")
    fldcount = Qrydef.Fields.Count - 1

    If fldcount > 6 Then
    MsgBox "the number of field is over (5) and only the (5) first fileds will be shown"
    fldcount = 6
    End If

    Set ctrl = Me.Controls("date1")
    Set ctrl2 = Me.Controls("total1")
    If fldcount >= 2 Then
    ctrl.ControlSource = Qrydef.Fields(2).Name
    Me("date1_Label").Caption = Qrydef.Fields(2).Name
    ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(2).Name & "])"
    End If

    Set ctrl = Me.Controls("date2")
    Set ctrl2 = Me.Controls("total2")
    If fldcount >= 3 Then
    ctrl.ControlSource = Qrydef.Fields(3).Name
    Me("date2_Label").Caption = Qrydef.Fields(3).Name
    ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(3).Name & "])"
    End If

    Set ctrl = Me.Controls("date3")
    Set ctrl2 = Me.Controls("total3")
    If fldcount >= 4 Then
    ctrl.ControlSource = Qrydef.Fields(4).Name
    Me("date3_Label").Caption = Qrydef.Fields(4).Name
    ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(4).Name & "])"
    End If

    Set ctrl = Me.Controls("date4")
    Set ctrl2 = Me.Controls("total4")
    If fldcount >= 5 Then
    ctrl.ControlSource = Qrydef.Fields(5).Name
    Me("date4_Label").Caption = Qrydef.Fields(5).Name
    ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(5).Name & "])"
    End If

    Set ctrl = Me.Controls("date5")
    Set ctrl2 = Me.Controls("total5")
    If fldcount = 6 Then
    ctrl.ControlSource = Qrydef.Fields(6).Name
    Me("date5_Label").Caption = Qrydef.Fields(6).Name
    ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(6).Name & "])"
    End If


    Report_Open_Exit:
    Exit Sub

    Report_Open_Err:
    MsgBox Err.Description, , "Report_0pen()"
    Resume Report_Open_Exit
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't see any reference to [Forms]![ypobolesfrm]![year] in your code.
    Have a nice day!

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Your problem is more likely the SQL of your query, not your report's code. Copy and paste the SQL so we can see that.

    SL

  4. #4
    Join Date
    Feb 2011
    Posts
    23
    Quote Originally Posted by Sinndho View Post
    I don't see any reference to [Forms]![ypobolesfrm]![year] in your code.
    i am absolutelly rookie on vba and have two parameters from form i need to use 1st [Forms]![ypobolesfrm]![year] 2nd [Forms]![ypobolesfrm]![month]

    should they be in the code?

  5. #5
    Join Date
    Feb 2011
    Posts
    23
    Quote Originally Posted by Sam Landy View Post
    Your problem is more likely the SQL of your query, not your report's code. Copy and paste the SQL so we can see that.

    SL
    the code from the crosstab query is

    TRANSFORM Max(Qrsavvataepilogi.[ΧΡ ΣΑΒΒΑΤΟΥ]) AS [MaxOfΧΡ ΣΑΒΒΑΤΟΥ]
    SELECT Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ, Sum(Qrsavvataepilogi.[ΧΡ ΣΑΒΒΑΤΟΥ]) AS [ΧΡ ΣΑΒΒΑΤΟΥ]
    FROM Qrsavvataepilogi
    GROUP BY Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ
    ORDER BY Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ
    PIVOT Qrsavvataepilogi.ΗΜΕΡΟΜΗΝΙΑ;


    the code from sthe query crosstab uses, (Qrsavvataepilogi) is

    SELECT Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy") AS [ΑΝΑ ΕΤΟΣ3], Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m") AS [ΑΝΑ ΜΗΝΑ3], [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ] AS ΟΝΟΜΑΤΕΠΩΝΥΜΟ, ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ AS ΗΜΕΡΟΜΗΝΙΑ, IIf(Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])=7 And nz([ΩΡΑΡΙΟID])<>5 And nz([ΩΡΑΡΙΟID])<>0 And [ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]<>NZ([ΑΡΓΙΑ]) And [ΧΡΕΩΣΗID]=4,1) AS [ΧΡ ΣΑΒΒΑΤΟΥ]
    FROM ypaliloitbl INNER JOIN (eidiyphresiontbl INNER JOIN (eidikeshreoseistbl INNER JOIN (ypiresiestbl LEFT JOIN argiestbl ON ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ = argiestbl.ΑΡΓΙΑ) ON eidikeshreoseistbl.ΕΙΔΙΚΗΧΡΕΩΣΗID = ypiresiestbl.ΧΡΕΩΣΗID) ON eidiyphresiontbl.ΕΙΔΟΣΥΠΗΡΕΣΙΑΣID = ypiresiestbl.ΕΙΔΟΣΥΠΗΡΕΣΙAΣID) ON ypaliloitbl.ΥΠΑΛΛΗΛΟΣID = ypiresiestbl.ΕΠΩΝΥΜΟID
    GROUP BY Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy"), Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m"), [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ], ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ, ypaliloitbl.ΕΠΩΝΥΜΟ, ypaliloitbl.ΟΝΟΜΑ, ypiresiestbl.ΧΡΕΩΣΗID, ypiresiestbl.ΩΡΑΡΙΟID, ypaliloitbl.ΥΠΑΛΛΗΛΟΣID, argiestbl.ΑΡΓΙΑ
    HAVING (((Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy"))=2011) AND ((Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m"))=4) AND ((ypaliloitbl.ΥΠΑΛΛΗΛΟΣID)<>58 And (ypaliloitbl.ΥΠΑΛΛΗΛΟΣID)<>60) AND ((Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]))=7))
    ORDER BY Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy"), Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m"), [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ], ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ;


    the problem is that in this way it works.. when i use parameters from form like [forms]![ypofovelsfrm]![year] in the select query then code doesnt work..

  6. #6
    Join Date
    Feb 2011
    Posts
    23

    the db

    i upload the db so that you can see it.

    the select query is "Qrsavvataepilogi"
    the crosstab query is "Qrsavvataepilogicross"
    the form is "ypobolesfrm" and the two unbound textboxes are the in forth
    line or textbox pairs
    the report is "Rptsavvataepilogicross"

    it runs when in the select query is "Qrsavvataepilogi" i manually type the
    year and month to have report run with results
    Attached Files Attached Files

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    It seems to me you're spelling the form name two different ways. Is that a typo, or are you mis-spelling the form name when you enter it as a parameter?

    Also, "year" is a function name, and is a "reserved word" in Access, meaning that when Access sees such a word, it has a special meaning. You should never use reserved words as object names or variables in your forms, reports, tables, queries, or code, because Access will get mixed up when you refer to them. The "month" control is also the same problem. Change both names to valid names, change any code as necessary, and try using the [Forms] etc. parameter again. It just might work now. Of course, make sure you spell the form name correctly.

    Good luck,

    SL

  8. #8
    Join Date
    Feb 2011
    Posts
    23
    Thank you and it is just a typo here, yes i ve changed the reserved words in fact i never had used them its just an example here because i ve had used the Greek words for year and month, still.. the parameters will not work with it even though i ve declared them also in the parameters in the query design. i think it is a bug you can try for yourself i ve upload my db. try it

  9. #9
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I downloaded it, but I get an error message saying the zipped folder is corrupted. When I tried to bypass that, I got a message saying there are no files to extract.

    I have another question, though, which you alluded to earlier. What is your report's RecordSource? Is it a table? If it's a query, copy and paste the SQL so we can see it. Eiher way, you may have hit the nail on the head when you said that maybe your parameters should be in your code. It depends on the answer to this question.

    Thanks,

    SL
    Last edited by Sam Landy; 05-26-11 at 13:22.

  10. #10
    Join Date
    Feb 2011
    Posts
    23
    Thank you Sam
    I tried to upload my db again because you had to see it (it;s weird),.
    but there is a probleb as you say with the upload as you describe

    anyway, my select query is "Qrsavvataepilogi":

    SELECT Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy") AS [ΑΝΑ ΕΤΟΣ3], Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m") AS [ΑΝΑ ΜΗΝΑ3], [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ] AS ΟΝΟΜΑΤΕΠΩΝΥΜΟ, ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ AS ΗΜΕΡΟΜΗΝΙΑ, IIf(Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])=7 And nz([ΩΡΑΡΙΟID])<>5 And nz([ΩΡΑΡΙΟID])<>0 And [ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]<>NZ([ΑΡΓΙΑ]) And [ΧΡΕΩΣΗID]=4,1) AS [ΧΡ ΣΑΒΒΑΤΟΥ]
    FROM ypaliloitbl INNER JOIN (eidiyphresiontbl INNER JOIN (eidikeshreoseistbl INNER JOIN (ypiresiestbl LEFT JOIN argiestbl ON ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ = argiestbl.ΑΡΓΙΑ) ON eidikeshreoseistbl.ΕΙΔΙΚΗΧΡΕΩΣΗID = ypiresiestbl.ΧΡΕΩΣΗID) ON eidiyphresiontbl.ΕΙΔΟΣΥΠΗΡΕΣΙΑΣID = ypiresiestbl.ΕΙΔΟΣΥΠΗΡΕΣΙAΣID) ON ypaliloitbl.ΥΠΑΛΛΗΛΟΣID = ypiresiestbl.ΕΠΩΝΥΜΟID
    GROUP BY Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy"), Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m"), [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ], ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ, ypaliloitbl.ΕΠΩΝΥΜΟ, ypaliloitbl.ΟΝΟΜΑ, ypiresiestbl.ΧΡΕΩΣΗID, ypiresiestbl.ΩΡΑΡΙΟID, ypaliloitbl.ΥΠΑΛΛΗΛΟΣID, argiestbl.ΑΡΓΙΑ
    HAVING (((Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy"))=2011) AND ((Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m"))=4) AND ((ypaliloitbl.ΥΠΑΛΛΗΛΟΣID)<>58 And (ypaliloitbl.ΥΠΑΛΛΗΛΟΣID)<>60) AND ((Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]))=7))
    ORDER BY Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy"), Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m"), [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ], ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ;

    the crosstab query "Qrsavvataepilogicross" is:


    TRANSFORM Max(Qrsavvataepilogi.[ΧΡ ΣΑΒΒΑΤΟΥ]) AS [MaxOfΧΡ ΣΑΒΒΑΤΟΥ]
    SELECT Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ, Sum(Qrsavvataepilogi.[ΧΡ ΣΑΒΒΑΤΟΥ]) AS [ΧΡ ΣΑΒΒΑΤΟΥ]
    FROM Qrsavvataepilogi
    GROUP BY Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ
    ORDER BY Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ
    PIVOT Qrsavvataepilogi.ΗΜΕΡΟΜΗΝΙΑ;

    right now the criteria are not set from form
    i would like these criteria
    1st [Forms]![ypobolesfrm]![ΕΤΟΣ] for year intead of manual type 2011
    2nd [Forms]![ypobolesfrm]![ΜΗΝΑΣ] for month

    the query runs when i set these parameters from form, returning values
    then the report doesnt
    Report only has values when parameters are like: 2011 and: 4

    that is why i said about the code,.. thank you in advance.
    Attached Files Attached Files
    Last edited by manos39; 05-27-11 at 04:46.

  11. #11
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Same problem. I think the db is too large. With the database open, click on Tools->Database Utilities->Compact and Repair Database. After it's finished, close the database, ZIP it, and try to upload it again.

    I think I may have part of the solution, though, after studying the select query again. Try changing the code
    HAVING (((Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy"))=2011) AND ((Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m"))=4)
    to
    HAVING (((Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"yyyy"))=[Forms]![ypobolesfrm]![ΕΤΟΣ]) AND ((Format([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ],"m"))=[Forms]![ypobolesfrm]![ΜΗΝΑΣ])
    Try that before bothering to upload the db again. If it doesn't work the way you expect, there's another trick up my sleeve, namely changing the HAVING clause to a WHERE clause. One thing at a time, though.

    Good luck,

    SL

  12. #12
    Join Date
    Feb 2011
    Posts
    23
    Hello and thank you Sam,
    I have tryed that before, i did it as you descrive, the query did not run, untill i spesifically declared the same parameters in the parameters design. Then the query run fine by having the form's values like 2011 and 4,... but as expected, the report run empty again (although i typed 2011 and 4 in the form required).
    That is the strange thing i was telling you about. ...???
    What else should i try.. change having to where can you explain? i think i ve tyryed that in design view having the expressions and instead put group by, had where and in the criteria the parameters to point the form texboxes. Again the guery run but the report came empty.. That is why i think about coding better the vba code from the begening or secondly a bug. I am grateful that you tryed so far with this problem.

  13. #13
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hi,

    You must be someone really special if you keep on trying after so much frustration.

    Do this. With the report open in design view, if the Property Sheet is not visible, right-click on the black square inside a square box in the upper left-hand corner, and select "Properties." In the Property Sheet, go to the top line, where it says "Record Source." What does it say on the line (in the white space)?

    It should either give the name of a table or query, or it will have a SQL statement. If it is blank, it's a problem. If it does say something, click anywhere on that line, and then click on the outer ellipsis on the right-hand side, and get into the object. Is it what you expected?

    By the way, did you try compacting the db, as I described on Friday, and uploading it again?

    SL

  14. #14
    Join Date
    Feb 2011
    Posts
    23
    Hi Sam

    i did as you instructed and yes in the field properties of the report, it says "Qrsavvataepilogicross" as recordsource. I have tryed to upload the database from home and work. Same problem it shows it is uploaded, but there is not extension like "mdb" on the end of the file when someone opens the zip file (tryed to download myself). I would like you to see it though and if so if you agree, my e-mail is manos39@gmail.com.
    Thank you for your effort again

  15. #15
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I admit that I didn't expect the crosstab query to be your recordsource. Now, though, maybe you can try to modify the crosstab query with the same HAVING clause we tried before, only different. Try modifying
    TRANSFORM Max(Qrsavvataepilogi.[ΧΡ ΣΑΒΒΑΤΟΥ]) AS [MaxOfΧΡ ΣΑΒΒΑΤΟΥ]
    SELECT Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ, Sum(Qrsavvataepilogi.[ΧΡ ΣΑΒΒΑΤΟΥ]) AS [ΧΡ ΣΑΒΒΑΤΟΥ]
    FROM Qrsavvataepilogi
    GROUP BY Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ
    ORDER BY Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ
    PIVOT Qrsavvataepilogi.ΗΜΕΡΟΜΗΝΙΑ;
    to
    TRANSFORM Max(Qrsavvataepilogi.[ΧΡ ΣΑΒΒΑΤΟΥ]) AS [MaxOfΧΡ ΣΑΒΒΑΤΟΥ]
    SELECT Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ, Sum(Qrsavvataepilogi.[ΧΡ ΣΑΒΒΑΤΟΥ]) AS [ΧΡ ΣΑΒΒΑΤΟΥ]
    FROM Qrsavvataepilogi
    WHERE (Year([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])=[Forms]![ypobolesfrm]![ΕΤΟΣ]) AND (Month([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])=[Forms]![ypobolesfrm]![ΜΗΝΑΣ])
    GROUP BY Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ
    ORDER BY Qrsavvataepilogi.ΟΝΟΜΑΤΕΠΩΝΥΜΟ
    PIVOT Qrsavvataepilogi.ΗΜΕΡΟΜΗΝΙΑ;
    Notice I'm using WHERE instead of HAVING, and Year() and Month() functions instead of the Format() function.
    Maybe put the upload aside for now.

    Good luck,

    Sam

Posting Permissions

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