Hi guys . I created a form that has 3 combo boxes. Their name are Project,Number, year and weekno . It has also a button that on click action supposed to generate a report baced on my combo box criteria and then loads that report for me. could any expert show me an example on how to do this. I have difficulty finding an example in google since i did not exactly what is called this method
of generating report in access 2000.Thanks

If you want a complicated answer, do your research at Microsoft Access Developer Center (http://www.msdn.microsoft.com/office...anding/access/)

Otherwise, if your report is already created (which it should be) you can just pass the data from the combo boxes. Here is how that would look when added to the button the user clicks to get the report:

'----------
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim lgProject As Integer
    Dim lgNumber As Integer
    Dim lgYrWk As Integer

    stDocName = "rptYourReportName"
    lgProject = Forms!frmYourFormName.cboProject
    lgNumber = Forms!frmYourFormName.cboNumber
    lgYrWk = Forms!frmYourFormName.cboYrWk

    stLinkCriteria = "[Project]= " & lgProject & _
        " AND " & "[Number] = " & lgNumber & _
        " AND " & "[YrWk] = " & lgYrWk


    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

'----------

This should get you started. Please note I am assuming that you are passing NUMBERS from your combo boxes. If you are passing text, you need to change the syntax slightly like this:

'----------
Dim stProject As String

stLinkCriteria = "[Project] = " & "'" & stProject & "'"

'----------

HTH


mmcdonal

Hi,
i would suggest u read "Beginning A2K2 VBA" by wrox. After i have got over
the missing files and errors of the edition this book has been of great help.
See the topic "Building a Query by form Interface", pg 275 and u'll create
QBF interface that will solve ur problem.
rgds Penta

Thank u for u reply. Could u tell me exact title of that book. i could not find it . I have already purchased the begining vba 2000 . Is that the same title ?

Roberth Smith,Dave Sussman,Ian Blackburn,John Colby, Mark Horner,Martin Reid,
Paul Turley, Helmut Watson are the authors and the title is "Beginning Access
2002 VBA", Wrox, ISBN 1-86100-821-X.
Hope will help,
P

i've used the code above to help with a little project i'm doing and it works great....i have one question when i call my report for some reason it is not sorting. i have a sort setup in my querry but when i run it from my form i doesn't work. any ideas??

Open your report in Design view, then select Sorting and Grouping from the report's properties menu (right click).

Find the item you want to sort in the left side combo boxes, and then select the sort order.

HTH

mmcdonal

Hello mmcdonal

I am new at VBA and I was trying modify the Query dynamically using VBA. When my code is executed I am getting Runtime Error 2176 " The Setting for this property is too long"; at the line where I am assigning the STRING (SQL) to the Form.Recordsource.

Me.Parent.Form("subform_qry_issues_and_actions").F orm.RecordSource = strFilterSQLz

Anyhelp would be highly appreciated.

The Code goes as follows:

Private Sub btn_RunQuery_Click()

Dim strFilterSQLz As String
Dim strFilterSQL2 As String
'Dim temp1, temp2, temp3, temp4, Date1, Date2, Date3, Date4
'Variable to hold filtered SQL string
'Dim strFilterSQLz As String
Dim str

'Set default record source of form
strFilterSQLz = "SELECT ACTIONS_T.ActionID, ACTIONS_T.AID, ACTIONS_T.IssueID, ACTIONS_T.ActionPointDescription, ACTIONS_T.TrafficLightColour,ACTIONS_T.ActionPoint PrimaryResponsibility, ACTIONS_T.ActionPointOwner, ACTIONS_T.DateAssignedToAP_Owner, ACTIONS_T.ProgressToDate,ACTIONS_T.OriginalTargetC ompletionDate, ACTIONS_T.ActualCompletionDate, ACTIONS_T.ActionStatus, ACTIONS_T.ExternalLegalCost,ACTIONS_T.HoursEffortS pent, ACTIONS_T.ALT, ACTIONS_T.ELT, ACTIONS_T.IT, ACTIONS_T.CustServ, ACTIONS_T.MaRS, ACTIONS_T.MMB, ACTIONS_T.[C&I]," & _
" ACTIONS_T.[W'sale], ACTIONS_T.Regulatory, ACTIONS_T.Legal, ACTIONS_T.ExtAff, ACTIONS_T.Finance, ACTIONS_T.Agility, ACTIONS_T.Alinta,ACTIONS_T.Audit, ACTIONS_T.[P&C], ACTIONS_T.BillOps, ACTIONS_T.CustTransf, ACTIONS_T.NetwOps, ACTIONS_T.SuppServ, ACTIONS_T.BusSyst,ACTIONS_T.BSCProj, ACTIONS_T.TrafficLightColour, VISUAL_STATUS_T.TrafficLightColour, ACTION_TEAMS_T.ActionTeam, ACTIONS_T.ActionPointPrimaryResponsibility, ACTION_OWNER_T.ActionOwner, ACTIONS_T.ActionPointOwner, ACTIONS_T.ActionStatus, issues_and_actions_subquery.IssueName," & _
" issues_and_actions_subquery.IssueDescription, issues_and_actions_subquery.IssueID, issues_and_actions_subquery.TeamName, issues_and_actions_subquery.ItemStatus, issues_and_actions_subquery.Priority, issues_and_actions_subquery.[Issue Owner], issues_and_actions_subquery.DateIssueRaised,issues _and_actions_subquery.DateIssueClosed, issues_and_actions_subquery.REGISTERS_T.RegisterNa me, issues_and_actions_subquery.STATE_T.State, issues_and_actions_subquery.FUELS_T.Fuel, issues_and_actions_subquery.SYSTEM_T.SystemName, issues_and_actions_subquery.[CaseManaged?]," & _
" issues_and_actions_subquery.[Audit?], issues_and_actions_subquery.[Regulatory?], issues_and_actions_subquery.NumberOfCustomersImpac ted, issues_and_actions_subquery.PotentialImpactOnAGL, issues_and_actions_subquery.PotentialIimpactOnAffe ctedParties FROM (ITEM_STATUS_T RIGHT JOIN (ACTION_OWNER_T RIGHT JOIN (ACTION_TEAMS_T RIGHT JOIN (VISUAL_STATUS_T RIGHT JOIN ACTIONS_T ON VISUAL_STATUS_T.VisualStatusID = ACTIONS_T.TrafficLightColour) ON ACTION_TEAMS_T.ActionTeamID = ACTIONS_T.ActionPointPrimaryResponsibility) ON ACTION_OWNER_T.ActionOwnerID = ACTIONS_T.ActionPointOwner) ON ITEM_STATUS_T.ItemStatusID = ACTIONS_T.ActionStatus) LEFT JOIN issues_and_actions_subquery ON ACTIONS_T.IssueID = issues_and_actions_subquery.IssueID" & _
" WHERE 1"


'============
If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDOFrom].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.DateAssignedtoAP_Owner >= #" & Format(tb_APDOFrom.Value, "mm/dd/yyyy") & "#"
End If

If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDOTo].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.DateAssignedtoAP_Owner <= #" & Format(tb_APDOTo.Value, "mm/dd/yyyy") & "#"
End If

If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDCFrom].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.ActualCompletionDate >= #" & Format(tb_APDCFrom.Value, "mm/dd/yyyy") & "#"
End If

If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDCTo].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.ActualCompletionDate <= #" & Format(tb_APDCTo.Value, "mm/dd/yyyy") & "#"
End If


' Set record source with filtered SQL
Me.Parent.Form("subform_qry_issues_and_actions").F orm.RecordSource = strFilterSQLz
Me.Parent.Form("subform_qry_issues_and_actions").F orm.Requery
'DoCmd.OpenQuery (strFilterSQL1)
End Sub