Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    97 (Form/report)

    Hi cheers for the help in my last reply

    Ive a table called DELTA in this table there are 3 fields Category Description and Time.

    1. I would like a form called GAMMA in this form there are 4 drop down boxes which displays all the different categories that are availabe in table DELTA under the category field. The user would select his/her categories for example 10YE 6YE 1YE AND 2YE.

    2. Once the user has selected the categories desired he/her then clicks a button which creates a report.

    3. In this report the following fields are displayed Description and Time which are associated with the category. For example If 1YE was selected in form GAMMA by the user then all the records with a category of 1YE in table DELTA would display on the report along with their Description and Time.

    Thanks in advance,
    Rob.

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

    Re: 97 (Form/report)

    Why do you want to have four drop down boxes on your form? A single one seems sufficient to me. Or do you want the user to be able to select multiple categories? In that case, I would use a multi-select list box instead of four combo boxes.

    Please indicate which option you want. The details of the answer are slightly different depending on your choice.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 97 (Form/report)

    Sorry, id like the user to make multiple selection categories.
    Rob

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

    Re: 97 (Form/report)

    OK, I would do it like this:

    First, create a report based on the DELTA table. You can use one of the Auto reports, or use the Report Wizard, or create it yourself, whatever you prefer. Don't worry about selecting specific categories yet. Save the report as rptDelta (or any other name you like). If you open it now, it should display ALL records from DELTA.

    Next, create a new form in design view. Leave it unbound, i.e. its Record Source property is blank.

    Put a List Box control on the form; name it lbxCategories.
    Set its Row Source property (in the Data tab of the Properties window) to SELECT DISTINCT Category FROM DELTA
    Set its Multi Select property (in the Other tab) to Simple or Extended.
    Simple means that the user can select multiple items by clicking on each of them; clicking an already selected item deselects it.
    Extended means that the user can select items the same way you can select files in Windows Explorer: click and drag or click and Shift+click to select a contiguous range of items, and Ctrl+click to select or deselect individual items.

    Also put a Command Button control on the form, name it cmdReport and set its caption to "Open Report", or something like that.
    With the command button selected, activate the Event tab of the Properties window.
    Click in the On Click event, then select Event Procedure from the dropdown list.
    Click the builder button (the three dots ... to the right of the dropdown arrow.)
    You will be taken to the Visual Basic Editor, with the first and last line of the On Click event procedure already created for you. Copy or type the code below to make it look like this:

    Private Sub cmdReport_Click()
    Dim strFilter As String
    Dim i As Long

    On Error GoTo ErrHandler

    ' Get out if nothing selected
    If Me.lbxCategories.ItemsSelected.Count = 0 Then
    MsgBox "You haven't selected anything!", vbInformation
    Exit Sub
    End If

    ' Initialize (not really necessary)
    strFilter = ""

    ' Loop through list box items
    For i = 0 To Me.lbxCategories.ListCount - 1
    If Me.lbxCategories.Selected(i) = True Then
    strFilter = strFilter & ", " & _
    Chr(34) & Me.lbxCategories.ItemData(i) & Chr(34)
    End If
    Next i

    ' Get rid of first ", "
    strFilter = "Category In (" & Mid(strFilter, 3) & ")"

    ' Open the report
    DoCmd.OpenReport "rptDelta", acViewPreview, , strFilter
    Exit Sub

    ErrHandler:
    ' Ignore error 2501 (report canceled)
    If Not (Err = 2501) Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    Now you can switch back to Access and close/save the form.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 97 (Form/report)

    I was able to use the code you provided using a multiselect list box to print a report filtered on the selected items. I tried to modify this code to open a query using the same multiselect list box but can not figure out how to identify the criteria as coming from the strFilter variable. I tried placing Forms!frmMultiSelect!strFilter in the criteria grid for the query. It didn't give me an error message but neither did it show me any records.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: 97 (Form/report)

    You can't specify criteria for a query in this way. An alternative would be to construct the complete SQL for a query in code, and do something with that. It all depends on what you want to accomplish, so if you could provide some details, we might be able to come up with something.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 97 (Form/report)

    I was just experimenting in order to learn more about using a multi select list box. I thought since it worked so well with passing the filter to the report, I could make it work for a query as well. It isn't something I need right now but I try to learn by trying out code that is posted and then experimenting with it for something that might come in handy in the future.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  8. #8
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 97 (Form/report)

    Hi Hans
    When i go to open the form Gamma i get a message saying "Syntax error in FROM clause" Any ideas ??
    Cheers,
    Rob.

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

    Re: 97 (Form/report)

    The problem is that the where-condition you pass to a (form or) report is a string. The criteria line in a query is an expression, which, of course, becomes a string in the SQL of the query, but is not "just" a string itself.

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

    Re: 97 (Form/report)

    Is DELTA really the name of your table? If not, substitute the correct name in SELECT Category FROM DELTA in the Row Source of the list box.

    Try the following:
    - Open the form in design view.
    - Select the list box.
    - Activate the Data tab of the Properties window.
    - Copy the text in the Row Source property to the clipboard.
    - Activate the Queries tab of the database window.
    - Create a new query in design view. Cancel the Show Table dialog.
    - Use View | SQL to switch to SQL view.
    - Paste the text you copied earlier (Ctrl+V)
    - Try to switch to design view, then to datasheet view.
    - Do you get error messages? If so, please paste the text you copied into a reply.

  11. #11
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 97 (Form/report)

    Hi Hans,
    The real name of my table is A340-600XX. This is what i typed into the SQL view "SELECT DISTINCT Category FROM A340-600XX" and i got the error message "syntax error from clause" when i tried to switch to design and database view.
    Cheers,
    Rob.

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

    Re: 97 (Form/report)

    Your table name contains a hyphen. Since this is also the subtraction operator, you must enclose the name in square brackets [ ] to let SQL know that it is part of the name. (For this reason I avoid using hyphens, spaces, etc. in object names; underscores _ are OK.) Try

    SELECT DISTINCT Category FROM [A340-600XX]

  13. #13
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 97 (Form/report)

    Hi Hans,
    I made that modification and i no longer get the syntax error message when i start the form. Once ive selected my categories on the form and click "open" the report doesnt appear instead i get a message saying "compile error method or data member not found" Once ive clicked ok the code behind it looks like this...
    Attached Images Attached Images

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

    Re: 97 (Form/report)

    Open your form in design view.
    Click on the list box.
    What is the Name property of the list box? This property is the first one in the Other tab of the properties window.

  15. #15
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 97 (Form/report)

    The name of the list box is lbxCategories
    Attached Images Attached Images

Page 1 of 2 12 LastLast

Posting Permissions

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