Inserting data into tables using vba

[Kate Chesser]

I am new to Access and VBA so apologies for the basic question! I am trying
to copy a record from the order table to the transaction table based on
whether the order.date_rcvd value is not null. I am trying to do this in
the OnExit event on the order.date_rcvd field. Please can anyone help?

 

[Allen Browne]

I'm not clear about the purpose of this, Kate.

If you use the Exit event of a control, you want Access will create a copy
of the record in another table? Every time the user visits the field?
Regardless if whether they have already visited? New and existing records?
Multiple times in one record if they click around with the mouse? Even if
they didn't change anything?

 

[Kate Chesser]

Hi Allen,

It sounds like I probably don't want to put the code on this event then.
Here's a bit more info to clarify.

Basically, I've got an order table that has the fields order_no,order_a_id,
order_date, order_qty, order_rcvd. Once the order has been received ie a
date has been entered into the order_rcvd field, I want the record to be
inserted into the transaction table, trans_a_id, trans_order_no, trans_date,
trans_qty.

I had envisaged a check would be done on the order table to see if the
date_rcvd is not null, and if it isn't null to then check the transaction
table for the trans_order_no. If the order_no does not exist in the
transaction table then insert the record.

Does this make more sense? Maybe you can recommend a different event to put
the code on? As mentioned before I don't really know Access so am open to as
much advice as possible!

Thank you.

 

[Allen Browne]

You could use the AfterUpdate event of the form to write the record to the
transaction table. However, you don't know if the order_date changed from
null to not null, as the OldValue is not available in this event. You would
therefore need to remember the OldValue from the form's BeforeUpdate event,
in a module-level variable, set the variable in Form_BeforeUpdate based on
the OldValue of the control, and examine it and the new value in
Form_AfterUpdate.

But that still doesn't cope with the case where someone changes the
order_date back to null after the record has been written to the transaction
table. You are still going to get a duplication when the order_date is later
filled in again.

The core issue here seems to be the dependency of the transaction table on
the order table. Is there another way to redesign the tables so that this
dependency disappears?

If you are struggling with whole question of assigning stock to orders,
partial orders, back orders, and fulfilling orders, you might consider
buying John Viescas' book, "Building MS Access Applications (Microsoft
Press, 2005). The CD that comes with the book has a sample application that
does all this stuff, and the book explains how it works.

 

[Kate Chesser]

Thank you for this advice..I'll have a look at the book you recommended.