cascade combobox and auto populate fields.

[Josué]

Hi, can anyone help me with this issue. I have searched the forum but I
haven´t found a similar situation with this one. I don´t if it is possible to
do what I am trying to do.

In a database I have a main form where the user selects a Supplier and all
the fields related to that supplier are automatically populated, in this form
theres is a subform with a combobox where the user needs to insert the
supplier code again, if the code is different the user will get an error, I
cascaded this combobox, named Supplier, with another combobox in the same
subform, named Products, so that the user can only select products sold by
the selected supplier. When i try to select a product I am getting a pop-up
window with the following text "forms!subformName!FieldName" where i need to
type the supplier code again so that the combobox Products shows me the
products for this supplier. the criteria for this combo is set as:
SELECT DISTINCTROW ProductDetail.Product FROM ProductDetail WHERE
(((ProductDetail.Supplier) Like Forms!OrderDetails!Supplier));
How can I stop that pop-up window?

Another question.
In the same subform, after i select a product on the combobox how can I
autopopulate the other fields related to the product that are also on the
table ProductDetails?

Sorry for my English if I am not being very clear exposing my case.

Thanks in advance

 

[Douglas J. Steele]

You can't refer directly to controls on subforms: you need to go via the
parent form:

Forms!ParentForm!SubformControl.Form!ControlOnSubform

Depending on how the form was added as a subform, the name of the subform
control on the parent form may be different than the name of the form being
used as a subform. Check http://www.mvps.org/access/forms/frm0031.htm at
"The Access Web" for more details about referring to subforms.

As to your second question, the easiest way is to include the various
additional fields in which you're interested in the query you're using as
the combo box's RowSource. It's not necessary that you display them in the
combo box: just make sure that the combo's ColumnCount property is correct
(you can hide columns by setting the ColumnWidths property appropriately).
Then, use VBA code in the combo box's AfterUpdate event:

Private Sub MyCombo_AfterUpdate()

Me.Text1 = MyCombo.Column(1)
Me.Text2 = MyCombo.Column(2)

End Sub

That will put whatever's in the second column of the selected row of the
combo box into textbox Text1, and whatever's in the third column of the
selected row into Text2. (The Column collection starts numbering at 0)

 

[Josué]

Thank you Douglas, that solved the problem of the pop-uo window but now
Products combobox is not showing me any results. How can i fix this?

About the 2nd question I know how to autopopulate fields but in this case it
is not working, should I insert all the fields I want to autopopulate on the
SQL instructions, shown below, of the criteria for the second combobox?


"Douglas J. Steele" escreveu:

 

[Douglas J. Steele]

What exactly is in the the Supplier field? Unless you're putting wild card
characters there, there's no point using LIKE: just use =.

As to the autopopulate, yes, you need to include all of the fields in the
query.

 

[Josué]

In the Supplier I am just putting the code of the Supplier, it is formatted
as text and I am not using any special characters, I change "Like" in the
query with "=" and it works exactly the same. After your help I solved the
problem of the popin-up window on thwe second combobox but now i am not able
to select a product on the combobox, it is completely blank.

Thank you for you time Mr. Steele

"Douglas J. Steele" escreveu:

 

[Ken Sheridan]

You also need to requery the products combo box in the AfterUpdate event
procedure of the supplier combo box, e.g.

Me.cboProduct.Requery

Ken Sheridan
Stafford, England

 

[Josué]

I am still getting a blank Product combobox after inserting the VBA requery
code in the AfterUpdate event. I dont get an error or something like that. I
select the Supplier in the combobox and when i try to select to Product i get
a blank combobox. Any thoughts on that?

Thanks

"Ken Sheridan" escreveu:

 

[Ken Sheridan]

Is the Supplier column in ProductDetails text data type or is it a number
data type referencing a numeric (e.g. an autonumber) primary key of the
Suppliers table? If it’s the latter and particularly if you used the 'lookup
field' wizard when designing the ProductDetails table you'd see the Supplier
Code text value in this field, but its true value would be a hidden number.
If this is the case but the value of the Suppliers combo box is the text
Supplier Code rather than the number then the products combo box would not
return any rows. Other than that I can't see any obvious explanation for the
behaviour which you are experiencing.

Ken Sheridan
Stafford, England

 

[Josué]

The Supplier column is text data and the table was designed manually in order
to relate to another table named Products, i did not use lookup wizard to
create any table or query. i have done it all manually

The database as several tables but for what i need i will explain how this
part works.

I have 3 tables, Products, Supplier, ProductDetails: In Products i define de
code, thname, and family of the product
In Suppliers I define Code and name of the supplier.
To insert the data on ProductDetails I created a subform on the Products
form, there i select the supplier from the Supplier table with a combobox and
then manually type all the other information.

Now I am trying to create another form named Request where I Select the
supplier on the main form from the Supplier table and all the data related to
the supplier is automatically populated.
Then I have a subform named RequestDetails , the data for this subform is on
the table ProductDetails, in the header of this subform i select the Supplier
through a combobox and it should be equal to the one selected in the form
otherwise the user will get an error, then in the detail body of the subform
I have the combo to select the Products associated with the chosen Supplier.
Everything is working except the combobox for Products in this subform.

I have checked queries and relationships and I really dont know how to fix
this.

Thanks for all the time you have spent trying to help me.

"Ken Sheridan" escreveu:

 

[Ken Sheridan]

It seems to me that each form/subform is simply a converse of the other.

1. The Products form includes a subform bound to the ProductDetails table
with its LinkMasterFields and LinkChildFields properties being Product, i.e.
the primary key of Products and the corresponding foreign key in
ProductDetails. The subform will include a combo box bound to the Supplier
column with a RowSource property which lists all suppliers, and controls
bound to other columns in the ProductDetails table, e.g. the unit price
charged by the supplier in question for the current product.

2. Conversely the Suppliers form includes a subform bound to the
ProductDetails table with its LinkMasterFields and LinkChildFields properties
being Supplier, i.e. the primary key of Suppliers and the corresponding
foreign key in ProductDetails. The subform will include a combo box bound to
the Product column with a RowSource property which lists all products, and
controls bound to other columns in the ProductDetails table, e.g. the unit
price charged by the supplier in question for the current product.

In neither case do you need to correlate two combo boxes. The subform in
the Products table will show each row from ProductDetails for the current
product by being linked on the primary/foreign keys of the two tables.
Similarly the subform in the Suppliers form will show each row from
ProductDetails for the current supplier.

I'm wondering if you are trying to do something which your current three
tables don't actually provide for. At the moment all these allow you to do
is record which suppliers supply which products along with details about each
suppliers supply of each product, no more than that. By using one or other
of the forms as described above you can either allocate one or more suppliers
to a product, or allocate one or more products to a supplier.

If you are trying to include additional functionality then you will probably
need one or more tables. To take a hypothetical example if you were building
a list of products for a project the you'd need a Projects table with columns
such as Project, ProjectDate etc. You'd also need a ProjectMaterials table
with columns Project, Product, SupplierCode and Quantity. In this table
Project would be a foreign key column referencing the primary key of the
Projects table, and Product and SupplierCode would be a composite primary key
referencing the primary key of ProductDetails, which itself is a composite
key made up of the Product and SupplierCode columns. In case like this you
could use correlated combo boxes, most likely to select a product first then
a supplier. This is just an example of course, but if its something similar
to this which you are trying to set up then if you can describe exactly what
it is you want to do in real-world terms rather than how you are trying to do
it in database terms, hopefully we'll be able to help.

Ken Sheridan
Stafford, England

 

[Josué]

Once again I thank you for the time spent with me.

What I am trying to do is a database that allows the user to make Offers to
costumers, after that if a offer is accepted I will make an Production Order
based on the information of the Offer. When I make the Offers I can select
all the parts (PRODUCTS) that assembled togheter make the final product.
After that I need to buy the materials to do the job so I make a Request
(Purchase Order) for every group of Products sold by the sme Supplier. It is
in those Request that I am experiencing problems.

About your 1st point, that is exactly what I have at the moment and I am not
doing what you thought on the 2nd point. On the Suppliers form I have a
subform but is only to insert the name of the person in charge of the sales
department of the supplier.

I hope this helps you understand what I am trying to do, I am sorry for not
giving this explanation sooner.

Best Regards

Josué

"Ken Sheridan" escreveu:

 

[Ken Sheridan]

So far you've only mentioned three tables, so do you have the other tables
necessary to model this? You will need:

1. A PurchaseOrders table with columns PurchaseOrderID, Supplier, OfferID,
CustomerID etc

2. A Customers table.

3. An Offers table (what I'd normally call Quotations).

4. A PurchaseOrderDetails table.

The last will reference the primary key of the ProductDetails table, a
composite key of Product and Supplier, so will include columns OrderID,
Product, Supplier and Quantity, along with any others as appropriate (see the
OrderDetails table in the sample Northwind database for a similar example).

It will also reference PurchaseOrders on PurchaseOrderID and Supplier,
which, while not the primary key of the PurchaseOrders table, is a candidate
key, so there should be a unique index on these two columns in the
PurchaseOrders table. By enforcing referential integrity in this
relationship you can ensure that each purchase order only includes products
from one supplier.

It will also reference the primary key of Offers, so will have a foreign key
column OfferID column.

Offers will reference Customers so will have a foreign key CustomerID column.

The relevant relationships would thus look like this:

Customer----<Offers----<PurchaseOrders---(continues…)
--<PurchaseOrderDetails>-----ProductDetails

So, for data input I'd envisage a parent frmPurchaseOrders form, based on
the PurchaseOrders. The parent form would include a combo box bound to the
Supplier column. The RowSource for the Supplier combo box would be like this:

SELECT Supplier FROM Suppliers ORDER BY Supplier;

It would also include a subform control housing a subform based on the
PurchaseOrderDetails table. The subform would be linked to the parent form
on OrderID;Supplier. This subform would be in continuous form view and would
include a combo box bound to the Product column.

The Product combo box, cboProduct say, would have the following RowSource:

SELECT Product
FROM ProductDetails
WHERE Supplier = Nz(Forms!frmPurchaseOrders!Supplier,"");

In the frmPurchaseOrders parent form's Current event procedure, and in the
AfterUpdate event procedure of the Supplier control on the parent form
requery the cboProduct combo box in the subform with:

Dim frm As form

Set frm = Me.sfcPurchaseOrderDetails.Form
frm.cboProduct.Requery

where sfcPurchaseOrderDetails is the name of the subform control on the
parent form, i.e. the control which houses the subform.

The primary key of PurchaseOrderDetails is a composite one of OrderID,
Supplier and Product.

The above is a simple robust model which I think should work without any
problem.

Ken Sheridan
Stafford, England