How to make records updateable in a form based on two tables

[Crusado]

Hi

I'm using Forms in my DB to update data held in tables. I've successfully built simple forms that just retrieve data from one table and allow you to change the values. I now need to base the form on 2 tables (one of the tables only contributes one field to the form, which I want to use to filter for the records that I need). The problem is that it wont now allow any of the values retrieved by the form to be changed. Can anyone tell me if forms are automatically not updateable if they get data from more thsn one source? How do I get around this?

I'm using Access 2003.
All field properties are set to "Enabled - Yes" and "Locked - No" in the properties.
The two tables do have the relationship defined in the DB.

Thx in advance

 

[neileg]

Base your form on a query that links the two tables.

 

[KeithG]

I think your problem is joining the two tables together because it is creating a read only result set. Does a 1 to 1 or 1 to Many relationship exist between the two tables? Or are you just joining them in this query? Try and go into your query prop[erties and change the Recordset property to Dynaset (Inconsistent Updates). Or you could use the Dlookup function to retrieve the field from the other table.

 

[Crusado]

neileg: I've tried basing the form on a query linking the two tables, but it will still not allow records to be changed.

KeithG: Its a 1 to many relationship. I've now changed the Dynaset property, but it still doesn't work.

The DLookup answer sounds like the one, but I cant get this to work either lol

Table structures:

Tbl 1: REF_PRODUCT_GROUPS
Fields: PRODUCT_KEY; PRODUCT_GROUP

Tbl 2: REF_UPLIFT_GROUPS
Fields: PRODUCT_KEY; STORE_KEY; UPLIFT_GROUP

Form: ADJUST_UPLIFT_PRODUCT_GROUPS

The two "PRODUCT_KEY" fields are equivalent and joined in Relationships.
I've got a form based on Table 2 and want to look up the PRODUCT_GROUP from Table 1 and use it to filter the results. I've tried following the instructions from Microsoft Help and come up with the following as the ControlSource property, but it doesn't like the expression - have I done something stupid?

= DLookup ( [PRODUCT_GROUP] , [REF_PRODUCT_GROUPS], [PRODUCT_KEY] = & Forms![ADJUST_UPLIFT_PRODUCT_GROUPS]![PRODUCT_KEY])

Thanks for the help so far

 

[neileg]

I would:

Use the table REF_PRODUCT_GROUPS to populate a combo box on the form header.
Create a query based on the table REF_UPLIFT_GROUPS that uses the PRODUCT _KEY from the combo box as a criterion.
Populate the detail section of the form with the results returned by the query.
In the After Update event of the combo box, refresh the query to show the records that match the selection in the combo.

 

[modest]

Use a subform based on the second table. Link the subform's child field with the main form's parent field.