Connecting Tables through a Query

[Becker]

I have a table "ordering" and a table "inventory" which are related by the field "chemical." On "ordering" the information doesn't change. It is just information about price, company, etc. On inventory when an order is made it will save the date, how many we currently have in stock, etc. I need some help with two things.

1. On one form they choose a chemical from a combo box and I want the information in "ordering" to display along with a new record in "inventory" for entering this information.

2. On another form they again choose a chemical from a combo box, but this time I want the date and such from the most recent order to show.

Any help is greatly appreciated. Thanks.

[Rabbit]

1) In your inventory form, you can either set the record source to a query where you join the table to ordering and return the fields you want. But that may make your query unupdateable. If it does, you can bind the control source of some controls to a DLookup of the Ordering table.

2) Make the control source of the form an aggregate query that returns the max order date by chemical.

[Becker]

Looking only at the first situation now. I have the form's record source set to the query with both tables connected by the field chemical. The query is updateable from the form. I want the fields in the query that come from the inventory table to be at a new record. Right now users would have to scroll through all of the old records with the arrows at the bottom.

Basically I guess my question is as follows (only worded better, I think). The table orders only has one record per chemical. They table inventory has multiple records for each chemical (all of the past orders). They are linked in a query by the chemical field and this query is the record source for the form. I want to know if it is possible automatically have the query pull up a new record for the inventory table from the query. I also, on another form, want to do the same but to view only the most recent order.

[twinnyfo]

For situation 1), you could set the form's Data Entry property set to "Yes" and this would automatically take the user to a new record.

For situation 2) Rabbit has a good idea, but I'm not sure the recordset would be updateable. If you needed it to be updateable, have a query, filtered by Chemical, sorted by date, newest to oldest, and just choose the first record.

SELECT TOP 1 ....{the rest of your query}
 

[Becker]

1. Data entry turned to yes does not do what I need. It is set to yes but I need a new record on only one of the tables in the query. I apologize, I am having a hard time explaining this. I need to view the information already in the the ordering table for the chemical selected and the the other table should have a new record where quantity and date can be filled out.

2. I think this will work. I do not need the query to be updateable for this part. Thanks.

[TheSmileyCoder]

I have the form's record source set to the query with both tables connected by the field chemical

It is rarely a good idea to combine two tables into a single form for updating.

First create a form bound to Ordering, showing all the details you need, and in its header place the combobox you want to use for selecting the chemical.

Use the afterupdate event of the combobox to navigate the form to the correct record. How this is done depends a bit on how the primary key of your ordering table is setup. (More info required)

Create a Continues form bound to Inventory, and add this as a subform into the previously mentioned form, and set the link properties to use the primary/Foreign key (you called it Chemical I believe)

From what you have described I believe a form/subform approach would be the best way to go.

[twinnyfo]

Perhaps, after the main form has opened, on the OnCurrent Event, you can use VBA to move the subform to a new record. This would ensure that the table underneath the subform is still pointing to the proper chemical, and it would be nearly invisible to the user....

[Becker]

A subform works great. I just had to rearrange some other things. Thanks.