Vba settings please help me

Manos39

Registered User.
Local time
Today, 06:15
Joined
Feb 14, 2011
Messages
248
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 in the way: [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:
Code:
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
 
Last edited by a moderator:
I think the problem might be with your parameter setting. Are you setting the parameter from a drop down box or a text field that users will input? If so you need to set the parameter to the control source like this

[Forms]![FormName]![txtbox1] this will pull make the parameter pull the data from that location.
 
I think the problem might be with your parameter setting. Are you setting the parameter from a drop down box or a text field that users will input? If so you need to set the parameter to the control source like this

[Forms]![FormName]![txtbox1] this will pull make the parameter pull the data from that location.

Thank you for your reply and yes i ve tried to do that, set the parameter from form (unbound textbox) but the code doesnt work then, maybe if i put the parameters in the code, but i dont know how to do it i am absolutelly rookie on vba
 
Please use code tags when posting code, especially if it is longer than just a couple of lines:

codetag001.png


Also, you need to set up parameters in your queries specifically:

attachment.php
 

Attachments

  • crosstabparameter01.png
    crosstabparameter01.png
    19.1 KB · Views: 296
Thank you Bob i have tried that, setting up parameters in my select query specifically, and it is working for the query!.. but then the report doesnt show anything...
When i use parameters
as [forms]![ypofovelsfrm]![year] from form, in the select query also specifically in the parameters, then code doesnt work.. (crosstab query works fine).
That is why i believe there is some issue is in the code of the report i earlier post
I could upload the db if you would be kind to take a look maybe its a bug.:)
 
Last edited:
You can upload the db. Remember to

1. Run Compact and Repair first

and

2. if necessary, zip the file as it has to be 2Mb or under to upload.
 
Thank you Bob,
I upload my db,.

-the form i need to have the parameters from is "ypobolesfrm"
and in there are two unbound textboxes on top, named after "ΕΤΟΣ" and "ΜΗΝΑΣ" ("year" and "month")
-the report which i need to work is "RptsavvataepilogicrossA4" which is called from the previous form by the Command130, and the crosstab query which feeds the report is "Qrsavvataepilogicross" (the select query which i would like the parameters go in to is "Qrsavvataepilogi".
Please take a look notice that when you put the parameters in "Qrsavvataepilogi", the report "RptsavvataepilogicrossA4" refuses to run with results. to have results someone sould type "2011" and "4" for example... and would like that changed..
thank you in advance
 

Attachments

Last edited:

Users who are viewing this thread

Back
Top Bottom