Access, Subforms, why does this thing hate me?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

TeflonSlinky

Thread Starter
Joined
Jul 18, 2005
Messages
8
I am a "surface user" of access - mostly skimming long with wizards and simple sql statements - rarely braving the dark VB underbelly, but I fear I may have to conquer my fears in order to solve this issue, and I would appreciate greatly a guide. I have recently purchased an Amish Country Store, and have been for the past few months endeavoring to modernize the inventory management aspects of the store (although the pen and paper is starting to look better and better...). I have written a small access database to help me with this since I cannot find any shrink-wrap software set up to deal with a bulk food store type environment. So the beast I have created is part inventory management, part printed catalog generator, and part broken.

Here is the broken part - I have a form for adding and editing products (I have about 1700 products in the database in a few dozen categories and from a few dozen suppliers). So my main form has 2 combo boxes - one for the vendor ID and one for the Category Id - My intent was to create the ability to sort in a sub form by manipulating these two controls. My sub form contains most of my product data, weight, container size, price etc. I have connected the forms using the parent-child relationships for the Category ID and the Vendor ID, and it works almost perfectly (it's the almost that has kept me up until 1am the past 2 nights). The fly in this particular ointment is one product tends to move itself to whatever vendor/category combo I have selected in the main form when I exit the form.

I.E. - the product (in this case it is fresh milled spelt flour) should live in the yoder's vendor ID, in the Flour category - but if I navigate to the Troyer vendor ID and the Cheese category, then close the form - when I open the form again (it opens to the last place I was) there sits the spelt flour happily among my cheeses.

I am at somewhat of loss, and if I had any hair it would be pulled long ago. Any ides would be greatly appreciated!

Thanks!
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Have you checked the Table record for Milled Spelt Flour as if it only happens with that record there must be something unique about it?
Is it the first record in the table?
Is the subform based on a "Query" or does it get it's data straight from the table?
Is a zipped version of the database too large to post on here?
If it is you could send it to me in zipped form and I will take a look at it for you.
 

TeflonSlinky

Thread Starter
Joined
Jul 18, 2005
Messages
8
The Spelt flour record is not the first record in the table. I looks the same as the other records as far as I can tell. I was using a table originally to populate the main form, and a query to populate the subform - and was having the same issue except it was with the first record in the database. I changed the main form to pull from a query (same query that populates the subform) and that brings us to where we are now.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Can you post a zipped version of the database?
What does the query show if you run it on it's own after selecting the Cheese category and closing the form?
Have you tried seperate queries?
One for the Main Form Combos with just their fields and a second for the subform with all the other data.
 

TeflonSlinky

Thread Starter
Joined
Jul 18, 2005
Messages
8
Thanks for your help - played around with using different queries for each subform etc - finally opted for using 2 separate forms and using a command button to run a macro to open and filter a product form in datasheet view. Not as pretty as a subform, and I have not totally given up on that option, but it works and my goodies stay in the categories they are suppose to. Anyone know how to have a macro open a and filter a subform?

Thanks again! What a great board. (y)
 

OBP

Joined
Mar 8, 2005
Messages
19,896
On your Main Form when you select the data in the Combo box do you store the selection in a field on the Main Form?
I can show you how to have the Query's selection criteria based on the Form's choices, that would probably do what you want.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Rick, please find attached database which illustrates a subform being controlled by the Main form's combo selection via a query.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top