Solved

MS Access Form Building With SubForm Sub-SubForm Linking Without Similar ID Fields

Posted on 2016-09-29
26
54 Views
Last Modified: 2016-09-30
Ok I am building my Product Entry Form in Access. I have several tables I would like to place on a single main form. The Tables involved are
SKUs
ProductImages
Products
ProdLocations

The Main Form is built on the SKUs Table.

Skus, Products, and ProductImages Tables all have the SkuID field in common.

ProdLocations does not have the field SKUid in common.  

Products and ProdLocations Tables do have the field ProdLocID in common

The SKUs table is the items being sold but they are not a PRODUCT until we review them and give them a Product Condition and then that PRODUCT is stored in the Products Table.

Once we make the PRODUCT we then have to Inventory the product in the ProdLocations table.

I would like a all in one form so that the user can input all the info and then in the ProdLocations Table put the Total amount in the RECEIVING AREA LOCATION in the ProdLocations.

The Receiving Area Location is just that Receiving and after that we can send it wherever. But we need to know how many Products we have in the Reviving area awaitng to be put away elsewhere.

So I need  to link the ProdLocations Sub Form and the Main SKUs Form together.

Thank You!

db44.jpg
0
Comment
Question by:Dustin Stanley
  • 15
  • 8
  • 3
26 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41822734
Rather than crowding all on one form, you might add a tab control and put the subforms on different tabs.

I just answered a similar question regarding how to link two subforms on an unbound main form.

https://www.experts-exchange.com/questions/28973434/Access-query-that-references-subform-for-criteria.html
0
 

Author Comment

by:Dustin Stanley
ID: 41822787
OK thanks. I am out of my office now and will try this tomorrow.  I have tinkered with the navigation control but I am kinda worried that with tabs something might get overlooked and not get filled out.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 41822967
Pat is right. Always try to keep a clean and simple interface.

I am kinda worried that with tabs something might get overlooked and not get filled out.

That you can (an should) take of by proper validation at BeforeUpdate.

/gustav
0
 

Author Comment

by:Dustin Stanley
ID: 41823199
Can you give me more detail on the validation. I was thinking about that but was a little unsure. I am about to be working on it today and would like a good heads up. I know of ways to do things and it  may work for now but it isn't always the best. That's where you Experts come into play. Thank you!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41823220
Uh, that's very broad, but for example for checking if a subform has any records:
    Dim Prompt As String

    If Me!SubformControlName.Form.RecordsetClone.RecordCount = 0 Then
        Cancel = True
        Prompt = "At least one location must be specified."
    End If

    ' More checks.

    If Cancel = True Then
        MsgBox Prompt, vbInformation + vbOkOnly, "New Product"
    End If

Open in new window


By second thought, this is a bad example, as child records cannot be created before the main record is created - but you hopefully get the idea.

/gustav
0
 

Author Comment

by:Dustin Stanley
ID: 41823225
Ok I get that it is very broad of a question. let's say there are three tabs. would the above code (yes I know not exactly string for string) :) be like what I would use to make sure the user has filled out all three tabbed subforms before proceeding onto a new record?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41823232
You could use the AfterUpdate of the main form:
    Dim ChildsMissing As Boolean

    If Me!SubformControl1Name.Form.RecordsetClone.RecordCount = 0 Then
        ChildsMissing = True
    ElseIf Me!SubformControl2Name.Form.RecordsetClone.RecordCount = 0 Then
        ChildsMissing = True
    ElseIf Me!SubformControl3Name.Form.RecordsetClone.RecordCount = 0 Then
        ChildsMissing = True
    End If
    
    Me.AllowAdditions = Not ChildsMissing

Open in new window

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41823572
Start by building the schema and creating the forms.  With proper Referential Integrity established you will have an easier time making it all hang together.  For example, in an order entry application, you can't order a product that doesn't exist.  So, you would first have to add the product to the product table and then you could choose it on the order entry form.

Occasionally, you have business rules that require a complete set of data be entered.  There are at least two methods for this.  One is to use a set of shadow tables.  You build all the data in work tables and then when the user says it is complete, you validate completeness and then move the data to the permanent tables and the other is to build them in situ but to keep a complete flag so that the the data is ignored in most queries unless it is flagged as complete.
0
 

Author Comment

by:Dustin Stanley
ID: 41823634
Ok I am glad you replied. i am trying to wrap my mind around the post you sent me a link to about the similar question.

You said there your example code was:
SELECT tblAccountMaster.CommodityID, tblAccountMaster.UtilityID, tblAccountMaster.RateEffectiveDate, tblAccountMaster.RateClassCode, tblAccountMaster.CalcRateExpires, tblAccountMaster.RateExpirationDate, tblAccountMaster.ContractExpirationDate, tblAccountMaster.ReadCycle, tblAccountMaster.EffPlusDropDead, tblAccountMaster.EffReadYear, tblAccountMaster.EffReadMonth, tblAccountMaster.ExpReadYear, tblAccountMaster.ExpReadMonth, tblAccountMaster.RatePrefix, tblAccountMaster.RateAnchorDate, tblAccountMaster.RateAnchorYear, tblAccountMaster.RateAnchorMonth, tblAccountMaster.MarketerAccountNumber, tblAccountMaster.UAN, tblAccountMaster.MeterNumber
FROM tblAccountMaster
WHERE (((tblAccountMaster.MarketerAccountNumber)=[forms]![frmAccountList]![sfrmUniqueAccounts]![MarketerAccountNumber]) AND ((tblAccountMaster.UAN)=[forms]![frmAccountList]![sfrmUniqueAccounts]![UAN]) AND ((tblAccountMaster.MeterNumber)=[forms]![frmAccountList]![sfrmUniqueAccounts]![MeterNumber]))
ORDER BY tblAccountMaster.CommodityID, tblAccountMaster.RateEffectiveDate DESC;

Open in new window


and it had a requery of:
Private Sub Form_Current()
    Me.Parent!sfrmAccountRateClasses.Requery
End Sub

Open in new window


I am going to start off with my "ProductImages" Table.

I made a main unbound form called "ProdMainEntryForm"

I then added my "SKUs" table Subformcalled "SKUsSubForm"  to the top of the unbound form.

I added a tab control of 3 Tabs. 1: Inventory, 2: Images, 3: Other

I made a query to SELECT all my colums in the "ProductsImages" Table. The three colums are "productImageID" , "SkuID" , "ImageURL"

I inserted a SubForm into the Images Tab on the tab control and chose the Query "ProdImageQuery" for the subform.

I will show you the code I made from what I seen in your code and understood.

SELECT ProductImages.ProductImageID, ProductImages.SkuID, ProductImages.ImageURL
FROM ProductImages
WHERE (ProductImages.SkuID=[forms]![ProdMainEntryForm]![ProdImageSubForm]![SkuID]);

Open in new window



I don't need a ORDER do I?

and it keeps  wanting a parameter "Forms!ProdMainEntryForm!ProdImageSubForm!SkuID"

Does the Requery go into the Subform "ProdImageSubForm"?
mine would be this correct?

Private Sub Form_Current()
    Me.Parent!ProdMainEntryForm.Requery
End Sub

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41823645
The technique the OP was asking about  uses a list type subform.  Choosing something from the list then synchronizes the other subforms to what is selected.  It is a hierarchical relationship.

Please post your schema and tell us what table is the "parent".
0
 

Author Comment

by:Dustin Stanley
ID: 41823667
Ok "ProdMainEntryForm" is the unbound main form.

"SKUs" is the parent table with the Subform "SkusSubForm"

Subform I am working on is "ProdImageQuerySubForm" in the middle Tab

db45.jpgdb46.jpg
0
 

Author Comment

by:Dustin Stanley
ID: 41823712
I got it!!

Private Sub Form_Current()
    Me.Parent!ProdImageQuerySubform.Requery
End Sub

Open in new window

0
 

Author Comment

by:Dustin Stanley
ID: 41823726
Ok now this brings me back to why I asked the original question.
table "ProdLocations" and table "SKUs" does not have a similar relational field.

"SKUs" table has a field "SkuID" and "ProdLocations" does not...

How do I link these in the Form SubForm?

So far I have:
SELECT ProdLocations.ProdLocID, ProdLocations.LocID, ProdLocations.ProductID, ProdLocations.QtyLoc
FROM ProdLocations
WHERE ([ProdLocations].[SkuID]=[forms]![ProdMainEntryForm]![ProdLocationSubform]![SkuID]);

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Dustin Stanley
ID: 41823834
Ok I figured it out thank you guys.

I made a query for the Products Table similar to above but this time:

SELECT ProdLocations.ProdLocID, ProdLocations.LocID, ProdLocations.ProductID, ProdLocations.QtyLoc
FROM ProdLocations
WHERE (ProdLocations.ProductID=[forms]![ProdMainEntryForm]![ProdSubForm]![ProductID]);

Open in new window


and then made the requery code:

Private Sub Form_Current()
    Me.Parent!ProdLocSubForm.Requery
End Sub

Open in new window


I put the Requery in the Products Subform and made it the master instead of the "SKUs Subform"

This made a chain reaction of requeries with the On Current Event.

Before I close this question I just want to double check and make sure this will be fine in the big picture? Thanks!
0
 

Author Comment

by:Dustin Stanley
ID: 41823913
By the way PatHartman I tried to use the Subform lock you provided from a previous question and now it just stays locked even after ID update:

On the previous Form before this one I used code:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.SkuID & "" = "" Then
        MsgBox "Please enter SKU first.", vbInformation, "Warning"
        Cancel = True
       Me.Parent!SKU.SetFocus
        Exit Sub
    End If
End Sub

Open in new window



and I changed it for this new form to:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.SkuID & "" = "" Then
        MsgBox "Please enter SKU first.", vbInformation, "Warning"
        Cancel = True
        Exit Sub
    End If
End Sub

Private Sub Form_Current()
    Me.Parent!ProdLocSubForm.Requery
End Sub

Open in new window


I think it is locking it not allowing it to requery and thus keeping it locked.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41823928
The BeforeInsert should be in the "child" subform.  Is that where it is?

Your situation seems to be different from the one I posted.  Your main form and subform are related  so you should stick with the standard master/child links.  It is the additional subform that is not related.  I am not sure what subform will control it.

I have forms where there are three forms chained.  The container form is unbound.  The master subform is a list.  Clicking on a row syncs a single record subform to show the details of the item selected from the list.  The third subform is a child of the single record subform and shows a subordinate list so I control both subforms from the  master list subform.

It sounds like you have an unrelated form which is actually a "lookup".  It really doesn't belong on the same form as the other two.

Product Locations MUST be added before Product records.  So I would not put the product location on the same container form as the other two.  That form should stand by itself unless you really are entering new locations whenever you enter a new product and in that case, your schema is probably wrong.
0
 

Author Comment

by:Dustin Stanley
ID: 41823969
The BeforeInsert should be in the "child" subform.  Is that where it is?

Yes it is in the "ProdSubForm"  from the Products Table.

My main Form is an unbound form called "ProdMainEntryForm"

Before as in yesterday. You helped me lock some subforms before the main form was finished. Then after asking this question you suggested a tab control form that was unbounded with several subforms.  So I created this new tabbed form and tried to use and convert yesterdays code to the new Form setting we made today.

The only Form that gave me syncing problems with the MASTER "SKUsSubForm" from the "SKUs" Table was the "ProdLocSubForm" and that was because it had different Id fields in the relationships. But I figured that out and they are all synced perfectly.

BUT when I tried to reset the coding to LOCK or KEEP THE USER FROM EDITING the other subforms before the "SKUsSubForm" was completed it just keeps it locked at all times.

I hope that makes sense.

Hey I may not be anywhere close to you guys but I am getting better at this and able to understand more of what is going on. THANK YOU!

db47.jpgdb48.jpgdb49.jpg
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41823999
It looks to me like the main form is bound (not bounded - bound is both present and past tense).  In that case, it IS the parent and so to requery the subform, you would use:
Me.SubformName.Requery.
0
 

Author Comment

by:Dustin Stanley
ID: 41824011
Yes in the Master Form called SKUsSubForm I set in the VBA the 3 requeries:

Private Sub Form_Current()
    Me.Parent!ProdImageSubForm.Requery
    Me.Parent!ProdSubForm.Requery
    Me.Parent!ProdLocSubForm.Requery
End Sub

Open in new window


That works great and requeries fine except when I add the coding

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.SkuID & "" = "" Then
        MsgBox "Please enter SKU first.", vbInformation, "Warning"
        Cancel = True
        Exit Sub
    End If
End Sub

Open in new window


 to lock the Subforms "ProdImageSubForm",  "ProdSubForm", "ProdLocSubForm". Like we previously did.
0
 

Author Comment

by:Dustin Stanley
ID: 41824023
Ok I see what you were just saying and I contradicted myself. I did accidentally have it in the subform and the master form at the same time (the requery) but the Locking with the previous code is still the same after deleting the requery from the child Subform. Otherwise it works great. But I need it not editable until the master is has been edited.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41824079
Go back over it form by form and make sure the BeforeInsert event is coded in the DEPENDENT subforms only.  Then they should reference the Parent's SKUID
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent!SkuID & "" = "" Then
        MsgBox "Please enter SKU first.", vbInformation, "Warning"
        Cancel = True
        Me.SomeParentControlName.SetFocus 'to move the user back to the main form
        Exit Sub
    End If
End Sub

Open in new window

0
 

Author Comment

by:Dustin Stanley
ID: 41824128
Me.SomeParentControlName.SetFocus 'to move the user back to the main form


How would I code this part exactly. When I put the Master Form Control Textbox "SKU"  in it errors with a compile error member not found.

the code
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent!SkuID & "" = "" Then
        MsgBox "Please enter SKU first.", vbInformation, "Warning"
        Cancel = True
        Me.SomeParentControlName.SetFocus 'to move the user back to the main form
        Exit Sub
    End

Open in new window


is only in the three Dependent Subforms. No where else. I looked back over it completely.

Ok this is my 2 cents if its even worth that! I think in the code part:

If Me.Parent!SkuID & "" = "" Then

Open in new window


It is not referencing the correct SkuID field from the correct Table. I am not genius so I can't tell you exactly what it is referencing but could it be referencing itself. As in the Same table?
0
 

Author Comment

by:Dustin Stanley
ID: 41824136
Ok get this. If I change

If Me.Parent!SkuID & "" = "" Then

Open in new window


to

If Me.Parent!Sku & "" = "" Then

Open in new window


which I know "SKU" is a field in the Master Subform then it says can't find field. So it isn't looking into the master subform table.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41824193
"Someparentcontrolname"  is the Name property of the control you want to move focus to.

Is SKU the name of the CONTROL or is it the name of the field in the form's RecordSource?

This is a case where I would use dot rather than bang because for forms and reports, Access adds the fields of the recordsource to the fields collection of the form/report.

So

Me.Parent.SKU would reference the field in the form's ControlSource

I ALWAYS give controls a name different from the bound field.  This allows me to differentiate in code whether I am referencing the CONTROL or the FIELD.  Textboxes are prefixed with txt, combos with cbo, buttons with cmd, etc.  There are various naming schemes that people propose.  The MOST IMPORTANT thing is CONSISTANCY.

So in my apps this would be
Me.Parent.txtSKU  and I would KNOW that I was referencing the control.
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41824318
Yeah I have not the clue. Once you enter the info into all the tables the requery works great But not the Lock SubForm until Main form is done now.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41824329
If the "lock" isn't working it is one of two things.
1. The code is in the wrong form or event
2. The field you are checking for null is not the correct field
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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 functions 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 Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 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

13 Experts available now in Live!

Get 1:1 Help Now