Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Help? (Access 2003 )

    I use the code below on a search form. I would like for the results of the list box search to populate a report instead of the list box. Is it possible to take the the sql and move it to a report? Thanks..

    Dim strsql As String, strOrder As String, strWhere As String
    Dim dbNm As Database
    Dim qryDef As QueryDef
    Set dbNm = CurrentDb()

    strsql = "SELECT SiteIssues_tbl.IssueID, SiteIssues_tbl.SITE_ID, SiteIssues_tbl.IssueID, SiteIssues_tbl.AdminDate, SiteIssues_tbl.Occurring, SiteIssues_tbl.Issue, SiteIssues_tbl.Administrative, SiteIssues_tbl.Technical, SiteIssues_tbl.IssueComments, SiteIssues_tbl.CandidatesAffected, SiteIssues_tbl.RecordCreated, SiteIssues_tbl.User, SiteIssues_tbl.DateModified " & _
    "FROM SiteIssues_tbl"


    strWhere = "WHERE"

    strOrder = "ORDER BY SiteIssues_tbl.AdminDate;"


    'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
    If Not IsNull(Me.txtIssue) Then '<--If the textbox txtCenterName contains no data THEN do nothing
    strWhere = strWhere & " (SiteIssues_tbl.Issue) Like '*" & Me.txtIssue & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
    End If

    If Not IsNull(Me.txtAdminDate) Then
    strWhere = strWhere & " (SiteIssues_tbl.AdminDate) Like '*" & Me.txtAdminDate & "*' AND"
    End If

    If Not IsNull(Me.txtSite) Then
    strWhere = strWhere & " (SiteIssues_tbl.SITE_ID) Like '*" & Me.txtSite & "*' AND"
    End If

    'Pass the SQL to the RowSource of the listbox

    Me.lstSearchResult.RowSource = strsql & " " & strWhere & "" & strOrder

    With Me.lstSearchResult
    If .ListCount > 0 Then
    GetListCount = .ListCount - 1 'Headings count
    Else
    GetListCount = 0 'Headings don't count if no items listed!!
    End If
    Me.Text31 = .ListCount - 1

    Dim ctl As Control
    End With
    If Me.Text31 = -1 Then
    Me.Text31.Value = 0
    Me.lstSearchResult.RowSource = ""
    DoCmd.RepaintObject acForm, "SitesIssues_qry"
    Me.Requery


    MsgBox "No Records Found."

    For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Or ctl.ControlType = acCheckBox Then
    ctl.Value = Null
    DoCmd.RepaintObject acForm, "SitesIssues_qry"
    Me.lstSearchResult.RowSource = ""
    Me.Requery
    End If
    Next ctl
    End If

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

    Re: Code Help? (Access 2003 )

    Create a report based on SiteIssues_tbl.
    Add the fields you want.
    Set the report to be sorted ascending on AdminDate in the Sorting and Grouping window.
    Save the report.
    Create a command button cmdReport on the form to open the report:
    <code>
    Sub cmdReport_Click()
    Dim strWhere As String

    On Error GoTo ErrHandler

    ' Assemble the WhereCondition
    If Not IsNull(Me.txtIssue) Then
    strWhere = strWhere & " Issue Like '*" & Me.txtIssue & "*' AND"
    End If

    If Not IsNull(Me.txtAdminDate) Then
    strWhere = strWhere & " (AdminDate Like '*" & Me.txtAdminDate & "*' AND"
    End If

    If Not IsNull(Me.txtSite) Then
    strWhere = strWhere & " SITE_ID Like '*" & Me.txtSite & "*' AND"
    End If

    ' Remove last " AND"
    If Not strWhere = "" Then
    strWhere = Left(strWhere, Len(strWhere) - 4)
    End If

    ' Open report
    DoCmd.OpenReport "rptSomething", acViewPreview, , strWhere
    Exit Sub

    ErrHandler:
    ' Ignore canceling the report
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub
    </code>
    Substitute the name of the report for rptSomething.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Help? (Access 2003 )

    Thanks Hans!!!! You just made my weekend!!! I really Appreciate it!!

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Help? (Access 2003 )

    Hans this works awesome but when I ran it on the admindate field it gave me the attached error. Any thoughts? Thanks...

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

    Re: Code Help? (Access 2003 )

    Sorry, my mistake. Remove the ( from the line

    strWhere = strWhere & " (AdminDate Like '*" & Me.txtAdminDate & "*' AND"

Posting Permissions

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