Create Record in related table when field equals a certain value

[Jasuan]

Hey all,

I am new to access, but pretty good at excel.

I have two tables. Table X is the table users primarily use to input case data. Table Y is the table users input case details should it to a proposed status. The tables are connected on a one-to-one relationship (ID). I would like a record to be created in table Y when a certain field is updated to equal a certain value.

Either:
A) The record is created when field "Status" is changed to "Proposed." Problem I foresee with this is if someone changes it to proposed, changes it, then goes back to proposed, it will create multiple records in Table Y for only one record in Table X; or

B) The record is created when field "CaseNumber" is anything but blank ("").

Thanks in advance for your help.

 

[ranman256]

in your main form, you could put a subform of the other table Y.
in the subform property
set the LINK MASTER FIELD = ID
set the LINK CHILD FIELD = ID
Now users can enter data into Y table linked to X.

now set the property of this form to VISIBLE = NO
set these 2 events:

form.onCurrent
subform.visible = cboStatus = "Proposed"

and
cboStatus_AFTERUPDATE
subform.visible = cboStatus = "Proposed"

the child record will only show if the status is proposed, or changed to proposed.