Please...ANYONE, Subform question (1 Viewer)

deadman

Registered User.
Local time
Today, 13:28
Joined
Feb 13, 2002
Messages
23
I know that there is a simple answer, I just can’t find it. This is the second post regarding this, the first got no replies. Maybe the way that I asked the question was poorly stated or too confusing. Please take a look at the tables and relationships that I have…http://mikeis.home.texas.net.

Lets assume that I am talking about grocery stores. There are many stores, all of which have basically the same categories (frozen foods, produce, canned veggies, etc.) but each store puts these categories on a different aisle, based on the layout of the store. What I have done is put the stores in one table, all of the categories in table 2, and then another table which will hold the value of the aisle, based on store and category.

I have the form with the stores being chosen from a combo box. I then have a subform that has the storecategories and aisles based on the store selection on the main form. However, if the aisle hasn’t been assigned to it yet, it won’t be on the list. I need all of the possible categories to be listed so that the user can basically go down the list and assign aisle values to each category.

I am trying to get it similar to a spreadsheet where once the store is chosen, column “A” would list all of the categories and column “B” would list the relevant aisle number.

If I am going about this all wrong, let me know. Any suggestions on how I might accomplish this would be greatly appreciated.


Original Post:


Newbie needs help. I have spent way to much time on this when I know that one of you experts out there can resolve this for me in less than a minute. I have created three tables as follows:

[tblStores].[store]

[tblStorecategories].[storecategory]

[tblStoreindex].[storeindexid]
[tblStoreindex].[store]
[tblStoreindex].[storecategory]
[tblStoreindex].[aisle]

I have a form with one subform. The main form has a combo box to look up the value in [tblStores].[store]. In the subform (tabular) I have two text boxes, one to display the value for [tblStorecategories].[storecategory] and the other to display the value for [tblStoreindex].[aisle]. Unfortunately, the subform works like one would normally expect. It displays the storecategories and aisles, based on the store selected and the records present in the tblStoreindex table. However, what I would like is for the subform to display all records from [tblStorecategories].[storecategory] wether or not there is a matching value in the tblStoreindex table or not so that the user can basically see a list of all of the storecategories available and type in an aisle value for each.

The query that I am using for the subform is: SELECT tblStores.*, tblStoreindex.aisle, tblStorecategory.storecategory
FROM tblStores INNER JOIN (tblStorecategory INNER JOIN tblStoreindex ON tblStorecategory.storecategory = tblStoreindex.storecategory) ON tblStores.store = tblStoreindex.store;

I could probably do this using a spreadsheet with the field [store] as the column headers and the [storecategories] listed in column A and the aisle data filling in the rest, but I don't want to involve a spreadsheet in this project. Anybody out there have any ideas? Thanks in advance.
 

jwindon

Registered User.
Local time
Today, 18:28
Joined
Aug 12, 2001
Messages
531
I see what you are needing to do here. I had the same problem last fall and Pat Hartman was kind enough to guide me thru this.

Best I can explain is that you need to have an event on your main form, that runs an append query to the table that contains both the categories and their respective isles. The default isleID should be left blank. The form should be requeried after running the append query and the subform should display the categories and their repective (unassigned isles).

Your query should look something like this:


INSERT INTO tblStoreIsles ( CategoryKey, StoreKey)
SELECT tblStorecategories.CategoryKey, [Forms]![frmYOURFORMNAME]![cmbSTORECOMBONAME] AS Expr1

That's kinda rough and you will need to tweak it to your field names.

Basically, you send ALL the categoryIDs with the StoreID to that IsleTable. If you have 12 isles, then the query will send 12 records to the table.

After running this event, don't forget to requery your subform to display the isles.

Good luck!

PS. Emailed you a demo.

[This message has been edited by jwindon (edited 02-24-2002).]
 

SimonC

Registered User.
Local time
Today, 18:28
Joined
Feb 25, 2002
Messages
48
This is a bit convoluted, but bear with me. It's the subform part that's causing you the grief and you don't really need it. Instead, start by putting the combo (or whatever other means you use to select the current store) in the header section of the main form. Then create three queries. Query one (which I've called qryAisle) looks like this:

SELECT tblAisleID, frgnStoreID, frgnCategoryID, Aisle FROM tblAisles WHERE frgnStoreID=[Forms]![frmData]![cboStoreID]

Query two (which I've called qryCategoryAisle) looks like this:

SELECT tblCategories.CategoryID, tblCategories.CategoryName, qryAisle.tblAisleID, qryAisle.frgnStoreID, qryAisle.frgnCategoryID, qryAisle.Aisle
FROM tblCategories LEFT JOIN qryAisle ON tblCategories.CategoryID = qryAisle.frgnCategoryID;

Query three (qryStoreCategoryAisle) is

SELECT tblStores.StoreID, qryCategoryAisle.CategoryID, qryCategoryAisle.CategoryName, qryCategoryAisle.tblAisleID, qryCategoryAisle.frgnStoreID, qryCategoryAisle.frgnCategoryID, qryCategoryAisle.Aisle
FROM tblStores, qryCategoryAisle
WHERE (((tblStores.StoreID)=[Forms]![frmData]![cboStoreID]));

In my example frmData is the main input form, cboStoreID is the combo I'd select the store from. Bind this form to qryStoreCategoryID and set the form to continuous forms rather than single.

On the detail section, add a text box for the Category Description (lock this control, you don't want anyone to change this value) and a text box for the aisle number. Also add three invisible text boxes to hold the CategoryID (from the Category record), the CategoryID (from the Aisle record which I've called frgnCategoryID to distinguish it from the other one) and the StoreID from the Aisle record (which I've called frgnStoreID).

In the AfterUpdate event for the combo cboStoreID include the line

Me.Requery

In the AfterUpdate event for the text box bound to the Aisle number add the lines

If Me.txtfrgnCategoryID = 0 Then Me.txtfrgnCategoryID = Me.txtCategoryID

If Me.txtfrgnStoreID = 0 Then Me.txtfrgnStoreID = Me.cboStoreID

Hope this makes some kind of sense.

Regards,

Simon.
 

SimonC

Registered User.
Local time
Today, 18:28
Joined
Feb 25, 2002
Messages
48
Sorry, where I said

> Bind this form to qryStoreCategoryID

I meant

> Bind this form to qryStoreCategoryAisle

Sorry,

Simon.
 

deadman

Registered User.
Local time
Today, 13:28
Joined
Feb 13, 2002
Messages
23
Thanks SimonC for the advice. It is similar to the advice Jen gave and what she used in the sample database that she sent me. I'm not going to have time in the next day or so to try it out, but I am sure that between the two, I'll be successful in what I have been trying to achieve. I have been working on just this one form for about 2 weeks and appreciate you all pointing me in the right direction. Thanks very much to both of you.
 

Users who are viewing this thread

Top Bottom