Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: Combo Box - Code to move down a selection

    Hey guys,

    I have a form with 3 variables on it. Val_Trainer, Val_TrainingCourse and Val_Category. All 3 of these fields are combo boxes and the idea is that when the report button is pressed the report prints of a report relating to the selection...Easy.

    The problem is my manager would like me to print a report for each trainer individually, which means constantly going back and forth and changing the trainer then printing and again...

    What I want is to create a code which will do this automatically i.e. Select top value in list, open report, print, close report, take 2nd value in list, open report and so on.

    I have the coding to open the report, print and close but I'm having difficulty getting a code that would move the selection in the combo list down one.

    Can anyone help or give a better suggestion?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Iterate thru the table/query or Value List the combobox is based on ... You might have to mod the report (if it is hardcoded to the comboboxes) to accept parameters differently ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Hi,

    I'm not too worried about how the actual report runs as they are based on global strings.

    The report button takes the value in the list and converts the global string to match, this in turn is the criteria for the report.

    I intend to set a loop to say print report, then move the value down the list one and report again.

    This will loop until it reaches the last value.

    The only reason I can't get this to work is I'm used to working on list boxes and the .selection value isn't valid for combos. As such I don't know how to tell VB or access to select record 1 or record 2 etc

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    clone the recordset for the combo box
    then as M Owen suggests cycle through each record in the recordset. In practise you don't even need to do that, just go though the rowes of the combobox directly. each and every row and column is addressable programatically

    alternatively why don't you just modify the report to run all trainers with valid data, do a form feed on change of trainer, single report, quick and easy to set up....... requires no further interbentionof the user

    KISS is a remarkably effective doctrine when designing systems y'know

  5. #5
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    On the report set up a report grouping on a trainer with a group header and footer. In the Trainer group footer force it create a new page after it has printed (it is one of the proeprties in the group property sheet).

    You can keep your form as it is to allow an individual trainer to be selected but have a check box (or a union query on the trainer combo box to add a 'All Trainers' option) to allow you to print all the trainers.
    Justin

  6. #6
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Many thanks for all your help guys but I have now solved this problem myself;

    Dim MyDimListTrainerCount
    Dim MyDimListTrainerIndex

    Private Sub Cmd_PrintAll_Click()

    MyDimListTrainerCount = Me.Val_Trainer.ListCount

    MyDimListTrainerIndex = 0

    Do Until MyDimListTrainerIndex = MyDimListTrainerCount

    MyGloTrainer = Me.Val_Trainer.ItemData(MyDimListTrainerIndex)
    Me.Val_Trainer = MyGloTrainer

    DoCmd.OpenReport "Rpt_FullReport", acNormal, "", "", acDialog

    DoCmd.Close acReport, "Rpt_FullReport"

    MyDimListTrainerIndex = MyDimListTrainerIndex + 1

    Loop

    End Sub

Posting Permissions

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