Continuous form trouble

[Wadero]

I am going to retype this as perhaps my original explanation was too wordy.

I need to display data from 2 tables in a continuous form. Sure I can do this with a query and a join, but the user needs to be able to add records to the form as well. When adding a new record, only one field need be entered (actually selected from a combo box) and the other 2 fields would auto-fill based on the selection.

Is this even possible?

 

[Wadero]

I have changed my original post as it was probably too wordy.

Could someone please tell me if what I am trying to do is even possible so I can then move on and stop trying to figure it out?

Thanks.

 

[roh_8_it_3]

if u join two tables to make a view and then specify it as a record source for the form then u can only update the records as long as u r only updating one table.U cant update the both tables at the same time.

Regarding the auto fill u can try some VBA Code in the after update of the combo box.

 

[Wadero]

I only want to update one table, and have the other fields auto-fill based on the matching record in the other table.

I can't use VBA to auto-fill unbound text boxes because this is a continuous form and when you change one, it changes for all records.

I have tblSalesPromotions which contains 4 fields. This table already has all its data and doesn't need to be updated by the form.

PromotionName
PromotionDate
ExpirationDate
Description


My other table is tblPromoOffers. It simply has 2 fields. This is the only table that needs to be updated (records added) but I need the continuos subform to show the related fields from the above table.

CustomerID (tied to the same field in the customers table)
PromotionName (tied to the same field in the above table)

 

[John Nelson]

It's definitely possible. Do you already have your master/child fields selected? If not, you need to set that (on the subform's properties) to a field that relates the two tables. It looks like PromoName is the only common field, but I like to use ID fields (and then just hide them in the subform).

Once you have that set your subform should be filtered by PromoName (if your source object is already set to the table you want to update). I'm assuming (from your subject) that you've already set your subform's default view property to "Continuous Forms". If not, set it to that and you'll have a blank text box (or whatever) on the bottom to add new records to that table. You could also use datasheet view, but that's not as nice to look at.

Hope that helps.
John

 

[Pat Hartman]

if u join two tables to make a view and then specify it as a record source for the form then u can only update the records as long as u r only updating one table.U cant update the both tables at the same time.

Sorry to disagree but you can add a row to the 1-side table as well as adding a row to the many-side table in the same query. The key is that you need to include the join field from BOTH tables in the select clause:

SELECT tblIncomeType.IncomeType, tblIncomeType.Description, tblIncome.PropertyID, tblIncome.IncomeType, tblIncome.IncomeAmt
FROM tblIncomeType INNER JOIN tblIncome ON tblIncomeType.IncomeType = tblIncome.IncomeType;

When you open a query such as this and start entering data, you enter the new type, a description, a propertyID, and then the many-side type field is AUTOMATICALLY populated. You then enter the rest of the many-side fields.

If you only want to update both records, you can omit the join key from the 1-side table.