have a subform show all records from a query

[Jaymin]

Hi All,
Hope someone give me some help and/or guidance of what to do, as I am new at this. I am trying to create an inventory database for work which needs to track first aid products.
I have three main tables:- 1, Products 2, Inventory 3,Purchase. By using a query I have been able to sort the data entered so that all I have left is a list of products that are below their reorder level and the number that needs to be ordered, great so far, this is what I want, but when I try to create a purchase order form as a form/sub form and put in the query I created, there are no products listed, it seems as if I cannot associate the two together, as soon as I link the query for the sub form with the purchase order form.
What I would like to happen is that when I open the purchase order form I can enter the purchase forms details and have all the products that need to be ordered visible in the sub form, the only way that I can get this to half work is to delete the link master and link child fields. If this is the way to go how do I link these products to the purchase order? Or can you advise me a better way to get around or do this problem.
Below are the table fields and their relationships.







:banghead:

 

[EternalMyrtle]

What is the recordsource of your form and subform? Based on your question, it sounds as if you are using a single query as the recordsource for both the main form and subform, which is not how this should be done.

To be honest, it isn't possible for any of us to figure out what is going on here without seeing forms and the underlying record source of the forms and sub forms (queries if that is what you are using). Just showing us the table setup and relationships is not enough.

As you seem to know, you should not have to delete your link master/child fields to get a subform to work properly. If you are doing that there is a problem.

You will probably have to post your database or try to explain your form/subform setup more in depth. Or you can try making the record source of your main form the main table (Purchases?) and the the record source for the subform the related table (Inventory?) and see if you can get it working on your own. If you need to query it out, make separate queries for the main form and subform based on the main table and the related table.

 

[EternalMyrtle]

Or a split form may be more appropriate in your situation if you really need to use a single query...

 

[Jaymin]

EternalMyrtle,
Thanks for your reply sorry that i have not posted a reply sooner, i now your time is valuable, But i have been away and no internet, i will go through what you have said and will reply ASAP
Jaymin

 

[Jaymin]

Hi EternalMyrtle,
Below is a copy of the data base, I am using Access 2010 with MS vista, if you open the "order" query, it will show you a list of only the products that need to be ordered, that is they are below their reorder level and the qty that needs to be ordered.
When I open the purchase form the subform does not get populated with these products that are listed in the "order" query.
What I was hoping to happen if it is possible is that when the "purchase" form is opened the subform is populated with the list of products, and the main form ready to create the new order form.
I tried what you suggested about the record source for the form/subform I could not get it to work, maybe i am missing something here; hope you have the time to help.
View attachment First-Aid-A.accdb
Regards

 

[EternalMyrtle]

Hi,

I cannot open this from my work computer because I have 2007.

You can import your relevant tables, queries and forms into a blank database and see if that works. Or if you tell me the record source of your form and subform and how they are related to each other I can try to help. it is usually the case that a parent form and a child form have a one to many relationship between them.

Here is an article about making your database "backward compatible": http://msdn.microsoft.com/en-us/office/cc907897.aspx

 

[Jaymin]

EternalMyrtle Hi,
I have converted the database hope it works....
View attachment First-Aid_Changed.accdb
Below are the details of the main form, subform, and the link fields





Hope this helps
Thanks for your time

 

[EternalMyrtle]

It is going to be a lot harder for me to be active in this forum because I have been moved to the "hot seat" at work but I took a quick look and saw some problems:

1. Your purchase form is set to data entry so no records are showing up

2. Your linked child field in the subform is PurchaseOrderID from the Inventory table (FK to Purchases). Since this field is null in your inventory table there are no records to show in your subform.

3. In your inventory table what is the number used field? That sounds like a calculated field to me which should not be in a table. Isn't number used the number ordered minus the number in stock?

I think a lot of your problem has to do with your table structure and relationships. My suggestion is that you post in the Tables forum. Hopefully someone with some more time will be able to help.

Good luck!

 

[Jaymin]

EternalMyrtle
Thanks for your help, i will try what you said and will post to the table forum.
regards