acNewRec

Disgruntled · March 26th, 2010, 06:04 PM

I have a form that will query a table called tbl_Schedules.

Code:

tbl_Schedules defined as:
   field1
   field2
   field3

On this form is a subform that has as its RecordSource a query of tbl_Schedules.

Code:

SELECT field2, field3 FROM tbl_Schedules WHERE field1 = Day.value;

The subform only displays a subset of the fields from tbl_Schedules. On the subform, i have a command button that will add a new record into tbl_Schedules.

This action is handled by

Code:

' can i set fields before issuing new record??
DoCmd.GoToRecord , , acNewRec

HOWEVER, i need to update a field that is not explicitly shown on the subform, namely field1.

How can I do this?

SerranoG · April 9th, 2010, 08:17 AM

The subform should not contain a query of the same table as the main form. There should be two tables with primary and foreign keys (PK and FK) linking the two in a (usually) one-to-many relationship. Then as you scroll through the main form, the subform (linked via parent/child property) will change based on the keys. You don't change the child Field1 in the subform ever. You change parent Field1 in the main form and that changes the child Field1 in the subform.

So you should have tbl_Schedules with Field1 (PK), Field2, Field3; and you should have tbl_Other with Field4 (PK), Field1 (FK), Field5, Field6, etc.

Disgruntled · May 4th, 2010, 06:06 PM

Thanks SerranoG, i appreciate the reply and suggestion.

I believe I have solved what I needed. I made hidden fields on the subform and passed the results of a query (that includes the fields i need) in the RecordSource of the subform.

So, although the user cannot see those fields/values, the values and more importantly the fields that need to be updated are readily available.

Best regards,
D