asked on
MS Access Form Building With SubForm Sub-SubForm Linking Without Similar ID Fields
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]()
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!
Microsoft Access
Last Comment
PatHartman
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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!
Uh, that's very broad, but for example for checking if a subform has any records:
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
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
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
ASKER
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?
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
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.
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.
ASKER
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:
and it had a requery of:
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.
I don't need a ORDER do I?
and it keeps wanting a parameter "Forms!ProdMainEntryForm!P
Does the Requery go into the Subform "ProdImageSubForm"?
mine would be this correct?
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;
and it had a requery of:
Private Sub Form_Current()
Me.Parent!sfrmAccountRateClasses.Requery
End Sub
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]);
I don't need a ORDER do I?
and it keeps wanting a parameter "Forms!ProdMainEntryForm!P
Does the Requery go into the Subform "ProdImageSubForm"?
mine would be this correct?
Private Sub Form_Current()
Me.Parent!ProdMainEntryForm.Requery
End Sub
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".
Please post your schema and tell us what table is the "parent".
ASKER
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.jpg]()
![db46.jpg]()
"SKUs" is the parent table with the Subform "SkusSubForm"
Subform I am working on is "ProdImageQuerySubForm" in the middle Tab
ASKER
I got it!!
Private Sub Form_Current()
Me.Parent!ProdImageQuerySubform.Requery
End Sub
ASKER
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:
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]);
ASKER
Ok I figured it out thank you guys.
I made a query for the Products Table similar to above but this time:
and then made the requery code:
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!
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]);
and then made the requery code:
Private Sub Form_Current()
Me.Parent!ProdLocSubForm.Requery
End Sub
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!
ASKER
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:
and I changed it for this new form to:
I think it is locking it not allowing it to requery and thus keeping it locked.
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
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
I think it is locking it not allowing it to requery and thus keeping it locked.
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.
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.
ASKER
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!
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.
Me.SubformName.Requery.
ASKER
Yes in the Master Form called SKUsSubForm I set in the VBA the 3 requeries:
That works great and requeries fine except when I add the coding
to lock the Subforms "ProdImageSubForm", "ProdSubForm", "ProdLocSubForm". Like we previously did.
Private Sub Form_Current()
Me.Parent!ProdImageSubForm.Requery
Me.Parent!ProdSubForm.Requery
Me.Parent!ProdLocSubForm.Requery
End Sub
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
to lock the Subforms "ProdImageSubForm", "ProdSubForm", "ProdLocSubForm". Like we previously did.
ASKER
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.
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
ASKER
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
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
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?
ASKER
Ok get this. If I change
to
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.
If Me.Parent!SkuID & "" = "" Then
to
If Me.Parent!Sku & "" = "" Then
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.
"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.
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.
ASKER
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.
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
1. The code is in the wrong form or event
2. The field you are checking for null is not the correct field