Tammysdatabase
Member
- Local time
- Today, 04:16
- 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
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
