Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006
    Posts
    48

    Unanswered: Multi-select controls

    Good day,

    I am trying to create an adhoc reporting facility for a database that I'm currently working on. I want to create a form with many different options whereby the user can select from combo boxes (the user can select any number of choices - multi-selection e.g. the user can select customer name, customer type, city, designation...)and enter date periods (e.g. the user can enter different start and end dates during which purchases were made). This form will also contain three command buttons, one to preview a report based on the search criteria selected and entered from the form, one to reset the options and another to close the form. The thing is I know what I want to do but not sure how especially since many different tables are involved.

    Initially, I created a query to pull data from some of the various tables but when I added fields from other tables, some records were knocked out.

    Can anyone give me some advice and help me along the way.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Posts
    19
    Good day to you Essence,

    First, you will probably need to get the query working to create the report you want. Answers are easier to come by if you can be more specific as to the problems as to what you are missing and how things are set up.

    As to the check box issue....I have done something like that. I'm sure there is a slicker way to do this, but this is what I have. It is in Visual Basic code as part of my command button.

    It is a little long, but I hope this helps. Sorry, I don't have codes for selecting dates.

    Steve
    ______________________________

    'You can open the report and control it using

    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria, Window = fit

    'The term we are interested in here is "stLinkCriteria"...it can control the query that your report is based on. It is SQL based.

    ' I call a routine to define the stLinkCriteria SQL depending on checkbox values.

    'The SQL I get looks something like this...

    stLinkCriteria = (Overview.Status = 5 or Overview.Status = 3 or Overview.Status = 4)
    'Where "Overview" is the query, and "Status" is a column in the query.

    Code:
    ____
    stLinkCriteria = "("
    Temp2 = ""
    Temp3 = 0 ' used to see if criteria was inputted

    If Me.Complete = True Then 'Me.Complete is a check box on my form
    Temp = stLinkCriteria
    Temp2 = " or"
    stLinkCriteria = Temp & Report_Name & ".status = 5"
    'Gives (Overview.Status = 5
    Temp3 = 1
    End If

    If Me.On_Hold = True Then 'Me.On_Hold is a check box on my form
    Temp = stLinkCriteria
    stLinkCriteria = Temp & " " & Temp2 & " " & Report_Name & ".status = 3"
    Temp2 = " or"
    Temp3 = 1
    End If

    If Me.In_Progress = True Then 'Me.In_Program is a check box on my form
    Temp = stLinkCriteria
    stLinkCriteria = Temp & " " & Temp2 & " " & Report_Name & ".status = 4"
    Temp2 = " or"
    Temp3 = 1
    End If

    'Complete the SQL statment
    Temp = stLinkCriteria
    stLinkCriteria = Temp & ")"

    If Temp3 = 0 Then ' Reset the SQL to remove any filter
    stLinkCriteria = "(" & Report_Name & ".status = -1)"
    End If

  3. #3
    Join Date
    Jun 2006
    Posts
    48
    Hi there, It is a bit overwhelming but I would look at the code carefully and give you some feedback. Thanks much.

Posting Permissions

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