Listing table entries in one field on a report 1

[jdgreen]

I have two tables that have a one to many relationship based on the WorkOrder fields. The main table records information on the WorkOrder and the part number table only collects part number information in the fields named "Prefix", "Body" and "Suffix". There can be multiple part numbers for each WorkOrder. I have a report that filters out only the open work orders. I am trying to take the part numbers that a apply to a specific WorkOrder and concatenate them into one text field for the report. The problem I am having is with "linking" the information on the report. I can't find any way to pull the value of the WO like you would on a form.

Private Sub Report_Open(Cancel As Integer)
Dim intWO As Integer
Dim strPN As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

intWO = Me.AlertNumber
Set db = CurrentDb
Set rs = db.OpenRecordset("qryAlertLogFinal", dbOpenDynaset)
rs.MoveFirst
strPN = ""
Do While rs!WorkOrder = intWO
If strPN = "" Then
strPN = rs!Prefix & "-" & rs!Body & "-" & rs!Suffix
Else
strPN = strPN & ", " & rs!Prefix & "-" & rs!Body & "-" & rs!Suffix
End If
rs.MoveNext
Loop
Me.PartNumbers = strPN

End Sub



John Green

 

[PaulBricker]

Well, the Open event for a Report isn't going to do what you need. You might try the Format Event for the Detail section, if that's where your info is, but I don't think the code is going to do what you want. My suggestion would be to use a sub-report and link your main report to the sub-report using the WorkOrder field (if that's the primary key field).
If you still want to use your method, try your code in the Format event and see how it runs. If you have problems, post back.

Paul

 

[jdgreen]

I figured as much. The main problem is reports don't seem to allow you to pull a value from one of the controls. I already did this with a subform, but because of the quantity of part numbers that can be with each work order it isn't the best looking solution.

John Green

 

[PaulBricker]

You can get the value from a control if you are in the right Event at the right time. For example if you are in the Format event for the Detail Section and the first record is formatted, you can pick up the value for any of the fields in that record. But a report doesn't operate exactly how you would expect which tends to make it harder to accomplish these types of things.
What you might try and do is send the info from your query to a Function and gather the info that way and then use the query as the Record Source for your Report. The function would look like this

Function PullTogether (myVal as Integer)
    Dim strPN As String
    Dim strSQL as String
    Dim rs As DAO.Recordset
    
    strSQL = "Select * From qryAlertLogFinal Where qryAlertLogFinal.WorkOrder = " & myVal
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    strPN = ""
    rs.MoveFirst
      If rs.EOF or rs.BOF Then
          Exit Function
      Else
      If strPN = "" Then
            strPN = rs!Prefix & "-" & rs!Body & "-" & rs!Suffix
        Else
            strPN = strPN & ", " & rs!Prefix & "-" & rs!Body & "-" & rs!Suffix
        End If
       End IF
    rs.MoveNext

    PullTogether = strPN
           
End Sub

Then if the query you would add a field

MyConcat:PullTogether(WorkOrder)

Look it over and post back with issues.

Paul

 

[dhookom]

There is a generic concatenate function with sample usage at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]