Solved

Problem with subform using query as it's record source

Posted on 2014-02-12
14
482 Views
Last Modified: 2014-02-12
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
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 10

Expert Comment

by:Gozreh
ID: 39853320
You need to link the subform to the main form with ShipmentID
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39853428
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
 

Author Comment

by:SteveL13
ID: 39853443
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 10

Expert Comment

by:Gozreh
ID: 39853476
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
 

Author Comment

by:SteveL13
ID: 39853481
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
 
LVL 10

Accepted Solution

by:
Gozreh earned 500 total points
ID: 39853542
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
 

Author Comment

by:SteveL13
ID: 39853633
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
 
LVL 10

Expert Comment

by:Gozreh
ID: 39853642
is this the same question ? or a new one
Will try to give a look on it .
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39853672
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
 

Author Comment

by:SteveL13
ID: 39853673
I will make a new question out of it and award this one. Ok?
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39853693
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
 

Author Comment

by:SteveL13
ID: 39853701
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
 

Author Comment

by:SteveL13
ID: 39853717
The new topic is titled, "How make field update in a form"
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39853719
Ok, will see if i can help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question