How to set a Textbox in the Footer of Subform to look up text values in a column and choose outcome based on Criteria. (1 Viewer)

Local time
Today, 13:10
Joined
Mar 18, 2020
Messages
34
First time posting here. Hope I've got this in the right place.

My experience with Access is limited, however, with alot of "googling" I can usually get things figured out. I have used the Northwinds template db to create a database here at work that ties together Projects, Opportunities, Clients and Procurement. So far I've got everything working ok, but I'm stuck on one of my forms. I have a "Receiving" form that pulls data from my "Purchase Orders" table. On this form is a Subform ("PORecSubfrm") that pulls data from the PO Details table (line items for the purchase orders).

I am trying to get the status of the POs to update when items are received to the system, but am having no luck. Here's my set up......

Mainform: Receiving (Table: Purchase Orders)
Subform: PORecSubfrm (Table: PO Details Field: [Line Status])
Subform Control: [LineStatus]
Textbox in Footer of Subform: Text35

I am placing this statement in the Control Source of [Text35}. I have tried IIF statements and DLookup and even tried nesting them to get the result I want. What I would like to happen is that when a PO is called for on the main form and the corresponding line items show in the subform, I would like the textbox to be able to determine the overall status of the PO. (Once the Line Items in the subform are updated with the received numbers, the LineStatus control determines if the items are fully received or not. This works great.) So, "If" all the [Line Status] fields say "Received", then [text35] is "Received".....this then passes onto the "Status" control on the mainform which is bound to the Purchase Orders table so that the updated status stays with the PO.

But, here's the problem......"If" the [Line Status] fields say a combination of "Received", "Partial" and/or "Purchased", then the PO status is "Partial". After that it's basically "If the fields match, then put whatever it says".....ie: Purchased, Received.

Not sure how to approach this. I've tried this just to see if I could at least get it to put "Received" in there but all i get is "?Name".....

=IIf(DLookUp([Line Status],[PO Details],"Received = '" & [Forms]![PORecSubfrm].[Form]![LineStatus]),"Received","Received")

I haven't used DLookup in Access before, but have had success in Excel.....Frustrated and looking for a better way.

Please be gentle, I'm a noob.
Tammy :oops:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:10
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

If I understand it correctly, I might try using a Totals query to determine how many different rows in the subform. If more than one, then I would use Partial; otherwise, use whatever showed up in the Totals query.
 
Local time
Today, 13:10
Joined
Mar 18, 2020
Messages
34
Thanks for the reply! I'll see how skilled I am at setting this up. Will post a reply if I either figure it out or get more frustrated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:10
Joined
Oct 29, 2018
Messages
21,358
Thanks for the reply! I'll see how skilled I am at setting this up. Will post a reply if I either figure it out or get more frustrated.
Hi. It might help if you could post a sample db, so we can show how to do it.
 
Local time
Today, 13:10
Joined
Mar 18, 2020
Messages
34
The database is full of sensitive information (I've imported all our data and am using it to keep track of projects at the moment)....wouldn't know how to clear it all out to send to be honest.

I've been mucking around with your suggestion though. So I've created a Totals Query and have it totaling the count of my Line Status (it would only let me do "count" here as this is text field) with the condition "Received". I've also got the query totaling the Purchase Order ID field just to have a record count. So now the POs that are "Received" and the "record count" match in number of records. Right?

Query.png
Subform.png


I've placed a field in the footer of my subform called "MyCount" and set the Control Source to =StatusQuery (my totals query). Next I've gone back to the Text35 control that I had already set up on the footer and tried: =IIf([MyCount]=[Line Status],[LineStatus],"Partial"). I get #Type! as a result. I also tried just using "Count(*)" in the MyCount control with the same iif statement.....I get "Partial" as a result, but for all the records so that doesn't work.

Am I getting close??
 

vhung

Member
Local time
Today, 12:10
Joined
Jul 8, 2020
Messages
235
I've placed a field in the footer of my subform called "MyCount" and set the Control Source to =StatusQuery (my totals query). Next I've gone back to the Text35 control that I had already set up on the footer and tried: =IIf([MyCount]=[Line Status],[LineStatus],"Partial"). I get #Type! as a result. I also tried just using "Count(*)" in the MyCount control with the same iif statement.....I get "Partial" as a result, but for all the records so that doesn't work.

Am I getting close??
quite tough
>your field [Line Status] should have a partner fieldname ="CountRecieved" then
if [Line Status] = "Received" then [CountRecieved] =1
>with that dataflow
[Text35] = Sum([CountRecived])
>try that simple way, i used it with my complex Forms
 
Local time
Today, 13:10
Joined
Mar 18, 2020
Messages
34
quite tough
>your field [Line Status] should have a partner fieldname ="CountRecieved" then
if [Line Status] = "Received" then [CountRecieved] =1
>with that dataflow
[Text35] = Sum([CountRecived])
>try that simple way, i used it with my complex Forms

Thanks for the input! I haven't had time to work on it today (busy), but will take a look at this approach as soon as I can. I'll let you know how it works out.
 

vhung

Member
Local time
Today, 12:10
Joined
Jul 8, 2020
Messages
235
Thanks for the input! I haven't had time to work on it today (busy), but will take a look at this approach as soon as I can. I'll let you know how it works out.
welcome tammy
>express out your vision for sure you shall meet the best result with your own way
>thanks for the like response
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:10
Joined
May 7, 2009
Messages
19,169
this is an alternative solution.
add code to the "Main Form's" Current Event:
Code:
Private Sub Form_Current()

    Call fncShowStatus
    
End Sub



Public Sub fncShowStatus()

    Dim intNumberOfItems As Integer
    Dim intNumberOfPartial As Integer
    Dim intNumberOfReceived As Integer
    Dim intNumberOfPurchased As Integer
    Dim strStatus As String
    Dim lngPO As Long
    
    lngPO = Nz(Me![Purchase Order ID], 0)
    intNumberOfItems = DCount("1", "theQueryName", "[Purchase Order ID]=" & lngPO)
    intNumberOfPartial = DCount("1", "theQueryName", "[Purchase Order ID]=" & lngPO & " And [Line Status]='Partial'")
    intNumberOfPurchased = DCount("1", "theQueryName", "[Purchase Order ID]=" & lngPO & " And [Line Status]='Purchased'")
    intNumberOfReceived = DCount("1", "theQueryName", "[Purchase Order ID]=" & lngPO & " And [Line Status]='Received'")
    
    Select Case True
        Case (lngPO > 0 And intNumberOfPartial > 0)
            strStatus = "Partial"
        Case (lngPO > 0 And intNumberOfPurchased > 0)
            strStatus = "Purchased"
        Case (lngPO > 0 And intNumberOfReceived = intNumberOfItems)
            strStatus = "Received"
    End Select
    Me.PORecSubfrm!Text35 = strStatus
    
End Sub

now, on the PORecSubfrm, add Code to it's AfterUpdate event to refresh the Status:
Code:
Private Sub Form_AfterUpdate()
    Forms("theNameOfMainFormHere").fncShowStatus
End Sub
 
Local time
Today, 13:10
Joined
Mar 18, 2020
Messages
34
this is an alternative solution.
add code to the "Main Form's" Current Event:
Code:
Private Sub Form_Current()

    Call fncShowStatus
   
End Sub



Public Sub fncShowStatus()

    Dim intNumberOfItems As Integer
    Dim intNumberOfPartial As Integer
    Dim intNumberOfReceived As Integer
    Dim intNumberOfPurchased As Integer
    Dim strStatus As String
    Dim lngPO As Long
   
    lngPO = Nz(Me![Purchase Order ID], 0)
    intNumberOfItems = DCount("1", "theQueryName", "[Purchase Order ID]=" & lngPO)
    intNumberOfPartial = DCount("1", "theQueryName", "[Purchase Order ID]=" & lngPO & " And [Line Status]='Partial'")
    intNumberOfPurchased = DCount("1", "theQueryName", "[Purchase Order ID]=" & lngPO & " And [Line Status]='Purchased'")
    intNumberOfReceived = DCount("1", "theQueryName", "[Purchase Order ID]=" & lngPO & " And [Line Status]='Received'")
   
    Select Case True
        Case (lngPO > 0 And intNumberOfPartial > 0)
            strStatus = "Partial"
        Case (lngPO > 0 And intNumberOfPurchased > 0)
            strStatus = "Purchased"
        Case (lngPO > 0 And intNumberOfReceived = intNumberOfItems)
            strStatus = "Received"
    End Select
    Me.PORecSubfrm!Text35 = strStatus
   
End Sub

now, on the PORecSubfrm, add Code to it's AfterUpdate event to refresh the Status:
Code:
Private Sub Form_AfterUpdate()
    Forms("theNameOfMainFormHere").fncShowStatus
End Sub
This is awesome! I've been busy today, but will take a look at it tomorrow as soon as I get in. Thank you so much for the suggestion!

I'm meeting with the managers next week to show them the database so would be nice to have this little bit of "eye candy" working!

I'll check back when I've tried it.

Tammy
 
Local time
Today, 13:10
Joined
Mar 18, 2020
Messages
34
This is awesome! I've been busy today, but will take a look at it tomorrow as soon as I get in. Thank you so much for the suggestion!

I'm meeting with the managers next week to show them the database so would be nice to have this little bit of "eye candy" working!

I'll check back when I've tried it.

Tammy


UPDATE! It worked!!!! Thank you arnelgp!! I had to change one little bit where you referenced
lngPO = Nz(Me![Purchase Order ID], 0)

Had to point this to the subform where the Purchase Order ID field is.

I'm tickled pink!! Thank you so much!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:10
Joined
May 7, 2009
Messages
19,169

Users who are viewing this thread

Top Bottom