Insert new record to other table while clicking next record

biant123

Hi,

I have problem inserting new record in a table B while clicking the next record of table A. Below is what I have in a form:

1) I have a main form showing a list of Customer (using table Customer)
2) This form contains a subform (using new table
3) When everytime I click on different record from the main form list, i need to insert new record to the table B and update the subform view automatically.
4) I have tried to write the insert new record codes in the main form - OnAfterGetCurrRecord() trigger, but it returns me such error: "You cannot make any changes in the database until a transaction has been started". I have tried to write these codes in OnTimer() too, but it's not always working and not stable.

Do you have any suggestion?

Thank you & regards,
Bianca

bob_up

Do you need to insert in table B every time you leave a record in table A ? Could it be just when the record in table A is modified ?

biant123

Hi bob_up, thanks for your reply. It only needs to insert new record to table B when selecting a Customer (table A = Customer table, the main form is using Customer table). When selecting a customer, the program needs to check if any existing data in table C that is related to the Customer, then insert these bunch of related existing data to table B. Forgot to mention, the main form is not editable.

Currently the codes are still allocated at onTimer trigger, it doesn't insert a complete set of data to the subform (table sometimes. Is there any method except using onTimer trigger??

garak

Whats the reason why do you need this?

biant123

A set of data is keyed in table C for different customer, so my client wants the program to auto add this set of data to the subform when the user select different customer on different date, so they do not need to repeat keying in the data (from table C) to this form. And they want to see all of them in one form and do not want to select any function button. They just want to select any customer and different date and the data is auto inserted immediately.

garak

if the datas are stored in a separate table (C) with the identify of the customer, why do you wish to store these datas also ion a new table?What is, if the Records in Table C are changed? Should the copied recs also changed?
Why do you not create a list form on table C and show this form as subform (with the needed filters) on you customized "customer form"?

biant123

Thank you for all the replies..

On the subform, it contains Customer, Date, and table C data.

Let's put table C as a set of data like a piece of information about the customer. So in the main form, whenever i click on a Customer on certain date, the program should get any related data from table C and insert them to the subform. if user select different date on the same customer, or different customer on the same date, the program should check if the subform already have existing data, if it doesn't, it will insert related data from table C to the subform. Summary:

- Main form: Customer list and a Date (date variable, key in by user)
- Subform : Customer No., Date, and table C data.
- table C: a list of data that has a relationship with Customer.
- Scenario A: User select customer A on 01/03/2009 -> insert table C data of customer A to subform
- Scenario B: User select customer B on 01/03/2009 -> insert table C data of customer B to subform
- Scenario C: User select customer B on 02/03/2009 -> insert table C data of customer B to subform
- So same set of table C data might be inserted again on different Date in the subform.
- Question: except using onTimer() trigger to allocate the code of inserting the table C data to subform, is there any better solution? anyone?