• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

Problem with subform using query as it's record source

I'll do my best to describe this issue I'm having.

I have a form and on that form is a subform.  The subform opens in datasheet view mode because we need to see all the records displayed AND be able to enter data in one of the fields.  The main form uses tblShipments as its record source.  The subform uses a query as its record source.  The query used by the subform is actually a combination of 3 tables joined.  Two of them are used to display information and the 3rd one is used to be able to enter a number in one of the fields.

All works fine for the 1st new record entered.  But when the form is opened again , or when we click an New Shipment button, we see the original record instead of a new record.  If we change the values in the one field that is unlocked on the subform all we are doing is overwriting the values from the first time.  I can not make it accept new data entry.

I've attached a stripped down version of the d/b.

If someone can figure this out I'd definitely appreciate it.  Just open the form frmShipment, select a customer in the main form, and make a numeric entry in the subform under Taken Qty and then attempt to make a new record.
Test.accdb
0
SteveL13
Asked:
SteveL13
  • 8
  • 6
1 Solution
 
GozrehCommented:
You need to link the subform to the main form with ShipmentID
0
 
GozrehCommented:
But what didn't understand:
do you want only to fill in the "Taken Qty" ? or you need to enter the tblReady Info or the tblCatalog Info ?
because you dont have no relation between the tblready and the shippment, or maybe you want to see all records from tblready and only enter the Taken Qty.

I made some changes in your query1, so my sample you need to enter the taken qty and then select from shippmentredy drop down.
but do'nt know if this is what you wanted.
Test.accdb
0
 
SteveL13Author Commented:
Yes, I want to see all records from tblready and only enter the Taken Qty.  That is the only field I want to enter anything into in the subform.

But when I linked the subform to the main form with ShipmentID no records appear in the subform.

??
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
GozrehCommented:
so all records from tblready will be used for all your customers till the qty is 0 ?

if you will remove the link, you will have all shipment_details by every shipment, so this will not be possible !

I thing the only way will be to make a union query, one should query list all tblready records if this customer didnt taken, second if he did take.
0
 
SteveL13Author Commented:
Yes, all records from tblready will be used for all your customers till the qty is 0.

I don't know how to make a union query so I'm lost.
0
 
GozrehCommented:
Ok
I got it to work without a union query.
i changed the query1 to
SELECT MyShipmentDetail.ShipmentID, MyShipmentDetail.ShipmentDetailID, tblCatalog.Category, tblCatalog.Units, tblCatalog.Description, tblReady.Location, MyShipmentDetail.ShipQty, tblReady.ReadyID, MyShipmentDetail.ShipmentReadyID, tblReady.InventoryQty, [tblReady].[AvailableQty]-Nz([tblShipmentDetails].[ShipQty],0) AS TrueAvailQty
FROM (tblReady LEFT JOIN tblCatalog ON tblReady.CatalogID = tblCatalog.CatalogID) LEFT JOIN (SELECT tblShipmentDetails.* FROM tblShipmentDetails WHERE (((tblShipmentDetails.ShipmentID)=[Forms]![frmShipment]![txtShipmentID])))  AS MyShipmentDetail ON tblReady.ReadyID = MyShipmentDetail.ShipmentReadyID;

Open in new window

so now you can remove the link, but need to requery the subform on current.

so now you need only to enter the takenqty
Test.accdb
0
 
SteveL13Author Commented:
I think you are a genius!  Thank you so much.  Now I just have to figure out how to make the next shipment only show the original qty minus all taken quantities for that record.
0
 
GozrehCommented:
is this the same question ? or a new one
Will try to give a look on it .
0
 
GozrehCommented:
this should be a new question !
so the answer will be: i changed the Query1 to
SELECT MyShipmentDetail.ShipmentID, MyShipmentDetail.ShipmentDetailID, tblCatalog.Category, tblCatalog.Units, tblCatalog.Description, tblReady.Location, MyShipmentDetail.ShipQty, tblReady.ReadyID, MyShipmentDetail.ShipmentReadyID, tblReady.InventoryQty, [tblReady].[AvailableQty]-Nz([tblShipmentDetails].[ShipQty],0) AS TrueAvailQty, [tblReady].[AvailableQty]-Nz(DSum("ShipQty","tblShipmentDetails","ShipmentReadyID=" & [tblReady]![ReadyID]),0) AS TotalAvailQty
FROM (tblReady LEFT JOIN tblCatalog ON tblReady.CatalogID = tblCatalog.CatalogID) LEFT JOIN (SELECT tblShipmentDetails.* FROM tblShipmentDetails WHERE (((tblShipmentDetails.ShipmentID)=[Forms]![frmShipment]![txtShipmentID])))  AS MyShipmentDetail ON tblReady.ReadyID = MyShipmentDetail.ShipmentReadyID
WHERE ((([tblReady].[AvailableQty]-Nz(DSum("ShipQty","tblShipmentDetails","ShipmentReadyID=" & [tblReady]![ReadyID]),0))<>0)) OR (((MyShipmentDetail.ShipmentID)=[Forms]![frmShipment]![txtShipmentID]));

Open in new window

0
 
SteveL13Author Commented:
I will make a new question out of it and award this one. Ok?
0
 
GozrehCommented:
I added to your Query1 a sum of all ShipQty and filtered by not 0, and also should be filtered with the shipmentID so that you will be able to see the previous shipments.
0
 
SteveL13Author Commented:
I am going to post another topic.  I am not sure it is working the way I need it to but I may not understand again.  I'll attached Test3 which I think is the latest from you.
0
 
SteveL13Author Commented:
The new topic is titled, "How make field update in a form"
0
 
GozrehCommented:
Ok, will see if i can help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now