One to many to many forms

[vx1100]

I have searched the FAQ and not found a souloution.
I have the following Tables
Customers: with these fields Key Custref, Address details in separate fields
Clients: as above Key Cliref
Qnumber Key Qnumber,Debit,Payee,Custref
Purchase: Key Autonum and financial and other specific details including Custref & Cliref and Qnumber all indexed & Number which consists of the date in yyyy.mm.dd,number(incrementing on that day) format

Relationships Customer 1 to Qnumber many
Qnumber 1 to Purchase qnumber many
Client 1 to Purchase many

I am trying to get a form in which I can enter the customer name(combo) this activated a combo with a list of Qnumbers and on selecting a qnumber this activated a sub form list of all the entries relating to that qnumber.

I have as a model use the examples in the Souloutions DB based on Northwind.

I have created the following queries: Customer: Custref,Names
Qnumber: Custref,Qnumber
Purchase: Number,Qnumber and other relevent fields

When I apply the Run command to these queries in the design view they return the correct values.

On the form header I have a combo SelectCustomer and a combo SelectCheque with a enabled utility module from the Solution db and properties set from the same source.

The subform was created using a wizard.

When I open the form the selectCustomer combo limits the value in the SelectCheque box however it will not run the subform ( I have even tried using a hidden text box to show the Qnumber from the SelectCheque combo but that will not run. If I run the subform from design view it asks for the SelectCheque Qnumber and then the Qnumber.qnumber and it will show the correct list of entries.

Could you help me attach these form so that they run together, and would it be possible to use a similar form to enter data in the same format by selecting the customer and entering a new cheque number?

 

[ToeShot]

I Have actually done this and I posted it for another person in another forum so I will post it here fo you to... (Took A Long Time To Find So I Need To Post It)

These instructions were written for someone who obviouslly had different table. All you need to do is sub in your tables. The key to the thought process here is that You have a Wholesaler this Wholesaler can have many Products and each one of the Products can have many SerialNumbers.
So what happens is that you have a drop down combo box that will show all your Wholesalers. After the Wholesaler is selected the another drop down combox will populate with only that Wholesalers Product. After the product is selected Then you will get a datsheet view of all the serial numbers for that product. Easy right!!!!

Good Luck and have fun. if you have any questions let me know.

1) Create a Manufactor Table with A unique manufactorID and other manufactor info.

2) Create a Product table with an uniquie ProductID and have the manufatcorId on this table also with product description and price etc.

3) Create a third table that will hold all the Barcodes on this table you will have the ProductID number.

4) Create a Relationship with a one to many from manufactor to product then go from product to the barcodes with a one to many relationship.

what this does is basically say I have one manufactor that has many products and each of these products can have many barcodes.

5) Make a form off the barcode table with the wizard and select the datasheet view and call it BarcodeSubform.

6) Make another form off the products table and include a the BarcodeSubform on that form. Using the wizard it will allow you to link the forms by ProductID. Name it ProductSubform

7) Make Another but not off any table. keep it unbound include a subform ProductSubform. Save this form as frmManufactor. add a combobox to this form and have it list just the manufator's name

8) On the ProductSubform add a ComboBox and for the row source select the query builder add the table product to the query the select ProductID, ManufactorID uncheck the show box then select product name. In the Criteria under manufactorID type in
Forms!frmManufactor!ComboBox_Name


Save Everything <-- very important

then run the form frmManufactor what will happen is that you will have a combobox with a list of manufator names after selecting a manufatcor your subform will show with a ComboBox with a list of products for that Manufactor. after selecting a product your datasheet will show you all the barcodes listed for that product. you can also add barcodes to that list in that view.

make seperate forms off the tables that will allow you to add a manufactor and all needed info on that and a form off the products table that will allow you to add the manufactorID and the unique productID

If you have any problems with this let me know and I will try and help you.

http://www.adate.com/accesshelp

 

[vx1100]

Thanks for the example. I had some difficulty in getting the form to run until I discovered that for some reason every time I assembled the forms Access was loading a filter based on a deleted query!

The only problem that remains with this set up is that having accessed one record when I want to access a further record it will not run. Any suggestions.

 

[ToeShot]

Email me your Email Address here

DSherer@Adatae.com

and I will send you a copy of the sample I have. I am not have any problems selecting another record