VBA with Access Reports

[MakinBacon91]

Hello All,

I have a report that is grouped by JobID,
I would like to add some VBA that will format a text box in each group section.

I want a text box that will dynamically display a "Priority Date"
This Priority date will be different for each job or nonexistent.
I want to check this JobDate table, and then set the "Priority Date" to the available date, or leave it blank.

JobID	SendS&I	CompletePre-Coat	ShipDate
DUK-1501	11/30/2015
DUK-1502		12/11/2015
DUK-1503			12/11/2015

<caption> JobDate </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

I am fairly good with vba, but I do not know how to perform this check for each item in the group (each JobID).

Here is a little bit of the code I am working on:

    Dim str_jobID As String, str_SQL As String, rs As Recordset, str_return As String
    
    str_jobID = Me.GroupLevel(1).ControlSource
    
    str_SQL = "SELECT JobDate.JobID, JobDate.[SendS&I], JobDate.[CompletePre-Coat], JobDate.ShipDate FROM JobDate WHERE (((JobDate.JobID)=" & str_jobID & "));"
    Set rs = CurrentDb.OpenRecordset(str_SQL)
    
    If rs("SendS&I") > "" Then
        str_return = "SendS&I"
    ElseIf rs("CompletePre-Coat") > "" Then
        str_return = "CompletePre-Coat"
    ElseIf rs("ShipDate") > "" Then
        str_return = "ShipDate"
    Else
        str_return = " "
    End If
    
    txt_priorityDate.ControlSource = str_return
    txt_jobID.ControlSource = str_jobID

I just don't know where to put my code so that it will run every time a new item is formed in the group.


Let me know if I can clarify anything.

Thanks!
-Bacon-

 

[BiocideJ]

Without seeing the report items I'm not entirely sure what is coming from or going to where, however, generically speaking I would create a VBA function that takes the JobID as input and give the priorityDate as an output.

e.g.

Public Function GetPriorityDate(strJobID as string) as String
...
your code to determine str_return PriorityDate based on strJobID
...
GetPriorityDate = str_return
End Function

I believe you can then set the Control Source of txt_priorityDate to
=GetPriorityDate([txt_jobID])
and it will run it automatically by group.

 

[MakinBacon91]

Without seeing the report items I'm not entirely sure what is coming from or going to where

Let me try and explain a little better.
Here is an image for the report I am working on: Pasteboard — Uploaded Image

Here are some sample tables
Job Info:

JobID	UnitID	SetNo	Status
1	DUK	1501	Open
2	LSP	1502	Open
3	DUK	1502	Open
4	DUK	1503	Open
5	DUK	1504	Open
6	DUK	1505	Open
7	DUK	1506	Open

<tbody>
</tbody>

JobDate:

JobID	SendS&I	CompletePre-Coat	ShipDate
DUK-1501	11/30/2015
DUK-1502		12/11/2015
DUK-1503			12/11/2015

<tbody>
</tbody>

So basically what I am trying to do is write some VBA code to fill in the "Priority Date" field with either the S&I Date, Pre-Coat Date, Ship Date, or to completely remove the box and label if there is no Date set.

 

[Micron]

Options that come to mind:
1) add the priority field to your report query and use the sql statement you have in code as a sub query and bind the report control to it.
2) put your code in one of the report section events (e.g. group section OnPrint event, or Format event). The report does not have to be printed to fire the OnPrint event. Note also that some of these events don't provide the desired outcome in report view, but work in print preview.
3) try placing your code in a standard module and call it from the unbound priority control (what I believe BiocideJ suggested).
However, if you mean you actually want to remove the control if there's no date, I don't believe you can do that at a group level. I think you'd remove it for all. I think you can hide it for a group though I don't recall ever trying.