Solved

Problem with subform using query as it's record source

Posted on 2014-02-12
14
461 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now