Form from a Query

[brharrii]

I'm designing a form that will allow users to interact with 3 tables constructed together with a Many to Many relationship:

tblProductInfo

tblPackaging

tblProductPackagingMM

I've setup all the relationships as they need to be and I'm in the process of designing the querries / forms. I'm using the form wizard to setup my forms based off of the querries but for some reason the options available to me in the form wizard are different depending on which query I'm using. I don't understand why.

When the form wizard looks at my first query, It gives me the options to design the form as I want:

SELECT tblProductInfo.ProductID, tblProductInfo.ItemNumber, tblProductInfo.JDEDescription, tblProductPackagingMM.PP_PackageIDFK, tblPackaging.PackageDesc, tblPackaging.JDEPackageID, tblPackaging.PackageType, tblPackaging.PalletConfiguration, tblPackaging.VacPacTemps, tblPackaging.FilmBottom, tblPackaging.BottomFilmDesc, tblPackaging.BottomFilmID, tblPackaging.PackageType2, tblPackageTypeData.PackageTypeTare, tblPackageTypeData.PackageTypeWeight, tblPackageTypeData.PackageTypeUnits
FROM tblPackageTypeData INNER JOIN (tblProductInfo INNER JOIN ((tblPackageType INNER JOIN tblPackaging ON tblPackageType.PackageTypeID = tblPackaging.PackageType) INNER JOIN tblProductPackagingMM ON tblPackaging.PackageID = tblProductPackagingMM.PP_PackageIDFK) ON tblProductInfo.ProductID = tblProductPackagingMM.PP_ProductIDFK) ON tblPackageTypeData.PackageTypeID = tblPackaging.PackageType2;

When I open the form wizard it gives me the option to select my fields I want on the form. I select all the available fields and push next.
*This next screen is the important one.*
The form Wizard then gives me the option to select: "Form With Subforms" or "Linked Forms". When I base the form off of my other query I don't have that option. Instead it gives me 4 options: "Columnar", "Tabular", "Datasheet", and "Justified".

I need the form wizard to allow me to choose between "Form with Subform" and "linked forms" regardless of which query I'm using.

This is the query that isn't doing what I want it to:

SELECT tblProductInfo.ProductID, tblProductInfo.ItemNumber, tblProductInfo.JDEDescription, tblProductInfo.JDEDescription2, tblProductPackagingMM.PP_ProductIDFK, tblProductPackagingMM.PP_PackageIDFK, tblPackaging.PackageDesc, tblPackaging.JDEPackageID, tblPackaging.PackageDesc2, tblPackaging.PackageType, tblPackaging.VacPacTemps, tblPackaging.FilmBottom, tblPackaging.BottomFilmDesc, tblPackaging.BottomFilmID, tblPackaging.PackageType2, tblPackageTypeData.PackageTypeTare
FROM tblPackageType INNER JOIN (tblPackageTypeData INNER JOIN (tblProductInfo INNER JOIN (tblPackaging INNER JOIN tblProductPackagingMM ON tblPackaging.PackageID = tblProductPackagingMM.PP_PackageIDFK) ON tblProductInfo.ProductID = tblProductPackagingMM.PP_ProductIDFK) ON tblPackageTypeData.PackageTypeID = tblPackaging.PackageType2) ON tblPackageType.PackageTypeID = tblPackaging.PackageType
WHERE (((tblPackaging.PackageType)=3));

Thanks for your help

 

[Beetle]

You shouldn't be joining all three tables in a query and then attempting to use that query as the record source of a single form (unless the purpose of the form is simply to display information, not interact with it).

You should be using a main form/sub form design where the main form is based either on the Product table or the Packaging table and the sub form is based on the junction table. You can use the Wizard for this. You need to select the necessary fields from both tables in step 1, then in step 2 you will see the option to choose Form with subform(s) or Linked forms.

 

[brharrii]

Thanks for your response beetle,

I was able to edit the records using the first sql I had that contained all 3 tables, it seems like i should be able to set it up the same way using this other sql, but maybe not. I'll play with it some more and see what I can find out.

Thanks!

 

[boblarson]

Thanks for your response beetle,

I was able to edit the records using the first sql I had that contained all 3 tables,

Just because you CAN do something doesn't mean you SHOULD do it.

 

[brharrii]

Good point Bob, Thanks again

 

[brharrii]

I'm currently working on trying to set it up as suggested by beetle. I think he's pointed me in a good direction. I'll post back if I run into more road blocks.

On a side note, I've really appreciated the help and support of the access community. you guys have been awesome. I'm always worried that someone is going to shout at me for posting dumb questions, but I've consistantly recieved considerate polite responses to my inquerries, even when I'm doing something completely wrong. Thank you again!

 

[boblarson]

We've all been there, so hopefully we remember what it was like and respond accordingly. Sometimes I'll get frustrated with some when they don't seem to think about things but other than that, I try to remember how I felt back then when I knew way less than I do today.