Have supplier forms, one frm for ordering need orders to go to supp frm automaticall

[moto485]

Hi, I am just starting to learn Access and have a lot to learn.

I have many suppliers each with their own form and only one form for ordering (call it purchase orders). When one row on the Purchase orders is filled out there is a drop down list at the end with the list of suppliers so when it is ordered we can select the supplier it was ordered from. Once the supplier has been selected in the drop down list I would like it to transfer to the correct supplier form carrying the details in that row, so at the end of the day that form should be blank as everything should be ordered.

Hope I am in the correct section.
Any help would be appreciated.
Thanks.

[June7]

You have multiple forms for suppliers because you have separate tables for suppliers? Doesn't sound like proper relational database design. The behavior you describe would require code (I use only VBA). In a properly designed db this should not be necessary.

Can you provide your project for analysis? Make copy, remove confidential data, run Compact & Repair, zip if still large, attach to post.

[moto485]

Hi June7, sorry for the late reply just been really busy.

As I have just started using Access I am probably going about it the wrong way.
I thought I could store all the data in one Table and then have the suppliers with their own forms (table layout) and one form called the purchase orders form which we fill out and when saved goes to the suppliers form but all the mixed data is saved in the one Table. Once in the Suppliers form I have more options so when it is ordered we know and if not also when stock arrives we can tick it off so we can keep track of stock arrival. I noticed this doesn't work but it was an idea.

We have been doing this on sheets of paper which is a killer and would be alot more effecient this way. I have attached a quick mok up of what I am generally thinking.

I am going to work on it but if you can point me in the right direction it would be great.

Thanks

[June7]

You show only one table so far - PurchaseOrders. Where are tables for products, suppliers, purchase details, customers, sales? Most purchase orders allow more than one item to be ordered, these would be the detail records related to a purchase order.

Separate identical forms for suppliers (or for customers or products or whatever) is a design maintenance nightmare and totally unnecessary. Normally only one form needed for a data entity. Use filtering to limit form's recordset. Organize data on form by stages of data entry; use a tab control if you have too many fields to keep the form compact. You used wizards to create the form and controls. I don't use the design wizards. They do things I don't like and make it harder to modify design. Controls are 'grouped' and cannot be moved and resized independently. Remove the grouping by selecting all the controls and right click>Layout>Remove.

There are project templates available you might find adaptable to your situation. Some of these are presented as choices when you start a new Access project.

Review the 'sticky' thread tutorials here http://forums.aspfree.com/microsoft-access-help-18/

[moto485]

Hi thanks you gave me some things to think about and I finally got a chance to get onto it. The only thing on the purchase orders is stock we don't have and or might be sold by consignment and when that stock arives we match it to the invoice number.

I found the queries very helpful with a bit of sql and am using the toggle filter for the PurchaseOrder_Form. So it goes like this:

PurchaseOrder_Form - Filled in by anyone a certain someone.
Query_Monza - The selected supplier query is then used to order by someone else.
Query_MonzaStockIn - The selected supplier query is then checked of when the stock arrives.

I found the Query great as I can use the SQL to specify what is displayed which is great, but I don't know if that is the correct way to use a Query or not.

I don't want to use the templates as I want to learn what I am actually doing so I can modify things.

Have attached a file. Only one supplier query, roughly done I am just trying to make sure I am going about this correctly (and i understand what I am doing) before creating a full detailed Purchase Order.

[June7]

So if you have hundreds of suppliers, you will have hundreds of queries? This is not the most efficient method to manage your data and it is not necessary. What you should do is filter the RecordSource of the form to display only the desired records.

Check out the tutorials on this site http://datapigtechnologies.com/AccessMain.htm. Start with:
Build a Custom Filter in Your Form in the Tips & Techniques section
and the 3 on comboboxes in Control Basics.

You still might look at some of the template projects just to get ideas and learn from.

[moto485]

Hi thanks for your help your right it would be quite messy with all the queries.
I had a look at that page seems very useful thanks. The tutorials you mentioned I don't think will work as I need it displayed in a spreadsheet view rather than individual orders, unless I can have the top of the form with a form view and the section below as a spreadsheet layout which seems possible I just need to do some study.
I did look at some of the templates and noticed I can have a form with a filter with a drop down box and the filtered results show up below in a spreadsheet.

[June7]

Yes, records can be displayed in a report to appear like a spreadsheet.

Might need report/subreport arrangement.

Same applies to forms.

[moto485]

Ok thanks for your help for now I have just put given each supplier one individual query which will get us by till I figure out the other bit.