Run If all records selected have same value in a field... Problem?

[Redbeard]

I am running Access 2007.
I have a button on a pop-up box, when clicked it runs a report on selected records based on whether a check box is selected. If no records are selected then it only prints a report for the current record you are on. It selects the report to print based on what is in a combo box on my main form. However if you select three records that have a different value in that combo box, it will print them all based on the first records value. So I get one report that is right and two that are wrong. I would like to have it not run if the records selected have different values in the combo box and give me a message “Can Not Print. Check to make sure you are running the same report!” I not sure how to do this? See code below

Private Sub Command10_Click()
 
 
'Declare stuff we need
    Dim DB As Database
    Dim Ptogset As Recordset
 
 
    'Setting the stuff
    Set DB = CurrentDb()
    Set Ptogset = DB.OpenRecordset("SELECT Search FROM Description")
 
 
    'Set the searcher counter
    Dim Searcher As Integer
        Searcher = 0
 
    'Search trought the recordset for any records that fits the condition
    Do Until Ptogset.EOF
        Searcher = Ptogset.Fields("Search").Value + Searcher
        Ptogset.MoveNext
    Loop
 
    'If there's no records with the match criteria, build simple condition
    Dim Condition As String
 
    If Searcher = 0 Then
        Condition = "[Accession Number]=Forms!frmDescription!txtAccNum"
    Else
        Condition = "[Search]=true"
    End If
 
   Dim printLocate As String
    printLocate = Forms!frmDescription!Combo6586
 
        Select Case printLocate
        Case "Photo File 4x5": DoCmd.OpenReport "rpt4x5", acViewPreview, , Condition
        Case "Photo File 5x7": DoCmd.OpenReport "rpt5X7", acViewPreview, , Condition
        Case "Photo File 8x10": DoCmd.OpenReport "rpt8x10", acViewPreview, , Condition
        Case "Photo File 9x12": DoCmd.OpenReport "rpt9x12", acViewPreview, , Condition
        Case "Photo File 9x14": DoCmd.OpenReport "rpt9x14", acViewPreview, , Condition
        Case "Photo File 11x15": DoCmd.OpenReport "rpt11x15", acViewPreview, , Condition
        Case "Archive File 4x5": DoCmd.OpenReport "rptA4x5", acViewPreview, , Condition
        Case "Archive File 5x7": DoCmd.OpenReport "rptA5X7", acViewPreview, , Condition
        Case "Archive File 8x10": DoCmd.OpenReport "rptA8x10", acViewPreview, , Condition
        Case "Archive File 9x12": DoCmd.OpenReport "rptA9x12", acViewPreview, , Condition
        Case "Archive File 9x14": DoCmd.OpenReport "rptA9x14", acViewPreview, , Condition
        Case "Archive File 11x15": DoCmd.OpenReport "rptA11x15", acViewPreview, , Condition
        Case "Stereograph Card File": DoCmd.OpenReport "rpt5X7", acViewPreview, , Condition
 
        Case Else:
            MsgBox "Cannot produce report for this artifact. Look in the permanent location."
 
 
    End Select
 
DoCmd.Close acForm, "frmPrintControlBox"
 
End Sub

Any help would be great!
Thanks

[NeoPa]

Redbeard:
It selects the report to print based on what is in a combo box on my main form. However if you select three records that have a different value in that combo box, it will print them all based on the first records value.

WTF does that mean? However many times I read that I cannot understand how records have values in the combobox. The only combobox referred to so far selects a report to run. What are you trying to say here?

[Redbeard]

I am sorry that I have not explained my problem well. It is hard sometime to explain what you are doing when you are so involved in it, I will try to clarify.
The database is set up to track photographs… so each record is of one photograph. The combo box has values from my main table which are the locations that the photos are stored it. So there are 13 different locations that the photos can be stored based on size. The reports are actually set to the size of envelops that the photos will be stored in. So when I click the button it looks at the combo box that has the location of the photo and selects the report to run based on that. For example if I select a record with the location of “Photo File 4x5” it prints on an envelope that holds a 4x5 photo (prints the name of photo and thumbnail photo on envelop). I have set the reports up to run based on the storage location. The problem is when I print the envelops, I do them 100 at a time. So if I select 100 records and 90 of the have the 4x5 location and 10 of them have a 5x7 location it will run whatever report size based on the first records location. So I would either get 100 4x5 envelops printed, 10 of which should have been on 5x7 envelops. Or 100 5x7 envelopes, which 90 of should have been on 4x5’s. I basically need something to stop me from printing if the location field does not match on all record selected for print. I am basically trying to idiot proof the operation.
I hope I have been clearer with this explanation. I think before it sounded like the combo box I was using was not connected to my records and had a list of values that were just linked to the report names. So I you selected “Photo File 4x5” it just ran that report for the records selected. I had not considered doing it that way, but I guess I could if the method about is too complex.

[NeoPa]

Sorry for the delay in posting. I must admit I was putting off trying to determine what you were saying as it looked indecipherable. It makes some sort of sense though (reading through it carefully) and I think I understand what you're after. The solution will be hard to explain though, as the code is as it stands is somewhat unorthodox and doesn't lend itself well to anything very flexible (which we will need to incorporate the solution I'm about to propose).

The basic concept is that the code determine the report that has been selected (It will be required in a string variable so the Select ... Case construct will need to be changed to handle this), it then gets the .RecordSource property of this report and opens a recordset using this data (See Subqueries in SQL for explanation of this idea.) - not forgetting to apply the filter already determined.

Assuming your .RecordSource value is "SELECT * FROM [Description]" (QueryDef names work just as well. This simply illustrates it can even handle a SQL string.) and your Condition variable is set to "[Accession Number]=31" we would need something like the following as a recordset to be opened and checked :

SELECT *
FROM (SELECT * FROM [Description])
WHERE ([Accession Number]=31)

You already have recordset processing in your code so you don't need me to tell you (as I have no information as to what you are checking in the dataset anyway) how to check through these records to ensure all match the requirements you have that we still don't know much about. Clearly, this code will enable you to throw an exception message or not depending on whether or not it finds an exception in the recordset.


All that said, a more sensible approach (fundamentally easier and probably a more logical place for the checking to be anyway) would be to add some (very similar) code to each of the reporst such that they test their own data as (or before) they run and either skip any offending records or throw an error message instead and abort.

These are my ideas on the issue. Let me know if you're still interested and/or whether you need any of them padded out somewhat.

[Redbeard]

Thanks for the response Neopa. I abandon my idea awhile ago after not being able to figure it out. However I did figure out another way to do it. I created a pop-up with a combo box that has a drop down with all the envelop sizes that I can print. When I select the size I want, it runs a query that looks for the ones I have selected and the size I picked. If there are none I get a pop up message that tells me there are no records selected. If there are some it will print them and if I have selected other of a different size it will not print those... which was the point of this to start with. So I got it to work just not the way I wanted it. Again, thanks for the help, I always appreciate it.

[Rabbit]

What you needed to do was to build in the size criteria into the report's record source. Then, you can do a count using each size and open up the report if there is at least one record.

[NeoPa]

It sounds like your solution is a pretty appropriate one in the end anyway, and every solution you find adds to your experience. Well done.