How to auto create records on one side of 1 to 1 relationship?

[Mark]

Hi There,

I have a one to one relationship with a linked read-only table in our
commercial accounts package software, so that I can store more information
for each record in that table.

Is there a way to automatically create a record with a default value in the
access side of the one to one relationship?

Specifically, for each sales invoice in our accounts package, I want to add
a record in the access database side of the relationship.

I'd like a field in access called 'expected payment date', with a default
value of '6 weeks from accounts package invoice date' (InvoiceDate + 42)

Can the records be created automatically in Access as new records are found
in the accounts package? Or only with code on a button/event?

Thanks,

Mark

 

[susiedba]

yeah.. if you used Access Data Projects; you would just use a TRIGGER
to automatically create this record.

MDB is for babies

-Susie, DBA

 

[John Vinson]

I'd like a field in access called 'expected payment date', with a default
value of '6 weeks from accounts package invoice date' (InvoiceDate + 42)

Can the records be created automatically in Access as new records are found
in the accounts package? Or only with code on a button/event?

No, they can't. You'll need some sort of event to populate this table.

BUT... if the expected payment date is always six weeks after the
invoice date, and you don't need to be able to edit it, why have a
table at all? Just use a Query with a calculated field

Payment Expected: DateAdd("ww", 6, [InvoiceDate])

and calculate it dynamically.

John W. Vinson[MVP]

 

[Joseph Meehan]

yeah.. if you used Access Data Projects; you would just use a TRIGGER
to automatically create this record.

MDB is for babies

-Susie, DBA

Aaron, why do you keep changing your name? Is it cold and damp under
that bridge?

 

[Mark]

HI John,

Thanks for the reply.

This date could change as our customers delay payment for whatever reason,
so a calculated field is not suitable.

Would the best way to do this be a command button to trigger an append
query?

I'm a bit of a novice, but I expect there's some way to use an append query
to create records to the access table (using the expected pay date
calculation) where a record doesn't exist for entries in the read-only
accounts table?

Any advice appreciated.

Thanks,

Mark

I'd like a field in access called 'expected payment date', with a default
value of '6 weeks from accounts package invoice date' (InvoiceDate + 42)

Can the records be created automatically in Access as new records are
found
in the accounts package? Or only with code on a button/event?

No, they can't. You'll need some sort of event to populate this table.

BUT... if the expected payment date is always six weeks after the
invoice date, and you don't need to be able to edit it, why have a
table at all? Just use a Query with a calculated field

Payment Expected: DateAdd("ww", 6, [InvoiceDate])

and calculate it dynamically.

John W. Vinson[MVP]

 

[John Vinson]

I'm a bit of a novice, but I expect there's some way to use an append query
to create records to the access table (using the expected pay date
calculation) where a record doesn't exist for entries in the read-only
accounts table?

Yes. You can create an Append query joining the linked table to the
Access table using a "unmatched" query - use a Left Outer Join and a
criterion of IS NULL on the Access table, to select only those records
which exist only in the linked table.

John W. Vinson[MVP]

 

[Mark]

Thanks very much. This worked great.

Mark

Yes. You can create an Append query joining the linked table to the
Access table using a "unmatched" query - use a Left Outer Join and a
criterion of IS NULL on the Access table, to select only those records
which exist only in the linked table.

John W. Vinson[MVP]