Full Version: Subform Value Defaults To Main Form's Value...
UtterAccess Forums > Microsoft® Access > Access Forms
crcool75
Hello,
I have a control source of [QtyShipped] in a subform that needs to default to [Quantity] in the main form. I've tried using =Forms!Shipping_F!Quantity in {QtyShipped]'s default value, but it changes to =[Forms]![Shipping_F]![Quantity]. Then it defaults every row in the subform's [QtyShipped] to the main form's first [Quantity] value only. There are actually different [Quantity]'s for each row and its corresponding [QtyShipped] subform value should default to each. These two forms run off of two separate queries. Any suggestions?
Thank you
cpetermann
crcool,
Could it be possible to zip & attach your db
so that we can see how these forms are designed?
crcool75
That might be a little difficult because of the complexity and user level security, but let me see what I can do.
crcool75
Ok, lets see if this works. Please see the attached file. It's the Shipping_F and Shipping_Details_F I'm working with to set defaults in the details form to values in the main form.
cpetermann
cr,
That happens because Access is correcting your syntax
this is where I get confused. Please explain why you want the Shipped Qty to default to the Ordered Qty?
Would you not want to display the original Order Qty and then in the subform enter the Qty Shipped on that date?
Try the attached on your Shipping_F Select the Record with OrderDetailID 101 and look at the subdatasheet.
Is this what you wanted to achieve?
crcool75
cpetermann,
The quantity may ship in different, smaller quantity sets depending on size and availability. Half of the time the same number of quantity ships in a single shipment. To save time I would like the QtyShipped to default to the same as Quantity since half the time this will be the case. The ship date will be similar but advance one day if the order is placed after 12:00.
I will look at your file tonight and let you know.
Thank you very much for your help!
crcool75
cpetermann,
looked at what you were talking about. That is an example of an item being shipped in two different shipments, one for 10 and the other for 20. This works fine.
Oneed the each subform row [QtyShipped] to default to it's parent row [Quantity]. When I use =[Forms]![Shipping_F]![Quantity] in the Default Value for [QtyShipped] it displays the first row's [Quantity] of "3" in all of the subform [QtyShipped] control sources. Each row should have a different [Quantity] so each subrow should have a different default value in the [QtyShippd] control source.
cpetermann
Haven't been able to think of an option for you.
put out a call to see if anyone with more know-how than me <
has any ideas.
However, if you are going to default to any value in QtyShipped,
it would seem to me that it would be better to default to the Qty that is remaining to be shipped.
Just my 2cents.gif
jimmy0305
Try this in the Default Value on each control in your main form.
!--c1-->
CODE
=Dlookup("FieldtoCopy","TableName","[IDField] = IDField")

hope this helps.
Jim
crcool75
Jimmy,
Thank you for your response(s). I saw that you edited it which I had already tried. This returns "1" only in each QtyShipped field as the default. I think I may know what the problem is. OrderDetailID is the link between the two tables. The OrderDetailID value doesn't exist in the QtyShipped table until data is entered. This is why DLookUp can't find matching values. Am I correct? Another words you can't match the two tables until data already exists.
jimmy0305
You are correct... OrderDetailID must be the same because this is your reference.
Good luck on your project! Cheers...
Jim
CyberCow
I'm curious why PO_T.ClientPO is not related to Invoice_T.ClientPO in your relationships. Because I do not know the business model, I'm unqualified to make the determination which way the relationship should engage. It just appears they should somehow be related based on the data model in your attachment. (The one Cynthia posted back.)
o my question is: "Does this data model fully satisfy the business model and normalization practices?"
Click to view attachment
datAdrenaline
My a brief look, I would suggest you use the BeforeInsert event of the form Shipping_Details_F ...
!--c1-->
CODE
Private Sub Form_BeforeInsert(Cancel As Integer)
    
    Dim frm As Form
    On Error Resume Next
    Set frm = Me.Parent
    On Error GoTo 0
    
    If Not frm Is Nothing Then
        Me.QtyShipped = frm.Quantity
    End If
    
End Sub

Now to make this work correctly, you should change the order of the columns (change the tab-order) in your sub-form as well so the user is likely typing into some other field than QtyShipped first. I changed the tab-order so ExpShipDate is the initial field/control. I have attached a db with my modifications. I used A2010 to modify the db -- so if incompatibilities arise, you will know why.
crcool75
Cybercow,
've often asked myself how I should set up the relationships. Relationships have always caused me issues. At first I wanted to keep PO's and Invoices completely separate so invoices could be made without PO's. However, the more I think about it this system doesn't have to be all things to everything. I should narrow down the overall function and only issue invoices on the PO's generated, eh?
What relationship structure would you recommend? Products -> PO's -> Invoices?
crcool75
Thank you, CyberCow... I will save these links.
atAdrenaline, unfortunately I'm unable to change the sequence of tabs in the form. So, to resolve this issue I'm going to leave the default blank for QtyShipped. It's not a big deal. However, after they enter a QtyShipped, a new record is formed allowing me to play with the default dates. I attempted to set the default ShipDate to the same as the OrderDate. The only thing I can get it to do is return a bogus time and not a date?
I have looked at my relationships and they PO_T -> PO_Details_T -> Shipping_T. There is no direct relationship between PO_T and Shipping_T, but Shipping_T needs to know what the OrderDate is from PO_T. I would like to use a SetValue macro after the user enters a QtyShipped value. I am not sure how to use DLookUp two tables up.
crcool75
Cybercow,
Back to your question regarding relationships between PO_T.ClientPO and Invoice_T.ClientPO... Yes, these two fields are related. The problem here is my initial design of allowing the PO's and invoices to be created separately of each other. I know these two fields are related, but I don't know how to relate them.
The business model, if I'm explaining this correctly, is straight forward and fairly similar to standard order processing:
1. We place PO (PO_T, PO_Details_T & Shipping_T). Shipping_T is used to manage shipment information for each individual product on PO_Details_T.
2. We issue Invoices off of PO_Details_T's individual products. So one PO has many invoices. (Invoice_T, Invoice_Details_T & Invoice_Serials_T). Invoice_Serials_T lists the individual serials for each product ordered.
The Products_T is used for PO's and Invoices along with other database features. The 'other database features 'is why I haven't related the Products_T to PO's and invoices yet. I only want them to read Products_T, not rely or update Products_T.
Does this better paint the business model picture?
crcool75
I managed to work the the shipping dates by adding the OrderDate to the form's query. Thanks for everybody's help!
How I still need help with my relationships, but that might need to be posted elsewhere? Anyway, I have attached a screenshot of my current database relationships.
CyberCow
crcool75,
OK, so here is the four part question . . . the answer is 'A', 'B', 'C' or 'D'
A) A Purchase Order can have many invoices (or none) {One-to-Many}
B) An Invoice can have many Purchase Orrders (or none) {Many-to-One}
C) A Purchase Order can only ever have one Invoice (or none) {One-to-ManyOne}
D) Many Purchase Orders can have many Invoices {Many-to-Many}
FOr would there any conditionals based on any of the above options? If so, what are those conditions?
crcool75
The answer is A, a PO can and most likely will have several Invoices.
CyberCow
OK, based on that, I suggest the following . . . .
!--coloro:#0000ff-->A) Modify your Invoice_T table to include a foreign key field relating to PO_T.OrderID . . . it should look like this:
Click to view attachment
B) Run the following SQL statement (create a new query in SQL mode and copy this into it and execute it - you will not need to save this query):
UPDATE Invoice_T SET Invoice_T.OrderID = DLookUp("[OrderID]","PO_T","[ClientPO]='" & [ClientPO] & "'");
C) Modify your relationships to to look like this:
Click to view attachment
You may have to modify the behavior of your form and subform to acquire the correct Master/Child connection. See the attached db that I've modified as described above. Click to view attachment
hope this helps
crcool75
Thank you, Cybercow... What do you mean modify the forms Master/Child connections? Are you referring to something like running a macro to update the OrderID control after the ClientPO is entered on the invoice form?
CyberCow
Just look at the form in design mode, then examine the properties of the sub-form - there, you'll see the Master/Child properties and how I've set them.
crcool75
I don't see anything different? I'm looking at how Invoice_Details_F relates to Invoice_F via master/child and also how PO_Details_F relates to PO_F via master/child.
CyberCow
I was referring to the PO_F form and it's PO_Related_Invoices_F subform - note how I've added the OrderID control to that subform.
You may want to add: =[Me].[Parent].[OrderID] as the property for the Default Value of the new OrderID control in that subform as well)
Remember that changing the relations in the middle of a project will generally require a good look and test around the forms and reports to ensure they remain working as they should; or to improve where they can now behave better because of the altered relationships.
Also, because there may be many invoices to one PO, only one "Related PO" will ever display in the Invoice_Related_PO_F subform of the Invoice_F form.
Look at this rendition to see what I mean . . . Click to view attachment
hope this helps
crcool75
Ok, I see. The Related PO was sort of an error checking device. Before if the user sees more than one related PO then they know something is wrong. Now it's not possible, eh?
The only thing I'm not sure how to handle is how a user will create a new Invoice. Creating it from a blank invoice will require the database to look up the ClientPO after they enter it, but what if they enter it wrong? Or I can only allow new invoices to be created within a PO form. I probably should have had all of this worked out before progressing this far.
CyberCow
Right, just not possible.
And exactly . . . that's those normalization, planning and tips links are so important . .. they can help get a newcomer better oriented towards good development.
The whole idea of jumping into a db without a plan just makes the continuing development process harder; as you are beginning to experience. But you're in the right place and the folks here are always happy to help.
Personally, I prefer to avoid incorrect user entries and use comboboxes that are populated on existing data to select a specific OrderID or PO.
If ya wanna lead ya gotta reading.gif
crcool75
I agree with you on every aspect, LOL. I had better planned the database then it wouldn't be so difficult. I orginally designed a database to maintain product pricing, end-of-life data and price history. I then decided since I have all of this already, why don't I just add PO and invoicing capabilities? Then PO and invoicing capabilites have driven me into needing to add even more capabilities in order for our ordering department to really benefit. So I've fallen into this complex maze of creating a customized order processing system. However, I do feel I'm working my way through it, thanks to you and everybdy else here at UtterAccess.
That I ended up doing is used the following code in an After Update event for the ClientPO:
CODE
Private Sub CustomerPO_AfterUpdate()
MatchingPO = DLookup("[OrderID]", "PO_T", "[ClientPO] = Form![ClientPO]")
VendorNum = DLookup("[VendorOrderNum]", "PO_T", "[ClientPO] = Form![ClientPO]")
If MatchingPO <> 0 Then
    Me.OrderID.Value = MatchingPO
    Me.VendorOrderNum = VendorNum
Else
    MsgBox "Not a valid Client PO."
End If
End Sub

This doesn't prevent the user from entering an invalid client PO, but it at least warns them.
I have another question regarding how to retrieve tracking information with my new database structure. Since you helped with this, maybe you might have an easy answer. When creating an invoice report, how can I retrieve the tracking numbers from the shipping table that pertain to those part numbers only and place them in a single text box on the report? This is my last difficult leap that I've been unsuccessful in figureing out. Should I post this elsewhere within the forums?
Thanks again, Cybercow and everybody else involved!
crcool75
You know I was able to create a quick query to list the tracking numbers for a particular PO and part number. I used the criteria to specify the PO number and part number. I just don't know how to automate this in a invoice report.
crcool75
Ok... We've decided to list the associated tracking numbers next to each, individual part number on the invoice. It is just like we list the serial numbers. However, I can't seem figure out how to list the associated tracking numbers for the corresponding part number only. It lists all the tracking numbers next to all the parts.
I am thinking out loud here.
crcool75
Cybercow, I posted a new thread regarding this in the proper location.
http://www.utteraccess.com/forum/Query-Tra...s-t1975645.html
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.